欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle12c_Row-archival

程序员文章站 2022-05-22 14:01:29
...

今天看了下oracle12c 的 In-Database Archiving – Row-archival ,做了下实验 环境准备 SQL CREATE TABLE row_arch (id NUMBER,name varchar2(30),addr varchar2(30),phone NUMBER);?TABLE created.?SQL INSERT INTO row_arch VALUES (100,'travel1','beiji

今天看了下oracle12c 的 In-Database Archiving – Row-archival ,做了下实验

环境准备

SQL> CREATE TABLE row_arch (id NUMBER,name varchar2(30),addr varchar2(30),phone NUMBER);
?
TABLE created.
?
SQL> INSERT INTO row_arch VALUES (100,'travel1','beijing','100')
  2  ;
?
1 ROW created.
?
SQL> INSERT INTO row_arch VALUES (101,'travel2','beijing2','100')
  2  ;
?
1 ROW created.
?
SQL> INSERT INTO row_arch VALUES (102,'travel3','beijing2','100');
?
1 ROW created.
?
SQL> INSERT INTO row_arch VALUES (103,'travel4','beijing2','100');
?
1 ROW created.
?
SQL> commit;
?
Commit complete.
?
SQL> @DESC row_arch
           Name                            NULL?    TYPE
           ------------------------------- -------- ----------------------------
    1      ID                                       NUMBER
    2      NAME                                     VARCHAR2(30)
    3      ADDR                                     VARCHAR2(30)
    4      PHONE                                    NUMBER

开启row archival

SQL> ALTER TABLE row_arch ROW ARCHIVAL;
?
TABLE altered.
?
SQL> col name FOR a10
SQL> col addr FOR a15
SQL> col ORA_ARCHIVE_STATE FOR a10
SQL> SELECT t.*,ORA_ARCHIVE_STATE FROM row_arch t;
?
        ID NAME       ADDR                 PHONE ORA_ARCHIV
---------- ---------- --------------- ---------- ----------
       100 travel1    beijing                100 0
       101 travel2    beijing2               100 0
       102 travel3    beijing2               100 0
       103 travel4    beijing2               100 0
?
?
SQL> SELECT t.*,ORA_ARCHIVE_STATE,rowid FROM row_arch t;
?
        ID NAME       ADDR                 PHONE ORA_ARCHIV ROWID
---------- ---------- --------------- ---------- ---------- ------------------
       100 travel1    beijing                100 0          AAAWegAAGAAAADdAAA
       101 travel2    beijing2               100 0          AAAWegAAGAAAADdAAB
       102 travel3    beijing2               100 0          AAAWegAAGAAAADdAAC
       103 travel4    beijing2               100 0          AAAWegAAGAAAADdAAD
?
SQL> @lookup_rowid AAAWegAAGAAAADdAAA
?
+------------------------------------------------------------------------+
| Report   : lookup_rowid.SQL                                            |
| Instance : noncdb                                                      |
| USER     : TRAVEL                                                      |
+------------------------------------------------------------------------+
?
ROWID: AAAWegAAGAAAADdAAA
Object#: 92064
RelFile#: 6
Block#: 221
ROW#: 0
?
PL/SQL PROCEDURE successfully completed.
?
SQL> @dump 6 221
?
?
NEW tracefile_identifier=/u01/app/oracle/diag/rdbms/noncdb/noncdb/trace/noncdb_ora_3526_0001.trc
?
SQL> 
?
?
SQL> @seg row_arch
?
OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              SEG_MB
-------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ----------
    BLOCKS     HDRFIL     HDRBLK
---------- ---------- ----------
TRAVEL               ROW_ARCH                                                      TABLE                USERS                                                               .06
         8          6        218
?
SQL> /
?
     FILE# NAME
---------- --------------------------------------------------
         1 /oradata/noncdb/system01.dbf
         2 /oradata/noncdb/ado_t1.dbf
         3 /oradata/noncdb/sysaux01.dbf
         4 /oradata/noncdb/undotbs01.dbf
         5 /oradata/noncdb/ado_t2.dbf
         6 /oradata/noncdb/users01.dbf
?
6 ROWS selected.
?
SQL> ALTER system dump datafile 6 block 221;
?
System altered.

查看下block dump

Block header dump:  0x018000dd
 Object id on Block? Y
 seg/obj: 0x167a0  csc: 0x00.1ce0a2  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x18000d8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
?
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.008.00000671  0x010037ca.00c7.2c  --U-    4  fsc 0x0000.001ce0b1
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x018000dd
data_block_dump,data header at 0x7f10171b6264
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7f10171b6264
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f2e
avsp=0x1f14
tosp=0x1f14
0xe:pti[0]      nrow=4  offs=0
0x12:pri[0]     offs=0x1f7f
0x14:pri[1]     offs=0x1f64
0x16:pri[2]     offs=0x1f49
0x18:pri[3]     offs=0x1f2e
block_row_dump:
tab 0, row 0, @0x1f7f
tl: 25 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 2]  c2 02
col  1: [ 7]  74 72 61 76 65 6c 31
col  2: [ 7]  62 65 69 6a 69 6e 67
col  3: [ 2]  c2 02
tab 0, row 1, @0x1f64
tl: 27 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 3]  c2 02 02
col  1: [ 7]  74 72 61 76 65 6c 32
col  2: [ 8]  62 65 69 6a 69 6e 67 32
col  3: [ 2]  c2 02
tab 0, row 2, @0x1f49
tl: 27 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 3]  c2 02 03
col  1: [ 7]  74 72 61 76 65 6c 33
col  2: [ 8]  62 65 69 6a 69 6e 67 32
col  3: [ 2]  c2 02
tab 0, row 3, @0x1f2e
tl: 27 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 3]  c2 02 04
col  1: [ 7]  74 72 61 76 65 6c 34
col  2: [ 8]  62 65 69 6a 69 6e 67 32
col  3: [ 2]  c2 02
end_of_block_dump
End dump data blocks tsn: 4 file#: 6 minblk 221 maxblk 221

在没有进行归档之前数据存储和普通块一样,下面进行归档

SQL> UPDATE row_arch SET ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1) WHERE id IN (100,101);
?
2 ROWS updated.
?
SQL> commit;
?
Commit complete.
?
?
?
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
?
SESSION altered.
?
SQL> SELECT t.*,ORA_ARCHIVE_STATE,rowid FROM row_arch t;
?
        ID NAME       ADDR            PHONE ORA_ARCHIV ROWID
---------- ---------- ---------- ---------- ---------- ------------------
       100 travel1    beijing           100 1          AAAWegAAGAAAADdAAA
       101 travel2    beijing2          100 1          AAAWegAAGAAAADdAAB
       102 travel3    beijing2          100 0          AAAWegAAGAAAADdAAC
       103 travel4    beijing2          100 0          AAAWegAAGAAAADdAAD
?
SQL> 
?
?
?
SQL> SELECT t.*,ORA_ARCHIVE_STATE,rowid FROM row_arch t;
?
        ID NAME       ADDR            PHONE ORA_ARCHIV ROWID
---------- ---------- ---------- ---------- ---------- ------------------
       102 travel3    beijing2          100 0          AAAWegAAGAAAADdAAC
       103 travel4    beijing2          100 0          AAAWegAAGAAAADdAAD
?
SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
?
SESSION altered.
?
SQL> SELECT t.*,ORA_ARCHIVE_STATE,rowid FROM row_arch t;
?
        ID NAME       ADDR            PHONE ORA_ARCHIV ROWID
---------- ---------- ---------- ---------- ---------- ------------------
       100 travel1    beijing           100 1          AAAWegAAGAAAADdAAA
       101 travel2    beijing2          100 1          AAAWegAAGAAAADdAAB
       102 travel3    beijing2          100 0          AAAWegAAGAAAADdAAC
       103 travel4    beijing2          100 0          AAAWegAAGAAAADdAAD
?
SQL> ALTER system checkpoint;
?
System altered.
?
SQL> ALTER system FLUSH buffer_Cachel
  2  
SQL> ALTER system FLUSH buffer_Cache;
?
System altered.
?
SQL> ALTER system dump datafile 6 block 221;
?
System altered.

可以看到在归档后,在没有设置ROW ARCHIVAL VISIBILITY = ALL之前是看不到归档的数据,看下dump

Block header dump:  0x018000dd
 Object id on Block? Y
 seg/obj: 0x167a0  csc: 0x00.1ce60f  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x18000d8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
?
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.008.00000671  0x010037ca.00c7.2c  C---    0  scn 0x0000.001ce0b1
0x02   0x0005.001.00000629  0x01003f2a.00e9.24  --U-    2  fsc 0x0000.001ce610
bdba: 0x018000dd
data_block_dump,data header at 0x7f10171b6264
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x7f10171b6264
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1ef2
avsp=0x1f0c
tosp=0x1f0c
0xe:pti[0]      nrow=4  offs=0
0x12:pri[0]     offs=0x1f11
0x14:pri[1]     offs=0x1ef2
0x16:pri[2]     offs=0x1f49
0x18:pri[3]     offs=0x1f2e
block_row_dump:
tab 0, row 0, @0x1f11
tl: 29 fb: --H-FL-- lb: 0x2  cc: 6
col  0: [ 2]  c2 02
col  1: [ 7]  74 72 61 76 65 6c 31
col  2: [ 7]  62 65 69 6a 69 6e 67
col  3: [ 2]  c2 02
col  4: [ 1]  01
col  5: [ 1]  31
tab 0, row 1, @0x1ef2
tl: 31 fb: --H-FL-- lb: 0x2  cc: 6
col  0: [ 3]  c2 02 02
col  1: [ 7]  74 72 61 76 65 6c 32
col  2: [ 8]  62 65 69 6a 69 6e 67 32
col  3: [ 2]  c2 02
col  4: [ 1]  01
col  5: [ 1]  31
tab 0, row 2, @0x1f49
tl: 27 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [ 3]  c2 02 03
col  1: [ 7]  74 72 61 76 65 6c 33
col  2: [ 8]  62 65 69 6a 69 6e 67 32
col  3: [ 2]  c2 02
tab 0, row 3, @0x1f2e
tl: 27 fb: --H-FL-- lb: 0x0  cc: 4
col  0: [ 3]  c2 02 04
col  1: [ 7]  74 72 61 76 65 6c 34
col  2: [ 8]  62 65 69 6a 69 6e 67 32
col  3: [ 2]  c2 02
end_of_block_dump
End dump data blocks tsn: 4 file#: 6 minblk 221 maxblk 221

oracle在归档的行增加了2列,查看下这两列是干什么的

SQL> col owner FOR a10
SQL> col TABLE_NAME FOR a10
SQL> col COLUMN_NAME FOR a15
SQL> col COLUMN_ID fro a10
SQL> col COLUMN_ID FOR a10
SQL> col COLUMN_ID FOR 9999
?
?
SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_ID FROM DBA_TAB_COLS WHERE TABLE_NAME='ROW_ARCH';
?
OWNER      TABLE_NAME COLUMN_NAME     COLUMN_ID
---------- ---------- --------------- ---------
TRAVEL     ROW_ARCH   ORA_ARCHIVE_STA
                      TE
?
TRAVEL     ROW_ARCH   SYS_NC00005$
TRAVEL     ROW_ARCH   PHONE                   4
TRAVEL     ROW_ARCH   ADDR                    3
TRAVEL     ROW_ARCH   NAME                    2
TRAVEL     ROW_ARCH   ID                      1
?
6 ROWS selected.
?
?
?
SQL> col NAME FOR a15
SQL> col DEFAULT$ FOR a10
SQL> col SPARE4 FOR a1
SQL> col SPARE5 FOR a1
SQL> col SPARE6 FOR a1
SQL> SELECT * FROM col$ WHERE obj#='92064';
?
      OBJ#       COL#    SEGCOL# SEGCOLLENGTH     OFFSET NAME                 TYPE#     LENGTH FIXEDSTORAGE PRECISION#      SCALE      NULL$  DEFLENGTH DEFAULT$      INTCOL#   PROPERTY  CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING#     SPARE1     SPARE2     SPARE3 S S S     SPARE7     SPARE8
---------- ---------- ---------- ------------ ---------- --------------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ------------ --------------- ------------------ ---------- ---------- ---------- - - - ---------- ----------
     92064          1          1           22          0 ID                       2         22            0                                0                                1          0          0           0            0                                             0          0          0
     92064          2          2           30          0 NAME                     1         30            0                                0                                2          0        873           1            0                                             0          0         30
     92064          3          3           30          0 ADDR                     1         30            0                                0                                3          0        873           1            0                                             0          0         30
     92064          4          4           22          0 PHONE                    2         22            0                                0                                4          0          0           0            0                                             0          0          0
     92064          0          5          126          0 SYS_NC00005$            23        126            0                                0                                5 5.4976E+11          0           0            0                                             0          0          0
     92064          0          6         4000          0 ORA_ARCHIVE_STA          1       4000            0                                0          1 0                   6 2.2001E+12        873           1            0                                             0          0       4000
                                                         TE
?
?
6 ROWS selected.

可以看出oracle在底层col$里增加了2列,并设置col#为0,不可正常看到

SQL> @v DBA_TAB_COLS
SHOW SQL text OF views matching "%DBA_TAB_COLS%"...
?
VIEW_NAME                      TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
DBA_TAB_COLS_V$                SELECT u.name, o.name,
                               c.name,
                               decode(c.TYPE#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                               2, decode(c.scale, NULL,
                               decode(c.PRECISION#, NULL, 'NUMBER', 'FLOAT'),
                               'NUMBER'),
                               8, 'LONG',
                               9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                               12, 'DATE',
                               23, 'RAW', 24, 'LONG RAW',
                               58, nvl2(ac.synobj#, (SELECT o.name FROM obj$ o
                               WHERE o.obj#=ac.synobj#), ot.name),
                               69, 'ROWID',
                               96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                               100, 'BINARY_FLOAT',
                               101, 'BINARY_DOUBLE',
                               105, 'MLSLABEL',
                               106, 'MLSLABEL',
                               111, nvl2(ac.synobj#, (SELECT o.name FROM obj$ o
                               WHERE o.obj#=ac.synobj#), ot.name),
                               112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                               113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                               121, nvl2(ac.synobj#, (SELECT o.name FROM obj$ o
                               WHERE o.obj#=ac.synobj#), ot.name),
                               122, nvl2(ac.synobj#, (SELECT o.name FROM obj$ o
                               WHERE o.obj#=ac.synobj#), ot.name),
                               123, nvl2(ac.synobj#, (SELECT o.name FROM obj$ o
                               WHERE o.obj#=ac.synobj#), ot.name),
                               178, 'TIME(' ||c.scale|| ')',
                               179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
                               180, 'TIMESTAMP(' ||c.scale|| ')',
                               181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
                               231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
                               182, 'INTERVAL YEAR(' ||c.PRECISION#||') TO MONTH',
                               183, 'INTERVAL DAY(' ||c.PRECISION#||') TO SECOND(' ||
                               c.scale || ')',
                               208, 'UROWID',
                               'UNDEFINED'),
                               decode(c.TYPE#, 111, 'REF'),
                               nvl2(ac.synobj#, (SELECT u.name FROM "_BASE_USER" u, obj$ o
                               WHERE o.owner#=u.USER# AND o.obj#=ac.synobj#),
                               ut.name),
                               c.LENGTH, c.PRECISION#, c.scale,
                               decode(sign(c.NULL$),-1,'D', 0, 'Y', 'N'),
                               decode(c.col#, 0, to_number(NULL), c.col#), --这里col#为0则转换为null
                               c.deflength,
                               c.DEFAULT$, h.distcnt,
                               CASE WHEN SYS_OP_DV_CHECK(o.name, o.owner#) = 1
                               THEN h.lowval
                               ELSE NULL
                               END,
                               CASE WHEN SYS_OP_DV_CHECK(o.name, o.owner#) = 1
                               THEN h.hival
                               ELSE NULL
                               END,
                               h.density, h.null_cnt,
                               CASE WHEN nvl(h.distcnt,0) = 0 THEN h.distcnt
                               -- no histogram
                               WHEN h.row_cnt = 0 THEN 1
                               -- hybrid
                               WHEN EXISTS(SELECT 1 FROM sys.histgrm$ hg
                               WHERE c.obj# = hg.obj# AND c.intcol# = hg.intcol#
                               AND hg.ep_repeat_count > 0 AND rownum  0
                               THEN h.row_cnt
                               -- freq
                               WHEN (bitand(h.spare2, 32) > 0 OR h.bucket_cnt > 2049 OR
                               (h.bucket_cnt >= h.distcnt AND h.density*h.bucket_cnt  0 AND rownum  0
                               THEN 'TOP-FREQUENCY'
                               WHEN (bitand(h.spare2, 32) > 0 OR h.bucket_cnt > 2049 OR
                               (h.bucket_cnt >= h.distcnt AND h.density*h.bucket_cnt 

测试下odu

  • 数据字典存在
[oracle@localhost odu]$ ./odu 
?
Oracle Data Unloader:Release 4.3.3
?
Copyright (c) 2008-2014 XiongJun. All rights reserved.
?
Web: http://www.oracleodu.com
Email: magic007cn@gmail.com
?
loading default config.......
?
byte_order little
block_size  8192
block_buffers 1024
db_timezone -7
Invalid db timezone:-7
client_timezone 8
Invalid client timezone:8
asmfile_extract_path   /asmfile
data_path   data
lob_path    /odu/data/lob
charset_name US7ASCII
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order big
trace_level 1
delimiter |
unload_deleted no
file_header_offset 0
is_tru64 no
record_row_addr no
convert_clob_charset yes
use_scanned_lob  yes
trim_scanned_blob yes
lob_switch_dir_rows 20000
db_block_checksum yes
db_block_checking yes
rdba_file_bits 10
compatible 10
load config file 'config.txt' successful
loading default asm disk file ......
?
?
grp# dsk# bsize ausize disksize diskname        groupname       path
---- ---- ----- ------ -------- --------------- --------------- --------------------------------------------
?
load asm disk file 'asmdisk.txt' successful
loading default control file ......
?
?
 ts#   fn  rfn bsize   blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
   0    1    1  8192    99840 N       0 /oradata/noncdb/system01.dbf
   6    2    2  8192    25600 N       0 /oradata/noncdb/ado_t1.dbf
   1    3    3  8192    98560 N       0 /oradata/noncdb/sysaux01.dbf
   2    4    4  8192    18560 N       0 /oradata/noncdb/undotbs01.dbf
   7    5    5  8192    51200 N       0 /oradata/noncdb/ado_t2.dbf
   4    6    6  8192     8160 N       0 /oradata/noncdb/users01.dbf
load control file 'oductl.dat' successful
loading dictionary data......done
?
loading scanned data......done
?
ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 208
TABLE OBJ$ obj_no: 18 file_no: 1 block_no: 240
CLUSTER C_OBJ# file_no: 1 block_no: 144
CLUSTER C_OBJ# file_no: 1 block_no: 144
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found TABPART$'s obj# 694
found TABPART$'s dataobj#:694,ts#:0,file#:1,block#:4712,tab#:0
found INDPART$'s obj# 699
found INDPART$'s dataobj#:699,ts#:0,file#:1,block#:4752,tab#:0
found TABSUBPART$'s obj# 706
found TABSUBPART$'s dataobj#:706,ts#:0,file#:1,block#:4808,tab#:0
found INDSUBPART$'s obj# 711
found INDSUBPART$'s dataobj#:711,ts#:0,file#:1,block#:4848,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found LOB$'s obj# 108
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6
found LOBFRAG$'s obj# 727
found LOBFRAG$'s dataobj#:727,ts#:0,file#:1,block#:4976,tab#:0
ODU> desc travel.row_arch
?
?
Object ID:92064
Storage(Obj#=92064 DataObj#=92064 TS#=4 File#=6 Block#=218 Cluster=0)
?
NO. SEG INT Column Name                    Null?     Type                          
--- --- --- ------------------------------ --------- ------------------------------
  0   5   5 SYS_NC00005$                             RAW(126)                      
  0   6   6 ORA_ARCHIVE_STATE                        VARCHAR2(4000)                
  1   1   1 ID                                       NUMBER                        
  2   2   2 NAME                                     VARCHAR2(30)                  
  3   3   3 ADDR                                     VARCHAR2(30)                  
  4   4   4 PHONE                                    NUMBER                        
?
ODU> unload table travel.row_arch
?
Unloading table: ROW_ARCH,object ID: 92064 at 2014-05-26 21:05:04
Unloading segment,storage(Obj#=92064 DataObj#=92064 TS#=4 File#=6 Block#=218 Cluster=0)
?
Table ROW_ARCH 4 rows unloaded
At 2014-05-26 21:05:04 
?
ODU> quit
Invalid command.
ODU> exit 
ODU> 
[oracle@localhost odu]$ ls -l
total 10232
-rwxr-xr-x 1 oracle oinstall      90 Mar 22  2011 asmdisk.txt
-rw-r--r-- 1 oracle oinstall 4447252 May 26 21:04 col.odu
-rwxr-xr-x 1 oracle oinstall     559 Apr  7  2011 config.txt
-rwxr-xr-x 1 oracle oinstall     492 May 26 20:38 control.txt
drwxr-xr-x 2 oracle oinstall    4096 May 26 21:05 data
-rw-r--r-- 1 oracle oinstall   55429 May 26 21:04 ind.odu
-rw-r--r-- 1 oracle oinstall     352 May 26 21:04 lobfrag.odu
-rw-r--r-- 1 oracle oinstall   34234 May 26 21:04 lob.odu
-rw-r--r-- 1 oracle oinstall 3420310 May 26 21:04 obj.odu
-rwxr-xr-x 1 oracle oinstall 2306912 Apr  7 12:09 odu
-rw-r--r-- 1 oracle oinstall    1051 May 26 21:04 oductl.dat
-rw-r--r-- 1 oracle oinstall     295 May 26 20:38 oductl.txt
-rw-r--r-- 1 oracle oinstall       0 May 26 20:38 odu_trace.txt
-rw-r--r-- 1 oracle oinstall  137024 May 26 21:04 tab.odu
-rw-r--r-- 1 oracle oinstall    2170 May 26 21:04 user.odu
[oracle@localhost odu]$ cd data/
[oracle@localhost data]$ ls -l
total 12
-rw-r--r-- 1 oracle oinstall 323 May 26 21:05 TRAVEL_ROW_ARCH.ctl
-rw-r--r-- 1 oracle oinstall 128 May 26 21:05 TRAVEL_ROW_ARCH.sql
-rw-r--r-- 1 oracle oinstall  99 May 26 21:05 TRAVEL_ROW_ARCH.txt
[oracle@localhost data]$ cat TRAVEL_ROW_ARCH.txt
100|travel1|beijing|100
101|travel2|beijing2|100
102|travel3|beijing2|100
103|travel4|beijing2|100
[oracle@localhost data]$ cat  TRAVEL_ROW_ARCH.sql
CREATE TABLE "TRAVEL"."ROW_ARCH"
(
    "ID" NUMBER ,
    "NAME" VARCHAR2(30) ,
    "ADDR" VARCHAR2(30) ,
    "PHONE" NUMBER 
);
[oracle@localhost data]$
  • 数据字典不存在
  • ODU> scan extent tablespace 4;
    ?
    scan extent start: 2014-05-26 21:39:18
    scanning extent...
    scanning extent finished.
    scan extent completed: 2014-05-26 21:39:18
    ?
    ODU> uload object all sample;
    Invalid command.
    ODU> unload object all sample
    ?
    Unloading Object,object ID: 73633,  Cluster: 0
    output data is in file :  'ODU_0000073633.txt' 
    ?
    Sample result:
      object id: 73633
      tablespace no: 4
      sampled 8 rows
      column count: 4
      column    1  type: NUMBER
      column    2  type: NUMBER
      column    3  type: VARCHAR2
      column    4  type: NUMBER
    ?
    COMMAND: 
    unload object 73633 tablespace 4 column NUMBER NUMBER VARCHAR2 NUMBER 
    ?
    ?
    Unloading Object,object ID: 73634,  Cluster: 0
    output data is in file :  'ODU_0000073634.txt' 
    block is not a iot index block
    ?
    Sample result:
      object id: 73634
      tablespace no: 4
      no data.
    ?
    ?
    Unloading Object,object ID: 73635,  Cluster: 0
    output data is in file :  'ODU_0000073635.txt' 
    ?
    Sample result:
      object id: 73635
      tablespace no: 4
      sampled 3 rows
      column count: 7
      column    1  type: NUMBER
      column    2  type: NUMBER
      column    3  type: NUMBER
      column    4  type: DATE
      column    5  type: DATE
      column    6  type: VARCHAR2
      column    7  type: NUMBER
    ?
    COMMAND: 
    unload object 73635 tablespace 4 column NUMBER NUMBER NUMBER DATE DATE VARCHAR2 NUMBER 
    ?
    ?
    Unloading Object,object ID: 73636,  Cluster: 0
    output data is in file :  'ODU_0000073636.txt' 
    block is not a iot index block
    ?
    Sample result:
      object id: 73636
      tablespace no: 4
      no data.
    ?
    ?
    Unloading Object,object ID: 73643,  Cluster: 0
    output data is in file :  'ODU_0000073643.txt' 
    ?
    Sample result:
      object id: 73643
      tablespace no: 4
      sampled 9 rows
      column count: 3
      column    1  type: NUMBER
      column    2  type: NUMBER
      column    3  type: VARCHAR2
    ?
    COMMAND: 
    unload object 73643 tablespace 4 column NUMBER NUMBER VARCHAR2 
    ?
    ?
    Unloading Object,object ID: 73644,  Cluster: 0
    output data is in file :  'ODU_0000073644.txt' 
    block is not a iot index block
    ?
    Sample result:
      object id: 73644
      tablespace no: 4
      no data.
    ?
    ?
    Unloading Object,object ID: 91884,  Cluster: 0
    output data is in file :  'ODU_0000091884.txt' 
    ?
    Sample result:
      object id: 91884
      tablespace no: 4
      sampled 4 rows
      column count: 3
      column    1  type: NUMBER
      column    2  type: VARCHAR2
      column    3  type: VARCHAR2
    ?
    COMMAND: 
    unload object 91884 tablespace 4 column NUMBER VARCHAR2 VARCHAR2 
    ?
    ?
    Unloading Object,object ID: 91885,  Cluster: 0
    output data is in file :  'ODU_0000091885.txt' 
    block is not a iot index block
    ?
    Sample result:
      object id: 91885
      tablespace no: 4
      no data.
    ?
    ?
    Unloading Object,object ID: 91890,  Cluster: 0
    output data is in file :  'ODU_0000091890.txt' 
    ?
    Sample result:
      object id: 91890
      tablespace no: 4
      sampled 14 rows
      column count: 8
      column    1  type: NUMBER
      column    2  type: VARCHAR2
      column    3  type: VARCHAR2
      column    4  type: NUMBER
      column    5  type: DATE
      column    6  type: NUMBER
      column    7  type: NUMBER
      column    8  type: NUMBER
    ?
    COMMAND: 
    unload object 91890 tablespace 4 column NUMBER VARCHAR2 VARCHAR2 NUMBER DATE NUMBER NUMBER NUMBER 
    ?
    ?
    Unloading Object,object ID: 91893,  Cluster: 0
    output data is in file :  'ODU_0000091893.txt' 
    block is not a iot index block
    ?
    Sample result:
      object id: 91893
      tablespace no: 4
      no data.
    ?
    ?
    Unloading Object,object ID: 91907,  Cluster: 0
    output data is in file :  'ODU_0000091907.txt' 
    ?
    Sample result:
      object id: 91907
      tablespace no: 4
      sampled 5 rows
      column count: 3
      column    1  type: NUMBER
      column    2  type: NUMBER
      column    3  type: NUMBER
    ?
    COMMAND: 
    unload object 91907 tablespace 4 column NUMBER NUMBER NUMBER 
    ?
    ?
    Unloading Object,object ID: 92007,  Cluster: 0
    output data is in file :  'ODU_0000092007.txt' 
    ?
    Sample result:
      object id: 92007
      tablespace no: 4
      sampled 1058 rows
      column count: 18
      column    1  type: VARCHAR2
      column    2  type: VARCHAR2
      column    3  type: RAW
      column    4  type: NUMBER
      column    5  type: NUMBER
      column    6  type: VARCHAR2
      column    7  type: DATE
      column    8  type: DATE
      column    9  type: VARCHAR2
      column   10  type: VARCHAR2
      column   11  type: VARCHAR2
      column   12  type: VARCHAR2
      column   13  type: VARCHAR2
      column   14  type: NUMBER
      column   15  type: RAW
      column   16  type: VARCHAR2
      column   17  type: VARCHAR2
      column   18  type: VARCHAR2
    ?
    COMMAND: 
    unload object 92007 tablespace 4 column VARCHAR2 VARCHAR2 RAW NUMBER NUMBER VARCHAR2 DATE DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER RAW VARCHAR2 VARCHAR2 VARCHAR2 
    ?
    ?
    Unloading Object,object ID: 92035,  Cluster: 0
    output data is in file :  'ODU_0000092035.txt' 
    ?
    Sample result:
      object id: 92035
      tablespace no: 4
      sampled 1127 rows
      column count: 18
      column    1  type: VARCHAR2
      column    2  type: VARCHAR2
      column    3  type: VARCHAR2
      column    4  type: NUMBER
      column    5  type: NUMBER
      column    6  type: VARCHAR2
      column    7  type: DATE
      column    8  type: DATE
      column    9  type: VARCHAR2
      column   10  type: VARCHAR2
      column   11  type: VARCHAR2
      column   12  type: VARCHAR2
      column   13  type: VARCHAR2
      column   14  type: NUMBER
      column   15  type: RAW
      column   16  type: VARCHAR2
      column   17  type: VARCHAR2
      column   18  type: VARCHAR2
    ?
    COMMAND: 
    unload object 92035 tablespace 4 column VARCHAR2 VARCHAR2 VARCHAR2 NUMBER NUMBER VARCHAR2 DATE DATE VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 NUMBER RAW VARCHAR2 VARCHAR2 VARCHAR2 
    ?
    ?
    Unloading Object,object ID: 92064,  Cluster: 0
    output data is in file :  'ODU_0000092064.txt' 
    ?
    Sample result:
      object id: 92064
      tablespace no: 4
      sampled 4 rows
      column count: 6
      column    1  type: NUMBER
      column    2  type: VARCHAR2
      column    3  type: VARCHAR2
      column    4  type: NUMBER
      column    5  type: RAW
      column    6  type: VARCHAR2
    ?
    COMMAND: 
    unload object 92064 tablespace 4 column NUMBER VARCHAR2 VARCHAR2 NUMBER RAW VARCHAR2 
    ?
    ODU> unload object 92064 tablespace 4 column NUMBER VARCHAR2 VARCHAR2 NUMBER RAW VARCHAR2 
    ?
    Unloading Object,object ID: 92064,  Cluster: 0 at 2014-05-26 21:39:47
    4 rows unloaded
    At 2014-05-26 21:39:47
    ?
    ODU> 
    ?
    [oracle@localhost data]$ cat ODU_0000092064.txt
    100|travel1|beijing|100|01|1
    101|travel2|beijing2|100|01|1
    102|travel3|beijing2|100
    103|travel4|beijing2|100
    ?
    [oracle@localhost data]$ cat ODU_0000092064.sql
    CREATE TABLE "ODU_0000092064"
    (
        "C0001" NUMBER ,
        "C0002" VARCHAR2(4000) ,
        "C0003" VARCHAR2(4000) ,
        "C0004" NUMBER ,
        "C0005" RAW(2000) ,
        "C0006" VARCHAR2(4000) 
    );

    在没有数据字典的情况下把字段全部识别,不光是这个功能包含以前的存在隐藏列的都在恢复都需要注意

    关闭ROW ARCHIVAL;

    SQL> ALTER TABLE travel.row_arch NO ROW ARCHIVAL;
    ?
    TABLE altered.
    ?
    SQL> SELECT * FROM travel.row_arch;
    ?
            ID NAME            ADDR                                                              PHONE
    ---------- --------------- ------------------------------------------------------------ ----------
           100 travel1         beijing                                                             100
           101 travel2         beijing2                                                            100
           102 travel3         beijing2                                                            100
           103 travel4         beijing2                                                            100
    ?
    ?
    SQL> ALTER system dump datafile 6 block 221;
    ?
    System altered.
    ?
    SQL> @show_trace
    ?
    TRACE_FILE_NAME
    -------------------------------------------------------------------------------------
    /u01/app/oracle/diag/rdbms/noncdb/noncdb/trace/noncdb_ora_4078.trc
    ?
    ?
    ?
    Block header dump:  0x018000dd
     Object id ON Block? Y
     seg/obj: 0x167a0  csc: 0x00.1ce60f  itc: 2  flg: E  typ: 1 - DATA
         brn: 0  bdba: 0x18000d8 ver: 0x01 opc: 0
         inc: 0  exflg: 0
    ?
     Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
    0x01   0x0006.008.00000671  0x010037ca.00c7.2c  C---    0  scn 0x0000.001ce0b1
    0x02   0x0005.001.00000629  0x01003f2a.00e9.24  --U-    2  fsc 0x0000.001ce610
    bdba: 0x018000dd
    data_block_dump,DATA header at 0x7f2cb3265064
    ===============
    tsiz: 0x1f98
    hsiz: 0x1a
    pbl: 0x7f2cb3265064
         76543210
    flag=--------
    ntab=1
    nrow=4
    frre=-1
    fsbo=0x1a
    fseo=0x1ef2
    avsp=0x1f0c
    tosp=0x1f0c
    0xe:pti[0]      nrow=4  offs=0
    0x12:pri[0]     offs=0x1f11
    0x14:pri[1]     offs=0x1ef2
    0x16:pri[2]     offs=0x1f49
    0x18:pri[3]     offs=0x1f2e
    block_row_dump:
    tab 0, ROW 0, @0x1f11
    tl: 29 fb: --H-FL-- lb: 0x2  cc: 6
    col  0: [ 2]  c2 02
    col  1: [ 7]  74 72 61 76 65 6c 31
    col  2: [ 7]  62 65 69 6a 69 6e 67
    col  3: [ 2]  c2 02
    tab 0, ROW 1, @0x1ef2
    tl: 31 fb: --H-FL-- lb: 0x2  cc: 6
    col  0: [ 3]  c2 02 02
    col  1: [ 7]  74 72 61 76 65 6c 32
    col  2: [ 8]  62 65 69 6a 69 6e 67 32
    col  3: [ 2]  c2 02
    tab 0, ROW 2, @0x1f49
    tl: 27 fb: --H-FL-- lb: 0x0  cc: 4
    col  0: [ 3]  c2 02 03
    col  1: [ 7]  74 72 61 76 65 6c 33
    col  2: [ 8]  62 65 69 6a 69 6e 67 32
    col  3: [ 2]  c2 02
    tab 0, ROW 3, @0x1f2e
    tl: 27 fb: --H-FL-- lb: 0x0  cc: 4
    col  0: [ 3]  c2 02 04
    col  1: [ 7]  74 72 61 76 65 6c 34
    col  2: [ 8]  62 65 69 6a 69 6e 67 32
    col  3: [ 2]  c2 02
    end_of_block_dump
    END dump DATA blocks tsn: 4 file#: 6 minblk 221 maxblk 221
    [oracle@localhost odu]$ 
    SQL> SELECT * FROM col$ WHERE obj#='92064';
    ?
          OBJ#       COL#    SEGCOL# SEGCOLLENGTH     OFFSET NAME                 TYPE#     LENGTH FIXEDSTORAGE PRECISION#      SCALE      NULL$  DEFLENGTH DEFAULT$      INTCOL#   PROPERTY  CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING#     SPARE1     SPARE2     SPARE3 S S S     SPARE7     SPARE8
    ---------- ---------- ---------- ------------ ---------- --------------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ------------ --------------- ------------------ ---------- ---------- ---------- - - - ---------- ----------
         92064          1          1           22          0 ID                       2         22            0                                0                                1          0          0           0            0                                             0          0          0
         92064          2          2           30          0 NAME                     1         30            0                                0                                2          0        873           1            0                                             0          0         30
         92064          3          3           30          0 ADDR                     1         30            0                                0                                3          0        873           1            0                                             0          0         30
         92064          4          4           22          0 PHONE                    2         22            0                                0                                4          0          0           0            0                                             0          0          0
    ?
    SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_ID FROM DBA_TAB_COLS WHERE TABLE_NAME='ROW_ARCH';
    ?
    OWNER      TABLE_NAME COLUMN_NAME     COLUMN_ID
    ---------- ---------- --------------- ---------
    TRAVEL     ROW_ARCH   PHONE                   4
    TRAVEL     ROW_ARCH   ADDR                    3
    TRAVEL     ROW_ARCH   NAME                    2
    TRAVEL     ROW_ARCH   ID                      1

    col$表的结构

    create table col$                                            /* column table */
    ( obj#          number not null,             /* object number of base object */
      col#          number not null,                 /* column number as created */
      segcol#       number not null,                 /* column number in segment */
      segcollength  number not null,             /* length of the segment column */
      offset        number not null,                         /* offset of column */
      name          varchar2("M_IDEN") not null,               /* name of column */
      type#         number  not null,                     /* data type of column */
                                               /* for ADT column, type# = DTYADT */
      length        number  not null,               /* length of column in bytes */
      fixedstorage  number  not null,   /* flags: 0x01 = fixed, 0x02 = read-only */
      precision#    number,                                         /* precision */
      scale         number,                                             /* scale */
      null$         number not null,                     /* 0 = NULLs permitted, */
                                                    /* > 0 = no NULLs permitted  */
      deflength     number,              /* default value expression text length */
      default$      long,                       /* default value expression text */
    ?
      /*
       * If a table T(c1, addr, c2) contains an ADT column addr which is stored
       * exploded, the table will be internally stored as
       *              T(c1, addr, C0003$, C0004$, C0005$, c2)
       * Of these, only c1, addr and c2 are user visible columns. Thus, the
       * user visible column numbers for (c1, addr, C0003$, C0004$, C0005$, c2)
       * will be 1,2,0,0,0,3. And the corresponding internal column numbers will
       * be 1,2,3,4,5,6.
       *
       * Some dictionary tables like icol$, ccol$ need to contain intcol# so
       * that we can have indexes and constraints on ADT attributes. Also, these
       * tables also need to contain col# to maintain backward compatibility.
       * Most of these tables will need to be accessed by col#, intcol# so
       * indexes are created on them based on (obj#, col#) and (obj#, intcol#).
       * Indexes based on col# have to be non-unique if ADT attributes might
       * appear in the table. Indexes based on intcol# can be unique.
       */
      intcol#       number not null,                   /* internal column number */
      property      number not null,           /* column properties (bit flags): */
                    /* 0x0001 =       1 = ADT attribute column                   */
                    /* 0x0002 =       2 = OID column                             */
                    /* 0x0004 =       4 = nested table column                    */
                    /* 0x0008 =       8 = virtual column                         */
                    /* 0x0010 =      16 = nested table's SETID$ column           */
                    /* 0x0020 =      32 = hidden column                          */
                    /* 0x0040 =      64 = primary-key based OID column           */
                    /* 0x0080 =     128 = column is stored in a lob              */
                    /* 0x0100 =     256 = system-generated column                */
                    /* 0x0200 =     512 = rowinfo column of typed table/view     */
                    /* 0x0400 =    1024 = nested table columns setid             */
                    /* 0x0800 =    2048 = column not insertable                  */
                    /* 0x1000 =    4096 = column not updatable                   */
                    /* 0x2000 =    8192 = column not deletable                   */
                    /* 0x4000 =   16384 = dropped column                         */
                    /* 0x8000 =   32768 = unused column - data still in row      */
                /* 0x00010000 =   65536 = virtual column                         */
                /* 0x00020000 =  131072 = place DESCEND operator on top          */
                /* 0x00040000 =  262144 = virtual column is NLS dependent        */
                /* 0x00080000 =  524288 = ref column (present as oid col)        */
                /* 0x00100000 = 1048576 = hidden snapshot base table column      */
                /* 0x00200000 = 2097152 = attribute column of a user-defined ref */
                /* 0x00400000 = 4194304 = export hidden column,RLS on hidden col */
                /* 0x00800000 = 8388608 = string column measured in characters   */
               /* 0x01000000 = 16777216 = virtual column expression specified    */
               /* 0x02000000 = 33554432 = typeid column                          */
               /* 0x04000000 = 67108864 = Column is encrypted                    */
              /* 0x20000000 = 536870912 = Column is encrypted without salt       */
    ?
          /* 0x000800000000 = 34359738368 = default with sequence                */
          /* 0x001000000000 = 68719476736 = default on null                      */
          /* 0x002000000000 = 137438953472 = generated always identity column    */
          /* 0x004000000000 = 274877906944 = generated by default identity col   */
          /* 0x080000000000 = 8796093022208 = Column is sensitive                */
    ?
      /* The spares may be used as the column's NLS character set,
       * the number of distinct column values, and the column's domain.
       */
      /* the universal character set id maintained by NLS group */
      charsetid     number,                              /* NLS character set id */
      /*
       * charsetform
       */
      charsetform   number,
      /* 1 = implicit: for CHAR, VARCHAR2, CLOB w/o a specified set */
      /* 2 = nchar: for NCHAR, NCHAR VARYING, NCLOB */
      /* 3 = explicit: for CHAR, etc. with "CHARACTER SET ..." clause */
      /* 4 = flexible: for PL/SQL "flexible" parameters */
      evaledition#  number,                                /* evaluation edition */
      unusablebefore#    number,                      /* unusable before edition */
      unusablebeginning# number,              /* unusable beginning with edition */
      spare1        number,                      /* fractional seconds precision */
      spare2        number,                  /* interval leading field precision */
      spare3        number,            /* maximum number of characters in string */
      spare4        varchar2(1000),          /* NLS settings for this expression */
      spare5        varchar2(1000),
      spare6        date,
      spare7        number,
      spare8        number
    )
    cluster c_obj#(obj#)
    /