Ensure that the workspace was created under the correct tablespaces for proper data storage and the reduction of disk contention. Using the DBTUNE configuration you modified previously, you can run the following script examples in Oracle SQL*Plus or Oracle SQL Developer.
Connect to the BISDB database.
sqlplus bis/bisadmin@bisdb
List the tables and the corresponding tablespace.
SELECT TABLE_NAME, TABLESPACE_NAME, STATUS FROM USER_TABLES ORDER BY 1;
List the indexes by table and tablespace.
SELECT INDEX_NAME, TABLE_NAME, TABLESPACE_NAME, STATUS FROM USER_INDEXES ORDER BY 2,1;
List large objects (LOB) by table.
SELECT * FROM USER_LOBS WHERE SEGMENT_NAME LIKE 'SYS_LOB%' ORDER BY TABLE_NAME;
If any tables or indexes are stored in the wrong tablespace, then ALTER TABLE and ALTER INDEX can be used to change the tablespace (see the following SQL syntax).
ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name>;
ALTER INDEX <index_name> REBUILD TABLESPACE <tablespace_name>;
If you are moving large objects (LOB), read Oracle MetaLink Doc ID: 130814.1, How to move LOB Data to Another Tablespace.
Feedback on this topic?