Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid TEMP tablespace to get full
How to avoid TEMP tablespace to get full [message #185260] Mon, 31 July 2006 16:03 Go to next message
Jaime Stuardo
Messages: 57
Registered: March 2004
Member
Hi all..

I have a PL/SQL query that implements an INSERT SELECT statement. The destination table is a table that was created using this script :

CREATE TABLE TEMP_VIVAMEJOR_COLECTIVOS
(
................(several fields)
)
TABLESPACE ACSEL_DAT
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 1040K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 1
FREELISTS 1
FREELIST GROUPS 1
)
NOCACHE
NOPARALLEL;


CREATE UNIQUE INDEX PK_VIVAMEJOR_COLECTIVOS ON TEMP_VIVAMEJOR_COLECTIVOS
(CON_EST, CON_FECING, CON_RUT, EMP_RUT, POL_NUM,
PRD_RAMO)
TABLESPACE ACSEL_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 1
FREELISTS 1
FREELIST GROUPS 1
)
NOPARALLEL;


ALTER TABLE TEMP_VIVAMEJOR_COLECTIVOS ADD (
CONSTRAINT PK_VIVAMEJOR_COLECTIVOS PRIMARY KEY (CON_EST, CON_FECING, CON_RUT, EMP_RUT, POL_NUM, PRD_RAMO)
USING INDEX
TABLESPACE ACSEL_DAT
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 1
FREELISTS 1
FREELIST GROUPS 1
));

When I run the query, TEMP tablespace get full, terminating the query abnormally with the error that cannot extend TEMP tablespace by 128 [the actual error is -> ORA-01652: unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent for temp segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.] (TEMP tablespace is 4 GB long).

How can I avoid using of TEMP tablespace or even, flushing it periodically so that it will never get full? I have Oracle 7.3.4 and this is the explain plan of the query:

INSERT STATEMENT Optimizer Mode=RULE
SORT UNIQUE
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY ROWID ACSEL.DATOS_PART_POLCOL
INDEX RANGE SCAN ACSEL.PART_NUMPOL_I
TABLE ACCESS BY ROWID ACSEL.POLIZAXCONVENIO_COLECTIVO
INDEX UNIQUE SCAN ACSEL.PK_POLIZAXCONVENIO_COLECTIVO
TABLE ACCESS BY ROWID ACSEL.C_ASEGURADO
INDEX RANGE SCAN ACSEL.FK_ASEG_I
TABLE ACCESS BY ROWID ACSEL.CLIENTE
INDEX UNIQUE SCAN ACSEL.PK_CLIENTE
TABLE ACCESS BY ROWID ACSEL.RAMO
INDEX UNIQUE SCAN ACSEL.PK_RAMO
TABLE ACCESS BY ROWID ACSEL.TERCERO
INDEX UNIQUE SCAN ACSEL.PK_TERCERO
TABLE ACCESS BY ROWID ACSEL.DATOS_EMPRESAS_POLCOL
INDEX RANGE SCAN ACSEL.PK_EMPRESAS
TABLE ACCESS BY ROWID ACSEL.TERCERO
INDEX UNIQUE SCAN ACSEL.PK_TERCERO

Any help will be greatly appreciated. the idea is not to extend TEMP tablespace

Thanks

Jaime
Re: How to avoid TEMP tablespace to get full [message #185275 is a reply to message #185260] Mon, 31 July 2006 21:33 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It would have helped if you included the SELECT. TEMP space is most frequently used to perform HASH joins (none in your query) and SORTs (there is a SORT-UNIQUE in your query). SORTs occur when you use DISTINCT, GROUP BY (most times), ORDER BY, Sort-Merge joins, UNION, MINUS, INTERSECT, and sometimes with IN-subqueries.

I suspect your query has a DISTINCT. It will not need TEMP space if you remove the DISTINCT, but you may not get the results you need. Can you write the query smarter so it does not need the distinct (filter rows, join on unique/primary keys)?

Ross Leishman
Previous Topic: census
Next Topic: how to change the default tab value
Goto Forum:
  


Current Time: Tue Dec 06 08:18:22 CST 2016

Total time taken to generate the page: 0.19673 seconds