How to query the structure of a table in oracle

Method: 1. Use the describe command to query the table structure, the syntax is "describe table name"; 2. When the target table belongs to the currently logged in user, you can use "SELECT DBMS_METADATA.GET_DDL('TABLE','uppercase Table name') FROM DUAL" statement query.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How does oracle query the structure of the table

DESCRIBE command

The usage method is as follows:

SQL> describe nchar_tst(nchar_tst为表名)

Display The results are as follows:

名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME                                              NCHAR(6)
ADDR                                              NVARCHAR2(16)
SAL                                                NUMBER(9,2)

2, DBMS_METADATA.GET_DDL package

The usage method is as follows:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST') FROM DUAL;

It must be ensured that the target table belongs to the currently logged in user, otherwise the result will not be found

If the displayed result is not complete, as follows:

CREATE TABLE "SCOTT"."NCHAR_TST"
(    "NAME" NCHAR(6),
"ADDR" NVARCHAR2(16

Then modify it as follows:

SQL> SET LONG 9999
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST') FROM DUAL;

Then the following result will be displayed:

DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST')
------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."NCHAR_TST"
(    "NAME" NCHAR(6),
"ADDR" NVARCHAR2(16),
"SAL" NUMBER(9,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

Recommended tutorial: "Oracle Tutorial》

The above is the detailed content of How to query the structure of a table in oracle. For more information, please follow other related articles on the PHP Chinese website!