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#) /
原文地址:oracle12c_Row-archival, 感谢原作者分享。
上一篇: Opencv 2.4.10 +VS2010 项目配置记录
下一篇: 创建自定义标签,struts2