当前位置:Gxlcms > mysql > oracle12c_Row-archival

oracle12c_Row-archival

时间:2021-07-01 10:21:17 帮助过:48人阅读

今天看了下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 < 2) THEN h.row_cnt
                               -- top-freq
                               WHEN bitand(h.spare2, 64) > 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 < 1))
                               THEN h.row_cnt
                               -- height
                               ELSE h.bucket_cnt
                               END,
                               h.TIMESTAMP#, h.sample_size,
                               decode(c.charsetform, 1, 'CHAR_CS',
                               2, 'NCHAR_CS',
                               3, NLS_CHARSET_NAME(c.charsetid),
                               4, 'ARG:'||c.charsetid),
                               decode(c.charsetid, 0, to_number(NULL),
                               nls_charset_decl_len(c.LENGTH, c.charsetid)),
                               decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
                               decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
                               decode(bitand(h.spare2, 8), 8, 'INCREMENTAL ', '') ||
                               decode(bitand(h.spare2, 256), 256, 'HISTOGRAM_ONLY ', '') ||
                               decode(bitand(h.spare2, 512), 512, 'STATS_ON_LOAD ', ''),
                               h.avgcln,
                               c.spare3,
                               decode(c.TYPE#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                               96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                               NULL),
                               decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
                               decode(o.STATUS, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
                               decode(bitand(ac.flags, 2), 2, 'NO',
                               decode(bitand(ac.flags, 4), 4, 'NO',
                               decode(bitand(ac.flags, 8), 8, 'NO',
                               'N/A')))),
                               decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
                               'NO')),
                               decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
                               'NO')),
                               decode(c.segcol#, 0, to_number(NULL), c.segcol#), c.intcol#,
                               -- warning! If you update stats related info, make sure to also update
                               -- GTT session private stats in cdoptim.sql
                               CASE WHEN nvl(h.row_cnt,0) = 0 THEN 'NONE'
                               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 < 2) THEN 'HYBRID'
                               WHEN bitand(h.spare2, 64) > 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 < 1))
                               THEN 'FREQUENCY'
                               ELSE 'HEIGHT BALANCED'
                               END,
                               decode(bitand(c.property, 1024), 1024,
                               (SELECT decode(bitand(cl.property, 1), 1, rc.name, cl.name)
                               FROM sys.col$ cl, attrcol$ rc WHERE cl.intcol# = c.intcol#-1
                               AND cl.obj# = c.obj# AND c.obj# = rc.obj#(+) AND
                               cl.intcol# = rc.intcol#(+)),
                               decode(bitand(c.property, 1), 0, c.name,
                               (SELECT tc.name FROM sys.attrcol$ tc
                               WHERE c.obj# = tc.obj# AND c.intcol# = tc.intcol#))),
                               decode(bitand(c.property, 17179869184), 17179869184, 'YES',
                               decode(bitand(c.property, 32), 32, 'NO', 'YES')),
                               decode(bitand(c.property, 68719476736), 68719476736, 'YES', 'NO'),
                               decode(bitand(c.property, 137438953472 + 274877906944),
                               137438953472, 'YES', 274877906944, 'YES', 'NO'),
                               decode(c.property, 0, 'NO', decode(bitand(c.property, 8796093022208),
                               8796093022208, 'YES', 'NO')),
                               CASE WHEN c.evaledition# IS NULL THEN NULL
                               ELSE (SELECT name FROM obj$ WHERE obj# = c.evaledition#) END,
                               CASE WHEN c.unusablebefore# IS NULL THEN NULL
                               ELSE (SELECT name FROM obj$ WHERE obj# = c.unusablebefore#) END,
                               CASE WHEN c.unusablebeginning# IS NULL THEN NULL
                               ELSE (SELECT name FROM obj$ WHERE obj# = c.unusablebeginning#) END
                               FROM sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h, sys.USER$ u,
                               sys.coltype$ ac, sys.obj$ ot, sys."_BASE_USER" ut, sys.tab$ t
                               WHERE o.obj# = c.obj#
                               AND o.owner# = u.USER#
                               AND o.obj# = t.obj#(+)
                               AND c.obj# = h.obj#(+) AND c.intcol# = h.intcol#(+)
                               AND c.obj# = ac.obj#(+) AND c.intcol# = ac.intcol#(+)
                               AND ac.toid = ot.oid$(+)
                               AND ot.TYPE#(+) = 13
                               AND ot.owner# = ut.USER#(+)
                               AND (o.TYPE# IN (3, 4)                                     /* cluster, view */
                               OR
                               (o.TYPE# = 2     /* tables, excluding iot - overflow and nested tables */
                               AND
                               NOT EXISTS (SELECT NULL
                               FROM sys.tab$ t
                               WHERE t.obj# = o.obj#
                               AND (bitand(t.property, 512) = 512 OR
                               bitand(t.property, 8192) = 8192))))
?
?
DBA_TAB_COLS                   SELECT
                               OWNER, TABLE_NAME,
                               COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
                               DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
                               DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
                               DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
                               CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
                               GLOBAL_STATS,
                               USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
                               V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN,
                               SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID, HISTOGRAM, QUALIFIED_COL_NAME,
                               USER_GENERATED, DEFAULT_ON_NULL, IDENTITY_COLUMN, SENSITIVE_COLUMN,
                               EVALUATION_EDITION, UNUSABLE_BEFORE, UNUSABLE_BEGINNING
                               FROM dba_tab_cols_v$
?
?
?
no ROWS selected

测试下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#)
/

人气教程排行