orauser34 wrote:Well, you have to try harder ;). Anyway, issue:
I have tried closing all my sessions
select * from v$lock where id1 = ( select object_id from dba_objects where owner = 'global-temp-table-owner-name-in-upper-case' and object_name = 'global-temp-table-name-in-upper-case' );
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.
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?
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?