Tablespaces & Datafiles

Tablespaces & Their Datafiles information..

No. of Datafiles in each Tablespaces.

SELECT  Tablespace_name,COUNT(file_name) no_OF_DATAFILES
FROM dba_data_files
GROUP BY TABLESPACE_NAME
ORDER BY TABLESPACE_NAME;



Tablepsaces & Their datafiles info.

SELECT  Tablespace_name,file_name, ROUND(bytes/1024000) USED_MB
FROM dba_data_files
ORDER BY 1;

Querry to f ind  Tablespaces , datafiles, auto extensiblity, usage statitics

SELECT    tablespace_name, file_name file_id, BYTES / 1024 / 1024 AS mb,
autoextensible, increment_by * 8192 / 1024 / 1042 AS next_mb,
maxbytes / 1024 / 1024 AS max_mb 
FROM    dba_data_files
ORDER BY tablespace_name, file_id;