Oracle 11g how to evaluate the required TEMP tablespace?

Question

We load the initial bulk load of some tables (both source and target are Oracle 11g). The process is as follows: 1. crop, 2. drop indices (PK and unique index), 3. bulk insert, 4. create indexes (again PK and unique index). Now I got the following error:

alter table TARGET_SCHEMA.MYBIGTABLE add constraint PK_MYBIGTABLE primary key (MYBIGTABLE_PK) ORA-01652: unable to extend temp segment by 128 in tablespace TEMP 

Therefore, it is obvious that the TEMP table space is small for creating PK (the FYI table has 6 columns and about 2.2 billion records). So I did this:

 explain plan for select line_1,line_2,line_3,line_4,line_5,line_6,count(*) as cnt from SOURCE_SCHEMA.MYBIGTABLE group by line_1,line_2,line_3,line_4,line_5,line_6; select * from table( dbms_xplan.display ); /* ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2274M| 63G| | 16M (2)| 00:05:06 | | 1 | HASH GROUP BY | | 2274M| 63G| 102G| 16M (2)| 00:05:06 | | 2 | TABLE ACCESS FULL| MYBIGTABLE | 2274M| 63G| | 744K (7)| 00:00:14 | ----------------------------------------------------------------------------------------------- */ 

How to determine how much TEMP table space is needed to create a PC (in my case - 102 GB)? Or will you make an assessment differently?

Optional: PC exists only in the target system. But fair point, so I run your request on the target PC:

 explain plan for select MYBIGTABLE_PK from TARGET_SCHEMA.MYBIGTABLE group by MYBIGTABLE_PK ; ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (34)| 00:00:01 | | 1 | HASH GROUP BY | | 1 | 13 | 3 (34)| 00:00:01 | | 2 | TABLE ACCESS FULL| MYBIGTABLE | 1 | 13 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- 

So how do I read this now?

Answer 1

That's a good question.

First, if you create the next primary key

 alter table TARGET_SCHEMA.MYBIGTABLE add constraint PK_MYBIGTABLE primary key (MYBIGTABLE_PK) 

then you should request

 explain plan for select PK_MYBIGTABLE from SOURCE_SCHEMA.MYBIGTABLE group by PK_MYBIGTABLE 

To get an estimate (make sure you collect exec dbms_stats.gather_table_stats('SOURCE_SCHEMA','MYBIGTABLE') statistics exec dbms_stats.gather_table_stats('SOURCE_SCHEMA','MYBIGTABLE') .

Secondly, you can query V$TEMPSEG_USAGE to find out how many temp blocks were used up before you selected it, and v$session_longops to see how many completed processes you completed.

Oracle docs suggests creating a dedicated temporary table space so that the process does not violate any other operations.

Please post the change if you find a more accurate solution.