Determining how many datafiles make up a tablespace

posted Sep 10, 2010, 9:14 AM by Sachchida Ojha   [ updated Sep 10, 2010, 10:33 AM ]
To determine how many and which datafiles make up a tablespace, you can use the following query: 
  SELECT  file_name , tablespace_name 
  FROM dba_data_files 
  WHEREtablespace_name ='<name of tablespace>';
All datafiles in all tablespaces.

SQL>col file_name format a50
SQL>col tablespace_name format a35
SQL>set pagesize 200
SQL>set linesize 180
SQL> SELECT    file_name , tablespace_name  FROM   dba_data_files order by file_name,tablespace_name

/
orphaned files  not used by any tablespace.
$find /u??/proddb -name \*.dbf -ls | grep -v "Sep  10"

 SELECT   tablespace_name,file_name,bytes/(1024*1024*1024) GB FROM  dba_data_files where tablespace_name='name of tablespace' order by file_name;

Comments