Wednesday, June 25, 2008

List tables and their corresponding tablespace name and data file [Oracle 9i]

Following is a way to list a table name and its corresponding tablespace name and data file.

SQL> select t.table_name,t.tablespace_name,df.file_name from dba_tables t, dba_data_files df

2 where t.tablespace_name = df.tablespace_name;

Further filters can be added as per requirement.

E.g.

To list the tables filtered by the owner of the table we write.

SQL> select t.table_name,t.tablespace_name,df.file_name from dba_tables t, dba_data_files df where

2 t.tablespace_name = df.tablespace_name and t.owner like 'TEMP1';

Additionally, to check the status of the tablespace we write,

SQL> select tablespace_name,status from dba_tablespaces;

No comments: