오라클 ATP SH 테이블 생성(Autonomous Transaction Processing)

OS환경 : Oracle Cloud Linux (64bit)

DB 환경 : Oracle Cloud ATP Database 18.4.0.0

방법 : 오라클 ATP SH 테이블 생성(Autonomous Transaction Processing)

오라클 ATP 환경에서 오라클 샘플 스키마인 SH 테이블(sales history)을 생성하는법을 설명함

아래 SQL을 복사 하여 SQL Developer 워크 시트에 붙여 넣고 스크립트를 실행하여 SH테이블을 생성

SH 테이블 생성 구문

CREATE TABLE atpc_user.sales (
    prod_id             NUMBER          NOT NULL,
    cust_id             NUMBER          NOT NULL,
    time_id             DATE            NOT NULL,
    channel_id          NUMBER          NOT NULL,
    promo_id            NUMBER          NOT NULL,
    quantity_sold       NUMBER(10,2)    NOT NULL,
    amount_sold         NUMBER(10,2)    NOT NULL);
     
CREATE TABLE atpc_user.salestemp (
    prod_id             NUMBER          NOT NULL,
    cust_id             NUMBER          NOT NULL,
    time_id             DATE            NOT NULL,
    channel_id          NUMBER          NOT NULL,
    promo_id            NUMBER          NOT NULL,
    quantity_sold       NUMBER(10,2)    NOT NULL,
    amount_sold         NUMBER(10,2)    NOT NULL,
    unit_cost         NUMBER(10,2)    ,
    unit_price         NUMBER(10,2)    );
     
 
CREATE TABLE atpc_user.costs (
    prod_id     NUMBER          NOT NULL,
    time_id     DATE                    NOT NULL,
    promo_id    NUMBER          NOT NULL,
    channel_id  NUMBER          NOT NULL,
    unit_cost   NUMBER(10,2)    NOT NULL,
    unit_price  NUMBER(10,2)    NOT NULL);
 
CREATE TABLE atpc_user.times (
    time_id                     DATE            NOT NULL,
    day_name                    VARCHAR2(9)     NOT NULL,
    day_number_in_week          NUMBER(1)       NOT NULL,
    day_number_in_month         NUMBER(2)       NOT NULL,
    calendar_week_number        NUMBER(2)       NOT NULL,
    fiscal_week_number          NUMBER(2)       NOT NULL,
    week_ending_day             DATE            NOT NULL,
    week_ending_day_id          NUMBER          NOT NULL,
    calendar_month_number       NUMBER(2)       NOT NULL,
    fiscal_month_number         NUMBER(2)       NOT NULL,
    calendar_month_desc         VARCHAR2(8)     NOT NULL,
    calendar_month_id           NUMBER          NOT NULL,
    fiscal_month_desc           VARCHAR2(8)     NOT NULL,
    fiscal_month_id             NUMBER          NOT NULL,
    days_in_cal_month           NUMBER          NOT NULL,
    days_in_fis_month           NUMBER          NOT NULL,
    end_of_cal_month            DATE            NOT NULL,
    end_of_fis_month            DATE            NOT NULL,
    calendar_month_name         VARCHAR2(9)     NOT NULL,
    fiscal_month_name           VARCHAR2(9)     NOT NULL,
    calendar_quarter_desc       CHAR(7)         NOT NULL,
    calendar_quarter_id         NUMBER          NOT NULL,
    fiscal_quarter_desc         CHAR(7)         NOT NULL,
    fiscal_quarter_id           NUMBER          NOT NULL,
    days_in_cal_quarter         NUMBER          NOT NULL,
    days_in_fis_quarter         NUMBER          NOT NULL,
    end_of_cal_quarter          DATE            NOT NULL,
    end_of_fis_quarter          DATE            NOT NULL,
    calendar_quarter_number     NUMBER(1)       NOT NULL,
    fiscal_quarter_number       NUMBER(1)       NOT NULL,
    calendar_year               NUMBER(4)       NOT NULL,
    calendar_year_id            NUMBER          NOT NULL,
    fiscal_year                 NUMBER(4)       NOT NULL,
    fiscal_year_id              NUMBER          NOT NULL,
    days_in_cal_year            NUMBER          NOT NULL,
    days_in_fis_year            NUMBER          NOT NULL,
    end_of_cal_year             DATE            NOT NULL,
    end_of_fis_year             DATE            NOT NULL );
 
CREATE TABLE atpc_user.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)     );
 
CREATE TABLE atpc_user.channels (
    channel_id                  NUMBER          NOT NULL,
    channel_desc                VARCHAR2(20)    NOT NULL,
    channel_class               VARCHAR2(20)    NOT NULL,
    channel_class_id            NUMBER          NOT NULL,
    channel_total               VARCHAR2(13)    NOT NULL,
    channel_total_id            NUMBER          NOT NULL);
 
CREATE TABLE atpc_user.promotions (
    promo_id                    NUMBER(6)       NOT NULL,
    promo_name                  VARCHAR2(30)    NOT NULL,
    promo_subcategory           VARCHAR2(30)    NOT NULL,
    promo_subcategory_id        NUMBER          NOT NULL,
    promo_category              VARCHAR2(30)    NOT NULL,
    promo_category_id           NUMBER          NOT NULL,
    promo_cost                  NUMBER(10,2)    NOT NULL,
    promo_begin_date            DATE            NOT NULL,
    promo_end_date              DATE            NOT NULL,
    promo_total                 VARCHAR2(15)    NOT NULL,
    promo_total_id              NUMBER          NOT NULL);
 
CREATE TABLE atpc_user.customers (
    cust_id                     NUMBER          NOT NULL,
    cust_first_name             VARCHAR2(20)    NOT NULL,
    cust_last_name              VARCHAR2(40)    NOT NULL,
    cust_gender                 CHAR(1)         NOT NULL,
    cust_year_of_birth          NUMBER(4)       NOT NULL,
    cust_marital_status         VARCHAR2(20)    ,
    cust_street_address         VARCHAR2(40)    NOT NULL,
    cust_postal_code            VARCHAR2(10)    NOT NULL,
    cust_city                   VARCHAR2(30)    NOT NULL,
    cust_city_id                NUMBER          NOT NULL,
    cust_state_province         VARCHAR2(40)    NOT NULL,
    cust_state_province_id      NUMBER          NOT NULL,
    country_id                  NUMBER          NOT NULL,
    cust_main_phone_number      VARCHAR2(25)    NOT NULL,
    cust_income_level           VARCHAR2(30)    ,
    cust_credit_limit           NUMBER          ,
    cust_email                  VARCHAR2(50)    ,
    cust_total                  VARCHAR2(14)    NOT NULL,
    cust_total_id               NUMBER          NOT NULL,
    cust_src_id                 NUMBER          ,
    cust_eff_from               DATE            ,
    cust_eff_to                 DATE            ,
    cust_valid                  VARCHAR2(1)     );
 
CREATE TABLE atpc_user.countries (
    country_id                  NUMBER          NOT NULL,
    country_iso_code            CHAR(2)         NOT NULL,
    country_name                VARCHAR2(40)    NOT NULL,
    country_subregion           VARCHAR2(30)    NOT NULL,
    country_subregion_id        NUMBER          NOT NULL,
    country_region              VARCHAR2(20)    NOT NULL,
    country_region_id           NUMBER          NOT NULL,
    country_total               VARCHAR2(11)    NOT NULL,
    country_total_id            NUMBER          NOT NULL,
    country_name_hist           VARCHAR2(40));
 
 
CREATE TABLE atpc_user.supplementary_demographics
  ( CUST_ID          NUMBER not null,
    EDUCATION        VARCHAR2(21),
    OCCUPATION       VARCHAR2(21),
    HOUSEHOLD_SIZE   VARCHAR2(21),
    YRS_RESIDENCE    NUMBER,
    AFFINITY_CARD    NUMBER(10),
    bulk_pack_diskettes NUMBER(10),
    flat_panel_monitor  NUMBER(10),
    home_theater_package NUMBER(10),
    bookkeeping_application NUMBER(10),
    printer_supplies NUMBER(10),
    y_box_games NUMBER(10),
    os_doc_set_kanji NUMBER(10),
    COMMENTS         VARCHAR2(4000));
 
ALTER TABLE atpc_user.promotions
  ADD CONSTRAINT promo_pk
  PRIMARY KEY (promo_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.sales
  ADD CONSTRAINT sales_promo_fk
  FOREIGN KEY (promo_id) REFERENCES atpc_user.promotions (promo_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.costs
  ADD CONSTRAINT costs_promo_fk
  FOREIGN KEY (promo_id) REFERENCES atpc_user.promotions (promo_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.customers
  ADD CONSTRAINT customers_pk
  PRIMARY KEY (cust_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.sales
  ADD CONSTRAINT sales_customer_fk
  FOREIGN KEY (cust_id) REFERENCES atpc_user.customers (cust_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.products
  ADD CONSTRAINT products_pk
  PRIMARY KEY (prod_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.sales
  ADD CONSTRAINT sales_product_fk
  FOREIGN KEY (prod_id) REFERENCES atpc_user.products (prod_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.costs
  ADD CONSTRAINT costs_product_fk
  FOREIGN KEY (prod_id) REFERENCES atpc_user.products (prod_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.times
  ADD CONSTRAINT times_pk
  PRIMARY KEY (time_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.sales
  ADD CONSTRAINT sales_time_fk
  FOREIGN KEY (time_id) REFERENCES atpc_user.times (time_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.costs
  ADD CONSTRAINT costs_time_fk
  FOREIGN KEY (time_id) REFERENCES atpc_user.times (time_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.channels
  ADD CONSTRAINT channels_pk
  PRIMARY KEY (channel_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.sales
  ADD CONSTRAINT sales_channel_fk
  FOREIGN KEY (channel_id) REFERENCES atpc_user.channels (channel_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.costs
  ADD CONSTRAINT costs_channel_fk
  FOREIGN KEY (channel_id) REFERENCES atpc_user.channels (channel_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.countries
  ADD CONSTRAINT countries_pk
  PRIMARY KEY (country_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.customers
  ADD CONSTRAINT customers_country_fk
  FOREIGN KEY (country_id) REFERENCES atpc_user.countries (country_id)
  RELY DISABLE NOVALIDATE;
 
ALTER TABLE atpc_user.supplementary_demographics
  ADD CONSTRAINT supp_demo_pk
  PRIMARY KEY (cust_id)
  RELY DISABLE NOVALIDATE;

아래  스크립트 출력 패널에 atpc_user라는 사용자에 생성된 테이블이 표시됨

왼쪽 연결패널-다른사용자-ATPC_USER-테이블에도 테이블 목록이 나타남

CHANNELS를 보고 각 컬럼에 대한 세부 사항 확인가능

CHANNELS에서 데이터를 눌려 데이터 확인가능(현재 데이터를 넣지 않아 비어있음)

관련글

오라클 ATP 접속하기(Autonomous Transaction Processing)

오라클 ATP 유저 생성(Autonomous Transaction Processing)

오라클 ATP SH 테이블 생성(Autonomous Transaction Processing)

오라클 ATP 데이터 로드(Autonomous Transaction Processing)

참조 : https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/atp/OBE_Connecting%20SQL%20Developer%20to%20Autonomous%20Transaction%20Processing/connecting_sql_developer_to_autonomous_transaction_processing.html