DB version : 19c
OS: RHEL 8.10
This is a standalone DB (not RAC)
I am trying to create a 3-column index on table which is 9.6 TB in size.
I used 6 DOP. As shown below, it is a function-based index.
CREATE INDEX SCH_ORDERS.PKT_DETAIL_IF2 ON SCH_ORDERS.PKT_DETAIL(PKT_MLT_ID, NVL(PKT_CLST_Z,9), PKT_QUCK_CODE)
PARALLEL 6
ONLINE
tablespace PKT_INDEX_LS invisible;
The database (PDB) uses TEMP tablespace which is its default, and it already has 320 GB of tempfiles.
But, the CREATE INDEX statement hangs with the following in alert log
2025-07-01T20:41:15.653315+02:00
PDB_ORD_PROD(9):ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [PDB_ORD_PROD]
PDB_ORD_PROD(9):statement in resumable session 'User SYS(0), Session 771, Instance 1' was suspended due to
I left the CREATE INDEX statement running at night and in the morning I realized that it was hanging due to the lack of temporary tablespace.
I killed all sessions running the CREATE INDEX statement. Multiple session because of DOP 6.
While the CREATE INDEX statement was running, it didn't even start using any space from the index tablespace as the sorting for index build was being done in TEMP tablespace and it seems to have failed in the sorting stage itself.
Execution plan of the CREATE INDEX command shows that the index size will be roughly 119 GB. I have assigned 140 GB on the index tablespace just to be on the safe side.
SQL>
explain plan for
2 CREATE INDEX SCH_ORDERS.PKT_DETAIL_IF2 ON SCH_ORDERS.PKT_DETAIL(PKT_MLT_ID, NVL(PKT_CLST_Z,9), PKT_QUCK_CODE) PARALLEL 6 ONLINE tablespace PKT_INDEX_LS invisible;
Explained.
set lines 2100 pages 3000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2476784548
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 10G| 119G| 64M (1)| 00:42:17 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 10G| 119G| | | Q1,01 | P->S | QC (ORDER) |
| 3 | INDEX BUILD NON UNIQUE| PKT_DETAIL_IF2 | | | | | Q1,01 | PCWP | |
| 4 | SORT CREATE INDEX | | 10G| 119G| | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 10G| 119G| 64M (1)| 00:42:17 | Q1,01 | PCWP | |
| 6 | PX SEND RANGE | :TQ10000 | 10G| 119G| 64M (1)| 00:42:17 | Q1,00 | P->P | RANGE |
| 7 | PX BLOCK ITERATOR | | 10G| 119G| 64M (1)| 00:42:17 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| PKT_DETAIL | 10G| 119G| 64M (1)| 00:42:17 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 6 because of table property
19 rows selected.
In the morning, when the sessions were hanging due to lack of temporary tablespace, I see each of the 6 sessions consuming roughly above 50 GB of temp tablespace ! I don't know why each of them have to consume 50+ GB. Maybe I should reduce the DOP too. Like 3 instead of 6.
TABLESPACE SIZE_MB INSTANCE SID_SERIAL USERNAME PROGRAM STATUS SQL_ID
-------------------- ------------------------- ------------------------- -------------------- ------------------------ --------------------------------------------- -------- -------------
TEMP 53952 1 4542,43269 SYS oracle@c23lxkn.gxc.dm (P005) ACTIVE 20h50r6dmdt00
TEMP 54352 1 13,49410 SYS oracle@c23lxkn.gxc.dm (P000) ACTIVE 20h50r6dmdt00
TEMP 54648 1 2272,3988 SYS oracle@c23lxkn.gxc.dm (P002) ACTIVE 20h50r6dmdt00
TEMP 54860 1 3023,29883 SYS oracle@c23lxkn.gxc.dm (P003) ACTIVE 20h50r6dmdt00
TEMP 54924 1 3785,54889 SYS oracle@c23lxkn.gxc.dm (P004) ACTIVE 20h50r6dmdt00
TEMP 54924 1 771,19443 SYS oracle@c23lxkn.gxc.dm (P001) ACTIVE 20h50r6dmdt00
My question:
For an index which will be roughly 120 GB in size, any idea how much temp tablespace is required ?