Home » RDBMS Server » Server Administration » Does a select statment hold a lock on the table?
Does a select statment hold a lock on the table? [message #273311] Tue, 09 October 2007 23:15 Go to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hi,all!
Does a select statment hold a lock on the table?
such as:
  select * from tname;

during the process,When there is a DDL on the table ,what will happy?
Thanks!
Regards!
Alan
Re: Does a select statment hold a lock on the table? [message #273313 is a reply to message #273311] Tue, 09 October 2007 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Does a select statment hold a lock on the table?
NO!
In Oracle readers do NOT block writers & writers do NOT block readers.

>When there is a DDL on the table ,what will happy? (happen?)
As a generalization, SELECT will not be impacted.
Re: Does a select statment hold a lock on the table? [message #273314 is a reply to message #273313] Tue, 09 October 2007 23:29 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

anacedent wrote on Tue, 09 October 2007 23:18


As a generalization, SELECT will not be impacted.


Thanks for your quikly reply!

But before the query finished,someone drop the table,I wonder how the query continue?where it find the data (that has been droped and space has been released) to process?

Regards!
Alan

[Updated on: Tue, 09 October 2007 23:30]

Report message to a moderator

Re: Does a select statment hold a lock on the table? [message #273315 is a reply to message #273311] Tue, 09 October 2007 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
I suggest that you stop asking RTFM questions.
Try actually studying on your own.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

Have you ever heard of UNDO and REDO log files?
Please explain what each does & how they are used.
Re: Does a select statment hold a lock on the table? [message #273316 is a reply to message #273315] Tue, 09 October 2007 23:39 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

anacedent wrote on Tue, 09 October 2007 23:33

I suggest that you stop asking RTFM questions.
Try actually studying on your own.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

Have you ever heard of UNDO and REDO log files?
Please explain what each does & how they are used.

Yes,I heard of UNDO and REDO.
But I don't think there is something related with REDO and UNDO.
DDL will not generate UNDO.
Regards!
Alan
Re: Does a select statment hold a lock on the table? [message #273319 is a reply to message #273311] Tue, 09 October 2007 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Are you claiming there is no difference between DROP TABLE CARE_LESS and TRUNCATE TABLE CARE_LESS with regard to existing/ongoing SELECT?

[Updated on: Tue, 09 October 2007 23:44] by Moderator

Report message to a moderator

Re: Does a select statment hold a lock on the table? [message #273323 is a reply to message #273319] Tue, 09 October 2007 23:55 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

anacedent wrote on Tue, 09 October 2007 23:43

Are you claiming there is no difference between DROP TABLE CARE_LESS and TRUNCATE TABLE CARE_LESS with regard to existing/ongoing SELECT?

No,I just want to know when a query is processing, some session drop the table,will it recieve an error?
Yes,I got it,I will recieved an error:
ORA-08103: object no longer exists.
Thanks!
Alan
Re: Does a select statment hold a lock on the table? [message #273343 is a reply to message #273323] Wed, 10 October 2007 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Behaviour depends on Oracle version (you didn't give it, with 4 decimals), parameters (rollback/undo), what you did (you didn't post it), access path (FTS, index) and so on.

Ana's answer is correct, if you just did a "select * from ..." then you get your answer even if someone drop your table. For instance:

-- Session 1
08:19:03 SQL> create table t (col integer);

Table created.

08:19:03 SQL> insert into t values (1);

1 row created.

08:19:03 SQL> insert into t values (2);

1 row created.

08:19:03 SQL> commit;

Commit complete.

08:19:03 SQL> declare
08:19:03   2    cursor c is select col from t;
08:19:03   3    v integer;
08:19:03   4  begin
08:19:03   5    open c;
08:19:03   6    loop 
08:19:03   7      dbms_lock.sleep (5);
08:19:03   8      fetch c into v;
08:19:03   9      exit when c%notfound;
08:19:03  10      dbms_output.put_line (to_char(sysdate,'HH24:MI:SS')||' get: '||v);
08:19:03  11    end loop;
08:19:03  12    dbms_lock.sleep (5);
08:19:03  13    close c;
08:19:03  14  end;
08:19:03  15  /
08:19:09 get: 1
08:19:14 get: 2

PL/SQL procedure successfully completed.

-- Sesssion 2
08:19:05 SQL>  drop table t purge;

Table dropped.


Regards
Michel
Re: Does a select statment hold a lock on the table? [message #273349 is a reply to message #273343] Wed, 10 October 2007 01:52 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Thanks,Michel!
It looks very intersting.
Where does oracle get the value '2'?
I dont think it was from UNDO Smile
Regards!
Alan
Re: Does a select statment hold a lock on the table? [message #273410 is a reply to message #273349] Wed, 10 October 2007 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually my example is constrained as all rows are in a single block.
So after the first read, the values are in a block that is in the SGA and even more in the process PGA.

I will soon build a more correct example with more blocks (more than is loaded in the SGA at first time), then we'll get an error "ORA-08103: object no longer exists".

Regards
Michel
Re: Does a select statment hold a lock on the table? [message #273427 is a reply to message #273410] Wed, 10 October 2007 07:34 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hi,Michel:
This are my test cases,the result are quiet deferents.
db oracle10.2.0.3
1:drop table
2:drop table purge
3:truncate table
  SQL> create table t1(id int);

Table created.

SQL> insert into t1 select object_id from all_objects;

50021 rows created.

SQL> commit;

case 1:
--session 1:
    declare cursor c is select id from t1;
    v int;
    con  int;
    begin
    con:=1;
   open c;
   dbms_lock.sleep(30); 
   loop
   con:=con+1;
   fetch c into v;
   exit when c%notfound;
   end loop;
  dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')|| ' rows fetched: '||con);
  end;
/
20:11:57SQL>  rows fetched: 50023
PL/SQL procedure successfully completed.
--session 2
20:12:10SQL> drop table t1;

case 2:
--session 1
20:12:10 SQL> /
20:12:40SQL> rows fetched:  3302
PL/SQL procedure successfully completed.
--session 2
20:12:20SQL> drop table t1 purge;

case 3
--session 1
19:41:39 SQL> /
    declare cursor c is select id from t1;
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 12

--session 2
19:41:39 SQL>truncate table t1;

I found
1:when I do drop ,select will fetch all rows even the table was drop before the fetch started.
2:when I do drop ... purge,select will only fetch fetch 3302 rows .
and 1 and 2 didn't return a ORA-08103 error;
3:when I do truncate table, the fetch result with error ora-08103.
Could you give me a explain for this,I am very confuzed.
Regards!
Alan.
Re: Does a select statment hold a lock on the table? [message #273430 is a reply to message #273349] Wed, 10 October 2007 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There it is:
-- Session 1
14:12:41 SQL> create table t as 
14:12:41   2    select * 
14:12:41   3    from (select rownum objid, o.* from dba_objects o order by object_id) 
14:12:41   4    where rownum <= 6000
14:12:41   5  /

Table created.

14:12:42 SQL> select max(extent_id) extent_id, file_id, block_id, max(blocks) blocks,
14:12:42   2         min(objid) min_id, max(objid) max_id 
14:12:42   3  from dba_extents e, t
14:12:42   4  where e.owner=user and e.segment_name='T' 
14:12:42   5    and dbms_rowid.rowid_relative_fno(t.rowid) = file_id
14:12:42   6    and dbms_rowid.rowid_block_number(t.rowid) between block_id and block_id+blocks-1
14:12:42   7  group by file_id, block_id
14:12:42   8  order by file_id, block_id
14:12:42   9  /
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS     MIN_ID     MAX_ID
---------- ---------- ---------- ---------- ---------- ----------
         0          4      10273          8          1        415
         1          4      10281          8        416       1037
         2          4      10289          8       1038       1578
         3          4      10297          8       1579       2197
         4          4      10305          8       2198       2743
         5          4      10313          8       2744       3417
         6          4      10321          8       3418       3947
         7          4      10329          8       3948       4572
         8          4      10337          8       4573       5100
         9          4      10345          8       5101       5690
        10          4      10353          8       5691       6001

11 rows selected.

14:12:46 SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

14:12:46 SQL> set time on
14:12:46 SQL> declare
14:12:46   2    cursor c is select objid from t;
14:12:46   3    v  pls_integer;
14:12:46   4    nb pls_integer := 0;
14:12:46   5  begin
14:12:46   6    open c;
14:12:46   7    loop 
14:12:46   8      dbms_lock.sleep (0.1);
14:12:46   9      nb := nb + 1;
14:12:46  10      fetch c into v;
14:12:46  11      exit when c%notfound;
14:12:46  12    end loop;
14:12:46  13    dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got '||nb||' rows');
14:12:46  14    close c;
14:12:46  15  exception when others then
14:12:46  16    dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got error '||sqlcode||' at row '||nb);
14:12:46  17    raise;
14:12:46  18  end;
14:12:46  19  /
14:22:57 Got error -8103 at row 5691
declare
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 17


14:22:57 SQL> alter session set events '10046 trace name context off';

Session altered.

-- Session 2
14:12:59 SQL>  drop table t purge;

Table dropped.

Actually trace shows that process still read extent per extent for all extents it has in memory from the first access.
Here's an abstract of the file:
PARSING IN CURSOR #4 len=19 dep=1 uid=49 oct=3 lid=49 tim=2341253732 hv=3872157983 ad='1ee9bbbc'
SELECT OBJID FROM T
END OF STMT
PARSE #4:c=15625,e=22047,p=0,cr=60,cu=0,mis=1,r=0,dep=1,og=1,tim=2341253726
BINDS #4:
EXEC #4:c=0,e=49,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=2341253883
WAIT #3: nam='PL/SQL lock timer' ela= 98179 duration=10 p2=0 p3=0 obj#=48672 tim=2341352853
>>> First rows accessed without file access as they are loaded in SGA <<<
>>> during parse along with segment header and bitmap <<<
FETCH #4:c=0,e=74,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=1,tim=2341353047
WAIT #3: nam='PL/SQL lock timer' ela= 108451 duration=10 p2=0 p3=0 obj#=48672 tim=2341461586
...
WAIT #3: nam='PL/SQL lock timer' ela= 109159 duration=10 p2=0 p3=0 obj#=48672 tim=2356555232
>>> Get the next 4 blocks till the end of first extent <<<
WAIT #4: nam='db file scattered read' ela= 8066 file#=4 block#=10277 blocks=4 obj#=48672 tim=2356564289
FETCH #4:c=0,e=8242,p=4,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2356564389
WAIT #3: nam='PL/SQL lock timer' ela= 100070 duration=10 p2=0 p3=0 obj#=48672 tim=2356664557
FETCH #4:c=0,e=32,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2356664730
WAIT #3: nam='PL/SQL lock timer' ela= 109160 duration=10 p2=0 p3=0 obj#=48672 tim=2356773991
...
WAIT #3: nam='PL/SQL lock timer' ela= 10669 duration=1 p2=0 p3=0 obj#=48672 tim=2386435180
FETCH #4:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2386435318
WAIT #3: nam='PL/SQL lock timer' ela= 100375 duration=10 p2=0 p3=0 obj#=48672 tim=2386535768
WAIT #3: nam='PL/SQL lock timer' ela= 10666 duration=1 p2=0 p3=0 obj#=48672 tim=2386546508
>>> Get the next extent (8 blocks) <<<
WAIT #4: nam='db file scattered read' ela= 2482 file#=4 block#=10281 blocks=8 obj#=48672 tim=2386549189
FETCH #4:c=0,e=2661,p=8,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2386549281
WAIT #3: nam='PL/SQL lock timer' ela= 99674 duration=10 p2=0 p3=0 obj#=48672 tim=2386649047
...
WAIT #3: nam='PL/SQL lock timer' ela= 10624 duration=1 p2=0 p3=0 obj#=48672 tim=2453081011
FETCH #4:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2453081147
WAIT #3: nam='PL/SQL lock timer' ela= 100691 duration=10 p2=0 p3=0 obj#=48672 tim=2453181910
WAIT #3: nam='PL/SQL lock timer' ela= 10447 duration=1 p2=0 p3=0 obj#=48672 tim=2453192418
>>> Get the next extent <<<
WAIT #4: nam='db file scattered read' ela= 15820 file#=4 block#=10290 blocks=7 obj#=48672 tim=2453208433
FETCH #4:c=0,e=16001,p=7,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2453208531
WAIT #3: nam='PL/SQL lock timer' ela= 99821 duration=10 p2=0 p3=0 obj#=48672 tim=2453308490

This until it needs to re-read the segment header (block 10275):
...
WAIT #3: nam='PL/SQL lock timer' ela= 100392 duration=10 p2=0 p3=0 obj#=48672 tim=2888674996
WAIT #3: nam='PL/SQL lock timer' ela= 11361 duration=1 p2=0 p3=0 obj#=48672 tim=2888686446
>>> Get the next extent <<<
WAIT #4: nam='db file scattered read' ela= 670 file#=4 block#=10345 blocks=8 obj#=48672 tim=2888687315
FETCH #4:c=0,e=820,p=8,cr=1,cu=0,mis=0,r=1,dep=1,og=1,tim=2888687381
WAIT #3: nam='PL/SQL lock timer' ela= 99821 duration=10 p2=0 p3=0 obj#=48672 tim=2888787281
...
WAIT #3: nam='PL/SQL lock timer' ela= 100371 duration=10 p2=0 p3=0 obj#=48672 tim=2951895997
>>> Get segment header block <<<
WAIT #4: nam='db file sequential read' ela= 12644 file#=4 block#=10275 blocks=1 obj#=48672 tim=2951908802
>>> Get segment header (from exception handler) <<<
WAIT #4: nam='db file sequential read' ela= 770 file#=4 block#=10275 blocks=1 obj#=48672 tim=2951909675
----- Call Stack Trace -----
...
----- End of Call Stack Trace -----
FETCH #4:c=46875,e=137753,p=2,cr=3,cu=1,mis=0,r=0,dep=1,og=1,tim=2952033868
EXEC #3:c=156250,e=610828889,p=74,cr=5756,cu=1,mis=0,r=0,dep=0,og=1,tim=2952056020
ERROR #3:err=8103 tim=122964187

At this time it sees that the object no more exists.
Note that I used ASSM, this explain that segment header is at segment block 2 and not segment block 0, the 2 first blocks are used for segment bitmap. A test with MSSM shows that error raises at row 6172 with block size 8k, just in case you want to reproduce you have to build a table with a little more rows. This is still first row of extent 10.

Regards
Michel

[Edit: last sentence]

[Updated on: Wed, 10 October 2007 08:52]

Report message to a moderator

Re: Does a select statment hold a lock on the table? [message #273443 is a reply to message #273430] Wed, 10 October 2007 09:00 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Greatly done,Michel!
I will repreduce it on my server to understand it.
And coud you kindly read my test cases and give some comments.
Regards!
Alan

Re: Does a select statment hold a lock on the table? [message #273481 is a reply to message #273443] Wed, 10 October 2007 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Case 1 is meaningless, it shows that your session 1 ends at 20:11:57 and the drop table is at 20:12:10, so afterward.
In addition, when recyclebin is on table is not dropped but just renamed so you still can query it.

Case 2, as I showed it if your table is not large enough you can query the whole table.
By the way how could you get 50000+ rows the fist time and 3300 the second one if it is the same test?

Case 3, it is the same as drop table purge: extents are invalidated but Oracle only sees it when it reread segment header.

Regards
Michel

[Updated on: Wed, 10 October 2007 10:53]

Report message to a moderator

Re: Does a select statment hold a lock on the table? [message #273549 is a reply to message #273481] Wed, 10 October 2007 21:06 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

By the way how could you get 50000+ rows the fist time and 3300 the second one if it is the same test?

Yes,it is the same test,the only different is case 1 I use drop table ,and case 2 I user drop ...purge.
I try it for some times,the result is the same ,and I alse want case 2 will rise a 8103 error,but it doesn't.
Regards!
Alan
Re: Does a select statment hold a lock on the table? [message #273570 is a reply to message #273481] Wed, 10 October 2007 23:27 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

A new test:
   SQL> create table t1 as select object_id from all_objects order by object_id;
    Table created.
    
    SQL> set serveroutput on;
    sql>declare cursor c is select id from t1;
    1 v int;
    2 con  int;
    3 begin
    4 con:=1;
    5 open c;
    6 dbms_lock.sleep(40); 
    7 loop
    8 con:=con+1;
    9 fetch c into v;
   10 exit when c%notfound;
   11 end loop;
  12  dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')|| ' rows       fetched: '||con);
  13  end;
    11:03:35 SQL> /

    11:04:19 rows fetched: 3287                                                    

    --the totals rows was 50021.

    PL/SQL procedure successfully completed.

     --session 2

    11:03:42 SQL> drop table t1 purge;

    Table dropped.

    11:04:03 SQL>




Re: Does a select statment hold a lock on the table? [message #273598 is a reply to message #273570] Thu, 11 October 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the tablespace type, segment space management, block size...?
Activate the trace and see what happens.

Regards
Michel
Re: Does a select statment hold a lock on the table? [message #273637 is a reply to message #273598] Thu, 11 October 2007 02:54 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Here is some infomation and 10046 trace file
15:22:48 SQL>  create table t1 as select * from  (select rownum objid,o.* from all_objects o order by o.object_id);

Table created.

15:23:25 SQL> select tablespace_name from user_tables where table_name='T1';

TABLESPACE_NAME                                                                 
------------------------------                                                  
TEST                                                                            

15:24:07 SQL> col tablespace_name for a10;
15:25:08 SQL> select tablespace_name,block_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name='TEST';

TABLESPACE BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN                               
---------- ---------- ---------- --------- ------                               
TEST             8192 LOCAL      SYSTEM    AUTO                                 



15:44:28 SQL> select max(extent_id) extent_id,file_id,block_id,max(blocks) blocks,min(objid)min_id,max(objid) max_id from dba_extents e,t1
15:45:22   2  where e.owner='TEST' and segment_name='T1'
15:45:22   3  	and
15:45:22   4  	dbms_rowid.rowid_relative_fno(t1.rowid) = file_id
15:45:22   5  	and
15:45:22   6  	dbms_rowid.rowid_block_number(t1.rowid) between block_id and block_id+blocks-1
15:45:22   7  	group by file_id,block_id
15:45:22   8  	order by file_id,block_id
15:45:22   9  /

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS     MIN_ID     MAX_ID               
---------- ---------- ---------- ---------- ---------- ----------               
         5          4        777          8       2750       3420               
         6          4        785          8       3421       3952               
         7          4        793          8       3953       4577               
         8          4        801          8       4578       5100               
         9          4        809          8       5101       5699               
        10          4        817          8       5700       6250               
        11          4        825          8       6251       6874               
        12          4        833          8       6875       7404               
        13          4        841          8       7405       7989               
        14          4        849          8       7990       8520               
        15          4        857          8       8521       9145               

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS     MIN_ID     MAX_ID               
---------- ---------- ---------- ---------- ---------- ----------               
         0          4        913          8          1        417               
         1          4       1001          8        418       1040               
         2          4       1009          8       1041       1582               
         3          4       1017          8       1583       2200               
         4          4       1025          8       2201       2749               
        16          4       1033        128       9146      17623               
        17          4       1161        128      17624      26097               
        18          4       1289        128      26098      34537               
        19          4       1417        128      34538      43168               
        20          4       1545        128       9081      50021               

21 rows selected.



--session 1


SQL> set serveroutput on;
SQL> set time on;
15:43:56 SQL> alter session set events '10046 trace name context forever,level 1
2';

Session altered.

15:44:50 SQL>
15:46:32 SQL>
15:46:32 SQL> ed
Wrote file afiedt.buf

  1   declare
  2     cursor c is select object_id from t1;
  3     v  pls_integer;
  4     nb pls_integer := 0;
  5   begin
  6     open c;
  7     dbms_lock.sleep (40);
  8     loop
  9       nb := nb + 1;
 10       fetch c into v;
 11       exit when c%notfound;
 12     end loop;
 13     dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got '||nb||' rows'
);
 14     close c;
 15   exception when others then
 16     dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got error '||sqlco
de||' at row '||nb);
 17     raise;
 18*  end;
15:46:50  19  /
15:47:34 Got 418 rows

PL/SQL procedure successfully completed.

15:47:34 SQL>


--session 2
15:46:55 SQL> drop table t1 purge;

Table dropped.

15:47:14 SQL> spool off;

[Updated on: Thu, 11 October 2007 02:55]

Report message to a moderator

Re: Does a select statment hold a lock on the table? [message #273641 is a reply to message #273637] Thu, 11 October 2007 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I can't download the file by this time.
Have a look to (post ?) the last relevant lines to check why it stops at first row of second extent (is there a read? on which block?...)

Regards
Michel
Re: Does a select statment hold a lock on the table? [message #273647 is a reply to message #273641] Thu, 11 October 2007 03:22 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hi,Mechel!
I notice when I:
 declare
   cursor c is select object_id from t1;
   v  pls_integer;
   nb pls_integer := 0;
 begin
   open c;
   dbms_lock.sleep (40);<< put sleep before fetch start
   loop
.
.
. 


the result is:
15:46:50  19  /
15:47:34 Got 418 rows
PL/SQL procedure successfully completed.
15:47:34 SQL>

and the context of trace file is quiet defferent from your,the part of 'select object_id from t1' is very small,I post it here:
PARSING IN CURSOR #9 len=210 dep=2 uid=0 oct=3 lid=0 tim=21553527761 hv=864012087 ad='65a5f2b0'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #9:c=0,e=101,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=21553527751
BINDS #9:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=07ed08d0  bln=22  avl=04  flg=05
  value=54914
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=07ed08ac  bln=24  avl=02  flg=05
  value=5
EXEC #9:c=0,e=406,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=21553528517
WAIT #9: nam='db file sequential read' ela= 33809 file#=1 block#=64530 blocks=1 obj#=355 tim=21553562452
FETCH #9:c=0,e=33999,p=1,cr=2,cu=0,mis=0,r=0,dep=2,og=3,tim=21553562584
STAT #9 id=1 cnt=0 pid=0 pos=1 obj=255 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=1 pw=0 time=34015 us)'
STAT #9 id=2 cnt=0 pid=1 pos=1 obj=257 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=1 pw=0 time=33994 us)'
=====================
---[B]this part,I think:[/B]
PARSING IN CURSOR #5 len=24 dep=1 uid=62 oct=3 lid=62 tim=21553563521 hv=346809284 ad='655476e4'
SELECT OBJECT_ID FROM T1
END OF STMT
PARSE #5:c=50072,e=524108,p=290,cr=62,cu=0,mis=1,r=0,dep=1,og=1,tim=21553563509
BINDS #5:
EXEC #5:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=21553563772
--[B]end [/B]
=====================
PARSING IN CURSOR #6 len=132 dep=1 uid=0 oct=3 lid=0 tim=21553564003 hv=2328831744 ad='65f4e068'
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #6:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=21553563994
BINDS #6:


and when I put the sleep into the loop :
 declare
   cursor c is select object_id from t1;
   v  pls_integer;
   nb pls_integer := 0;
 begin
   open c;
   loop
   dbms_lock.sleep (0.01);<< put sleep into loop
.
.
. 

I got the error as your test:
16:06:14 SQL> ed
Wrote file afiedt.buf

  1   declare
  2     cursor c is select object_id from t1;
  3     v  pls_integer;
  4     nb pls_integer := 0;
  5   begin
  6     open c;
  7     loop
  8       dbms_lock.sleep (0.01);
  9       nb := nb + 1;
 10       fetch c into v;
 11       exit when c%notfound;
 12     end loop;
 13     dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got '||nb||' rows'
);
 14     close c;
 15   exception when others then
 16     dbms_output.put_line(to_char(sysdate,'HH24:MI:SS')||' Got error '||sqlco
de||' at row '||nb);
 17     raise;
 18*  end;
16:07:13  19  /
16:08:26 Got error -8103 at row 5700
 declare
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at line 17


The trace file is also the same:

=====================
PARSING IN CURSOR #4 len=210 dep=2 uid=0 oct=3 lid=0 tim=21990584442 hv=864012087 ad='65a5f2b0'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
EXEC #4:c=0,e=414,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,tim=21990584432
WAIT #4: nam='db file sequential read' ela= 34095 file#=1 block#=64530 blocks=1 obj#=54915 tim=21990618753
FETCH #4:c=0,e=34267,p=1,cr=2,cu=0,mis=0,r=0,dep=2,og=3,tim=21990618873

--this part we want ,I think
=====================
PARSING IN CURSOR #8 len=24 dep=1 uid=62 oct=3 lid=62 tim=21990619902 hv=346809284 ad='65ca1b80'
SELECT OBJECT_ID FROM T1
END OF STMT
PARSE #8:c=10014,e=511084,p=292,cr=64,cu=0,mis=1,r=0,dep=1,og=1,tim=21990619890
BINDS #8:
EXEC #8:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=21990620160
=====================

--end

PARSING IN CURSOR #7 len=132 dep=1 uid=0 oct=3 lid=0 tim=21990620410 hv=2328831744 ad='65f4e068'
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #7:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=21990620401
BINDS #7:


Regards!
Alan
Re: Does a select statment hold a lock on the table? [message #273660 is a reply to message #273647] Thu, 11 October 2007 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the answer seems to be clear: behaviour depends on what is in your process memory and cleaning process concurrency.
I expect an error instead of an erroneous result.
Maybe it is time to open a SR and raise a bug (but I doubt it will be fixed).

Regards
Michel
Re: Does a select statment hold a lock on the table? [message #273668 is a reply to message #273660] Thu, 11 October 2007 04:58 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

I think i would open a sr soon.
But why the contents of my trace file are defferent as that of yours?
Regards!
Alan

Re: Does a select statment hold a lock on the table? [message #273672 is a reply to message #273668] Thu, 11 October 2007 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I don't see in what it is different.
The only interesting things are about cursor #5, skip the rest and post what is different.

Regards
Michel
Re: Does a select statment hold a lock on the table? [message #273680 is a reply to message #273672] Thu, 11 October 2007 06:03 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hi,Michel!
I think I don't understat the trace file,maybe I would read some doc about it:(
How can I find which part are caused by this query.
I mean what the meaning of the number in the trace file,such as:
cursor #1,#2...,does every number represent a sql?
So my sql here is CURSOR #5,so every CURSOR #5 is represent this sql?
Hope you can understand me:(
Regards!
Alan
Re: Does a select statment hold a lock on the table? [message #273685 is a reply to message #273680] Thu, 11 October 2007 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you have to read about trace file, see Metalink note Interpreting Raw SQL_TRACE, n39817.1

Regards
Michel
Re: Does a select statment hold a lock on the table? [message #273695 is a reply to message #273685] Thu, 11 October 2007 06:36 Go to previous message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hi,Michel!
Very thanks for your gread help!
Regards!
Alan
Previous Topic: Error - Invalid staging area
Next Topic: init.ora
Goto Forum:
  


Current Time: Wed Dec 07 08:48:28 CST 2016

Total time taken to generate the page: 0.22757 seconds