Query to display tablespace (PSAPBTABD) table

[Former Member]

Experts,

We need to take the list of tables only in "PSAPBTABD" tablespace with following requirements.

NUM_ROWS,

AVG_ROW_LEN ROWLEN,

BLOCKS,

We have query but it downloads common for tables.

SELECT * FROM

( SELECT

SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,

NUM_ROWS,

AVG_ROW_LEN ROWLEN,

BLOCKS,

ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0)

NET_MB,

ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *

(1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,

ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE/ 100) -

(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"

FROM DBA_TABLES

WHERE

NUM_ROWS IS NOT NULL AND

PARTITIONED = 'NO' AND

(IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)

ORDER BY 7 DESC)

WHERE ROWNUM <= 10;

Please let us know how to query a particular tablespace table only

Thanks

Malai

[fidel_vales · Accepted solution]

Hi,

I do not know where you copy your query, but you may try to understand it and then you would find your answer.

The documentation is your friend

 
SELECT * FROM
( SELECT
"BBABLABLA"
FROM 
                                      DBA_TABLES
WHERE
NUM_ROWS IS NOT NULL AND
PARTITIONED = 'NO' AND
(IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)
ORDER BY 7 DESC)
WHERE ROWNUM <= 10;

you are selecting from DBA_TABLES

what are the columns of this view? clue, look at the documentation

..

..

..

..

..

There is a column called "TABLESPACE_NAME" ....

Then you can put a condition very easily to show ONLY one tablespace

[Former Member · Accepted solution]

Thanks Vales..

Malai

[Vivek_Hegde]

Hi,

Lets assume you want to display the contents from XYZ table within tablespace PSAPBTABD then you can query PSAPBTABD.XYZ

select * from tablespace.table ................................................

Regards

Vivek