oracle 基表 查询用户,oracle基表使用小记（一）_更新col$调整scott用户表test的排列次序...

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来查看数据字典对应的基表