Re: 이수자 평가 10번 문제

[22기_정민수]

-- AS-IS(ora12c)
-- 
CREATE TABLESPACE TS800
DATAFILE '/u01/app/oracle/oradata/ora12/ts800.dbf' SIZE 100m;

-- 확인.
select t.name, d.enabled
  from v$tablespace  t, v$datafile  d
 where  t.ts# = d.ts#;

CREATE USER hr800 IDENTIFIED BY hr800;

GRANT CONNECT, RESOURCE TO hr800;

ALTER USER hr800 DEFAULT TABLESPACE TS800;

ALTER USER hr800 QUOTA UNLIMITED ON TS800;

CONNECT hr800/hr800;

create table products100( 
prod_id number(6,0) not null enable, 
prod_name varchar2(50 byte) not null enable, 
prod_desc varchar2(4000 byte) not null enable, 
prod_subcategory varchar2(50 byte) not null enable, 
prod_subcategory_id number not null enable, 
prod_subcategory_desc varchar2(2000 byte) not null enable, 
prod_category varchar2(50 byte) not null enable, 
prod_category_id number not null enable, 
prod_category_desc varchar2(2000 byte) not null enable, 
prod_weight_class number(3,0) not null enable, 
prod_unit_of_measure varchar2(20 byte), 
prod_pack_size varchar2(30 byte) not null enable, 
supplier_id number(6,0) not null enable, 
prod_status varchar2(20 byte) not null enable, 
prod_list_price number(8,2) not null enable, 
prod_min_price number(8,2) not null enable, 
prod_total varchar2(13 byte) not null enable, 
prod_total_id number not null enable, 
prod_src_id number, 
prod_eff_from date, 
prod_eff_to date, 
prod_valid varchar2(1 byte)  
);

create table times100(
    time_id date not null enable, 
day_name varchar2(9 byte) not null enable, 
day_number_in_week number(1,0) not null enable, 
day_number_in_month number(2,0) not null enable, 
calendar_week_number number(2,0) not null enable, 
fiscal_week_number number(2,0) not null enable, 
week_ending_day date not null enable, 
week_ending_day_id number not null enable, 
calendar_month_number number(2,0) not null enable, 
fiscal_month_number number(2,0) not null enable, 
calendar_month_desc varchar2(8 byte) not null enable, 
calendar_month_id number not null enable, 
fiscal_month_desc varchar2(8 byte) not null enable, 
fiscal_month_id number not null enable, 
days_in_cal_month number not null enable, 
days_in_fis_month number not null enable, 
end_of_cal_month date not null enable, 
end_of_fis_month date not null enable, 
calendar_month_name varchar2(9 byte) not null enable, 
fiscal_month_name varchar2(9 byte) not null enable, 
calendar_quarter_desc char(7 byte) not null enable, 
calendar_quarter_id number not null enable, 
fiscal_quarter_desc char(7 byte) not null enable, 
fiscal_quarter_id number not null enable, 
days_in_cal_quarter number not null enable, 
days_in_fis_quarter number not null enable, 
end_of_cal_quarter date not null enable, 
end_of_fis_quarter date not null enable, 
calendar_quarter_number number(1,0) not null enable, 
fiscal_quarter_number number(1,0) not null enable, 
calendar_year number(4,0) not null enable, 
calendar_year_id number not null enable, 
fiscal_year number(4,0) not null enable, 
fiscal_year_id number not null enable, 
days_in_cal_year number not null enable, 
days_in_fis_year number not null enable, 
end_of_cal_year date not null enable, 
    end_of_fis_year date not null enable 
); 

CREATE TABLE sales100 (
    prod_id       NUMBER,        
    cust_id       NUMBER,        
    time_id       DATE,          
    channel_id    NUMBER,        
    promo_id      NUMBER,        
    quantity_sold NUMBER,        
    amount_sold   NUMBER(10, 2), 
    prod_name     VARCHAR2(200)  
);



[sh]
---------------------------------------------------------------------------
-- vi /home/oracle/ctl/sales.ctl
---------------------------------------------------------------------------
options(skip=1)
load data
infile '/home/oracle/sales.csv'
into table sales100
fields terminated by ','
optionally enclosed by '"'
(PROD_ID,CUST_ID,TIME_ID date 'YYYY/MM/DD HH24:MI:SS',CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD,PROD_NAME)


---------------------------------------------------------------------------
-- /home/oracle/ctl/products.ctl

options(skip=1)
load data
infile '/home/oracle/csv/products.csv'
into table products100
fields terminated by ','
optionally enclosed by '"'
(prod_id,prod_name,prod_desc,prod_subcategory,prod_subcategory_id,prod_subcategory_desc,prod_category,prod_category_id,prod_category_desc,prod_weight_class,prod_unit_of_measure,prod_pack_size,supplier_id,prod_status,prod_list_price,prod_min_price,prod_total,prod_total_id,prod_src_id,prod_eff_from DATE 'YYYY/MM/DD HH24:MI:SS', prod_eff_to DATE 'YYYY/MM/DD HH24:MI:SS' ,prod_valid)


---------------------------------------------------------------------------
-- /home/oracle/ctl/times.ctl

options(skip=1)
load data
infile '/home/oracle/csv/times.csv'
into table times100
fields terminated by ','
optionally enclosed by '"'
("TIME_ID" DATE 'YYYY/MM/DD HH24:MI:SS',
"DAY_NAME",
"DAY_NUMBER_IN_WEEK",
"DAY_NUMBER_IN_MONTH",
"CALENDAR_WEEK_NUMBER",
"FISCAL_WEEK_NUMBER",
"WEEK_ENDING_DAY" DATE 'YYYY/MM/DD HH24:MI:SS',
"WEEK_ENDING_DAY_ID",
"CALENDAR_MONTH_NUMBER",
"FISCAL_MONTH_NUMBER",
"CALENDAR_MONTH_DESC",
"CALENDAR_MONTH_ID",
"FISCAL_MONTH_DESC",
"FISCAL_MONTH_ID",
"DAYS_IN_CAL_MONTH",
"DAYS_IN_FIS_MONTH",
"END_OF_CAL_MONTH" DATE 'YYYY/MM/DD HH24:MI:SS',
"END_OF_FIS_MONTH" DATE 'YYYY/MM/DD HH24:MI:SS',
"CALENDAR_MONTH_NAME",
"FISCAL_MONTH_NAME",
"CALENDAR_QUARTER_DESC",
"CALENDAR_QUARTER_ID",
"FISCAL_QUARTER_DESC",
"FISCAL_QUARTER_ID",
"DAYS_IN_CAL_QUARTER",
"DAYS_IN_FIS_QUARTER",
"END_OF_CAL_QUARTER" DATE 'YYYY/MM/DD HH24:MI:SS',
"END_OF_FIS_QUARTER" DATE 'YYYY/MM/DD HH24:MI:SS',
"CALENDAR_QUARTER_NUMBER",
"FISCAL_QUARTER_NUMBER",
"CALENDAR_YEAR",
"CALENDAR_YEAR_ID",
"FISCAL_YEAR",
"FISCAL_YEAR_ID",
"DAYS_IN_CAL_YEAR",
"DAYS_IN_FIS_YEAR",
"END_OF_CAL_YEAR" DATE 'YYYY/MM/DD HH24:MI:SS',
"END_OF_FIS_YEAR" DATE 'YYYY/MM/DD HH24:MI:SS')

---------------------------------------------------------------------------

sqlldr hr800/hr800 control=/home/oracle/ctl/sales.ctl data=/home/oracle/csv/sales.csv
sqlldr hr800/hr800 control=/home/oracle/ctl/products.ctl data=/home/oracle/csv/products.csv
sqlldr hr800/hr800 control=/home/oracle/ctl/times.ctl data=/home/oracle/csv/times.csv

-- 검수
[sql]
CONNECT hr800/hr800
SELECT COUNT(*) FROM sales100; -- 918843
SELECT COUNT(*) FROM products100;   -- 72
SELECT COUNT(*) FROM times100; -- 1826


SELECT table_name, tablespace_name FROM user_tables;
TABLE_NAME           TABLESPACE_NAME
-------------------- --------------------
PRODUCTS100          TS800
TIMES100             TS800
SALES100             TS800





-- 테이블 스페이스 사전 설정 (READ WRITE -> READ ONLY)

CONNECT / as sysdba

ALTER TABLESPACE ts800 READ ONLY;

select t.name, d.enabled
  from v$tablespace  t, v$datafile  d
 where  t.ts# = d.ts#;
 /*
NAME                           ENABLED
------------------------------ ----------
SYSTEM                         READ WRITE
TS800                          READ ONLY
SYSAUX                         READ WRITE
UNDOTBS1                       READ WRITE
TS700                          READ WRITE
USERS                          READ WRITE
 */
 


[sh]

exp transport_tablespace=y tablespaces=ts800 file=/home/oracle/dmp/ts800_12c.dmp

scp /u01/app/oracle/oradata/ora12/ts800.dbf oracle@192.168.23.175:/home/oracle/oradata/ts800.dbf
scp /home/oracle/dmp/ts800_12c.dmp oracle@192.168.23.175:/home/oracle/dmp/ts800_12c.dmp



-- TO-BE(orcl19c)
[sql]
-- TS800 중복 여부 확인.
SELECT tablespace_name, file_name FROM dba_data_files;

-- 계정 생성.
CREATE USER hr800 IDENTIFIED BY hr800;

GRANT CONNECT, RESOURCE TO hr800;


CONNECT hr800/hr800;


[sh]
imp transport_tablespace=y file=/home/oracle/dmp/ts800_12c.dmp datafiles='/home/oracle/oradata/ts800.dbf'


[sql]

select t.name, d.enabled
  from v$tablespace  t, v$datafile  d
 where  t.ts# = d.ts#;

ALTER TABLESPACE TS800 READ WRITE; 

CREATE DATABASE LINK hr800_link
CONNECT TO hr800 IDENTIFIED BY hr800
USING '192.168.23.225/ora12' ;


SELECT object_name, object_type 
  FROM user_objects;

OBJECT_NAME        OBJECT_TYPE
------------------ -----------------------
PRODUCTS100        TABLE
TIMES100           TABLE
SALES100           TABLE


SELECT COUNT(*)  FROM user_constraints;

SELECT COUNT(*) FROM PRODUCTS100;
SELECT COUNT(*) FROM TIMES100;
SELECT COUNT(*) FROM SALES100;


SELECT object_name, object_type
  FROM user_objects@hr800_link;

OBJECT_NAME        OBJECT_TYPE
------------------ -----------------------
PRODUCTS100        TABLE
TIMES100           TABLE
SALES100           TABLE
SELECT COUNT(*) FROM user_constraints@hr800_link

SELECT COUNT(*) FROM PRODUCTS100@hr800_link;
SELECT COUNT(*) FROM TIMES100@hr800_link;
SELECT COUNT(*) FROM SALES100@hr800_link;