달력

72025  이전 다음

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

오라클 조인 업데이트

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

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                           
               ) 

'Program > Oracel' 카테고리의 다른 글

oracle DB 이관 expdp, impdp, tablespace  (0) 2017.08.23
오라클 EXPDP/IMPDP 순서 및 명령어  (1) 2017.01.06
Posted by 파란참새
|



오라클 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














'Program > Oracel' 카테고리의 다른 글

oracle join update 예제  (0) 2020.05.06
오라클 EXPDP/IMPDP 순서 및 명령어  (1) 2017.01.06
Posted by 파란참새
|


집에서 LG꺼 모니터랑 티비 되는걸로 해서 보고 있었는데

회사에서 일할 경우가 생겨서 불편하여 모니터를 구매하게 되었습니다.


이리저리 찾아보니 27인치는 알파스캔에서 많이 사는것 같아 사게 됨.


아직 몇일 써보진 않았지만 베젤이 얇고 화면도 깨끗해 보임.




'기타' 카테고리의 다른 글

인생온라인 시작  (0) 2016.11.30
Posted by 파란참새
|