Hello,Tom!
I am very lucky to get this chance.I have 2 quesitons:
1:If a table have a fk,when I do insert /*+ append */ into tname select ...,oracle will ignore the hint ,yes?
2:I know ,a select will not hold a lock a the table,so I want to know ,when a select is onging,before it finished,we drop or truncate the table ,what will happen,so I did these cases below:
DB: 10.2.0.3 EE
case 1:drop table before select finished.
case 2: drop table purge before select finished
case 3: truncage table before select finished.
--Here are cases:
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 <<all rows fetched
PL/SQL procedure successfully completed.
--session 2
20:11:20SQL> drop table t1;
case 2:
--session 1
20:14:10 SQL> /
20:14:40SQL> rows fetched: 3302 <<only fetchs 3302 rows and no error.
PL/SQL procedure successfully completed.
--session 2
20:14:20SQL> drop table t1 purge;
case 3
--session 1
20:16:39 SQL> /
declare cursor c is select id from t1;
*
ERROR at line 1:
ORA-08103: object no longer exists <<error 8103 rised
ORA-06512: at line 12
--session 2
20:16:50 SQL>truncate table t1;I noticed:
--when I do drop ,select will fetch all rows even the table was drop before the fetch started.
--when I do drop ... purge,select will only fetch fetch 3302 rows .
and 1 and 2 didn't return a ORA-08103 error;
--when I do truncate table, the fetch result with error ora-08103.
Could you tell me what exactly oracle does in these cases?Why case 2 ,fetch only get 3302 rows,but does not rise ora-08103?
1) correct, the foreign key will prevent the direct path insert, it will be a conventional path insert, you can observe this easily:
ops$tkyte%ORA10GR2> create table p ( x int primary key );
Table created.
ops$tkyte%ORA10GR2> create table c1 ( x references p );
Table created.
ops$tkyte%ORA10GR2> create table c2 ( x int );
Table created.
ops$tkyte%ORA10GR2> insert into p select rownum from all_users;
57 rows created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> insert /*+ append */ into c1 select * from p;
57 rows created.
ops$tkyte%ORA10GR2> insert /*+ append */ into c2 select * from p;
57 rows created.
ops$tkyte%ORA10GR2> select count(*) from c1;
COUNT(*)
----------
57
<b>the fact we read that that implies it was not direct pathed - because if it was, we would get:</b>
ops$tkyte%ORA10GR2> select count(*) from c2;
select count(*) from c2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
<b>that error, until we commit the modification:</b>
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> select count(*) from c2;
COUNT(*)
----------
57
2) the answer is "it depends"
If you drop/truncate the table after opening the cursor, but before you fetch - you will likely get the 8103.
If you do not get the 8103 error, it is likely you will fetch the data, unless something comes along and reuses the space (as you emulated).
Since we do not do DDL in a production system :) this is a purely academic question of course.