ORACLE 12C SAMPLE DATABASE 만들기

SQL> CREATE USER OT IDENTIFIED BY Orcl1234;

User created.

SQL> GRANT CONNECT, RESOURCE, DBA TO OT;

Grant succeeded.

SQL> CONNECT ot@orclpdb
Enter password: ​

sqlplus
user-name : /as sysdba
SQL >

상태로 접속한다.

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

이렇게 ROOT로 있으면 된다.

SQL> alter session set container = pdborcl;
ERROR:
ORA-65011: Pluggable database PDBORCL does not exist.

container를 가져오는데 처음에 만들었던 이름이 생각이 안나거나 default 이름이 아니여서 session을 가져올 수 없는 경우가 있다. 이럴 때엔 select를 통해 확인해본다.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME
--------------------------------------------------------------------------------
STATUS
----------
ORCLPDB
NORMAL

나는 ORCLPDB였다.

SQL> alter session set container = orclpdb;

Session altered.

그 다음 con_name을 확인해본다.

SQL> SHOW con_name;

CON_NAME
------------------------------
ORCLPDB

SQL> alter database open;

SQL> CREATE USER OT IDENTIFIED BY Orcl1234;

User created.

SQL> GRANT CONNECT, RESOURCE, DBA TO OT;

Grant succeeded.

SQL> CONNECT ot@orclpdb
Enter password: 

새로운 계정을 하나 파고 접속해본다. (나는 이미 있는걸로 했다)

CREATE TABLE regions
  (
    region_id NUMBER GENERATED BY DEFAULT AS IDENTITY
    START WITH 5 PRIMARY KEY,
    region_name VARCHAR2( 50 ) NOT NULL
  );
-- countries table
CREATE TABLE countries
  (
    country_id   CHAR( 2 ) PRIMARY KEY  ,
    country_name VARCHAR2( 40 ) NOT NULL,
    region_id    NUMBER                 , -- fk
    CONSTRAINT fk_countries_regions FOREIGN KEY( region_id )
      REFERENCES regions( region_id ) 
      ON DELETE CASCADE
  );
 
-- location
CREATE TABLE locations
  (
    location_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 24 
                PRIMARY KEY       ,
    address     VARCHAR2( 255 ) NOT NULL,
    postal_code VARCHAR2( 20 )          ,
    city        VARCHAR2( 50 )          ,
    state       VARCHAR2( 50 )          ,
    country_id  CHAR( 2 )               , -- fk
    CONSTRAINT fk_locations_countries 
      FOREIGN KEY( country_id )
      REFERENCES countries( country_id ) 
      ON DELETE CASCADE
  );
-- warehouses
CREATE TABLE warehouses
  (
    warehouse_id NUMBER 
                 GENERATED BY DEFAULT AS IDENTITY START WITH 10 
                 PRIMARY KEY,
    warehouse_name VARCHAR( 255 ) ,
    location_id    NUMBER( 12, 0 ), -- fk
    CONSTRAINT fk_warehouses_locations 
      FOREIGN KEY( location_id )
      REFERENCES locations( location_id ) 
      ON DELETE CASCADE
  );
-- employees
CREATE TABLE employees
  (
    employee_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 108 
                PRIMARY KEY,
    first_name VARCHAR( 255 ) NOT NULL,
    last_name  VARCHAR( 255 ) NOT NULL,
    email      VARCHAR( 255 ) NOT NULL,
    phone      VARCHAR( 50 ) NOT NULL ,
    hire_date  DATE NOT NULL          ,
    manager_id NUMBER( 12, 0 )        , -- fk
    job_title  VARCHAR( 255 ) NOT NULL,
    CONSTRAINT fk_employees_manager 
        FOREIGN KEY( manager_id )
        REFERENCES employees( employee_id )
        ON DELETE CASCADE
  );
-- product category
CREATE TABLE product_categories
  (
    category_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 6 
                PRIMARY KEY,
    category_name VARCHAR2( 255 ) NOT NULL
  );
 
-- products table
CREATE TABLE products
  (
    product_id NUMBER 
               GENERATED BY DEFAULT AS IDENTITY START WITH 289 
               PRIMARY KEY,
    product_name  VARCHAR2( 255 ) NOT NULL,
    description   VARCHAR2( 2000 )        ,
    standard_cost NUMBER( 9, 2 )          ,
    list_price    NUMBER( 9, 2 )          ,
    category_id   NUMBER NOT NULL         ,
    CONSTRAINT fk_products_categories 
      FOREIGN KEY( category_id )
      REFERENCES product_categories( category_id ) 
      ON DELETE CASCADE
  );
-- customers
CREATE TABLE customers
  (
    customer_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 320 
                PRIMARY KEY,
    name         VARCHAR2( 255 ) NOT NULL,
    address      VARCHAR2( 255 )         ,
    website      VARCHAR2( 255 )         ,
    credit_limit NUMBER( 8, 2 )
  );
-- contacts
CREATE TABLE contacts
  (
    contact_id NUMBER 
               GENERATED BY DEFAULT AS IDENTITY START WITH 320 
               PRIMARY KEY,
    first_name  VARCHAR2( 255 ) NOT NULL,
    last_name   VARCHAR2( 255 ) NOT NULL,
    email       VARCHAR2( 255 ) NOT NULL,
    phone       VARCHAR2( 20 )          ,
    customer_id NUMBER                  ,
    CONSTRAINT fk_contacts_customers 
      FOREIGN KEY( customer_id )
      REFERENCES customers( customer_id ) 
      ON DELETE CASCADE
  );
-- orders table
CREATE TABLE orders
  (
    order_id NUMBER 
             GENERATED BY DEFAULT AS IDENTITY START WITH 106 
             PRIMARY KEY,
    customer_id NUMBER( 6, 0 ) NOT NULL, -- fk
    status      VARCHAR( 20 ) NOT NULL ,
    salesman_id NUMBER( 6, 0 )         , -- fk
    order_date  DATE NOT NULL          ,
    CONSTRAINT fk_orders_customers 
      FOREIGN KEY( customer_id )
      REFERENCES customers( customer_id )
      ON DELETE CASCADE,
    CONSTRAINT fk_orders_employees 
      FOREIGN KEY( salesman_id )
      REFERENCES employees( employee_id ) 
      ON DELETE SET NULL
  );
-- order items
CREATE TABLE order_items
  (
    order_id   NUMBER( 12, 0 )                                , -- fk
    item_id    NUMBER( 12, 0 )                                ,
    product_id NUMBER( 12, 0 ) NOT NULL                       , -- fk
    quantity   NUMBER( 8, 2 ) NOT NULL                        ,
    unit_price NUMBER( 8, 2 ) NOT NULL                        ,
    CONSTRAINT pk_order_items 
      PRIMARY KEY( order_id, item_id ),
    CONSTRAINT fk_order_items_products 
      FOREIGN KEY( product_id )
      REFERENCES products( product_id ) 
      ON DELETE CASCADE,
    CONSTRAINT fk_order_items_orders 
      FOREIGN KEY( order_id )
      REFERENCES orders( order_id ) 
      ON DELETE CASCADE
  );
-- inventories
CREATE TABLE inventories
  (
    product_id   NUMBER( 12, 0 )        , -- fk
    warehouse_id NUMBER( 12, 0 )        , -- fk
    quantity     NUMBER( 8, 0 ) NOT NULL,
    CONSTRAINT pk_inventories 
      PRIMARY KEY( product_id, warehouse_id ),
    CONSTRAINT fk_inventories_products 
      FOREIGN KEY( product_id )
      REFERENCES products( product_id ) 
      ON DELETE CASCADE,
    CONSTRAINT fk_inventories_warehouses 
      FOREIGN KEY( warehouse_id )
      REFERENCES warehouses( warehouse_id ) 
      ON DELETE CASCADE
  );

테이블을 만들고 확인해준다.

오라클 샘플 데이터를 받아 적당한 디렉터리에 넣고 unzip 해준다.

[root@orads sample]# pwd
/home/oracle/database/sample
[root@orads sample]# ls
oracle-sample-database.zip
[root@orads sample]# unzip oracle-sample-database.zip 
Archive:  oracle-sample-database.zip
  inflating: ot_create_user.sql      
  inflating: ot_data.sql             
  inflating: ot_drop.sql             
  inflating: ot_schema.sql 

테이블 만들기 힘들면 여기있는 sql문으로 써도 된다.

SQL> @/home/oracle/database/sample/ot_schema.sql

Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.


Table created.

SQL> select table_name from user_tables order by table_name;

TABLE_NAME
--------------------------------------------------------------------------------
CONTACTS
COUNTRIES
CUSTOMERS
EMPLOYEES
INVENTORIES
LOCATIONS
ORDERS
ORDER_ITEMS
PRODUCTS
PRODUCT_CATEGORIES
REGIONS

TABLE_NAME
--------------------------------------------------------------------------------
WAREHOUSES

12 rows selected.

테이블을 만들었으면 data를 넣어준다. 이 때 나는 scott 계정에 넣어줬기 때문에 sql문을 조금 바꿔주었다.

@/home/oracle/database/sample/ot_data.sql

vi로 저 sql문을 열어보면 다음과 같은 INSERT 문이다.

Insert into OT.INVENTORIES (PRODUCT_ID,WAREHOUSE_ID,QUANTITY) values (207,8,121);

vi ot_data.sql
:%s/OT/scott/g

티스토리 에디터 진짜 별로다 하..............아무튼 이렇게 치환해주고 다시 해주면 됨.

SQL> SELECT COUNT(*) FROM contacts;

  COUNT(*)
----------
       319

count 해준 이후 319개가 나오면 잘 들어간것이다.

oracle-sample-database.zip

0.05MB

이건 사용한 SQL문!