1、对象定义

当执行CREATE命令建立对象时，Oracle会将对象定义存放到数据字典中。

03:31:40 SQL> select dbms_metadata.get_ddl('TABLE','DEPT') DDL FROM DUAL;

DDL

--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."DEPT"

(    "DEPTNO" NUMBER(2,0),

"DNAME" VARCHAR2(14

2、查看对象占用的空间

03:36:48 SQL> conn scott/tiger

Connected.

03:37:38 SQL> select bytes from user_segments where segment_name='EMP';

BYTES

----------

65536

查看段的存储：

03:40:41 SQL> analyze table emp compute statistics;

Table analyzed.

03:40:50 SQL> select empty_blocks,blocks from user_tables;

EMPTY_BLOCKS     BLOCKS

------------ ----------

0          5

3          5

0          0

0          5

0          5

6 rows selected.

03:41:08 SQL> select empty_blocks,blocks from user_tables

03:41:20   2  where table_name='EMP';

EMPTY_BLOCKS     BLOCKS

------------ ----------

3          5

3、查看列信息

03:45:14 SQL> col column_name format a15

03:45:17 SQL> col data_type for a15

03:45:26 SQL> col data_default for a15

03:46:05 SQL> select column_name,data_type,data_default

03:46:14   2   from user_tab_columns

03:46:23   3   where table_name='DEPT';

COLUMN_NAME     DATA_TYPE       DATA_DEFAULT

--------------- --------------- ---------------

DEPTNO          NUMBER

DNAME           VARCHAR2

LOC             VARCHAR2

4、查看约束信息

03:52:29 SQL>  select a.constraint_name,a.constraint_type,b.column_name,b.position

2  from user_constraints a ,user_cons_columns b

3* where a.constraint_name=b.constraint_name and b.table_name='EMP'

CONSTRAINT_NAME                CONSTRAINT_TYPE COLUMN_NAME       POSITION

------------------------------ --------------- --------------- ----------

FK_DEPTNO                      R               DEPTNO                   1

PK_EMP                         P               EMPNO                    1

5、查看用户名、权限、角色

4:20:02 SQL> select username,granted_role from user_role_privs;

USERNAME                       GRANTED_ROLE

------------------------------ ------------------------------

PUBLIC                         PLUSTRACE

SCOTT                          CONNECT

SCOTT                          RESOURCE