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
