How Can I Tell What Tablespace a Particular Oracle Table or Index Resides In?
- Article Type: General
- Product: Aleph
- Product Version: 18.01
Description:
How can I tell what tablespace a particular Oracle table or index resides in?
Resolution:
If the table or index is in the library's $data_root/file_list, that should show you the tablespace. If it is not, you should be able to see it in the util a/17/10/2 Merged file list (which gets its values from the file_list.xxx template in the $aleph_root/tab/ directory -- in addition to the $data_root/file_list).
Also, you can do the following SQL. (Note: you can be connected to any library in doing this.)
To get the tablespace for a particular Oracle table:
SQL> select tablespace_name from all_tables where owner = 'USR00' and table_name = 'Z303';
To get the tablespaces for all Oracle tables in a particular library:
SQL> select table_name, tablespace_name from all_tables where owner = 'USR00';
To get the tablespace for a particular Oracle index:
SQL> select tablespace_name from all_indexes where owner = 'USR00' and index_name = 'Z303_ID';
To get the tablespaces for all Oracle indexes in a particular library:
SQL> select index_name, tablespace_name from all_indexes where owner = 'USR00';
- Article last edited: 10/8/2013