Verifying the tablespace storage for the Reviewer workspace in Oracle

Available with Data Reviewer license.

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.

Note:

Copying and pasting the examples may cause syntax errors.

Connect to the REVDB database.

sqlplus rev/rev@revdb

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, 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.