테이블 이동(exp/imp)

2013.11.13 18:07

개발서버(dev)에 있는 특정 계정(mis)의 테이블(mis_table_01, mis_table_02)을 운영서버(MIS)로 옮기기(exp/imp)


ㅇ 개발서버에서 테이블 덤프(exp)

[dev:/] su - oracle

dev:/> mkdir /exp_temp

dev:/> exp userid=system/***** file='/exp_temp/mis_exp.dmp' tables=mis.mis_table_01, mis.mis_table_02;


Export: Release 9.2.0.8.0 - Production on Tue Nov 5 14:48:30 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

Export done in KO16KSC5601 character set and AL16UTF16 NCHAR character set


About to export specified tables via Conventional Path ...

Current user changed to GMIS


. . exporting table                     mis_table_01        9 rows exported

. . exporting table                     mis_table_02   367780 rows exported


Export terminated successfully without warnings.


ㅇ 덤프 파일을 ftp로 개발서버에서 운영서버로 복사


ㅇ 운영서버(MIS)로 테이블 import

[mis:/exp_dev/temp] chown oramis:dba mis.dmp  // 운영서버의 오라클 계정으로 소유주 chown

[mis:/exp_dev/temp] ls -alrt

total 80400

drwxr-xr-x    5 oragmis  dba            4096 11월  5일 15:08 ..

drwxr-xr-x    2 oragmis  dba             256 11월  5일 15:09 .

-rw-r-----    1 oragmis  dba        41160704 11월  5일 15:09 mis.dmp


 [mis:/exp_dev/temp] su - oramis

mis@oramis:> imp system/***** file=/exp_dev/temp/mis.dmp tables=mis_table_01, mis_table_02, fromuser=mis touser=gmis log=/exp_mis/temp/misimp.log ignore='y' commit='y'


Import: Release 9.2.0.8.0 - Production on Tue Nov 5 15:17:49 2013

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

 

Export file created by EXPORT:V09.02.00 via conventional path

import done in KO16KSC5601 character set and AL16UTF16 NCHAR character set

. importing MIS's objects into MIS

. . importing table                    "mis_table_01"          9 rows imported

. . importing table                   "mis_table_02"     367780 rows imported


Import terminated successfully without warnings.



ㅇ 테이블 imoort 확인

SQL> select count(*) from mis.mis_table_01;

  COUNT(*)

----------

         9


SQL> select count(*) from mis.mis_table_02;

  COUNT(*)

----------

    367780

 

 

 

또루아빠 SQL(오라클,mysql) , , ,