I want to know how I can retrieve the all column names,their data type,and any constraints that are defined for any column.

为了做到这一点,您可以查询(取决于授予您的权限)[user | all | dba] _tab_columns,[user | all | dba] _cons_columns,[user | all | dba] _constraints视图.

这是一个快速的例子：

select decode( t.table_name,lag(t.table_name,1) over(order by t.table_name),null,t.table_name ) as table_name -- <- just to eliminate,t.column_name                       -- repeated tab_name,t.data_type,cc.constraint_name,uc.constraint_type
   from user_tab_columns t
        left join user_cons_columns cc
          on (cc.table_name = t.table_name and
              cc.column_name = t.column_name)
        left join user_constraints uc
          on (t.table_name = uc.table_name and
              uc.constraint_name = cc.constraint_name )
 where t.table_name in ('EMPLOYEES','DEPARTMENTS');

结果：

TABLE_NAME    COLUMN_NAME       DATA_TYPE      CONSTRAINT_NAME   CONSTRAINT_TYPE
------------- ----------------- -------------- -------------------------------
DEPARTMENTS   LOCATION_ID       NUMBER         DEPT_LOC_FK       R
              DEPARTMENT_ID     NUMBER         DEPT_ID_PK        P
              DEPARTMENT_NAME   VARCHAR2       DEPT_NAME_NN      C
              MANAGER_ID        NUMBER         DEPT_MGR_FK       R
EMPLOYEES     SALARY            NUMBER         EMP_SALARY_MIN    C
              PHONE_NUMBER      VARCHAR2                            
              EMPLOYEE_ID       NUMBER         EMP_EMP_ID_PK     P
              DEPARTMENT_ID     NUMBER         EMP_DEPT_FK       R
              JOB_ID            VARCHAR2       EMP_JOB_FK        R
              MANAGER_ID        NUMBER         EMP_MANAGER_FK    R
              COMMISSION_PCT    NUMBER                              
              FIRST_NAME        VARCHAR2                            
              JOB_ID            VARCHAR2       EMP_JOB_NN        C
              HIRE_DATE         DATE           EMP_HIRE_DATE_NN  C
              EMAIL             VARCHAR2       EMP_EMAIL_NN      C
              LAST_NAME         VARCHAR2       EMP_LAST_NAME_NN  C
              EMAIL             VARCHAR2       EMP_EMAIL_UK      U

17 rows selected

还要检索表的完整规范(如果需要),可以使用该包的dbms_Metadata包和get_ddl函数：

select dbms_Metadata.get_ddl('TABLE','EMPLOYEES') as table_ddl
  from dual;

 table_ddl
 --------------------------------------------------------------------------------

  CREATE TABLE "HR"."EMPLOYEES"
   ("EMPLOYEE_ID" NUMBER(6,0),"FIRST_NAME" VARCHAR2(20),"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,"PHONE_NUMBER" VARCHAR2(20),"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,"SALARY" NUMBER(8,2),"COMMISSION_PCT" NUMBER(2,"MANAGER_ID" NUMBER(6,"DEPARTMENT_ID" NUMBER(4,CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")

   -- ... other attributes

   )