Please help, I inherited this code but have to optimize it as it is failing with error message "ORA-01652: unable to extend temp segment by 512 in tablespace TEMP". The DBA will not extend the tablespace as he suggested that the query can be optimize to get rid of this error message.
This is the query below.BER () OVER (PARTITION BY
SELECT TRL_STUC
, TRL_APFS
, TRL_CAPS
, TRL_AYRC
, TRL_UCRS
, TRL_MCRC
, TRL_STAC
, TRL_CHGD
, TRL_UNRC
, TRL_UNDC
, TRL_IDRC
FROM
(
SELECT TRL_STUC
, TRL_APFS
, TRL_CAPS
, TRL_AYRC
, TRL_UCRS
, TRL_MCRC
, TRL_STAC
, TRL_CHGD
, TRL_UNRC
, TRL_UNDC
, TRL_IDRC
, ROW_NUMBER () OVER (PARTITION BY TRL_STUC, TRL_APFS, TRL_CAPS ORDER BY TRL_CHGD ASC) AS RN_TRL_CHGD
, ROW_NUMBER () OVER (PARTITION BY TRL_STUC, TRL_APFS, TRL_CAPS, TRL_CHGD ORDER BY TRL_CHGT ASC) AS RN_TRL_CHGT
, ROW_NUMBER () OVER (PARTITION BY TRL_STUC, TRL_APFS, TRL_CAPS, TRL_CHGD, TRL_CHGT ORDER BY TRL_CHTI ASC) AS RN_TRL_CHTI
FROM TRS.TRS_MAS_TRL_ARCHIVE
) S1
WHERE S1.RN_TRL_CHGD = 1
AND S1.RN_TRL_CHGT = 1
AND S1.RN_TRL_CHTI = 1 )
This is the error message
ORA-01652: unable to extend temp segment by 512 in tablespace TEMP
01652. 00000 - "unable to extend temp segment by %s in tablespace %s"
*Cause: Failed to allocate an extent of the required number of blocks for
a temporary segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.
This is the table definition
CREATE TABLE "TRS_MAS_TRL_ARCHIVE"
("TRL_STUC" VARCHAR2(12 BYTE),
"TRL_APFS" VARCHAR2(2 BYTE),
"TRL_CAPS" VARCHAR2(2 BYTE),
"TRL_CHGD" DATE,
"TRL_CHGT" DATE,
"TRL_STAC" VARCHAR2(6 BYTE),
"TRL_IDRC" VARCHAR2(6 BYTE),
"TRL_UNDC" VARCHAR2(1 BYTE),
"TRL_UNRC" VARCHAR2(1 BYTE),
"TRL_UTTC" VARCHAR2(2 BYTE),
"TRL_UNRC" VARCHAR2(1 BYTE),
"TRL_USDO" VARCHAR2(1 BYTE),
"TRL_SOFR" VARCHAR2(1 BYTE),
"TRL_MCRC" VARCHAR2(12 BYTE),
"TRL_UCRS" VARCHAR2(8 BYTE),
"TRL_AYRC" VARCHAR2(12 BYTE),
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 10240 NEXT 955613184 MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MAIN" ;
CREATE UNIQUE INDEX "TRS"."TRS_MAS_TRL_ARCHIVE_PK" ON "TRS_MAS_TRL_ARCHIVE" ("TRL_STUC", "TRL_APFS", "TRL_CAPS", "TRL_CHGD", "TRL_CHGT", "TRL_CHTI")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10240 NEXT 637075456 MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MAIN" ;
Your help will be appreciated. ROW_NUMBER () OVER (PARTITION BY
The Analytics "ROW_NUMBER () OVER (PARTITION ......" is the cause of this problem because when I comment this out, I don't get the errors. If there is another way I can write this, I will be grateful.