Tech Tip: Moving tables and indexes created in the wrong tablespace at installation time

Document created by AdrianJohnsonUK Employee on Sep 1, 2017
Version 1Show Document
  • View in full screen mode

For a typical CCC / Data Manager installation, 4 tablespaces CCCUSER_TBL, CCCUSER_IDX, CCCUSER_STG_TBL, CCCUSER_STG_IDX and an Oracle user CCCUSER with default tablespace CCCUSER_TBL - Script to create CCC tablespaces, CCC user name and Grant Privileges provides an example of a script that will create the necessary tablespaces and user.

 

On occasion, it may be noted that certain tables and indexes are not created in the expected tablespace - in fact, they are created in the user's default tablespace (usually, CCCUSER_TBL).  Often this goes unnoticed and performance / scalability is not an issue.  However, in some cases, it can cause tablespaces to fill up unexpectedly which results in system stability issues.

 

In a recent case, the default tablespace for CCCUSER was actually the USERS tablespace, which had a fixed size of 5GB.  After several months of successful operation, data load errors started to occur - looking in the error logs implied there was lack of available space, even though none of the 4 main tablespaces were anywhere near full.  Only after further investigation did the underlying issue become apparent.

 

(It appears that the root cause is a number of statements in the install scripts to create the schema objects which do not use the 'TABLESPACE ${tbl_space}' clause.)

 

Fortunately, this is usually easy to solve - either proactively immediately after install, or retrospectively without any further loss of data - by employing Oracle's 'alter table move' and 'alter index rebuild' statements.

 

Suggested approach - requires access to Oracle (via SQL Developer, sqlplus or similar tool):

1. Determine all affected tables and indexes by reviewing the output of the following two commands:

select 
  table_name
, tablespace_name
from user_tables
order by tablespace_name, table_name
;
select 
  table_name
, index_name
, index_type
, tablespace_name
, status
, global_stats
from user_indexes
order by tablespace_name, table_name, index_name
;

2. Move affected tables:

a. for each table above:

alter table <Table_Name> move tablespace <Correct_Tablespace>;

b. find indexes of affected table:

select 
  table_name
, index_name
, tablespace_name
from user_indexes
where table_name = '<Table_Name>'
order by tablespace_name, table_name, index_name
;

c. for each index of the affected table:

alter index <Index_Name> rebuild tablespace <Correct_Tablespace> PARALLEL NOLOGGING;

4. Rebuild any other affected indexes:

alter index <Index_Name> rebuild tablespace <Correct_Tablespace> PARALLEL NOLOGGING;

In a freshly installed CCC / Data Manager instance, this procedure should take a matter of moments.

 

For an install where this issue has only become apparent after some time, it may take tens of minutes, and should be done at a quiet period with no Stage / Migrate / Rollup / Pre-Calc activity.

Attachments

    Outcomes