Hello everyone,
I want to know if using bulk collect into with limit will help to avoid the error TEMP tablespace run out.
We use Oracle 11g R1.
I am assigned with a task to create journal facility for all tables in an APEX application.
I create some procedures to run some dynamic sql to create a CTAS (Create table as select), then triggers on these tables.
We have about three tables that have more than 26 million records.
It seems running fine until we hit some table with more than 15 million of record, we got an error said that running out of TEMP tablespace.
I googled about this, and get back the tips:
Use NO LOGGING
Use Parallel
BULK COLLECT INTO with limit
However, the questions for these above usually running out of memory rather than running out of TEMPORARY tablespace.
I am just a junior developer, and has not dealed with more than 10 million records table at once like this before.
The Database support is outsourced. So we try to keep as minimal contact with the DBA as possible. My manager asks me to find a solution without asking the DBA to extend the TEMP tablespace.
I wrote some BULK COLLECT INTO to insert about 300 000 as a time on Development environment. It seems to run fine.
But the code only runs against a 4000 000 records table. I am trying to add more data in the Test table but again we run out of tablespace on DEV (this time it is a data not a TEMP)
I will give it a go against the 26 million records table on Production this weekend. I just want to know if it is worth trying.
Thanks for reading this.
Ann