Thursday, March 22, 2012

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

SELECT T.TABLE_NAME,T.TABLESPACE_NAME,DF.FILE_NAME FROM DBA_TABLES T, DBA_DATA_FILES DF

 

SELECT T.TABLE_NAME,T.TABLESPACE_NAME,DF.FILE_NAME FROM DBA_TABLES T, DBA_DATA_FILES DF WHERE T.TABLESPACE_NAME = DF.TABLESPACE_NAME AND T.OWNER LIKE 'USER';