Menu
How to find out which 'tablespace' a particular 'index' belongs to. (oracle) (need to use it via jdbc)
691
The index segments have to be stored in a tablespace; it isn't that strange to want to specify which one, surely? Its a basic part of the way Oracle structures the DB.
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';
Table and tablespace location. Finding out who owns a table and what tablespace it is in is a pretty common need of the DBA. In this query, we use the dba_tables view to find the owner and tablespace name of the EMP table.
You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX . The following statement creates an index named emp_ename for the ename column of the emp table: CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75);
The information is in the ALL_INDEXES (or USER_INDEXES) view:
select tablespace_name
from all_indexes
where owner = 'MYSCHEMA'
and index_name = 'MYINDEX';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
