Saturday, June 30, 2012

Range partitioned table with LOB example


create table t1
(
  a  number not null,
  b  date   not null,
  c  clob
) tablespace mytbs
  lob(c) store as lob_c (tablespace mytbs)
  partition by range (b)
  (
   partition b1 values less than (to_date('2010-01-01', 'YYYY-MM-DD')) tablespace mytbs
     lob(c) store as  lob_c1 (tablespace mytbs),
   partition b2 values less than (to_date('2011-01-01', 'YYYY-MM-DD')) tablespace mytbs
     lob(c) store as  lob_c2 (tablespace mytbs),
   partition b3 values less than (to_date('2012-01-01', 'YYYY-MM-DD')) tablespace mytbs
     lob(c) store as  lob_c3 (tablespace mytbs),
   partition b4 values less than (to_date('2013-01-01', 'YYYY-MM-DD')) tablespace mytbs
     lob(c) store as  lob_c4 (tablespace mytbs)
  )
;

create index t1_i1 on t1(a) local
(
  partition i1_1 tablespace mytbs,
  partition i1_2 tablespace mytbs,
  partition i1_3 tablespace mytbs,
  partition i1_4 tablespace mytbs
);

Dumping Oracle data block


Getting Absolute File number and Block number of a rowid
select rowid,
       dbms_rowid.rowid_to_absolute_fno(rowid, 'MURTY', 'T1') "File no" ,
       dbms_rowid.rowid_block_number(rowid) "Block no"
from t1;

ROWID              File no      Block no
------------------ ---------- ----------
AAADaGAAEAAAACEAAA          4        132

Getting header block of a segment
select header_file, header_block
from   dba_segments
where  owner='MURTY'
and    segment_name = 'T1';

HEADER_FILE HEADER_BLOCK
----------- ------------
          4          130

Dumping a single block
alter system dump datafile 4 block 132;

Finding an extent start and end
select file_id, block_id, blocks
from   dba_extents
where  owner = 'MURTY'
and    segment_name = 'T1';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         4        128          8

Dumping more than one block
alter system dump datafile 4 block min 128 block max 135;