Verifying the tablespace storage

Available with Production Mapping 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.

sqlplus prodlib/prodlib@prodlibdb

List tables:

SELECT TABLE_NAME, TABLESPACE_NAME, STATUS FROM USER_TABLES ORDER BY 1;

List indexes:

SELECT INDEX_NAME, TABLE_NAME, TABLESPACE_NAME, STATUS FROM USER_INDEXES ORDER BY 2,1;

List LOBS:

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.