This content has been marked as final. Show 9 replies
Suri wrote:True, but not the cmplete answer. For a session specifict GTT, i.e. one created with ON COMMIT PRESERVE ROWS, the session needs to truncate the GTT (Or end the session whereby table will get truncated).
If you are not completing any transaction in any session and if you try to drop the table then you will get this error.
Does this error indicate that there is a bug in the code?Difficult to tell when not knowing what that code is, what you are trying to do and why you are doing it.
Let's say you have two sessions:
DROP_SESSION - This is the session trying to drop the Global Temporary Table (GTT)
LOCK_SESSION - This is the session using that GTT, hence preventing DROP_SESSION from dropping it.
Note: They could be the same session.
If you by "code" mean code as in a program executed by DROP_SESSION, you definitely have a problem.
Application code should NOT drop objects, unless it is very special code (Like an ETL process) and
this is code is executed in controlled down-time windows where no ordinary users have access.
And dropping a GTT is meaningless and seems to defy the purpose of a GTT - Define it once, like other tables,
and use over and over.
If this is your case, solution is Don't drop it.
If you by "code" mean code as in a program executed by LOCK_SESSION, you need to tell more about the GTT.
How is it created, ON COMMIT DELETE, or ON COMMIT PRESERVE?
In either case, it brings the question why are you dropping it?
- Is this part of a one time operation, then why are you dropping objects while code is using them?
- Is it not part of a one time operation, see above Don't drop it .
The LOCK_SESSION may have legitimate need for using that GTT.
It could also be a bug, if an application has a long time transaction that is "never" committed.
It could also be a bug, if an application have data in that GTT during the entire session, and it should not have.
Impossible to tell.
Do I need to do anything to the code besides killing the hanging session so that I do not get this error again?Again, I don't know what that code is. And you have said nothing about a hanging session, which session would that be?
You need to run SY's query to find who is using the GTT, find the responsible for that session, and tell him to complete his session.
Still and again, I'm not sure of what you are doing, but one thing I do know. You should not be dropping GTTs (Or other objects)
as a usual operation in an Oracle database.
orauser34 wrote:No you don't:
I need to drop the GTT because I need to add a column to it
SQL> create global temporary table temp1 ( 2 col1 number, 3 col2 varchar2(10)) 4 on commit preserve rows; Table created. SQL> alter table temp1 add (col3 date); Table altered.
It will be a one time operation and my project is still in the Development phase.No more locking issues than delete rows. If a session is actively using the table, then it will be locked, at least as far as DDL is concerned. If you need to keep the contents of the table for a session across commits, and there are many valid reasons why you might, then you need to have it as preserve rows.
The GTT is created with "ON COMMIT PRESERVE ROW". Does this leading to any locking issue?