oracle12c_Row-archival
o
今天看了下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 NUMBERCopy after login
开启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.Copy after login
查看下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 221Copy after login
在没有进行归档之前数据存储和普通块一样，下面进行归档
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.Copy after login
可以看到在归档后，在没有设置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 221Copy after login
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.Copy after login
可以看出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>
Copy after login
- 数据字典存在
[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]$Copy after login
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)
);Copy after login
在没有数据字典的情况下把字段全部识别，不光是这个功能包含以前的存在隐藏列的都在恢复都需要注意
关闭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 1Copy after login
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#)
/Copy after login
原文地址：oracle12c_Row-archival, 感谢原作者分享。
Statement of this Website
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
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
How Long Does It Take To Beat Split Fiction?
3 weeks ago
By DDD
R.E.P.O. Save File Location: Where Is It & How to Protect It?
3 weeks ago
By DDD
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Digital audio output interface on the motherboard-SPDIF OUT
Jan 14, 2024 pm 04:42 PM
SPDIFOUT connection line sequence on the motherboard. Recently, I encountered a problem regarding the wiring sequence of the wires. I checked online. Some information says that 1, 2, and 4 correspond to out, +5V, and ground; while other information says that 1, 2, and 4 correspond to out, ground, and +5V. The best way is to check your motherboard manual. If you can't find the manual, you can use a multimeter to measure it. Find the ground first, then you can determine the order of the rest of the wiring. How to connect motherboard VDG wiring When connecting the VDG wiring of the motherboard, you need to plug one end of the VGA cable into the VGA interface of the monitor and the other end into the VGA interface of the computer's graphics card. Please be careful not to plug it into the motherboard's VGA port. Once connected, you can
How to install and register the btc trading app?
Feb 21, 2025 pm 07:09 PM
This article will provide a detailed introduction to how to install and register a Bitcoin trading application. The Bitcoin trading app allows users to manage and trade cryptocurrencies such as Bitcoin. The article guides users through the installation and registration process step by step, including downloading applications, creating accounts, performing identity verification, and first deposit. The goal of the article is to provide beginners with clear and easy-to-understand guidelines to help them easily enter the world of Bitcoin trading.
Top 10 global digital currency trading apps recommended (2025 currency trading software ranking)
Mar 12, 2025 pm 05:48 PM
This article recommends the top ten digital currency trading apps in the world, including Binance, OKX, Huobi Global, Coinbase, Kraken, Gate.io, KuCoin, Bitfinex, Gemini and Bitstamp. These platforms have their own characteristics in terms of transaction pair quantity, transaction speed, security, compliance, user experience, etc. For example, Binance is known for its high transaction speed and extensive services, while Coinbase is more suitable for novices. Choosing a platform that suits you requires comprehensive consideration of your own needs and risk tolerance. Learn about the world's mainstream digital currency trading platforms to help you conduct digital asset trading safely and efficiently.
Which digital currency app trading software is the best? Digital currency trading software big spot
Mar 07, 2025 pm 06:45 PM
There is no single "best" digital currency trading app, and the choice depends on personal needs. 1. OKX has powerful functions and rich currency types; 2. Binance has high liquidity and diverse transaction types; 3. Gate.io provides unique functions such as staking mining; 4. Huobi Global has a friendly interface and multi-language support; 5. Kraken focuses on security; 6. Coinbase is suitable for beginners and focuses on user education. When choosing, you need to consider security, liquidity, handling fees, functions, user experience and other factors.
Ouyi Exchange Download Official Portal
Feb 21, 2025 pm 07:51 PM
Ouyi, also known as OKX, is a world-leading cryptocurrency trading platform. The article provides a download portal for Ouyi's official installation package, which facilitates users to install Ouyi client on different devices. This installation package supports Windows, Mac, Android and iOS systems. Users can choose the corresponding version to download according to their device type. After the installation is completed, users can register or log in to the Ouyi account, start trading cryptocurrencies and enjoy other services provided by the platform.
Top 10 virtual digital currency trading platforms 2025 rankings, top ten virtual currency app exchanges
Feb 21, 2025 pm 09:03 PM
This article introduces 10 mainstream cryptocurrency exchanges, covering basic information such as their establishment time, service scope, security, liquidity, transaction fees, etc. These exchanges include: OKX, Binance, Gate.io, Bitget, Coinbase, Huobi, KuCoin, Crypto.com, Gemini and Kraken.
Digital currency trading platform 2025
Mar 04, 2025 pm 07:06 PM
Based on global information, this article compiles the rankings of the world's leading virtual digital currency trading platforms, including Binance, OKX, Gate.io, Huobi, Coinbase, Kraken, Crypto.com, bitget, KuCoin and Bitstamp. These platforms have their own characteristics in terms of user count, transaction volume, transaction types, security, compliance, etc. For example, Binance is known for its large user base and extensive trading options, while Coinbase is known for its leadership and ease of use in the US market. Choosing a suitable trading platform requires weighing factors such as security, fees, and trading products based on your own needs.
What is Ouyi Exchange? What is the use of Ouyi Exchange?
Feb 15, 2025 pm 08:57 PM
Ouyi Exchange, formerly known as Binance, is one of the world's leading cryptocurrency trading platforms. It was founded in 2017 by Zhao Changpeng and is headquartered in Hong Kong, China. Ouyi Exchange provides users with a wide range of cryptocurrency trading services, including spot trading, futures trading, over-the-counter trading and crypto asset management.
