반응형
/********************************************************************************************
-- Title : [8i] Export 및 Import 유틸리티(OLN)
-- Reference : OLN
-- Key word :
********************************************************************************************/
/****************************************************************************************
-- 명령행 EXPORT 예
****************************************************************************************/
-- SCOTT 스키마에 속한 emp및 dept 테이블 EXPORT
$ exp scott/tiger tables=emp,dept rows=y file=expincr1.dmp
-- Title : [8i] Export 및 Import 유틸리티(OLN)
-- Reference : OLN
-- Key word :
********************************************************************************************/
/****************************************************************************************
-- 명령행 EXPORT 예
****************************************************************************************/
-- SCOTT 스키마에 속한 emp및 dept 테이블 EXPORT
$ exp scott/tiger tables=emp,dept rows=y file=expincr1.dmp
Export: Release 8.1.7.0.0 - Production on Sat Oct 2 02:27:08 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
. . exporting table EMP 14 rows exported
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
-- SCOTT 스키마에 속한 모든 객체를 direct방식으로 EXPORT
$ exp system/manager owner=SCOTT DIRECT=y
$ exp system/manager owner=SCOTT DIRECT=y
Export: Release 8.1.7.0.0 - Production on Sat Oct 2 02:29:06 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Direct Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Direct Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
-- users 테이블스페이스에 속한 모든 객에츼 정의를 포함하는 엑스프트 파일을 생성하고
ts_user.log 파일을 생성(에러 발생. 원인 모름??)
$ exp system/manager TRANSPORT_TABLESPACE=y TABLESPACES=users LOG=ts_users.log
ts_user.log 파일을 생성(에러 발생. 원인 모름??)
$ exp system/manager TRANSPORT_TABLESPACE=y TABLESPACES=users LOG=ts_users.log
Export: Release 8.1.7.0.0 - Production on Mon Oct 4 08:27:03 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
EXP-00044: must be connected 'AS SYSDBA' to do Point-in-time Recovery or
Transportable Tablespace import
EXP-00000: Export terminated unsuccessfully
-- 마지막 누적 또는 완전 엑스포트 이후 DB에서 수정된 모든 정의 및 데이터 EXPORT
$ exp system/manager FULL=y INCTYPE=cumulative FILE=expcum1.dmp
$ exp system/manager FULL=y INCTYPE=cumulative FILE=expcum1.dmp
Export: Release 8.1.7.0.0 - Production on Sat Oct 2 02:36:28 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table DEF$_AQCALL 0 rows exported
. . exporting table DEF$_AQERROR 0 rows exported
. . exporting table DEF$_CALLDEST 0 rows exported
. . exporting table DEF$_DEFAULTDEST 0 rows exported
. . exporting table DEF$_DESTINATION 0 rows exported
. . exporting table DEF$_ERROR 0 rows exported
. . exporting table DEF$_LOB 0 rows exported
. . exporting table DEF$_ORIGIN 0 rows exported
. . exporting table DEF$_PROPAGATOR 0 rows exported
. . exporting table DEF$_PUSHED_TRANSACTIONS 0 rows exported
. . exporting table DEF$_TEMP$LOB 0 rows exported
. . exporting table HELP 817 rows exported
. . exporting table SQLPLUS_PRODUCT_PROFILE 0 rows exported
. about to export OUTLN's tables via Conventional Path ...
. . exporting table OL$ 0 rows exported
. . exporting table OL$HINTS 0 rows exported
. about to export DBSNMP's tables via Conventional Path ...
. about to export TRACESVR's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. about to export ADAMS's tables via Conventional Path ...
. about to export JONES's tables via Conventional Path ...
. about to export CLARK's tables via Conventional Path ...
. about to export BLAKE's tables via Conventional Path ...
. exporting referential integrity constraints
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting information about dropped objects
. exporting statistics
Export terminated successfully without warnings.
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table DEF$_AQCALL 0 rows exported
. . exporting table DEF$_AQERROR 0 rows exported
. . exporting table DEF$_CALLDEST 0 rows exported
. . exporting table DEF$_DEFAULTDEST 0 rows exported
. . exporting table DEF$_DESTINATION 0 rows exported
. . exporting table DEF$_ERROR 0 rows exported
. . exporting table DEF$_LOB 0 rows exported
. . exporting table DEF$_ORIGIN 0 rows exported
. . exporting table DEF$_PROPAGATOR 0 rows exported
. . exporting table DEF$_PUSHED_TRANSACTIONS 0 rows exported
. . exporting table DEF$_TEMP$LOB 0 rows exported
. . exporting table HELP 817 rows exported
. . exporting table SQLPLUS_PRODUCT_PROFILE 0 rows exported
. about to export OUTLN's tables via Conventional Path ...
. . exporting table OL$ 0 rows exported
. . exporting table OL$HINTS 0 rows exported
. about to export DBSNMP's tables via Conventional Path ...
. about to export TRACESVR's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. about to export ADAMS's tables via Conventional Path ...
. about to export JONES's tables via Conventional Path ...
. about to export CLARK's tables via Conventional Path ...
. about to export BLAKE's tables via Conventional Path ...
. exporting referential integrity constraints
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting information about dropped objects
. exporting statistics
Export terminated successfully without warnings.
/****************************************************************************************
-- EXPORT 매개 변수
****************************************************************************************/
/****************************************************************************************
-- Incremental Export와 Cumulative Export 예
****************************************************************************************/
-- incremental export 경우
$ exp sys/change_on_install file=filename full=y inctype=incremental
-- cumulative export 경우
$ exp sys/change_on_install file=filename full=y inctype=cumulative
$ exp sys/change_on_install file=filename full=y inctype=cumulative
/****************************************************************************************
-- Direct-Path Export 명시 예
****************************************************************************************/
-- Direct-Path Export의 명시
Direct-Path Export 사용 전에 catexp.sql 스크립트를 실행해야 한다.
-- direct 파라미터 사용
$ exp user=scott/tiger full=y direct=y
$ exp user=scott/tiger full=y direct=y
-- 파라미터 파일 사용
$ vi exp_par.txt
...
USERID=scott/tiger
TABLES= (emp,dept)
FILE=exp_one.dmp
DIRECT=Y
...
$ vi exp_par.txt
...
USERID=scott/tiger
TABLES= (emp,dept)
FILE=exp_one.dmp
DIRECT=Y
...
-- 운영체제 프롬프트에서 파라미터 실행
$ exp parfile=exe_par.txt
$ exp parfile=exe_par.txt
/****************************************************************************************
-- Import 예
****************************************************************************************/
-- expincr1.dmp 엑스포트 파일을 사용하여 emp및 dept 테이블을 scott 스키마로 임포트.
$ imp scott/tiger tables=emp,dept rows=y file=expincr1.dmp
Import: Release 8.1.7.0.0 - Production on Mon Oct 4 19:45:41 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "EMP" 14 rows imported
. . importing table "DEPT" 4 rows imported
About to enable constraints...
Import terminated successfully without warnings.
import done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "EMP" 14 rows imported
. . importing table "DEPT" 4 rows imported
About to enable constraints...
Import terminated successfully without warnings.
-- expincr1.dmp 엑스포트파일을 사용하여 행을 호함해 Scott 스키마에 속한 모든 객체임포트.
$ imp system/manager FROMUSER=scott file=expincr1.dmp
$ imp system/manager FROMUSER=scott file=expincr1.dmp
Import: Release 8.1.7.0.0 - Production on Mon Oct 4 19:47:38 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set
. importing SCOTT's objects into SYSTEM
. . importing table "EMP" 14 rows imported
. . importing table "DEPT" 4 rows imported
About to enable constraints...
Import terminated successfully without warnings.
. importing SCOTT's objects into SYSTEM
. . importing table "EMP" 14 rows imported
. . importing table "DEPT" 4 rows imported
About to enable constraints...
Import terminated successfully without warnings.
-- expdat.dmp 엑스포트 파일을 사용하여 ts_users 테이블스페이스에 속한 모든 객체 정의 임포트.
(Export에서 에러가 발생하여 Import 에러 발생)
$ imp system/manager TRANSPORT_TABLESPACE=y TABLESPACES=ts_user
Import: Release 8.1.7.0.0 - Production on Mon Oct 4 19:48:33 2004
(Export에서 에러가 발생하여 Import 에러 발생)
$ imp system/manager TRANSPORT_TABLESPACE=y TABLESPACES=ts_user
Import: Release 8.1.7.0.0 - Production on Mon Oct 4 19:48:33 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
IMP-00009: abnormal end of export file
IMP-00000: Import terminated unsuccessfully
IMP-00000: Import terminated unsuccessfully
/****************************************************************************************
-- EXPORT 매개 변수
****************************************************************************************/
반응형