I'm using a procedure which creates a table using a heavy query (running ~1 hr). Query is something like 'select * from table', and the columns in the table can change. Oftentimes it turns out that there is no free space in schema to create the table, so I get an exception, the time is spent in vain and I need to do the same calculations once again.
The error I get:
ORA-01536: space quota exceeded for tablespace ORA-06512: at "UPDATE_REPORT", line 37
What I would want to do: - Store query's results in temporary segment in a cursor; - Try to create table using cursor. In case of exception (not enough space), drop a special space-holding table to free table space in schema; - Try to create the table again from cursor.
I tried to solve this using dynamic SQL, but it leads to overcomplications while the problem seems to have a simple solution. And the main problem I faced is that there is no evident way to create a table using cursor.
Is there any simple solution I somehow missed out? Maybe cursor are the wrong way to work this out?
What I would want to do: - Store query's results in temporary segment in a cursor; - Try to create table using cursor. In case of exception (not enough space), drop a special space-holding table to free table space in schema; - Try to create the table again from cursor.
Don't go through the trouble. Just tell Oracle not to die because of space issues.
You can make your session "resumable" so that, rather than giving you an error when you run out of space, Oracle will suspend your session until the problem is corrected (and then continue on automatically).
Assuming you have all the permissions you need (notably, GRANT RESUMABLE TO yourschema), you enable it like this:
alter session enable resumable timeout 1800 name 'your process name, can be anything';
The 1800 number is in seconds, giving your DBA's 30 minutes to fix the problem before your session times out. The "my process" will show up in V$RESUMABLE for use in queries and automated alerts.
Your DBAs can monitor V$RESUMABLE and/or you can create a schema-level database trigger on the AFTER SUSPEND event to fire off an e-mail to them when they need to jump in.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments