Yes, I think the name "GLOBAL TEMPORARY" is a bit misleading. It IS "global" in the sense that any user with permission can use it. However, that is also true of a normal table.
I think a better name would have been INTRASESSION TEMPORARY table or PRIVATE TEMPORARY table. So, yeah, create a normal table and things will work the way you think they should. Regards, Mike On Wed, Apr 27, 2011 at 10:21 AM, Avinash Naidu <avisna...@gmail.com> wrote: > just what I suspected > > create table as > > CREATE GLOBAL TEMPORARY TABLE "XXX"."TABLEA" > ( > ... > ) ON COMMIT PRESERVE ROWS ; > > depending on the usage of table, > > Global temporary table is treated differently in oracle, every session can > have it's own data in the table, which can not be viewed from other > sessions. > you may want to understand what is your requirement > > On Wed, Apr 27, 2011 at 10:17 AM, Avinash Naidu <avisna...@gmail.com>wrote: > >> Hi, >> >> I do not know what is Table A from this example, >> but if table A is temporary table in this example, you will have to create >> temporary table with 'ON COMMIT PRESERVE ROWS' >> >> by deault global temporary tables in oracle delete rows on commit >> >> google it for examples. >> >> Thanks, >> Avinash >> >> On Wed, Apr 27, 2011 at 9:42 AM, ddf <orat...@msn.com> wrote: >> >>> >>> >>> On Apr 27, 8:58 am, Michael Moore <michaeljmo...@gmail.com> wrote: >>> > That's not the way it should work. After you run the INSERT you should >>> be >>> > able to do a SELECT, and see the records in table A. Other sessions >>> will NOT >>> > see those records in TABLE A until you do the commit. >>> > >>> > I am guessing that you've got multiple sessions open (multiple sqlplus >>> > windows or multiple sql developer windows) and that your results are >>> due to >>> > testing from different sessions. >>> > >>> > Make sure you are issuing all of your commands from the same session >>> > (window). >>> > >>> > Mike >>> > >>> > >>> > >>> > On Wed, Apr 27, 2011 at 8:15 AM, Dom <dolivas...@gmail.com> wrote: >>> > > I'm new to Oracle -- I've always used SQL Server, with autocommit >>> > > turned on. My problem is the following two statements: >>> > >>> > > 1. insert into TableA (select * from TableB where rownum < 10); >>> > > 2. commit; >>> > >>> > > After I do the first, I can see the records TableA. Then I do the >>> > > second command, and suddenly the records disappear. Any ideas? >>> > >>> > > -- >>> > > You received this message because you are subscribed to the Google >>> > > Groups "Oracle PL/SQL" group. >>> > > To post to this group, send email to Oracle-PLSQL@googlegroups.com >>> > > To unsubscribe from this group, send email to >>> > > oracle-plsql-unsubscr...@googlegroups.com >>> > > For more options, visit this group at >>> > >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text - >>> > >>> > - Show quoted text - >>> >>> I agree with Mike; a commit in the same session as the transaction >>> will preserve the transaction, not erase it: >>> >>> SQL> create table tableb as select * From all_objects where rownum >>> <=500; >>> >>> Table created. >>> >>> SQL> >>> SQL> create table tablea as select * From tableb where 0=1; >>> >>> Table created. >>> >>> SQL> >>> SQL> insert into TableA (select * from TableB where rownum < 10); >>> >>> 9 rows created. >>> >>> SQL> >>> SQL> select owner, object_name, object_id From tablea; >>> >>> OWNER OBJECT_NAME >>> OBJECT_ID >>> ------------------------------ ------------------------------ >>> ---------- >>> SYS DUAL >>> 258 >>> PUBLIC DUAL >>> 259 >>> SYS SYSTEM_PRIVILEGE_MAP >>> 311 >>> PUBLIC SYSTEM_PRIVILEGE_MAP >>> 313 >>> SYS TABLE_PRIVILEGE_MAP >>> 314 >>> PUBLIC TABLE_PRIVILEGE_MAP >>> 316 >>> SYS STMT_AUDIT_OPTION_MAP >>> 317 >>> PUBLIC STMT_AUDIT_OPTION_MAP >>> 319 >>> SYS KOTTD >>> 334 >>> >>> 9 rows selected. >>> >>> SQL> >>> SQL> commit; >>> >>> Commit complete. >>> >>> SQL> >>> SQL> select owner, object_name, object_id From tablea; >>> >>> OWNER OBJECT_NAME >>> OBJECT_ID >>> ------------------------------ ------------------------------ >>> ---------- >>> SYS DUAL >>> 258 >>> PUBLIC DUAL >>> 259 >>> SYS SYSTEM_PRIVILEGE_MAP >>> 311 >>> PUBLIC SYSTEM_PRIVILEGE_MAP >>> 313 >>> SYS TABLE_PRIVILEGE_MAP >>> 314 >>> PUBLIC TABLE_PRIVILEGE_MAP >>> 316 >>> SYS STMT_AUDIT_OPTION_MAP >>> 317 >>> PUBLIC STMT_AUDIT_OPTION_MAP >>> 319 >>> SYS KOTTD >>> 334 >>> >>> 9 rows selected. >>> >>> SQL> >>> SQL> truncate table tablea; >>> >>> Table truncated. >>> >>> SQL> >>> SQL> insert into TableA (select * from TableB where rownum < 10); >>> >>> 9 rows created. >>> >>> SQL> >>> SQL> select owner, object_name, object_id From tablea; >>> >>> OWNER OBJECT_NAME >>> OBJECT_ID >>> ------------------------------ ------------------------------ >>> ---------- >>> SYS DUAL >>> 258 >>> PUBLIC DUAL >>> 259 >>> SYS SYSTEM_PRIVILEGE_MAP >>> 311 >>> PUBLIC SYSTEM_PRIVILEGE_MAP >>> 313 >>> SYS TABLE_PRIVILEGE_MAP >>> 314 >>> PUBLIC TABLE_PRIVILEGE_MAP >>> 316 >>> SYS STMT_AUDIT_OPTION_MAP >>> 317 >>> PUBLIC STMT_AUDIT_OPTION_MAP >>> 319 >>> SYS KOTTD >>> 334 >>> >>> 9 rows selected. >>> >>> SQL> >>> SQL> pause >>> >>> >>> Here we start a second session, query tablea, commit, then query >>> tablea again: >>> >>> SQL> select * From tablea; >>> >>> no rows selected >>> >>> SQL> >>> SQL> commit; >>> >>> Commit complete. >>> >>> SQL> >>> SQL> select * from tablea; >>> >>> no rows selected >>> >>> SQL> >>> >>> We go back to the original session, issue a commit then query tablea: >>> >>> SQL> >>> SQL> commit; >>> >>> Commit complete. >>> >>> SQL> >>> SQL> select owner, object_name, object_id From tablea; >>> >>> OWNER OBJECT_NAME >>> OBJECT_ID >>> ------------------------------ ------------------------------ >>> ---------- >>> SYS DUAL >>> 258 >>> PUBLIC DUAL >>> 259 >>> SYS SYSTEM_PRIVILEGE_MAP >>> 311 >>> PUBLIC SYSTEM_PRIVILEGE_MAP >>> 313 >>> SYS TABLE_PRIVILEGE_MAP >>> 314 >>> PUBLIC TABLE_PRIVILEGE_MAP >>> 316 >>> SYS STMT_AUDIT_OPTION_MAP >>> 317 >>> PUBLIC STMT_AUDIT_OPTION_MAP >>> 319 >>> SYS KOTTD >>> 334 >>> >>> 9 rows selected. >>> >>> SQL> >>> >>> We return to the second session and issue the same query as above; >>> this time we see data: >>> >>> SQL> select owner, object_name, object_id From tablea; >>> >>> OWNER OBJECT_NAME >>> OBJECT_ID >>> ------------------------------ ------------------------------ >>> ---------- >>> SYS DUAL >>> 258 >>> PUBLIC DUAL >>> 259 >>> SYS SYSTEM_PRIVILEGE_MAP >>> 311 >>> PUBLIC SYSTEM_PRIVILEGE_MAP >>> 313 >>> SYS TABLE_PRIVILEGE_MAP >>> 314 >>> PUBLIC TABLE_PRIVILEGE_MAP >>> 316 >>> SYS STMT_AUDIT_OPTION_MAP >>> 317 >>> PUBLIC STMT_AUDIT_OPTION_MAP >>> 319 >>> SYS KOTTD >>> 334 >>> >>> 9 rows selected. >>> >>> SQL> >>> >>> You're not issuing the commit from the session performing the insert. >>> >>> >>> David Fitzjarrell >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Oracle PL/SQL" group. >>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>> To unsubscribe from this group, send email to >>> oracle-plsql-unsubscr...@googlegroups.com >>> For more options, visit this group at >>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>> >> >> > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
