查看表结构的两种方式-CSDN博客

1.desc table

如

SQL> desc prd;
Name       Type           Nullable Default Comments
---------- -------------- -------- ------- ------------------------------
CODE       VARCHAR2(30)                    Product Code
LNAME      VARCHAR2(400)                   Product Long Name
SNAME      VARCHAR2(30)   Y                Product Short Name
CNAME      VARCHAR2(400) Y                Product Chinese Name
GRP        VARCHAR2(30)                    Product Group
SEASON     VARCHAR2(10)   Y
CTRPTY     VARCHAR2(30)   Y
CTRPTYREF VARCHAR2(100) Y
PATTERN    VARCHAR2(100) Y                Paper Pattern
QUALITY    VARCHAR2(400) Y
UNIT       VARCHAR2(5)                     Unit
CRCY       VARCHAR2(5)    Y                Currency
CONVUNIT   VARCHAR2(5)    Y
FACTOR     NUMBER         Y
WEIGHT     NUMBER         Y
WEIGHTUNIT VARCHAR2(5)    Y
SPECUNIT   VARCHAR2(5)    Y                Specification Measurement Unit
RELPRD     VARCHAR2(30)   Y
STAFF      VARCHAR2(30)   Y
TEAM       VARCHAR2(5)    Y
CL         VARCHAR2(400) Y
SZ         VARCHAR2(400) Y
PRC        NUMBER         Y
NOTE       NUMBER(20)                      Note
REF1       VARCHAR2(100) Y
REF2       VARCHAR2(100) Y
REF3       VARCHAR2(100) Y
REF4       VARCHAR2(100) Y
REF5       VARCHAR2(100) Y
REF6       VARCHAR2(100) Y
REF7       VARCHAR2(100) Y
REF8       VARCHAR2(100) Y
REF9       VARCHAR2(100) Y
STFG       VARCHAR2(5)    Y                Status Flag
STDT       DATE           Y                Status Date
STREASON   VARCHAR2(400) Y                Status Reason
CRSITE     VARCHAR2(30)                    Create Site
UPDUSER    VARCHAR2(30)                    Update User
UPDDTTM    DATE                            Update Date Time
GRP1       VARCHAR2(30)   Y
GRP2       VARCHAR2(30)   Y
GRP3       VARCHAR2(30)   Y
GRP4       VARCHAR2(30)   Y
GRP5       VARCHAR2(30)   Y
GRP6       VARCHAR2(30)   Y
GRP7       VARCHAR2(30)   Y
GRP8       VARCHAR2(30)   Y
GRP9       VARCHAR2(30)   Y
GRPPCT1    NUMBER         Y
GRPPCT2    NUMBER         Y
GRPPCT3    NUMBER         Y
GRPPCT4    NUMBER         Y
GRPPCT5    NUMBER         Y
GRPPCT6    NUMBER         Y
GRPPCT7    NUMBER         Y
GRPPCT8    NUMBER         Y
GRPPCT9    NUMBER         Y
TMPLTY     VARCHAR2(100) Y
TMPLNO     VARCHAR2(30)   Y
WASTE      NUMBER         Y
LEADDAY    NUMBER         Y
MINQTY     NUMBER         Y
RNDQTY     NUMBER         Y
SRC        VARCHAR2(400) Y
PCKUNIT    VARCHAR2(5)    Y
PCKQTY     NUMBER         Y
PCKGW      NUMBER         Y
PCKGWUNIT VARCHAR2(5)    Y
PCKDIM1    NUMBER         Y
PCKDIM2    NUMBER         Y
PCKDIM3    NUMBER         Y
PCKDIM4    NUMBER         Y
PCKDIM5    NUMBER         Y
PCKDIMUNIT VARCHAR2(5)    Y
PCKCBM     NUMBER         Y
PCKRMK     VARCHAR2(2000) Y
REFDT1     DATE           Y
REFDT2     DATE           Y
REFDT3     DATE           Y
REFDT4     DATE           Y
REFDT5     DATE           Y
REFDT6     DATE           Y
REFDT7     DATE           Y
REFDT8     DATE           Y
REFDT9     DATE           Y
REFNUM1    NUMBER         Y
REFNUM2    NUMBER         Y
REFNUM3    NUMBER         Y
REFNUM4    NUMBER         Y
REFNUM5    NUMBER         Y
REFNUM6    NUMBER         Y
REFNUM7    NUMBER         Y
REFNUM8    NUMBER         Y
REFNUM9    NUMBER         Y
READYDAY   NUMBER         Y

2.dbms_metadata.get_ddl

这种方式看的表结构比较详细

如 select dbms_metadata.get_ddl('TABLE','PRD') from dual;

CREATE TABLE "DBOWN"."PRD"
   ( "CODE" VARCHAR2(30) NOT NULL ENABLE,
"LNAME" VARCHAR2(400) NOT NULL ENABLE,
"SNAME" VARCHAR2(30),
"CNAME" VARCHAR2(400),
"GRP" VARCHAR2(30) NOT NULL ENABLE,
"SEASON" VARCHAR2(10),
"CTRPTY" VARCHAR2(30),
"CTRPTYREF" VARCHAR2(100),
"PATTERN" VARCHAR2(100),
"QUALITY" VARCHAR2(400),
"UNIT" VARCHAR2(5) NOT NULL ENABLE,
"CRCY" VARCHAR2(5),
"CONVUNIT" VARCHAR2(5),
"FACTOR" NUMBER,
"WEIGHT" NUMBER,
"WEIGHTUNIT" VARCHAR2(5),
"SPECUNIT" VARCHAR2(5),
"RELPRD" VARCHAR2(30),
"STAFF" VARCHAR2(30),
"TEAM" VARCHAR2(5),
"CL" VARCHAR2(400),
"SZ" VARCHAR2(400),
"PRC" NUMBER,
"NOTE" NUMBER(20,0) NOT NULL ENABLE,
"REF1" VARCHAR2(100),
"REF2" VARCHAR2(100),
"REF3" VARCHAR2(100),
"REF4" VARCHAR2(100),
"REF5" VARCHAR2(100),
"REF6" VARCHAR2(100),
"REF7" VARCHAR2(100),
"REF8" VARCHAR2(100),
"REF9" VARCHAR2(100),
"STFG" VARCHAR2(5),
"STDT" DATE,
"STREASON" VARCHAR2(400),
"CRSITE" VARCHAR2(30) NOT NULL ENABLE,
"UPDUSER" VARCHAR2(30) NOT NULL ENABLE,
"UPDDTTM" DATE NOT NULL ENABLE,
"GRP1" VARCHAR2(30),
"GRP2" VARCHAR2(30),
"GRP3" VARCHAR2(30),
"GRP4" VARCHAR2(30),
"GRP5" VARCHAR2(30),
"GRP6" VARCHAR2(30),
"GRP7" VARCHAR2(30),
"GRP8" VARCHAR2(30),
"GRP9" VARCHAR2(30),
"GRPPCT1" NUMBER,
"GRPPCT2" NUMBER,
"GRPPCT3" NUMBER,
"GRPPCT4" NUMBER,
"GRPPCT5" NUMBER,
"GRPPCT6" NUMBER,
"GRPPCT7" NUMBER,
"GRPPCT8" NUMBER,
"GRPPCT9" NUMBER,
"TMPLTY" VARCHAR2(100),
"TMPLNO" VARCHAR2(30),
"WASTE" NUMBER,
"LEADDAY" NUMBER,
"MINQTY" NUMBER,
"RNDQTY" NUMBER,
"SRC" VARCHAR2(400),
"PCKUNIT" VARCHAR2(5),
"PCKQTY" NUMBER,
"PCKGW" NUMBER,
"PCKGWUNIT" VARCHAR2(5),
"PCKDIM1" NUMBER,
"PCKDIM2" NUMBER,
"PCKDIM3" NUMBER,
"PCKDIM4" NUMBER,
"PCKDIM5" NUMBER,
"PCKDIMUNIT" VARCHAR2(5),
"PCKCBM" NUMBER,
"PCKRMK" VARCHAR2(2000),
"REFDT1" DATE,
"REFDT2" DATE,
"REFDT3" DATE,
"REFDT4" DATE,
"REFDT5" DATE,
"REFDT6" DATE,
"REFDT7" DATE,
"REFDT8" DATE,
"REFDT9" DATE,
"REFNUM1" NUMBER,
"REFNUM2" NUMBER,
"REFNUM3" NUMBER,
"REFNUM4" NUMBER,
"REFNUM5" NUMBER,
"REFNUM6" NUMBER,
"REFNUM7" NUMBER,
"REFNUM8" NUMBER,
"REFNUM9" NUMBER,
"READYDAY" NUMBER,
CONSTRAINT "PRD$PK" PRIMARY KEY ("CODE") DEFERRABLE
USING INDEX PCTFREE 10 INITRANS 3 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 303104 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA_NDX" ENABLE,
CONSTRAINT "PRD$FK$PRD" FOREIGN KEY ("RELPRD")
   REFERENCES "DBOWN"."PRD" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$UNIT_PCK" FOREIGN KEY ("PCKUNIT")
   REFERENCES "DBOWN"."UNIT" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$UNIT_PCKGW" FOREIGN KEY ("PCKGWUNIT")
   REFERENCES "DBOWN"."UNIT" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$UNIT_PCKDIM" FOREIGN KEY ("PCKDIMUNIT")
   REFERENCES "DBOWN"."UNIT" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$UNIT_WEIGHT" FOREIGN KEY ("WEIGHTUNIT")
   REFERENCES "DBOWN"."UNIT" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$SEASON" FOREIGN KEY ("SEASON")
   REFERENCES "DBOWN"."SEASON" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP" FOREIGN KEY ("GRP")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$UNIT" FOREIGN KEY ("UNIT")
   REFERENCES "DBOWN"."UNIT" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$UNIT_CONV" FOREIGN KEY ("CONVUNIT")
   REFERENCES "DBOWN"."UNIT" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$UNIT_SPEC" FOREIGN KEY ("SPECUNIT")
   REFERENCES "DBOWN"."UNIT" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$TEAM" FOREIGN KEY ("TEAM")
   REFERENCES "DBOWN"."TEAM" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$STAFF" FOREIGN KEY ("STAFF")
   REFERENCES "DBOWN"."STAFF" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$CTRPTY" FOREIGN KEY ("CTRPTY")
   REFERENCES "DBOWN"."CTRPTY" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$CRCY" FOREIGN KEY ("CRCY")
   REFERENCES "DBOWN"."CRCY" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$TMPL" FOREIGN KEY ("TMPLTY", "TMPLNO")
   REFERENCES "DBOWN"."TMPL" ("TMPLTY", "TMPLNO") ENABLE,
CONSTRAINT "PRD$FK$PRDGP1" FOREIGN KEY ("GRP1")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP2" FOREIGN KEY ("GRP2")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP3" FOREIGN KEY ("GRP3")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP4" FOREIGN KEY ("GRP4")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP5" FOREIGN KEY ("GRP5")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP6" FOREIGN KEY ("GRP6")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP7" FOREIGN KEY ("GRP7")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP8" FOREIGN KEY ("GRP8")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE,
CONSTRAINT "PRD$FK$PRDGP9" FOREIGN KEY ("GRP9")
   REFERENCES "DBOWN"."PRDGP" ("CODE") DEFERRABLE ENABLE
   ) CLUSTER "DBOWN"."PRD$CLUSTER" ("CODE")