테이블과 스키마 이행

exp <USERNAME>/<PASSWORD> tables=emp file=emp.dmp           # 테이블 범위
exp system/<PASSWORD> owner=scott file=scott.dmp             # 스키마 범위
scp scott.dmp oracle@<TARGET_HOST>:<WORK_DIR>/

CREATE USER scott2 IDENTIFIED BY <PASSWORD>
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO scott2;

imp system/<PASSWORD> file=scott.dmp fromuser=scott touser=scott2

CREATE DIRECTORY datapump_dir AS '<DUMP_DIR>';
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'DATAPUMP_DIR';
GRANT READ, WRITE ON DIRECTORY datapump_dir TO hr;

expdp <USERNAME>/<PASSWORD> directory=datapump_dir tables=employees dumpfile=emp_pump.dmp
expdp system/<PASSWORD> directory=datapump_dir schemas=scott dumpfile=scott_pump.dmp
ls -lh <DUMP_DIR>/          # 덤프와 로그 파일
scp emp_pump.dmp oracle@<TARGET_HOST>:<WORK_DIR>/

CREATE DIRECTORY datapump_dir AS '<DUMP_DIR>';
CREATE TABLESPACE ts100 DATAFILE '<DATA_DIR>/ts100.dbf' SIZE 100m;
CREATE USER hr2 IDENTIFIED BY <PASSWORD>
  DEFAULT TABLESPACE ts100 QUOTA UNLIMITED ON ts100;
GRANT CONNECT, RESOURCE TO hr2;
GRANT READ, WRITE ON DIRECTORY datapump_dir TO hr2;

-- 원래 테이블스페이스 확인 (REMAP_TABLESPACE의 왼쪽 값)
SELECT table_name, tablespace_name, owner FROM dba_tables WHERE table_name IN ('EMP', 'DEPT');

mv <WORK_DIR>/emp_pump.dmp <DUMP_DIR>/
impdp <USERNAME>/<PASSWORD> directory=datapump_dir dumpfile=emp_pump.dmp \
  remap_schema=hr:hr2 remap_tablespace=USERS:TS100 logfile=emp_imp.log

CREATE DATABASE LINK src_link CONNECT TO <USERNAME> IDENTIFIED BY <PASSWORD> USING '<SOURCE_HOST>:1521/<SERVICE_NAME>';

SELECT table_name FROM user_tables
MINUS
SELECT table_name FROM user_tables@src_link;

SELECT table_name FROM user_tables@src_link
MINUS
SELECT table_name FROM user_tables;

-- 소스: 두 인스턴스 모두 <PDB_NAME>이 READ WRITE인지
SELECT inst_id, name, open_mode FROM gv$pdbs WHERE name = '<PDB_NAME>';
-- 타깃: non-CDB, 캐릭터셋(소스와 같아야 함), USERS 유무, HR이 아직 없는지
SELECT name, cdb FROM v$database;
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
SELECT tablespace_name FROM dba_tablespaces;
SELECT username FROM dba_users WHERE username = 'HR';

mkdir -p <DUMP_DIR>
sqlplus system/<PASSWORD>@<RAC2_HOST>:1521/<SERVICE_NAME>
# SQL> SELECT instance_name, host_name FROM v$instance;   → 2번 인스턴스인지 확인
# SQL> CREATE OR REPLACE DIRECTORY dp_dir AS '<DUMP_DIR>';
expdp system/<PASSWORD>@<RAC2_HOST>:1521/<SERVICE_NAME> \
  schemas=hr directory=dp_dir dumpfile=hr_export.dmp logfile=exp_hr_export.log cluster=n
ls -l <DUMP_DIR>/
scp <DUMP_DIR>/hr_export.dmp <RAC1_HOST>:<DUMP_DIR>/

echo $ORACLE_SID      # 타깃 <TARGET_INSTANCE>인지 확인, RAC 인스턴스면 소스 쪽에 들어감
sqlplus / as sysdba
# SQL> CREATE OR REPLACE DIRECTORY dp_dir AS '<DUMP_DIR>';
impdp system/<PASSWORD> schemas=hr directory=dp_dir dumpfile=hr_export.dmp logfile=imp_hr.log

SELECT sys_context('userenv', 'db_name') db FROM dual;
SELECT object_type, COUNT(*) FROM user_objects GROUP BY object_type ORDER BY 1;
SELECT COUNT(*) FROM user_constraints;
SELECT object_name, object_type, status FROM user_objects WHERE status = 'INVALID';
SELECT first_name, last_name, department_name, city FROM emp_details_view WHERE employee_id = 150;

SELECT employees_seq.NEXTVAL FROM dual;
UPDATE employees SET job_id = 'SA_MAN' WHERE employee_id = 150;

SELECT COUNT(*) FROM job_history;

ROLLBACK;

# <DB_NAME>에서 먼저: CREATE TABLESPACE hr_ts DATAFILE '<DATA_DIR>/hr_ts01.dbf' SIZE 100M;
expdp system/<PASSWORD>@<RAC1_HOST>:1521/<SERVICE_NAME> \
  schemas=hr directory=dp_dir dumpfile=hr_export_node1.dmp logfile=exp_hr_export_node1.log cluster=n
# <DB_NAME> 환경으로 바꾼 뒤
impdp system/<PASSWORD> schemas=hr directory=dp_dir dumpfile=hr_export_node1.dmp logfile=imp_hr_test.log \
  remap_schema=hr:hr_test remap_tablespace=users:hr_ts

SELECT owner, tablespace_name, COUNT(*) FROM dba_tables
 WHERE owner IN ('HR', 'HR_TEST') GROUP BY owner, tablespace_name;