oracle12c_Row-archival
今天看了下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, 感谢原作者分享。

Heiße KI -Werkzeuge

Undresser.AI Undress
KI-gestützte App zum Erstellen realistischer Aktfotos

AI Clothes Remover
Online-KI-Tool zum Entfernen von Kleidung aus Fotos.

Undress AI Tool
Ausziehbilder kostenlos

Clothoff.io
KI-Kleiderentferner

AI Hentai Generator
Erstellen Sie kostenlos Ai Hentai.

Heißer Artikel

Heiße Werkzeuge

Notepad++7.3.1
Einfach zu bedienender und kostenloser Code-Editor

SublimeText3 chinesische Version
Chinesische Version, sehr einfach zu bedienen

Senden Sie Studio 13.0.1
Leistungsstarke integrierte PHP-Entwicklungsumgebung

Dreamweaver CS6
Visuelle Webentwicklungstools

SublimeText3 Mac-Version
Codebearbeitungssoftware auf Gottesniveau (SublimeText3)

Heiße Themen

Digitale Audio-Ausgangsschnittstelle auf dem Motherboard-SPDIF OUT

Top 10 Global Digital Currency Trading Apps empfohlen (2025 Ranking für Währungssoftware)

Wie installiere und registriere ich die BTC Trading App?

Welche Handelssoftware für digitale Währung ist die beste?

Top 10 Top -Plattformen für virtuelle digitale Währung 2025 Rankings, Top Ten Virtual Currency App Börsen

Ouyi Exchange Download Official Portal

Handelsplattform für digitale Währung 2025

Was ist Ouyi Exchange? Was nutzt Ouyi Exchange?
