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.