Noob here-
i successfully created a global temporary table using:
CREATE GLOBAL TEMPORARY TABLE myTemp ON COMMIT PRESERVE ROWS AS (SELECT * FROM myTable);
Then, lost my network connection, Oracle SQL Developer froze up, and i force-quit the application (on Mac).
Then, re-opened Oracle SQL Developer. I can see myTemp in the Tables tree.
Why can i see it in a different session? Isn't GLOBAL TEMPORARY TABLE exclusive to the session that created it?
In the new session, When i attempt to delete it in the new session, i get a "table in-use" error.
i attempted to truncate the table, and then drop it. Truncate worked, drop failed again.
I compared the session numbers:
select object_id from all_objects where object_name = 'myTemp';
-- returns obj ID: 4456549
select SID from v$lock WHERE ID1=4456549;
-- returns SID of user locking myTemp: 15
select distinct sid from v$mystat;
from How to display your Oracle session ID number
-- returns SID of new session: 1014.
The session numbers are not the same, yet the new session can see the old session's table.
Why do these 2 SID's have a different number of digits?
Ideas:
- Possibly, the object is really in use by the current session, but the above results don't seem to show that.
- Or maybe i'm comparing the wrong numbers, and i'm still in the same session?
- Or, maybe ON COMMIT PRESERVE ROWS makes it visible to other sessions? (i thought ON COMMIT PRESERVE ROWS simply extends the scope of the temp table across all connections of the same session (eg to different procedures), but not to other sessions).
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
(i posted this in SQL Developer, because that's where this all happened. Plz let me know if posted in wrong forum space)
thx