oracle基表使用小記(一)_更新col$調整scott使用者表test的排列次序

wisdomone1發表於2010-08-06

SQL> conn /as sysdba
Connected.
SQL> desc col$;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#                                      NOT NULL NUMBER
 COL#                                      NOT NULL NUMBER
 SEGCOL#                                   NOT NULL NUMBER
 SEGCOLLENGTH                              NOT NULL NUMBER
 OFFSET                                    NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 LENGTH                                    NOT NULL NUMBER
 FIXEDSTORAGE                              NOT NULL NUMBER
 PRECISION#                                         NUMBER
 SCALE                                              NUMBER
 NULL$                                     NOT NULL NUMBER
 DEFLENGTH                                          NUMBER
 DEFAULT$                                           LONG
 INTCOL#                                   NOT NULL NUMBER
 PROPERTY                                  NOT NULL NUMBER
 CHARSETID                                          NUMBER
 CHARSETFORM                                        NUMBER
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE

SQL> desc dba_objects;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> desc scott.test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 B                                                  NUMBER(38)
 A                                                  NUMBER(38)

SQL> select object_name,object_id,data_object_id from dba_objects where object_name='TEST';

OBJECT_NAME
--------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
TEST
     52504          52504


SQL> select obj#,col#,name from col$ where name='TEST';

      OBJ#       COL# NAME
---------- ---------- ------------------------------
     50113         10 TEST

SQL> select obj#,col#,name from col$ where obj#=52504;

      OBJ#       COL# NAME
---------- ---------- ------------------------------
     52504          2 A
     52504          1 B

可以update col$ set col#=1 where name='A'  and obj#=52504,其他表的列同理,這樣就可以調整desc scott.test的顯示次序了

 

 

 小結:

1,col$等所有基表,僅在sysdba使用者可見且可操作

2,可以用set autot trace來檢視資料字典對應的基表

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-670457/,如需轉載,請註明出處,否則將追究法律責任。

相關文章