Borndba

When you want to return a table in a function, Ref cursor is preferred compared to Plsql table.
If you return a ref cursor, result will be send to the end user in chunk instead of sending full result set. So to the end user, it will appear to be faster.
Additionally, if I was to return an array -- I would have to fetch all of the rows, allocate
storage in plsql for them, copy the data there and then send that data back over the network in one
big chunk where the client would have to allocate storage to hold the whole thing in memory.  With
a ref cursor -- i send back a pointer (don't have the overhead of allocating the storage in plsql
and copying all of the data into it).  I send rows in chunks -- not all at once (10 rows at a time
by default -- you control the size).  I don't have to allocate memory to hold the entire thing in
the client -- just a slice of it.

A ref cursor is infintely more scalable then sending a data structure back and forth for these
reasons.

Pctused,freelist and freelist groups has become obsolete with introduction of ASSM.
The Pctfree is still valid because it is not a space management tool. It still used to tackle row migration.
There are 6 different freeness statuses when a tablespace is declared with ASSM.
0 = unformatted, 1 = logically full, 2 = 0-25% free, 3 = 25-50% free, 4 = 50%-75% free, 5= 75-100% free.
Theoretically a block is considered 90% full (FS=2), if the free space is 0-25%. However if the pctfree is declared more than 10% and freeness falls below pctfree, that block is considered full. This is irrespective of how much the block is full. You can track the behaviors in Event 10612 and Event 10613.

When there is switches in Primary will there be switches in Standby also? This question was itching me for sometime. Not able to get these detail anywhere. Tried asking few expert in DG. But didn’t get any convincing answer. Luckily got the answer to this question in corner of Oracle Doc 10g. “Physical standby db doesn’t have redolog associated with it”.

It means changes will be applied to physical standby datafiles directly from
archived log files or standby redolog files. So no redo generation in managed recovery stage. But when the standby is opened in read-only mode, there might be redo generation which will get into redolog. These redo may be from dictionary changes ( Not sure delayed block cleanout come into play in standby). Like oracle keep increasing the SCN even in read-only or there could be other internal reasons.

But redolog is required for logical standby as the db will be opened for read/write.

Before any changes getting applied to standby from standby redo log files, it has to be archived. However if you have enabled real-time apply the changes will get applied to datafiles from standby redolog file itself and oracle doesn’t wait for standby redo log to get archived.

To enabled real-time apply,

in physical standby,
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT;

in logical standby,
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

If you want to know whether real-time apply is enabled query recovery_mode.V$ARCHIVE_DEST_STATUS.

But need to check how real-time apply will work. Whether real time apply is 10g feature?

In 10g, oracle allows us to bring the HWM down. Prerequest; table should be in ASSM and compatible should be =>10.

I am using Dbms_Space package to show the effect of Shrink command. Please refer to dbms_space.sql script below.

During shrink there are 2 phases.
1. Compact the space. This involves moving all the rows to blocks in beginning of the segments. This will be done using insert/delete. These insert/deletes will be done in several small unit transactions. So, there will be locks in rows/blocks during this phase, but it will be short lived and won’t affect the concurrent transactions. Also please note any dml trigger will not get fired by this insert/delete operations as it happens internally.

2. Bringing the HWM down. This requires exculusive locks. This phase affects the availability. But this will be for very short interval. As it requires basically updating some records in dictionary.

--dbms_space.sql

declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('&1', '&2', '&3', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
         dbms_output.put_line('FS1 Blocks       = '||v_fs1_blocks);
         dbms_output.put_line('FS2 Blocks       = '||v_fs2_blocks);
         dbms_output.put_line('FS3 Blocks       = '||v_fs3_blocks);
         dbms_output.put_line('FS4 Blocks       = '||v_fs4_blocks);
         dbms_output.put_line('Full Blocks       = '||v_full_blocks);
end;
/

satheeshb@10.2.0.2.0> @dbms_space.sql  "SATHEESHB" "T3" "TABLE"
FS1 Blocks       = 0
FS2 Blocks       = 45
FS3 Blocks       = 45
FS4 Blocks       = 295
Full Blocks       = 7739
PL/SQL procedure successfully completed.
Note:
FS1 => Blocks which are 0-25% free space
Fs2 => Blocks which are 25-50% free space
Fs3 => Blocks which are 50-75% free space
Fs4 => Blocks which are 75-99% free space
Full Blocks => Blocks which are 100% free space

satheeshb@10.2.0.2.0> alter table t3 shrink space compact;
alter table t3 shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

Note: You need to enable row movement for the Shrink command to work. 

satheeshb@10.2.0.2.0> alter table t3 enable row movement;
Table altered.

satheeshb@10.2.0.2.0>  alter table t3 shrink space compact;
Table altered.
Note: The above statement is phase 1 of shrink operation. These moves the data to beginning of the segment using simple insert/delete. 

satheeshb@10.2.0.2.0> @dbms_space  "SATHEESHB" "T3" "TABLE"
FS1 Blocks       = 2
FS2 Blocks       = 0
FS3 Blocks       = 0
FS4 Blocks       = 402
Full Blocks       = 7720
PL/SQL procedure successfully completed.

satheeshb@10.2.0.2.0> alter table t3 shrink space;

Table altered.

Note: This is the Second phase of shrink operation. This involves exclusive lock. Only this stage affects the availablity. This will be quick phase, as it as to adjust some dictionary information.
 

satheeshb@10.2.0.2.0> @dbms_space  "SATHEESHB" "T3" "TABLE"
FS1 Blocks       = 2
FS2 Blocks       = 0
FS3 Blocks       = 0
FS4 Blocks       = 0
Full Blocks       = 7720
PL/SQL procedure successfully completed.

Index rebuild seems to make sense in some extreme condition. There were many heated discussion on index rebuild in oracle community. Some say height should be considered before rebuild , some other says blevel should be considered.
The function “sys_op_lbid” gets number of rows in each leaf block of index. By seeing how the rows are distributed across the leaf blocks, one can judge whether index needs rebuilding/coalesce or not.

I am still not convinced that rebuilding index is ever required. As there will be spike in redo due to index-splic block. LBID in SYS_OP_LBID should be “leaf block id”. I will test this when i have free time to burn.

If you set Plsql_Optimize_Level =2, oracle can rewrite your code to give best output. If you want your package to have the advantage of POL, define it while compiling itself. Since oracle will reqwrite the code during compilation stage and not during execution stage. I have noticed some package taking long time to compile when set to POL=2. So be aware of this little trap.

satheeshb@10.2.0.2.0> show parameter plsql_optimize_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_optimize_level                 integer     2

satheeshb@10.2.0.2.0> declare
  2    lv number;
  3  begin
  4    lv:=0;
  5     for i in (select rownum r1 from t3) loop
  6          i.r1 := i.r1;
  7          lv := lv+1;
  8          exit when lv=200;
  9     end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

satheeshb@10.2.0.2.0> select rows_processed,fetches from v$sql
  2* where lower(sql_text)='select rownum r1 from t3';

ROWS_PROCESSED    FETCHES
-------------- ----------
           200          2


Note the number of fetches above.

satheeshb@10.2.0.2.0> alter system set plsql_optimize_level=1 scope=memory;

System altered.

satheeshb@10.2.0.2.0> declare
  2    lv number;
  3  begin
  4    lv:=0;
  5     for i in (select no r1 from t3) loop
  6          i.r1 := i.r1;
  7          lv := lv+1;
  8          exit when lv=200;
  9     end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

satheeshb@10.2.0.2.0> select rows_processed,fetches from v$sql
  2   where lower(sql_text)='select no r1 from t3';

ROWS_PROCESSED    FETCHES
-------------- ----------
           200        200

satheeshb@10.2.0.2.0> 

See the spike in number of fetches when the optimize level is changed.

When “Plsql_optimizer_level=2″ the number of fetch is TWO, however for Plsql_Optimizer_Level=1 the fetch is 200. One interesting point to note here is per fetch 100 rows. Not sure why is that magic number Hundred..

If the sysdate of OS is by mistake is not correct, can lead to lot of confusion in database involving financial transaction. One way to prevent that is using Fixed_date parameter.

satheeshb@10.2.0.2.0> alter session set nls_date_format='dd/mon/yyyy hh24:mi:ss';

Session altered.

satheeshb@10.2.0.2.0> select sysdate from dual;

SYSDATE
--------------------
07/oct/2006 04:02:15

satheeshb@10.2.0.2.0> alter system set fixed_date='2001-11-01' scope=memory;

System altered.

satheeshb@10.2.0.2.0> alter session set nls_date_format='dd/mon/yyyy hh24:mi:ss';

Session altered.

satheeshb@10.2.0.2.0> select sysdate from dual;

SYSDATE
--------------------
01/nov/2001 00:00:00

satheeshb@10.2.0.2.0> alter session set  fixed_date='2001-11-01' scope=memory;
alter session set  fixed_date='2001-11-01' scope=memory
                   *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

This rises lot of questions regarding security. So never give “ALTER SYSTEM” privs to non-dba users. However alter session will not change this parameter.
Neverever give “ALTER SYSTEM” privs to developer account. There are many other security violation involving “ALTER SYSTEM’.

Freelist will not have unused blocks.

Only used blocks, which falls below pctused will get into Transaction freelist. If oracle not able find the blocks below hwm or freelist, it will bump the HWM level by 1 block for 5 blocks and there after by greater of 5 blocks or “_bump_highwater_mark_count” blocks (which defaults to 0) times the number of process free lists plus 1 (for the master free list), up to the number of blocks remaining in the extent.

Below is a small demonstration of only used blocks getting into Transaction Freelist. You need to use ASSM, as MSSM doesnt have Freelists,Freelist groups and PctUsed.

satheeshb@10.2.0.2.0> drop table t purge;

Table dropped.

Elapsed: 00:00:01.10
satheeshb@10.2.0.2.0> create table t tablespace system as select * from all_objects;

Table created.

Elapsed: 00:00:05.18
satheeshb@10.2.0.2.0> analyze table t compute statistics;

Table analyzed.

Elapsed: 00:00:01.33
satheeshb@10.2.0.2.0> select num_freelist_blocks from user_tables where table_name='T';

NUM_FREELIST_BLOCKS
-------------------
                  0

Elapsed: 00:00:00.08
satheeshb@10.2.0.2.0> delete t ;

49302 rows deleted.

Elapsed: 00:00:16.78
satheeshb@10.2.0.2.0> analyze table t compute statistics;

Table analyzed.

Elapsed: 00:00:01.39
satheeshb@10.2.0.2.0> select num_freelist_blocks from user_tables where table_name='T';

NUM_FREELIST_BLOCKS
-------------------
                677

Elapsed: 00:00:00.02

There are 3 kind of freelists in oracle, Master/Process/Transactions Freelist. The freelist shows in Num_Freelists_blocks is “Transaction Freelists”. Transaction Freelist is the one which you define in Create/Alter table command. Unused blocks will not get into Transaction Freelist, however i believe they get into Master/Process Freelist. Need to test the uses of Master/Process freelist.

If a tablespace is having more than one datafile and extend of an object will be allocated in round robin fashion to each of the datafiles. However there are various factors which will affect this round robin fashion like how other objects in this tablespace grows, autoextend on or off of a datafiles, any space getting available in the existing datafiles etc.,

satheeshb@10.2.0.2.0> create tablespace s datafile 'c:1.dbf' size 1m,
  2  'c:2.dbf' size 1m,
  3  'c:3.dbf' size 1m,
  4  'c:4.dbf' size 1m,
  5  'c:5.dbf' size 1m
  6* extent management local uniform size 64k;

Tablespace created.

satheeshb@10.2.0.2.0> select file_id from dba_data_files where tablespace_name='S';

   FILE_ID
----------
         5
         6
         7
         8
         9

satheeshb@10.2.0.2.0> drop table s1;

Table dropped.

satheeshb@10.2.0.2.0> create table s1 ( no number )
  2  tablespace s
  3* storage (initial 1m );

Table created.

satheeshb@10.2.0.2.0> select file_id,extent_id from dba_extents
  2* where owner=user and segment_name='S1' order by extent_id
satheeshb@10.2.0.2.0> /

   FILE_ID  EXTENT_ID
---------- ----------
         7          0
         8          1
         9          2
         5          3
         6          4
         7          5
         8          6
         9          7
         5          8
         6          9
         7         10
         8         11
         9         12
         5         13
         6         14
         7         15

16 rows selected.

satheeshb@10.2.0.2.0> drop tablespace s including contents and datafiles;

Tablespace dropped.

     The same could be true for DMT and LMT with uniform extent. When tried with LMT with autoallocate, it gave a different behaviour. It will be based on extent size. That is once the 64k extent is over and followed by 128k extents, same patter will be followed for 128k extents as it was followed in 64k. But again number factors to change this behaviour.

satheeshb@10.2.0.2.0> select file_id,extent_id,bytes from dba_extents
  2  where owner=user and segment_name='S1' order by extent_id
  3  /

   FILE_ID  EXTENT_ID      BYTES
---------- ---------- ----------
         7          0      65536
         7          1      65536
         7          2      65536
         7          3      65536
         7          4      65536
         7          5      65536
         7          6      65536
         7          7      65536
         7          8      65536
         7          9      65536
         7         10      65536
         7         11      65536
         7         12      65536
         7         13      65536
         7         14      65536
         8         15      65536

16 rows selected.

The checkpoing command in Alter table drop column will be used If a instance crashes while dropping a column in big table.

satheeshb@10.2.0.2.0> alter table s1 drop column id checkpoint 10;

Table altered.

You are basically checkpointing every 10 rows. So that it gets to the datafiles. One thing which i am not clear is whether it is object level checkpoint or instance level checkpoint. It may not be object level checkpoint, since i guess it is introduced in 10g.

Alternatively, if you want to restart the drop column command after restart you can use,

satheeshb@10.2.0.2.0> alter table s1 drop columns continue;

Table altered.

Note the columnS. If you give column, oracle will try to drop the column “continue”

Addendum on 8th oct.
The checkpoint here is not a real checkpoint. It is something like commit. It will go to redolog files and may or maynot got datafiles.

Like MS-Sql if you want to autoincrement a column you can use sys_guid from 9iR2. But this will not be in number instead it will be in raw.

satheeshb@10.2.0.2.0> drop table s1;

Table dropped.

satheeshb@10.2.0.2.0> create table s1 ( id raw(6) default hextoraw(substr(sys_guid(),1,12)) primary key,
  2  Descrip varchar2(10));

Table created.

satheeshb@10.2.0.2.0> insert into s1(Descrip) values ('first row');

1 row created.

satheeshb@10.2.0.2.0> insert into s1(Descrip) values ('second row');

1 row created.

satheeshb@10.2.0.2.0> select * from s1;

ID                  DESCRIP
------------        ----------
8628D8F81FD2        first row
6D695F7F7547        second row

satheeshb@10.2.0.2.0> commit;

Commit complete.

When sizeable portion of table to be updated which is better Update or CTAS?

Using update,

SQL> update t1 set no=no*1.1;

5000000 rows updated.

Elapsed: 00:24:00.08

Statistics
----------------------------------------------------------
      19775  recursive calls
    9813469  db block gets
    5038061  consistent gets
      56546  physical reads
 2306478592  redo size
        674  bytes sent via SQL*Net to client
        554  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

SQL>
Please note the time taken and the redo size.

Same using CTAS using append,
SQL> insert /*+append*/ into t2 select no*1.1 from t1;

5000000 rows created.

Elapsed: 00:00:13.31

Statistics
----------------------------------------------------------
       4585  recursive calls
      10666  db block gets
       9836  consistent gets
       8031  physical reads
     255516  redo size
        668  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         24  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

SQL>

SQL> insert into t2 select no*1.1 from t1;

5000000 rows created.

Elapsed: 00:01:12.43

Statistics
----------------------------------------------------------
       4311  recursive calls
      95857  db block gets
      19944  consistent gets
       7999  physical reads
   87332300  redo size
        684  bytes sent via SQL*Net to client
        567  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         23  sorts (memory)
          0  sorts (disk)
    5000000  rows processed

SQL>

Some interesting behaviour of Dual.

SQL> startup
ORACLE instance started.

Total System Global Area   71303168 bytes
Fixed Size                  1287836 bytes
Variable Size              62916964 bytes
Database Buffers            4194304 bytes
Redo Buffers                2904064 bytes
Database mounted.
Database opened.
SQL> select  * from dual;

D
-
X

SQL> alter database close;

Database altered.

SQL> select  * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
036B2174          0          1 X

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area   71303168 bytes
Fixed Size                  1287836 bytes
Variable Size              62916964 bytes
Database Buffers            4194304 bytes
Redo Buffers                2904064 bytes
Database mounted.
SQL> desc dual;
ERROR:
ORA-04043: object dual does not exist

SQL> select * from dual;

ADDR           INDX    INST_ID D
-------- ---------- ---------- -
036B2174          0          1 X

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> select 2*2 from dual ;

       2*2
----------
         4

1 row selected.

SQL> column 2*2 format 9
SQL>
SQL> select 2*2 from dual ;

2*2
---
  4

1 row selected.

SQL> column 2*2 format 5
SQL>
SQL> select 3 from dual ;

         3
----------
         3

1 row selected.

SQL> select 3 "2*2" from dual ;

2*2
---
  5

1 row selected.

SQL>

Also if you want to generate N-number of records using dual then use,

select rownum from dual connect by level <=100

I have recently came across a interesting behaviour of optimizer_dynamic_sampling. If optimizer_dynamic_sampling <=3 and set in session or system level it is not getting effective. If the table is unanalyzed then it is getting sampled.

SQL> create table t1 (id1 number(3), id2 number(3));

Table created

SQL> insert into t1 values (1,33);

1 row created.

SQL> insert into t1 values (1,33);

1 row created.

SQL> insert into t1 values (7,34);

1 row created.

SQL> insert into t1 values (7,34);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ARYAA','T1');

PL/SQL procedure successfully completed.

SQL> alter session set optimizer_dynamic_sampling=2;

Session altered.

SQL> select * from t1 where id1=1 and id2=33;

ID1        ID2
---------- ----------
1          33
1          33

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 6 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID1"=1 AND "ID2"=33)

Statistics
----------------------------------------------------------
355       recursive calls
  0       db block gets
 65       consistent gets
  1       physical reads
  0       redo size
498       bytes sent via SQL*Net to client
380       bytes received via SQL*Net from client
  2       SQL*Net roundtrips to/from client
 11       sorts (memory)
  0       sorts (disk)

2 rows processed

SQL> select /*+ dynamic_sampling(t1 2) */ * from t1 where id1=1 and id2=33;

ID1        ID2
---------- ----------
1          33
1          33

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 12 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID1"=1 AND "ID2"=33)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
700      recursive calls
  0      db block gets
118      consistent gets
  6      physical reads
  0      redo size
498      bytes sent via SQL*Net to client
380      bytes received via SQL*Net from client
  2      SQL*Net roundtrips to/from client
  8      sorts (memory)
  0      sorts (disk)

2 rows processed

SQL>

There is an interesting thread on Fast Object Ckpt in google groups. Extent Checkpoint is from 7.2, then it converted as tablespace checkpoint and now in 10.2 as segment Checkpoint. It can be enabled or disable by _db_fast_obj_ckpt =TRUE/FALSE. During Extent checking times (oracle 7.2), there were impacts on performance. Because checkpoint queues where not introduced in 7.2.

While doing export with direct=y sometime oracle does a segment checkpoint, then does direct path read. This behaviour can be seen in 10g.
http://tinyurl.com/pdnvr

There is an general belief that sort_area_size is session level parameter. But actually it is operational parameter. That means, if the sort_area_size=5m, in a session you might actually use 5m/25m/50m etc.

Assume you open three cursor in a session which does huge sort. Each cursor will hold 5m sort_area_size.

In 9i Work_area_size=AUTO is valid only for dedicated server. If you are shared server, this parameter is not effective, you have to manually set *area_size. This behaviour is changed in 10g.

When you change pctfree or pctused, the value will get effective only to new blocks. However if oracle ever changes old blocks, that time these values will be set to pctfree and pctused.
However for initrans, only new blocks will be affected. If you want the existing block to use the new initrans, we need to rebuild the object again.

This is tried in 10.2.0.2,

SQL> select state,count(*) from x$bh group by state;

     STATE   COUNT(*)

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

         1        940

         0          6

SQL> alter system flush buffer_cache;

System altered.

SQL> select state,count(*) from x$bh group by state;

     STATE   COUNT(*)

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

         1          3

         0        943

SQL>

The STATE column of all the blocks will be set to Zero. While the buffers head which are pinned will remain same.

There is no Flush buffer_cache in 9i, you need to set the event to do the same.

ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;

create table x ( no number ) initrans 2 maxtrans 2 pctfree 0;
begin
 for i in 1..734 loop
   insert into x values (i);
 end loop;
end;
/
commit;

exec dbms_stats.gather_table_stats(user,'X');

first session,
SQL> update x set no=no where no=1;

1 row updated.
--don't commit
second session,
SQL> update x set no=no where no=2;

1 row updated.
--don't commit

third session,
SQL> update x set no=no where no=3;

The third session will hang due to insufficient itl. In 10g it will be waiting on “enq: TX – allocate ITL entry”. In previous release it was confusion as ITL wait will get added “enqueue” wait.

initrans is initial number of transaction slot require, additional slots are defined by maxtrans.

High number of maxtrans will compete for space with data layer. Earlier version consumes 23 bytes and latest version consumes 24 bytes, if you want to find out size a slot for your version run,

select type_size from v$type_size where description='TRANSACTION VARIABLE HEADER'
/
TYPE_SIZE
----------
24

ITL is valid only for UPDATE and DELETE. Not for INSERT,if there is no itl slot in a block, oracle will put the record into the other blocks which are in the freelist. Therefore there will not be any ITL contention for INSERT.

But if a table has index and if INSERT transalates into UPDATE in the index block, you might get ITL contention if the index block itl slot is full. If there is ITL shortage on index block during insert, then there will be block split.

ITL will have 3 parts, Transaction Id(TID),Undo DBA (uid) and SCN. If Delayed clean out happens, SCN part will be only set during this phase.

Maxtrans is deprecated in 10.1

There is an hidden parameter in export command, which will enable us to trace the export operation. Otherwise it will be difficult to get the sid of the export command and trace that session.

exp trace=y and other command.

Trace file will get generated in udump area. Use tkprof to format this trace file.