This discussion is archived
4 Replies Latest reply: Dec 4, 2012 12:52 AM by Aman.... RSS

Library cache Lock and Pin (select vs DDL)

872581 Newbie
Currently Being Moderated
Hi, all.

The oracle is 11.2.0.3 on a linux machine.

As you see in the following, I issued select statement in session 1.

While session 1 is running (not finished) , I issued alter table statement in session 2.

"alter table " statement has finished although session 1 were running.
Soon later, session 1 has finished without an error.

My expectation is that :
1. session 1 (select) holds library cache pin(shared mode) on the query(LCO) and the related objects LCO(table/index).
2. session 2 (ddl) must wait in order to get library cache lock/pin (exclusive mode ) on the related objets LCO(table/index).
until session 1 is done.

After the table has been renamed, how can "select count(*) " be running?

I have tested "DML vs DDL" . In this case, I have got "resource busy error" as expected.
-- session 1 :

11:11:17 HOHO@PD1MGD>select /*+ full(t) */ count(*) from hoho.kkk t;

  COUNT(*)
----------
   9975591

Elapsed: 00:00:07.14
11:12:34 HOHO@PD1MGD>


-- session 2 :
11:12:05 HOHO@PD1MGD>alter table hoho.kkk rename to hhh;

Table altered.

Elapsed: 00:00:00.02
11:12:30 HOHO@PD1MGD>
Thanks in advance.
Best Regards.

Edited by: 869578 on 2012. 12. 3 오후 10:12
  • 1. Re: Library cache Lock and Pin (select vs DDL)
    Purvesh K Guru
    Currently Being Moderated
    Since, SELECT on an object does not register any Lock on it, hence any other DML/DDL can be performed. However, a DML puts a Shared Lock on the Table to prevent modification and hence does not allow any DDL to be executed against it.
    -- session 1 :
    
    11:11:17 HOHO@PD1MGD>select /*+ full(t) */ count(*) from hoho.kkk t;
    
    COUNT(*)
    ----------
    9975591
    
    Elapsed: 00:00:07.14
    11:12:34 HOHO@PD1MGD>
    
    
    -- session 2 :
    11:12:05 HOHO@PD1MGD>alter table hoho.kkk rename to hhh;
    
    Table altered.
    
    Elapsed: 00:00:00.02
    11:12:30 HOHO@PD1MGD>
    It doesn't look like the timing of your Alter statement overlaps with the SELECT statement. and there is no error displayed too.

    Did you check the trace file for the particular session?
  • 2. Re: Library cache Lock and Pin (select vs DDL)
    872581 Newbie
    Currently Being Moderated
    Thanks for your reply.
    It doesn't look like the timing of your Alter statement overlaps with the SELECT statement. and there is no error displayed too.
    Session 1 took 7.14 seconds and it finished at "11:12:34", and thus, session 1 started at "11:12:27".
    Session 2 took 0.02 seconds and it finished at "11:12:30", and thus, session 2 started at "11:12:29".

    Therefore, session 1 and session 2 were overlapped.

    As far as I know, when a select statement is running, the query needs library cache pin on the executable LCO and its related object LCOs.

    Suppose that "select col1, col2 from table_a" is running and "alter table table_a drop column col2" is issued.
    The query needs the col2 column and the DDL drops the col2 column.

    Is this possible?

    Thanks in advance.
    Best Regards.
  • 3. Re: Library cache Lock and Pin (select vs DDL)
    872581 Newbie
    Currently Being Moderated
    Another test is as followings.

    session 1 : select col1 from big_test_table; -- this run first
    session 2 : truncate table big_test_table; -- this started to run in the MIDDLE of session1
    -- session 1 : select col1 from big_test_table;
    
    .
    .
    .
    .
    .
    07
    07
    07
    07
    07
    ERROR:
    ORA-08103: object no longer exists
    
    
    
    1470075 rows selected.
    
    Elapsed: 00:00:40.30
    17:36:39 SYS@PD1MGD>
    
    
    -- session 2 :
    
    17:17:27 HOHO@PD1MGD>truncate table big_test_table;
    
    Table truncated.
    
    Elapsed: 00:00:35.25
    17:36:58 HOHO@PD1MGD>
    I issued "truncate table big_test_table;" in session 2 while session1 is reading the table.

    After the table was truncated, the session 1 encountered "ORA-08103: object no longer exists" error.

    This is how oracle works?

    Best Regards.
  • 4. Re: Library cache Lock and Pin (select vs DDL)
    Aman.... Oracle ACE
    Currently Being Moderated
    See if this helps,
    http://uhesse.com/2009/10/27/dropping-a-table-during-select/

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points