4 Replies Latest reply on Dec 4, 2012 8:52 AM by Aman....

    Library cache Lock and Pin (select vs DDL)

    872581
    872581 Dec 4, 2012 6:12 AM
      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
      • 212 Views
      • Tags:
        • 1. Re: Library cache Lock and Pin (select vs DDL)
          Purvesh K
          Purvesh K Dec 4, 2012 6:49 AM (in response to 872581)
          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?
          1 person found this helpful
          • 2. Re: Library cache Lock and Pin (select vs DDL)
            872581
            872581 Dec 4, 2012 7:42 AM (in response to Purvesh K)
            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
              872581 Dec 4, 2012 8:42 AM (in response to 872581)
              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....
                Aman.... Dec 4, 2012 8:52 AM (in response to 872581)
                See if this helps,
                http://uhesse.com/2009/10/27/dropping-a-table-during-select/

                Aman....