Oracle Scratchpad

February 19, 2010

TO Locks

Filed under: Infrastructure,Troubleshooting — Jonathan Lewis @ 8:57 am BST Feb 19,2010

Have you ever seen any unexpected ‘TO’ locks in your system ? These are ‘Temp Object’ locks and appear when you’ve been using global temporary tables (GTTs) – and possibly at other times, but I haven’t noticed them in other situations yet. But here’s a simple cut-n-paste, from a freshly started session, which results in a ‘TO’ lock appearing for no apparent reason (this is running 11g, which is why we also see an ‘AE’ (application edition) lock):

SQL> create table t1 (n1 number);

Table created.

SQL>
SQL> select
  2      type, id1, id2, lmode, request
  3   from
  4      v$lock
  5   where
  6      sid = (select sid from v$mystat where rownum = 1)
  7  ;

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
AE         99          0          4          0

1 row selected.

SQL> drop table t1;

Table dropped.

SQL> select
  2      type, id1, id2, lmode, request
  3   from
  4      v$lock
  5   where
  6      sid = (select sid from v$mystat where rownum = 1)
  7  ;

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
AE         99          0          4          0
TO      62986          1          3          0

2 rows selected.

SQL>

We can find out what’s happened fairly easily because the ID1 on a TO lock is the object_id of the temporary object.

SQL> select object_id, object_name from dba_objects where object_id = 62986;

 OBJECT_ID OBJECT_NAME
---------- ---------------------
     62986 SDO_GEOR_DDL__TABLE$$

We’ve got a global temporary table in our system called SDO_GEOR_DDL__TABLE$$. This is a system which had been installed as a “default” database, and the person installing it had just accepted everything the DBCA had offered without bothering to eliminate all the options they don’t want or need. So this system has Oracle Spatial installed, and Spatial has installed a DDL trigger that does a lot of stuff in the background when you drop a table – using global temporary table SDO_GEOR_DDL__TABLE$$ to keep track of what’s going on. And this isn’t the only hidden stuff installed by the various add-on packages, so keep an eye out for unexpected activity on wierd tables.

As a simple check you can always try executing a “drop table” command with sql_trace enabled and how much much extra SQL appears in the trace file.

5 Comments »

  1. Hi Jonathan,

    I know exactly what you’re talking about:

    Installing it the default way no matter what gets installed just get it up and running – the [Next] button is your friend! And then you have a lot of things going on in the background which you won’t need.

    But I have one question here: When gets the lock released? As drop table is a DDL statement there happens an implicit commit, right? So is Oracle Spatial taking care of the lock release?

    Comment by Venzi — February 19, 2010 @ 6:56 pm BST Feb 19,2010 | Reply

  2. Venzi,

    I’ve just done a quick check in 11.1.0.6.
    The table is of duration “SYS$SESSION”, i.e. it’s “on commit preserve rows”.

    This means the temporary extent your session has been assigned for the table will only be released when your session ends, or when you issue “truncate table …”.

    The TO lock disappeared when I executed:

    truncate table mdsys.SDO_GEOR_DDL__TABLE$$;
    

    Comment by Jonathan Lewis — February 20, 2010 @ 8:46 pm BST Feb 20,2010 | Reply

  3. Hi Jonathan,

    Ah ok I understand!

    Thanks very much,

    Venzi

    Comment by Venzi — February 21, 2010 @ 12:20 am BST Feb 21,2010 | Reply

  4. [...] 15-Unusual temporary object (TO) locks when spatial is installed Jonathan Lewis-TO Locks [...]

    Pingback by Blogroll Report 12/02/2009 – 19/02/2010 « Coskan’s Approach to Oracle — March 18, 2010 @ 4:44 pm BST Mar 18,2010 | Reply

  5. disable trigger mdsys.SDO_GEOR_ERR_TRIGGER

    Comment by The_Duck — September 17, 2011 @ 5:14 pm BST Sep 17,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers