Verifying the tablespace storage for the Workflow Manager workspace in Oracle

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.

Remarque :

Copying and pasting the examples may cause syntax errors.

Connect to the WMXDB database.

sqlplus wmx/wmx@wmxdb

List the tables and 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.