| Problem with Temporary Tables [message #382468] |
Thu, 22 January 2009 07:49  |
robgerrrr
Messages: 4
Registered: January 2009
|
Junior Member |
|
|
Hi,
We have a problem with one of databases that recently had to be recovered due to catastrophic power failure of primary and backup power systems. This resulted in a complete power off of the database server and multiple disk failures in our SAN which caused data loss and several DBF files had to be recovered from tape.
Now our system is up and running normally. However there is an application that runs hourly on our system that creates global temporary tables:-
CREATE GLOBAL TEMPORARY TABLE nokrww_ct_traffic_wcel_hour ON COMMIT PRESERVE ROWS AS ......
This program is now failing because the temporary table name (nokrww_ct_traffic_wcel_hour above is one of several) still exists. I don't know much about temporary tables but from what I've read I thought they should only exist while the program is running.
Of course now I cannot delete them by dropping them (even after truncating the table as I'm not using the session that created them) as I get the oracle error :-
"ORA-14452: attempt to create, alter or drop an index on temporary table already in use".
I joined the OBJECT_ID from ALL_OBJECTS for these "hanging" temporary tables to the V$LOCKED_OBJECT table but there are no entries for them.
The creation date for these temporary tables is after the date when the database was recovered so I can't understand why they are not being removed by the program that created them when they finish.
I wonder if anyone has any ideas how to remove these "hanging" global temporary tables ?
|
|
|
|
| Re: Problem with Temporary Tables [message #382472 is a reply to message #382468] |
Thu, 22 January 2009 08:07  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I am not quite sure I understand 100%. I think you left out some important info, but Global Temporary Tables do not disappear after they are used. The ROWS do, but not the table. If you want to get rid of the table, you have to drop it like any other table.
You said you are using ALL_OBJECT, but that would only give you access to objects YOU can see. Tye using DBA_OBJECTS from an account that has access to it.
|
|
|
|
| Re: Problem with Temporary Tables [message #382474 is a reply to message #382472] |
Thu, 22 January 2009 08:22 |
robgerrrr
Messages: 4
Registered: January 2009
|
Junior Member |
|
|
Hi,
Sorry if I've left some information out, but I'd not come across temporary tables until this problem was identified.
When I try to drop these temporary tables I get the oracle error message "ORA-14452: attempt to create, alter or drop an index on temporary table already in use".
I can see the OBJECT_ID's for the temporary tables from ALL_OBJECTS and DBA_OBJECTS but those OBJECT_ID's don't have a corresponding entry in V$LOCKED so I cannot find a session that might have created the temporary table.
The program that creates the temporary tables normally TRUNCATES the table data and then DROP's them. Now however it cannot create the tables in the first place.
|
|
|
|
| Re: Problem with Temporary Tables [message #382499 is a reply to message #382474] |
Thu, 22 January 2009 12:36 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| robgerrrr wrote on Thu, 22 January 2009 09:22 |
The program that creates the temporary tables normally TRUNCATES the table data and then DROP's them. Now however it cannot create the tables in the first place.
|
Ok, it does sound like something may be wrong, but why are you truncating a table before dropping it? Even if you weren't dropping it, a Global Temporary Table will be "truncated" as the end of it's session.
You say you cannot create the table now, but why would you want to do that. The purpose of a Global Temporary table is that it is always there and the rows will be there for that session. What is the purpose of dropping it and recreating it?
Just use the table as is. Insert, update and delete rows at will. First see if that works.
|
|
|
|
| Re: Problem with Temporary Tables [message #382615 is a reply to message #382499] |
Fri, 23 January 2009 04:11 |
robgerrrr
Messages: 4
Registered: January 2009
|
Junior Member |
|
|
Hi,
I am not truncating the table, but the program that was written by our product people in Finland or India. This program creates the temporary table when it starts, uses it, truncates it and drops the table at the end. I have no control over what the program does.
Robg
|
|
|
|
| Re: Problem with Temporary Tables [message #382667 is a reply to message #382615] |
Fri, 23 January 2009 09:37 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Then the "program people" wrote a program with a bug. Tables should not be created and dropped "on the fly" by procedures. (Because that will create problems like the ones you are experiencing now sooner or later)
The problem you are seeing now is that some session is using that table, so it can not be dropped. You should be able to drop it when that other session is finished using it.
You could find out what session is using the table by querying v$access and v$session.
|
|
|
|
| Re: Problem with Temporary Tables [message #382691 is a reply to message #382667] |
Fri, 23 January 2009 10:33 |
robgerrrr
Messages: 4
Registered: January 2009
|
Junior Member |
|
|
Hi,
Thanks for the information. I looked in V$ACCESS assuming that OBJECT was the name of the temporary tables and found no entries.
I assume if there is a session using these temporary tables then there should be an entry in V$ACCESS and the SID equates to the SID in V$SESSION which would show the process using that table ?
Robg
|
|
|
|
| Re: Problem with Temporary Tables [message #382702 is a reply to message #382691] |
Fri, 23 January 2009 11:19 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Basically yes.
Some session that is still connected has inserted some lines in the Global Temporary Table. It MIGHT show up in v$access when the session is still accessing it, but it might also not be used anymore and not show up in v$access, but the lines might still be present. As long as that session is connected the table can not be altered or dropped.
Depends also on if the table was created with "on commit delete row" or "on commit preserve rows".
Then basically you can just end all other client-sessions (or restart the database to get rid of them) and try again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
