[20141106]type and table.txt

[20141106]type and table.txt

--看看建立type與表.

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

CREATE OR REPLACE TYPE SCOTT.ty_obj as object
      (
       output varchar2(250)
      );
/

SCOTT@test> CREATE TABLE TX OF TY_OBJ;
Table created.

SCOTT@test> select * from dba_tables where table_name='TX' and owner=user;
no rows selected
--竟然無法查詢.

SCOTT@test> select owner,table_name,tablespace_name from dba_all_tables where table_name='TX' and owner=user;
OWNER  TABLE_NAME TABLESPACE_NAME
------ ---------- ------------------------------
SCOTT  TX         USERS

SCOTT@test> @ddl scott.tx
C100
----------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."TX" OF "SCOTT"."TY_OBJ"
OIDINDEX  ( PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

SCOTT@test> select column_name,data_type,column_id,hidden_column,virtual_column,segment_column_id,internal_column_id,data_default c30 from dba_tab_cols where owner=user and table_name='TX';
COLUMN_NAME          DATA_TYPE   COLUMN_ID HID VIR SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID C30
-------------------- ---------- ---------- --- --- ----------------- ------------------ ------------------------------
SYS_NC_OID$          RAW                   YES NO                  1                  1 SYS_OP_GUID()
SYS_NC_ROWINFO$      TY_OBJ                YES YES                                    2
OUTPUT               VARCHAR2            1 NO  NO                  2                  3

--可以發現僅僅顯示OUTPUT,在段中儲存SYS_NC_OID$,OUTPUT.SYS_NC_OID$的預設值SYS_OP_GUID(),guid的串.好像隨機生產的串,衝突的機率很低.

--如何插入呢?
SCOTT@test> insert into tx (output) values ('this is a test!');
1 row created.

SCOTT@test> select * from tx;
OUTPUT
-----------------------------
this is a test!

SCOTT@test> select SYS_NC_OID$ c40 ,SYS_NC_ROWINFO$ c50 ,OUTPUT c20 from tx ;
C40                                      C50(OUTPUT)                                        C20
---------------------------------------- -------------------------------------------------- --------------------
0727ECBEB9CB37C2E0532864A8C0A4BF         TY_OBJ('this is a test!')                          this is a test!

SCOTT@test> select TY_OBJ('aaa') c20 from dual ;
C20(OUTPUT)
--------------------
TY_OBJ('aaa')

SCOTT@test> select SYS_OP_GUID() from dual ;
SYS_OP_GUID()
--------------------------------
0727ECBEB9CC37C2E0532864A8C0A4BF

--看看塊記憶體儲:

SCOTT@test> select rowid,SYS_NC_OID$ c40 ,SYS_NC_ROWINFO$ c50 ,OUTPUT c20 from tx ;
ROWID              C40                                      C50(OUTPUT)                                        C20
------------------ ---------------------------------------- -------------------------------------------------- --------------------
AABHdYAAEAAAAcDAAA 0727ECBEB9CB37C2E0532864A8C0A4BF         TY_OBJ('this is a test!')                          this is a test!

SCOTT@test> @lookup_rowid AABHdYAAEAAAAcDAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    292696          4       1795          0 4,1795               alter system dump datafile 4 block 1795;

--bbed 觀察:
BBED> set dba  4,1795
        DBA             0x01000703 (16779011 4,1795)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8152     0x2c

BBED> x /rxc
rowdata[0]                                  @8152
----------
flag@8152: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8153: 0x01
cols@8154:    2

col   0[16] @8155:  0x07  0x27  0xec  0xbe  0xb9  0xcb  0x37  0xc2  0xe0  0x53  0x28  0x64  0xa8  0xc0  0xa4  0xbf
col   1[15] @8172: this is a test!