# 사전 정보 확인
## oracle 12c
[sql]
SELECT tablespace_name, status FROM dba_tablespaces WHERE TABLESPACE_NAME='TS20000';
-- CHARACTER SET 확인
select *
from database_properties
where property_name='NLS_CHARACTERSET';
select *
from database_properties
where property_name='NLS_NCHAR_CHARACTERSET';
# ts20000 없을시
CREATE TABLESPACE ts20000 DATAFILE '/u01/app/oracle/oradata/ora12/ts20000.dbf'
ALTER TABLESPACE ts20000 READ ONLY;
[sh]
# DDL + 데이터 + 인덱스 이관
expdp directory=datapump_dir \
dumpfile=ts20000.dmp \
transport_tablespace=y \
tablespaces=ts20000
# META 정보 이관(SEQEUNCE,VIEW,PROCEDURE,FUNCTION,PACKAGE)
expdp directory=datapump_dir \
dumpfile=ts20000_meta.dmp \
content=metadata_only \
schemas=SH \
EXCLUDE=TABLE,INDEX,CONSTRAINT
# 데이터 파일 이관
scp /u01/app/oracle/oradata/ora12/ts20000.dbf oracle@192.168.23.175:/home/oracle/oradata/
# 덤프 파일 이관
scp /home/oracle/pump_ora12c/ts20000* oracle@192.168.23.175:/home/oracle/pump_ora19c/
## oracle 19c
[sql]
-- 계정생성 및 권한 부여
drop user sh2 cascade;
CREATE USER sh2
IDENTIFIED BY sh2
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT connect, resource TO sh2;
GRANT create view TO sh2;
GRANT create sequence TO sh2;
GRANT CREATE DATABASE LINK TO sh2;
# IMPORT
[sh]
impdp transport_datafiles='/home/oracle/oradata/ts20000.dbf' directory=data_pump_dir dumpfile=ts20000.dmp remap_schema=sh:sh2
impdp directory=data_pump_dir dumpfile=ts20000_meta.dmp remap_schema=sh:sh2
[sql]
-- 검수
-- sys
CREATE DATABASE LINK sh_link CONNECT TO sh IDENTIFIED BY sh USING '192.168.23.225/ora12';
-- sh2
SELECT object_name, object_type FROM user_objects@sh_link WHERE object_type != 'DATABASE LINK'
MINUS
SELECT object_name, object_type FROM user_objects WHERE object_type != 'DATABASE LINK';
SELECT object_name, object_type FROM user_objects WHERE object_type != 'DATABASE LINK' AND OBJECT_NAME NOT LIKE 'SYS%'
MINUS
SELECT object_name, object_type FROM user_objects@sh_link WHERE object_type != 'DATABASE LINK'AND OBJECT_NAME NOT LIKE 'SYS%';
SELECT constraint_name FROM user_constraints@sh_link
MINUS
SELECT constraint_name FROM user_constraints;
SELECT constraint_name FROM user_constraints
MINUS
SELECT constraint_name FROM user_constraints@sh_link;
SELECT 'channels' AS tbl, COUNT(*) AS cnt FROM channels
UNION ALL
SELECT 'countries' , COUNT(*) FROM countries
UNION ALL
SELECT 'products' , COUNT(*) FROM products
UNION ALL
SELECT 'times' , COUNT(*) FROM times
UNION ALL
SELECT 'promotions' , COUNT(*) FROM promotions
UNION ALL
SELECT 'customers' , COUNT(*) FROM customers
UNION ALL
SELECT 'costs' , COUNT(*) FROM costs
UNION ALL
SELECT 'sales' , COUNT(*) FROM sales
UNION ALL
SELECT 'supplementary_demographics' , COUNT(*) FROM supplementary_demographics;
TBL CNT
-------------------------- ---------- -------------------------- ----------
channels 5 channels 5
countries 35 countries 35
products 72 products 72
times 1826 times 1826
promotions 503 promotions 503
customers 55500 customers 55500
costs 82112 costs 82112
sales 918843 sales 918843
supplementary_demographics 4500 supplementary_demographics 4500
SELECT 'channels' AS tbl, COUNT(*) AS cnt FROM channels@sh_link
UNION ALL
SELECT 'countries' , COUNT(*) FROM countries@sh_link
UNION ALL
SELECT 'products' , COUNT(*) FROM products@sh_link
UNION ALL
SELECT 'times' , COUNT(*) FROM times@sh_link
UNION ALL
SELECT 'promotions' , COUNT(*) FROM promotions@sh_link
UNION ALL
SELECT 'customers' , COUNT(*) FROM customers@sh_link
UNION ALL
SELECT 'costs' , COUNT(*) FROM costs@sh_link
UNION ALL
SELECT 'sales' , COUNT(*) FROM sales@sh_link
UNION ALL
SELECT 'supplementary_demographics' , COUNT(*) FROM supplementary_demographics@sh_link;
TBL CNT
TBL CNT
-------------------------- ----------
channels 5
countries 35
products 72
times 1826
promotions 503
customers 55500
costs 82112
sales 918843
supplementary_demographics 4500