Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: GTT Locks

RE: GTT Locks

From: Ravi Kulkarni <kulkarni.ravi_at_heb.com>
Date: Wed, 03 Mar 2004 13:56:07 -0600
Message-id: <0HU000B6WNDIKK@sys98057.heb.com>


You would not see the same result when you insert/delete from tmp_t without sourcing any regular table.

Say ...
SQL-> delete from tmp_t;

0 rows deleted.

You would expect a lock on tmp_t like it did in your example.

SQL-> select object_name, object_type, a.object_id, b.locked_mode from dba_objects a, v$locked_object b where a.object_id=b.object_id;

no rows selected

It is not the same in 8174.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of zhu chao Sent: Tuesday, March 02, 2004 7:00 PM
To: oracle-l_at_freelists.org
Subject: Re: GTT Locks

I am running it on 9204, did not see what you say:

SQL> create global temporary table tmp_t on commit delete rows as select * from dba_objects;

Table created.

SQL> select * from tmp_t;

no rows selected

SQL> insert into tmp_t select * from dba_objects where rownum<30;

29 rows created.

SQL> select object_name, object_type, a.object_id, b.locked_mode from dba_objects a, v$locked_object b where a.object_id=b.object_id;

OBJECT_NAME




OBJECT_TYPE OBJECT_ID LOCKED_MODE
------------------ ---------- -----------
TMP_T
TABLE                  225775           3
SQL> select * from v$version;

BANNER



Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production

> Sorry .. I should have mentioned. I am on 9204(solaris8).
> Is this a "New Feature" of 92 then ?

>
>
>

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Connor McDonald
> Sent: Tuesday, March 02, 2004 6:28 PM
> To: oracle-l_at_freelists.org
> Subject: Re: GTT Locks
>
>

> I don't observe your results on a system here (8174) - the entry appears
in v$locked_object.
>

> SQL> insert into GTT values (1);
>

> 1 row created.

>
> SQL> select object_name, object_type, a.object_id, b.locked_mode from
dba_objects a,
> 2 v$locked_object b where a.object_id=b.object_id;
>
> OBJECT_NAME OBJECT_TYPE OBJECT_ID
LOCKED_MODE
> ---------------------------------------- ------------------ ---------- ---


> GTT TABLE 102302
3
>

> hth
> connor

>
> --- Ravi Kulkarni <kulkarni.ravi_at_heb.com> wrote: > Trying to understand
the locking behavior of
> GTTs (Global Temporary Tables) :
> > (GTT_T is a GTT. T is a regular table)
> >
> > SQL> insert into gtt_t values (1000);
> > 1 row created.
> >
> > SQL> select object_name, object_type, a.object_id, b.locked_mode from
dba_objects a,
> > v$locked_object b where a.object_id=b.object_id;
> > no rows selected
> >
> > SQL> insert into gtt_t select *from T;
> > 1 row created.
> >
> > SQL> select object_name, object_type, a.object_id, b.locked_mode from
dba_objects a,
> > v$locked_object b where a.object_id=b.object_id;
> >
> > OBJECT_NAME OBJECT_TYPE OBJECT_ID OBJECT_ID LOCKED_MODE
> > ----------------------------------------------------------------
> > GTT_T TABLE 204835 204835 3
> >
> >
> > The user acquires a lock on the GTT for performing DML ONLY when
selecting from a Non-GTT.
> > 1. How is this different from the first insert on GTT? (In either case,
I noticed a 'TO'
> > lock-type in v$lock)
> > 2. Where can I find more info on GTT-peculiarities ?
> >
> > TIA,
> > Ravi.
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
>

> =====
> Connor McDonald
> Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: connor_mcdonald_at_yahoo.com

>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
>
>
>
>
>

> ___________________________________________________________
> Yahoo! Messenger - Communicate instantly..."Ping"
> your friends today! Download Messenger Now
> http://uk.messenger.yahoo.com/download/index.html
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Mar 03 2004 - 13:53:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US