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;