This content has been marked as final.
Show 4 replies
-
1. Re: Library cache Lock and Pin (select vs DDL)
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.1 person found this helpful
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 : 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>
Did you check the trace file for the particular session? -
2. Re: Library cache Lock and Pin (select vs DDL)
872581 Dec 4, 2012 7:42 AM (in response to Purvesh K)Thanks for your reply.
Session 1 took 7.14 seconds and it finished at "11:12:34", and thus, session 1 started at "11:12:27".It doesn't look like the timing of your Alter statement overlaps with the SELECT statement. and there is no error displayed too.
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 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
I issued "truncate table big_test_table;" in session 2 while session1 is reading the table.-- 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>
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.... 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....