From oracle-l-bounce@freelists.org Wed Mar 3 13:53:23 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i23JrMB22544 for ; Wed, 3 Mar 2004 13:53:22 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i23JrJo22534 for ; Wed, 3 Mar 2004 13:53:20 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 18130394FC9; Wed, 3 Mar 2004 14:53:24 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 03 Mar 2004 14:52:17 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mta-int3.heb.com (unknown [199.59.33.181]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0315E3949C8 for ; Wed, 3 Mar 2004 14:52:15 -0500 (EST) Received: from D_4LXPL21 (sys98057.heb.com [172.17.12.32]) by sys98057.heb.com (iPlanet Messaging Server 5.2 Patch 1 (built Aug 19 2002)) with ESMTP id <0HU000B6VNDHKK@sys98057.heb.com> for oracle-l@freelists.org; Wed, 03 Mar 2004 13:56:06 -0600 (CST) Date: Wed, 03 Mar 2004 13:56:07 -0600 From: Ravi Kulkarni Subject: RE: GTT Locks In-reply-to: <001301c400ba$e6e8a5a0$9005a8c0@chaos> To: oracle-l@freelists.org Message-id: <0HU000B6WNDIKK@sys98057.heb.com> MIME-version: 1.0 Content-Type: TEXT/PLAIN; CHARSET=iso-8859-1 X-archive-position: 230 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: kulkarni.ravi@heb.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of zhu chao Sent: Tuesday, March 02, 2004 7:00 PM To: oracle-l@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 ----- Original Message ----- From: "Ravi Kulkarni" To: Sent: Wednesday, March 03, 2004 8:37 AM Subject: RE: GTT Locks > Sorry .. I should have mentioned. I am on 9204(solaris8). > Is this a "New Feature" of 92 then ? > > > > -----Original Message----- > From: oracle-l-bounce@freelists.org > [mailto:oracle-l-bounce@freelists.org]On Behalf Of Connor McDonald > Sent: Tuesday, March 02, 2004 6:28 PM > To: oracle-l@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 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@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@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@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@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@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@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 -----------------------------------------------------------------