oracle查看表结构的3种方法
Three ways to get the structure of the table in oracle
Method 1:
The DESCRIBE SQL*PLUS command
SQL> DESC employees
Name
Null?
Type
----------------------------- --------
-------------------
EMPLOYEE_ID
NOT NULL
NUMBER(6)
FIRST_NAME
VARCHAR2(20)
LAST_NAME
NOT NULL
VARCHAR2(25)
EMAIL
NOT NULL VARCHAR2(25)
PHONE_NUMBER
VARCHAR2(20)
HIRE_DATE
NOT NULL
DATE
JOB_ID
NOT NULL VARCHAR2(10)
SALARY
NUMBER(8,2)
COMMISSION_PCT
NUMBER(2,2)
MANAGER_ID
NUMBER(6)
DEPARTMENT_ID
NUMBER(4)
Method 2:
The DBMS_METADATA.GET_DDL package
SQL> SELECT dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR')
FROM dual;
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
CREATE TABLE "HR"."EMPLOYEES"
(
"EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME"
SQL> SET LONG 9999
SQL> SET PAGESIZE 100
SQL> SELECT dbms_metadata.get_ddl('TABLE','EMPLOYEES','HR')
FROM dual;
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','HR')
--------------------------------------------------------------------------------
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,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN"
CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK"
UNIQUE ("EMAIL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS
255 COMPUTE STATISTICS NOLOGGING
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 "EXAMPLE"
ENABLE,
CONSTRAINT "EMP_EMP_ID_PK"
PRIMARY KEY ("EMPLOYEE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS
255 COMPUTE STATISTICS NOLOGGING
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 "EXAMPLE"
ENABLE,
CONSTRAINT "EMP_DEPT_FK"
FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS"
("DEPARTMENT_ID") ENABLE,
CONSTRAINT "EMP_JOB_FK"
FOREIGN KEY ("JOB_ID")
REFERENCES "HR"."JOBS"
("JOB_ID") ENABLE,
CONSTRAINT "EMP_MANAGER_FK"
FOREIGN KEY ("MANAGER_ID")
REFERENCES "HR"."EMPLOYEES"
("EMPLOYEE_ID") ENABLE
) SEGMENT CREATION
IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
NOCOMPRESS NOLOGGING
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 "EXAMPLE"
Method 3:
Using multiple tables to query the information of the table
user_tab_cols: 用来获取对应用户表的列信息;
user_col_comments: 用来获取对应用户表列的注释信息;
user_constraints: 用来获取用户表的约束条件;
user_cons_columns: 约束中用户可访问列。
SQL> SELECT
t.table_name,t.column_name,t.data_type,t.data_length,t.nullable,t.column_id,c.comments,
(SELECT CASE WHEN t.column_name = m.column_name
THEN 1 ELSE 0 END FROM DUAL)
iskey
FROM user_tab_cols
t,
user_col_comments c,
(SELECT m.column_name from user_constraints s,
user_cons_columns m
WHERE
m.table_name = 'EMPLOYEES'
AND
m.table_name=s.table_name
AND
m.constraint_name = s.constraint_name
AND
s.constraint_type='P') m
WHERE t.table_name =
'EMPLOYEES'
AND c.table_name =
t.table_name
AND c.column_name =
t.column_name
AND t.hidden_column =
'NO'
ORDER BY t.column_id;
TABLE_NAME COLUMN_NAME
DATA_TYPE DATA_LENGTH N
COLUMN_ID COMMENTS
ISKEY
---------- -------------- ---------- ----------- - ----------
------------------------------------------------------------------------
--------
EMPLOYEES EMPLOYEE_ID
NUMBER
22 N
1 Primary key of employees
table.
1
EMPLOYEES FIRST_NAME
VARCHAR2
20 Y
2 First name of the employee. A not null
column.
0
EMPLOYEES LAST_NAME
VARCHAR2
25 N
3 Last name of the employee. A not null
column.
0
EMPLOYEES EMAIL
VARCHAR2
25 N
4 Email id
of the employee
0
EMPLOYEES PHONE_NUMBER
VARCHAR2
20 Y
5 Phone
number of the employee; includes country code and area
code
0
EMPLOYEES HIRE_DATE
DATE
7 N
6 Date when the employee started on this job. A
not null column
0
EMPLOYEES JOB_ID
VARCHAR2
10 N
7 Current
job of the employee; foreign key to job_id column of the
jobs
0
table. A not null column.
EMPLOYEES SALARY
NUMBER
22 Y
8 Monthly salary of the employee. Must be
greater
0
than zero (enforced
byemp_salary_min)
EMPLOYEES COMMISSION_PCT
NUMBER
22 Y
9
Commission percentage of the employee; Only employees
in
0
sales department elgmmission
percentage
EMPLOYEES MANAGER_ID
NUMBER
22
Y
10 Manager id of the employee;
has same domain as manager_id in
0
departments table. Foreign ey to employee_id
column of empl
oyees table.(useful for reflexive joins and
CONNECT BY query)
EMPLOYEES DEPARTMENT_ID
NUMBER
22 Y
11
Department id where employee works; foreign key
to
0
department_id column of the departments
table
!!The End!!
加载中,请稍候......