Developer 현실 Online

오라클 조인 업데이트

업데이트 대상 테이블이랑 조인걸어서 업데이트

WHERE 절도 조인걸어서 해당 대상만 업데이트 되도록 쿼리 작성 

UPDATE PD_PRODINFO_SIZE A
   SET FIRSTDELIVERYDT =( 
                       SELECT B.DELIVERYDT
                         FROM(SELECT MIN(DELIVERYDT) DELIVERYDT, PRODCD, COLORCD, SIZECD
                                FROM IO_DELIVERY_170331 B
                               WHERE COMPCD = '10'   
                               GROUP BY PRODCD, COLORCD, SIZECD
                              ) B 
                         WHERE A.PRODCD = B.PRODCD
                           AND A.COLORCD = B.COLORCD
                           AND A.SIZECD = B.SIZECD
                         )
  WHERE EXISTS ( SELECT 1
                   FROM (SELECT MIN(DELIVERYDT) DELIVERYDT, PRODCD, COLORCD, SIZECD
                            FROM IO_DELIVERY_170331 
                           WHERE COMPCD = '10'   
                           GROUP BY PRODCD, COLORCD, SIZECD) X
                  WHERE A.PRODCD = X.PRODCD
                  AND A.COLORCD = X.COLORCD
                  AND A.SIZECD = X.SIZECD                           
               ) 

오라클 DB이관작업. 

expdp로 system 계정전체 pump후 계정별로 impdp 가능.

SHUTDOWN IMMEDEATE

---------------1. 디비 테이블 스페이스 생성

create tablespace KBTEST_ERP_TS datafile 'D:/oradata/TableSpace/KBTEST_ERP_TS_01.dbf' size 10G autoextend on next 1000M MAXSIZE UNLIMITED;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_02.dbf' SIZE 10G;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_03.dbf' SIZE 10G;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_04.dbf' SIZE 10G;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_05.dbf' SIZE 10G;

ALTER TABLESPACE KBTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KBTEST_ERP_TS_06.dbf' SIZE 10G;

create TEMPORARY tablespace KBTEST_ERP_TEMP_TS tempfile 'D:/oradata/TableSpace/KBTEST_ERP_TEMP_TS_01.dbf' size 5000M;

create tablespace TEL_TEST_LOGIS_TS datafile 'D:/oradata/TableSpace/TEL_TEST_LOGIS_TS_01.dbf' size 1G autoextend on next 100M MAXSIZE UNLIMITED;

create TEMPORARY tablespace TEL_TEST_LOGIS_TEMP_TS tempfile 'D:/oradata/TableSpace/TEL_TEST_LOGIS_TEMP_TS.dbf' size 500M;

create tablespace KFTEST_ERP_TS datafile 'D:/oradata/TableSpace/KFTEST_ERP_TS_01.dbf' size 10G autoextend on next 1000M MAXSIZE UNLIMITED;

ALTER TABLESPACE KFTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KFTEST_ERP_TS_02.dbf' SIZE 10G;

create TEMPORARY tablespace KFTEST_ERP_TEMP_TS tempfile 'D:/oradata/TableSpace/KFTEST_ERP_TEMP_TS_01.dbf' size 500M;

create tablespace KNTEST_ERP_TS datafile 'D:/oradata/TableSpace/KNTEST_ERP_TS_01.dbf' size 10G autoextend on next 1000M MAXSIZE UNLIMITED;

ALTER TABLESPACE KNTEST_ERP_TS ADD DATAFILE 'D:/oradata/TableSpace/KNTEST_ERP_TS_02.dbf' SIZE 10G;

create TEMPORARY tablespace KNTEST_ERP_TEMP_TS tempfile 'D:/oradata/TableSpace/KNTEST_ERP_TEMP_TS_01.dbf' size 500M;

---------------1. 디비 테이블 스페이스 생성

---------------- 템프러리 테이블 스페이스 -----------------

3.----------------- 유저 생성, 권한 주기, 

create user KBTEST_ERP identified by KBTEST_ERP default tablespace KBTEST_ERP_TS temporary tablespace KBTEST_ERP_TEMP_TS;

create user KFTEST_ERP identified by KFTEST_ERP default tablespace KFTEST_ERP_TS temporary tablespace KFTEST_ERP_TEMP_TS;

create user KNTEST_ERP identified by KNTEST_ERP default tablespace KNTEST_ERP_TS temporary tablespace KNTEST_ERP_TEMP_TS;

create user KNTEST_ERPUSER identified by KNTEST_ERP default tablespace KNTEST_ERP_TS temporary tablespace KNTEST_ERP_TEMP_TS;

create user ASP_GET identified by ASP_GET default tablespace ASP_GET_TS temporary tablespace ASP_GET_TEMP_TS;

create user ASP_NIX identified by ASP_NIX default tablespace ASP_NIX_TS temporary tablespace ASP_NIX_TEMP_TS;

create user USER_DAS identified by USER_DAS default tablespace ASP_GET_TS temporary tablespace ASP_GET_TEMP_TS;

create user TEL_TEST_LOGIS identified by TEL_TEST_LOGIS default tablespace TEL_TEST_LOGIST_TS temporary tablespace TEL_TEST_LOGIS_TEMP_TS;

grant connect, resource, DBA to KBTEST_ERP;

grant create view to KBTEST_ERP;

grant create SYNONYM to KBTEST_ERP;

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

---- 디렉토리 생성-------------------------------------

CREATE DIRECTORY IMPORT as 'd:/IMPORT';

CREATE DIRECTORY OraPump as 'd:/OraPump';

grant write,read on directory OraPump to KBTEST_ERP ;

----------------디렉토리 생성-------------------------------

----------------유저, 테이블스페이스 삭제-----------

drop user KBTEST_ERP cascade;

drop tablespace KBTEST_ERP_TS including contents;

drop tablespace KBTEST_ERP_TEMP_TS including contents;

----------------유저, 테이블스페이스 삭제-----------

-- 데이터펌프 

------- 시스템 펌프.

expdp system/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.dmp full=y logfile=KB_SYSTEM_EXPDP_LOG.log

expdp system/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP2.dmp full=y logfile=KB_SYSTEM_EXPDP_LOG2.log

------- 유저 펌프.

expdp KBTEST_ERP/KBTEST_ERP directory=OraPump dumpfile=KBTEST_ERP_EXPDP.dmp full=y logfile=KBTEST_ERP_EXPDP_LOG.log;

imp KBTEST_ERP/KBTEST_ERP file='D:\import\KBTEST_ERPDB.dmp' logfile=IMPLOG2.log

-- 시스템 임프 

impdp SYSTEM/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KB_SYSTEM_IMPDP.log TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KB_SYSTEM_IMPDP.log REMAP_SCHEMA=KFTEST_ERP:KFTEST_ERP TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=TELLOGIS_IMPDP.log SCHEMAS=TEL_TEST_LOGIS TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=GETUSER_DAS.log SCHEMAS=USER_DAS TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/sysdba directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KBTEST_ERP_IMPDP.log SCHEMAS=KBTEST_ERP TABLE_EXISTS_ACTION=REPLACE

--2차시도 

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KBTEST_ERP_IMPDP3.log SCHEMAS=KBTEST_ERP TABLE_EXISTS_ACTION=TRUNCATE

-- 마지막테스트 

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=GETUSER_DAS2.log SCHEMAS=USER_DAS TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP.DMP logfile=KBTEST_ERP_IMPDP4.log SCHEMAS=KBTEST_ERP TABLE_EXISTS_ACTION=TRUNCATE

--실제 

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP2.DMP logfile=GETUSER_DAS5.log SCHEMAS=USER_DAS TABLE_EXISTS_ACTION=REPLACE

impdp SYSTEM/KBTEST_ERP directory=OraPump dumpfile=KB_SYSTEM_EXPDP2.DMP logfile=KBTEST_ERP_IMPDP7.log SCHEMAS=KBTEST_ERP TABLE_EXISTS_ACTION=TRUNCATE

- 오라클 EXPDP / IMPDP 순서 및 명령어 정리

EXP, IMP 개량해서 나온게 DP 인데 10부터 있음.

- 주의점. EXPDP랑 IMPDP할때 오라클 버전이 맞아야함.

EX : oracle10g -> 11g 은 되는데 11g->10g 반대로는 안됨. 

해결책 : EXPDP할때 버전을 써줘야함.

0. 오라클 dba로 접속 

= sqlplus "/as sysdba"

1. 디렉토리 생성, 권한 부여 

= CREATE DIRECTORY TESTPUMP AS 'D:\Oracle_backup\PUMP';

= GRANT READ, WRITE ON DIRECTORY TESTPUMP TO TESTERP;

2. EXPDP 실행

= expdp tcf/tcf dumpfile=pump.dmp directory=ora_pump full=y logfile=pump.log version=10.2.0.1

= 오라클 버전이 다르면 VERSION 써줘야함. 

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

##옮길 DB로 이동.##

3. 테이블 스페이스 생성

=create tablespace TS_TEST_DAT datafile 'D:/oradata/TS_TEST_DAT.dbf' size 10G autoextend on next 1G MAXSIZE UNLIMITED;

= create tablespace TS_TEST_IDX datafile 'D:/oradata/TS_TEST_IDX.dbf' size 5G autoextend on next 1G MAXSIZE UNLIMITED;

4. TEMP 테이블스페이스 생성

= create TEMPORARY tablespace TS_TEST_DAT_TEMP tempfile 'D:/oradata/TS_TEST_DAT_TEMP.dbf' size 2000m;

5. 유저생성

= create user TEST identified by TEST default tablespace TS_TEST_DAT;

6. 권한 부여

grant connect, resource, SYSDBA to TEST ;

grant create view to TEST ;

grant create SYNONYM to TEST ;

grant write,read on directory TESTPUMP to TEST ;

7. IMPDP 실행

IMPDP TCFSERP/TCFSERP directory=TCFSPUMP dumpfile=EXP_TO_USER.DMP logfile=EXP_TO_USER.LOG TABLE_EXISTS_ACTION=TRUNCATE transform=segment_attributes:n