EXPORT 백업 예제

[환경]

  사용자 환경변수 NLS_LANG 과 Database Characterset은 반드시 일치해야 한다.

[전체 백업]

 EXP_FULL_DATABASE role을 가지는 사용자는 데이타베이스상에 다른 사용자의
  오브젝트를 Exporting 할 수 있다.

  $ exp system/manager file=full.dmp full=y buffer=1024000 log=full.log

  Export: Release 8.1.7.0.0 - Production on Mon Dec 16 18:25:12 2002

  (c) Copyright 2000 Oracle Corporation.  All rights reserved.


  Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
  JServer Release 8.1.7.4.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
  . 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 TRACESVR's tables via Conventional Path ...
  . about to export DBSNMP's tables via Conventional Path ...
  . about to export ORANGE's tables via Conventional Path ...
  . about to export DEMO's tables via Conventional Path ...
  . . exporting table                          BONUS            0 rows exported
  . . exporting table                          DEPT            4 rows exported
  . . exporting table                          DUMMY            1 rows exported
  . . exporting table                      SALGRADE          5 rows exported
  . about to export SCOTT's tables via Conventional Path ...
  . . exporting table                          BONUS          0 rows exported
  . . exporting table                          DEPT            4 rows exported
  . 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 statistics
  Export terminated successfully without warnings.

[사용자별 백업]

  $ exp system/manager file=scott.dmp full=n owner=scott log=scott.log
  $ exp scott/tiger file=scott.dmp full=n owner=scott log=scott.log
  $ exp scott/tiger file=scott.dmp log=scott.log

  Export: Release 8.1.7.0.0 - Production on Mon Dec 16 18:15:09 2002

  (c) Copyright 2000 Oracle Corporation.  All rights reserved.


  Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
  JServer Release 8.1.7.4.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 Conventional Path ...
  . . exporting table                          BONUS          0 rows exported
  . . exporting table                          DEPT          4 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.

[테이블 단위 백업]

EXP_FULL_DATABASE role를 가지는 사용자만 다른 사용자 소유의 오브젝트를
  Exporting 할 수 있다.

  $ exp system/manager file=demo.dmp full=n tables=help,scott.bonus,test.s_emp

  Export: Release 8.1.7.0.0 - Production on Mon Dec 16 18:37:26 2002

  (c) Copyright 2000 Oracle Corporation.  All rights reserved.

  Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
  JServer Release 8.1.7.4.0 - Production
  Export done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set

  About to export specified tables via Conventional Path ...
  . . exporting table                          HELP              817 rows exported
  Current user changed to SCOTT
  . . exporting table                          BONUS  0 rows exported
  Current user changed to TEST
  . . exporting table                          S_EMP          8 rows exported
  Export terminated successfully without warnings.

  $ exp system/manager file=demo.dmp full=n tables=scott.bonus,test.s_emp

  Export: Release 8.1.7.0.0 - Production on Mon Dec 16 18:36:34 2002

  (c) Copyright 2000 Oracle Corporation.  All rights reserved.


  Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
  JServer Release 8.1.7.4.0 - Production
  Export done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set

  About to export specified tables via Conventional Path ...
  Current user changed to SCOTT
  . . exporting table                          BONUS          0 rows exported
  Current user changed to TEST
  . . exporting table                          S_EMP          8 rows exported
  Export terminated successfully with warnings.

  $ exp scott/tiger file=scott.dmp tables=bonus,dept log=scott.log

  Export: Release 8.1.7.0.0 - Production on Mon Dec 16 18:44:25 2002

  (c) Copyright 2000 Oracle Corporation.  All rights reserved.


  Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
  JServer Release 8.1.7.4.0 - Production
  Export done in KO16KSC5601 character set and KO16KSC5601 NCHAR character set

  About to export specified tables via Conventional Path ...
  . . exporting table                          BONUS    0 rows exported
  . . exporting table                          DEPT    4 rows exported
  Export terminated successfully without warnings.


[파티션 레벨 백업]

  $ exp scott/tiger file=emp.dmp tables=emp log=emp.log

  Export: Release 8.1.7.0.0 - Production on Wed Oct 6 15:24:46 1999
 
  (c) Copyright 1999 Oracle Corporation.  All rights reserved.

  Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
  With the Partitioning and Java options
  PL/SQL 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
  . . exporting partition                              M          8 rows exported
  . . exporting partition                              Z          6 rows exported
  Export terminated successfully without warnings.

  $ exp scott/tiger file=emp.dmp tables=emp:m log=emp.log

  Export: Release 8.1.7.0.0 - Production on Wed Oct 6 15:24:48 1999

  (c) Copyright 1999 Oracle Corporation.  All rights reserved.


  Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
  With the Partitioning and Java options
  PL/SQL 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
  . . exporting partition                              M          8 rows exported
  Export terminated successfully without warnings.

  $ exp scott/tiger file=emp.dmp tables=(emp:m,emp:sp4) log=emp.log

    EMP 테이블은 M과 Z의 파티션으로 구성되어 있으며 각 파티션에 SubPartion이
    다음과 같이 존재한다고 가정 : M(sp1,sp2),Z(sp3,sp4)

  Export: Release 8.1.6.0.0 - Production on Wed Oct 6 15:24:48 1999

  (c) Copyright 1999 Oracle Corporation.  All rights reserved.

  Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
  With the Partitioning and Java options
  PL/SQL Release 8.1.6.0.0 - Production
  Export done in WE8DEC character set and WE8DEC NCHAR character set

  About to export specified tables via Conventional Path ...

  . . exporting table                            EMP
  . . exporting composite partition                    M
  . . exporting subpartition                          SP1        4 rows exported
  . . exporting subpartition                          SP2        0 rows exported
  . . exporting composite partition                    Z
  . . exporting subpartition                          SP4        1 rows exported
  Export terminated successfully without warnings.