Home » SQL & PL/SQL » SQL & PL/SQL » How do I know if I can truncate a table? (Oracle 10gR2)
How do I know if I can truncate a table? [message #357079] Mon, 03 November 2008 16:08 Go to next message
rasa
Messages: 45
Registered: February 2006
Member
In a stored procedure, I will have to eliminate data in a "staging table." I can do this:
EXECUTE IMMEDIATE 'truncate table mytable reuse storage';


However, what can I do to know that "mytable" is not being held by another process that may be loading/reading data before issuing the dynamic SQL? Kindly help.
Re: How do I know if I can truncate a table? [message #357081 is a reply to message #357079] Mon, 03 November 2008 16:45 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
and what would you do with this information?

WHat is the benefit of finding out the information before running the command?

You might as well run the command and handle the output (ie. pause for 5 mins and retry, log an error and abort, whatever).
Re: How do I know if I can truncate a table? [message #357083 is a reply to message #357081] Mon, 03 November 2008 16:48 Go to previous messageGo to next message
rasa
Messages: 45
Registered: February 2006
Member
I would like to not wait. Rather, I would like to raise an error message that would inform the user that the process cannot be run currently as another process is busy loading data.
Re: How do I know if I can truncate a table? [message #357119 is a reply to message #357079] Tue, 04 November 2008 01:02 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If data are currently modified, truncate will fail with an error "ORA-00054: resource busy and acquire with NOWAIT specified".
If data are currently read, there is nothing you can do to prevent from truncating unless you add a usage counter somewhere and test it before truncating.

Regards
Michel
Previous Topic: Pl Sql - Error
Next Topic: numbering different records of each patient
Goto Forum:
  


Current Time: Sun Dec 11 04:17:53 CST 2016

Total time taken to generate the page: 0.16370 seconds