oracle12c_Row-archival
- WBOYOriginal
- 2016-06-07 16:36:492635browse
今天看了下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
<p>测试下odu</p>
- 数据字典存在
[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#)
/
原文地址：oracle12c_Row-archival, 感谢原作者分享。
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PRM For Oracle Database 3.0新版本已提供下载Next article：convert database异构同字节迁移