0

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?

  • (Have you considered the cost of 1T hard drive versus the time you're spending trying to shoe this in?) What are your uptime/availability requirements? – Mat Sep 29 '14 at 19:51
  • I'm a developer (beginner though). The 'DBA' section is a subcontractor has it's own policy. Hard drive extension is expected in several months, while table redefinition - in couple weeks. – Andrew Sep 29 '14 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.