[Oracle] 테이블 생성 스크립트 추출

[실습1] 오렌지에서 테이블 생성 스크립트를 추출하시오

ㄴ 마우스 커서를 두고 F4키 누르기

---

[실습2] dbms_metadata 패키지를 이용해서 테이블 생성 스크립트를 추출하시오

SCOTT @ ORA19 >
set long 50000
set pages 4000
set lines 4000

select dbms_metadata.get_ddl('TABLE', 'SALES100', 'SCOTT')
 from dual;

DBMS_METADATA.GET_DDL('TABLE','SALES100','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."SALES100"
   (    "PROD_ID" NUMBER NOT NULL ENABLE,
        "CUST_ID" NUMBER NOT NULL ENABLE,
        "TIME_ID" DATE NOT NULL ENABLE,
        "CHANNEL_ID" NUMBER NOT NULL ENABLE,
        "PROMO_ID" NUMBER NOT NULL ENABLE,
        "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE,
        "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE,
        "PROD_NAME" VARCHAR2(20)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2
55
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXT
ENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
 DEFAULT)
  TABLESPACE "USERS"


SCOTT @ ORA19 >

---

문제1. products 테이블의 생성 스크립트를 추출하시오

-- DDL Script was generated by Orange for ORACLE
-- using session 'SCOTT@YYS' on '2025/09/15 13:53:03.000000'.

CREATE TABLE SH.PRODUCTS
(
    PROD_ID                 NUMBER(6) NOT NULL,
    PROD_NAME               VARCHAR2(50) NOT NULL,
    PROD_DESC               VARCHAR2(4000) NOT NULL,
    PROD_SUBCATEGORY        VARCHAR2(50) NOT NULL,
    PROD_SUBCATEGORY_ID     NUMBER NOT NULL,
    PROD_SUBCATEGORY_DESC   VARCHAR2(2000) NOT NULL,
    PROD_CATEGORY           VARCHAR2(50) NOT NULL,
    PROD_CATEGORY_ID        NUMBER NOT NULL,
    PROD_CATEGORY_DESC      VARCHAR2(2000) NOT NULL,
    PROD_WEIGHT_CLASS       NUMBER(3) NOT NULL,
    PROD_UNIT_OF_MEASURE    VARCHAR2(20),
    PROD_PACK_SIZE          VARCHAR2(30) NOT NULL,
    SUPPLIER_ID             NUMBER(6) NOT NULL,
    PROD_STATUS             VARCHAR2(20) NOT NULL,
    PROD_LIST_PRICE         NUMBER(8,2) NOT NULL,
    PROD_MIN_PRICE          NUMBER(8,2) NOT NULL,
    PROD_TOTAL              VARCHAR2(13) NOT NULL,
    PROD_TOTAL_ID           NUMBER NOT NULL,
    PROD_SRC_ID             NUMBER,
    PROD_EFF_FROM           DATE,
    PROD_EFF_TO             DATE,
    PROD_VALID              VARCHAR2(1)
)
TABLESPACE TS700
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

CREATE UNIQUE INDEX SH.PRODUCTS_PK
ON SH.PRODUCTS (PROD_ID) 
TABLESPACE TS700
STORAGE
(
    INITIAL 64K
    NEXT 1M
);

ALTER TABLE SH.PRODUCTS
ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (PROD_ID)
        NOVALIDATE;

COMMENT ON TABLE SH.PRODUCTS IS 'dimension table';
COMMENT ON COLUMN SH.PRODUCTS.PROD_ID IS 'primary key';
COMMENT ON COLUMN SH.PRODUCTS.PROD_NAME IS 'product name';
COMMENT ON COLUMN SH.PRODUCTS.PROD_DESC IS 'product description';
COMMENT ON COLUMN SH.PRODUCTS.PROD_SUBCATEGORY IS 'product subcategory';
COMMENT ON COLUMN SH.PRODUCTS.PROD_SUBCATEGORY_DESC IS 'product subcategory description';
COMMENT ON COLUMN SH.PRODUCTS.PROD_CATEGORY IS 'product category';
COMMENT ON COLUMN SH.PRODUCTS.PROD_CATEGORY_DESC IS 'product category description';
COMMENT ON COLUMN SH.PRODUCTS.PROD_WEIGHT_CLASS IS 'product weight class';
COMMENT ON COLUMN SH.PRODUCTS.PROD_UNIT_OF_MEASURE IS 'product unit of measure';
COMMENT ON COLUMN SH.PRODUCTS.PROD_PACK_SIZE IS 'product package size';
COMMENT ON COLUMN SH.PRODUCTS.SUPPLIER_ID IS 'this column';
COMMENT ON COLUMN SH.PRODUCTS.PROD_STATUS IS 'product status';
COMMENT ON COLUMN SH.PRODUCTS.PROD_LIST_PRICE IS 'product list price';
COMMENT ON COLUMN SH.PRODUCTS.PROD_MIN_PRICE IS 'product minimum price';