| urgent help required [message #64948] |
Tue, 16 March 2004 05:36  |
Kaushik Choudhury
Messages: 2
Registered: March 2004
|
Junior Member |
|
|
Hi,
Please help. I have a script which inserts some records from data tables into temporary tables, and then using join statements on the temporary tables, the data tables are deleted(this is because the data tables have some complex referential integrity issues and is cumbersome to delete without the temporary tables). But the problem is the script hangs after inserting into the temporary tables and after executing the first delete statement. The number of records here are large(1-2million) and for smaller tables the whole script runs fine. We have disabled redo log, and decreased the undo time to 3 sec from 3 hours. Still, it seems the system runs out of tablespace. Any suggestions what the problem could be?????
P.S.-I am using oracle 9i
|
|
|
|
| Re: urgent help required [message #64951 is a reply to message #64948] |
Tue, 16 March 2004 21:33  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
According to me at the first shot following things should be done :
1) Check the execution plan of select statement which u r using to fetch the data from multiple tables using joins. Check is it properly tuned or need some tuning like status of indexes etc. If it is not using any indexes since u have millions of records try to write the query in better way.(According to me this is the only problem with u)
2) As u said u r using automatict undo management and retention is also very low, so i dont think that there would be any problem at undo segment side except the sizes of files u r using for undo tablespace. But since u r using temporary tables to hold millions of records, just check the size of ur temp tablespace. Try to use unlimited size for the files of temp tablespace and also check the available disk space for the same.
Check these things and revert back.
Daljit Singh.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
