Oracle truncate table operations

 

 

Oracle has its own unique practices for truncate table operations. It takes almost the same time to truncate dozens of data records and tens of millions of data records,

Oracle only updates the data dictionary and does not operate on the actual data. It only needs to pull back the hwm location.

 

Sys @ orcl> Create Table big_table as select * From all_objects;

 

Table created.

 

Sys @ orcl> insert into big_table select * From big_table;

 

68480 rows created.

 

Sys @ orcl> commit;

 

Commit complete.

 

Sys @ orcl> select count (*) from big_table;

 

Count (*)

----------

136960

 

 

Sys @ orcl> select object_id, data_object_id from dba_objects where owner = 'sys 'and object_name = 'Big _ table ';

 

Object_id data_object_id

------------------------

71485 71485

 

Sys @ orcl> select dbms_rowid.rowid_object (rowid) from big_table where rownum <10;

 

Dbms_rowid.rowid_object (rowid)

------------------------------

71485

71485

71485

71485

71485

71485

71485

71485

71485

 

9 rows selected.

 

 

Sys @ orcl> alter session set events '10046 trace name context forever, level 12 ';

 

Session altered.

 

Sys @ orcl> truncate table big_table;

 

Table truncated.

 

Sys @ orcl> alter session set events '10046 trace name context off ';

 

Session altered.

 

Check the data dictionary and find that the data_object_id has changed.

 

Sys @ orcl> select object_id, data_object_id from dba_objects where owner = 'sys 'and object_name = 'Big _ table ';

 

Object_id data_object_id

------------------------

71485 71486

 

 

Find the corresponding trace file:

 

Sys @ orcl> select spid from V $ process where ADDR in (select paddr from V $ session where Sid in (select Sid from V $ mystat where rownum <= 1 ));

 

Sys @ orcl> show parameter user_dump

 

Name type value

-----------------------------------------------------------------------------

User_dump_dest string/u01/APP/Oracle/diag/rdbms/ORC

L/orcl/Trace

 

Truncate table big_table

....

Update Tab $ set ts # =: 2, file # =: 3, block # =: 4, bobj # = decode (: 5, 0, null,: 5 ), tab # = decode (: 6, 0, null,: 6), intcols =: 7, kernelcols =: 8, clucols = decode (: 9, 0, null,: 9 ), audit $ =: 10, flags =: 11, pctfree $ =: 12, pctused $ =: 13, initrans =: 14, maxtrans =: 15, rowcnt =: 16, blkcnt =: 17, empcnt =: 18, avgspc =: 19, chncnt =: 20, avgrln =: 21, analyzetime =: 22, samplesize =: 23, cols =: 24, property =: 25, Degree = decode (: 26,1, null,: 26), instances = decode (: 27,1, null,: 27), dataobj # =: 28, avgspc_flb =: 29, flbcnt =: 30, trigflag =: 31, spare1 =: 32, spare2 = decode (: 33,0, null,: 33), spare4 =: 34, spare6 =: 35 where OBJ # =: 1

....

Update seg $ set type #=: 4, blocks =: 5, extents =: 6, minexts =: 7, maxexts =: 8, extsize =: 9, extpct =: 10, user # =: 11, iniexts =: 12, lists = decode (: 13,655 35, null,: 13), groups = decode (: 14,655 35, null,: 14 ), cachehint =: 15, hwmincr =: 16, spare1 = decode (: 17,0, null,: 17), scanhint =: 18, bitmapranges =: 19 Where ts # =: 1 and file # =: 2 and block # =: 3

...

Update OBJ $ set OBJ # =: 6, type # =: 7, ctime =: 8, mtime =: 9, stime =: 10, status =: 11, dataobj # =: 13, flags =: 14, oId $ =: 15, spare1 =: 16, spare2 =: 17 where owner # =: 1 and name =: 2 and namespace =: 3 and (remoteowner =: 4 or remoteowner is null and: 4 is null) and (linkname =: 5 or linkname is null and: 5 is null) and (subname =: 12 or subname is null and: 12 is null)

....