내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
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 테이블 생성 구문
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 | 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)
'ORACLE > Admin' 카테고리의 다른 글
오라클 패스워드 조건 설정, VERIFY_FUNCTION, oracle profile (2) | 2019.01.23 |
---|---|
오라클 ATP 데이터 로드(Autonomous Transaction Processing) (0) | 2019.01.14 |
오라클 ATP 유저 생성(Autonomous Transaction Processing) (2) | 2019.01.14 |
오라클 클라우드 Wallet으로 sql developer 접속 (0) | 2019.01.14 |
오라클 ATP 접속하기(Autonomous Transaction Processing) (7) | 2019.01.10 |