Oracle Environment. Table size is 500Gb. Free space left on hard drive - 300Gb. TEMP Tablespace is currently 20GB Task: do the DBMS_Redefinition for the table (partition by month) and optimize performance.
PROBLEM 1 - INDEXES I decided to try out the process on a smaller table first. I took a similar table by structure around 50 GB size. The redefinition ran okay, but I exceeded the temp tablespace when creating indexes. Tried several options: Regular Indexes Creation, Global Partitioned Index Creation, but no luck.
Ran the following to identify the TEMP tablespace required. Doesn't look like it exceeds the allocated space.
declare u_bytes number; a_bytes number; begin dbms_space.create_index_cost ( 'create index days_ind on Tab (PAr1, PAr2)', used_bytes => u_bytes, alloc_bytes => a_bytes ); dbms_output.put_line ('Used MBytes = '|| u_bytes/1024/1024); dbms_output.put_line ('Allocated MBytes = '|| a_bytes/1024/1024); end;
Is there a way to sacrifice performance, but minimize the temp tablespace used? I realize that indexing of a 500 GB will require over 200GB.
PROBLEM 2 - SPACE REQUIRED I realize that DBMS_REDEFINITION requires free space of the same size as the table itself. Therefore it looks logical enough to do ONE partition at a time.
QUESTIONS: 1. Any useful queries to monitor the resources? (I've already seen this) 2. Suggestions about the right format of INDEXES, optimizing INDEX CREATION to fit into a smaller TEMP TABLESPACE? 3. Would the DBMS_REDEFINITION partition by partition (around 60 partitions) work the best in this case? Any suggestions on performing the procedure?