| Global Temporary Table - Creating Locks [message #180193] |
Fri, 30 June 2006 10:25  |
gentleman777us
Messages: 122
Registered: April 2005
|
Senior Member |
|
|
Hello,
Iam running a Business Objects report based on database stored procedure in oracle. The database procedures inserts records into a Global Temporary Table(created with NOLOG option).
The issue is these temporary tables are creating locks on the database there by jamming other processes.
Could some one help me how to release these locks that were created on the database automatically. Because of these locks the report never comes up.
Your prompt ideas to resolve this issue is greatly appreciated.
Thanks
- Raj
|
|
|
|
| Re: Global Temporary Table - Creating Locks [message #180199 is a reply to message #180193] |
Fri, 30 June 2006 11:34  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
| Quote: |
The issue is these temporary tables are creating locks on the database there by jamming other processes
|
How so? How exactly is this happening? How do you know?
You haven't provided enough information...
|
|
|
|
| Re: Global Temporary Table - Creating Locks [message #180401 is a reply to message #180193] |
Mon, 03 July 2006 06:43 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I would be suprised if that were the case.
As the data inserted into a GTT is only visible from the session that inserted the data, you cannot, by definition, be getting locking problems with other sessions trying to access the data.
The only locking problem I can think you'd hit would be if you were trying to drop the table while someone else had rows in it.
Also - you say you created it using the NOLOG (I presume you mean NOLOGGING) option.
1) There would be no point doing this. Temporary tables don't generate Redo at all - that's one of the big reasons to use them
2) You can't specift the Nologging option with temporary tables
SQL> create global temporary table temp_gtt (col_1 number, col_2 date) on commit delete rows;
Table created.
SQL> drop table temp_gtt;
Table dropped.
SQL> create global temporary table temp_gtt (col_1 number, col_2 date) on commit delete rows nologging;
create global temporary table temp_gtt (col_1 number, col_2 date) on commit delete rows nologging
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
So, how about you do us a favour, and tell us what you've really done (SQL Scripts liek the one above would be nice) and then we'll see if we can help you fix the problems.
|
|
|
|
| Re: Global Temporary Table - Creating Locks [message #180403 is a reply to message #180193] |
Mon, 03 July 2006 06:53 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
As said by SMartin and JRowbottom,
More information may be useful.
Just to add what JRowbottom already said,
DML on GTT will NOT generate any redo (by itself).
But during this DML some UNDO will always be generated which in turn will create some REDO.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
