export / import

 

## Export

 

1. 전체 데이터베이스를 백업한다.

exp damin/ full=y log=full_log.log file=/home/oracle/full_test001.dmp

 

2. 이렇게하면 옵션이 나오는데 여기서 내가 원하는 옵션을 설정하면 된다.

 

[/home/oracle]$ exp damin/ full=y log=full_log.log file=/home/oracle/full_test001.dmp

Export: Release 19.0.0.0.0 - Production on Sun May 14 20:41:15 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
EXP-00023: must be a DBA to do Full Database or Tablespace export
(2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DAMIN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DAMIN
About to export DAMIN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DAMIN's tables via Conventional Path ...
. . exporting table                     DEPARTMENT         12 rows exported
. . exporting table                      PROFESSOR         16 rows exported
. . exporting table                        STUDENT         20 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 materialized views
. 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 with warnings.

 

 

 

### export 옵션

tablespaces=(sysaux,undotbsl)
tables=(example,undotbsl)
owner=(scott,damin)

 

 

---

 

 

## Import

 

1. 전체 데이터베이스를 import한다.

 imp scott/ full=y log=full_log.log file=/home/oracle/full_test001.dmp

 

 

2. 자동으로 import 된다.

[/home/oracle]$ imp scott/ full=y log=full_log.log file=/home/oracle/full_test001.dmp

Import: Release 19.0.0.0.0 - Production on Sun May 14 20:46:29 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Export file created by EXPORT:V19.00.00 via conventional path

Warning: the objects were exported by DAMIN, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing DAMIN's objects into SCOTT
. . importing table                   "DEPARTMENT"         12 rows imported
. . importing table                    "PROFESSOR"         16 rows imported
. . importing table                      "STUDENT"         20 rows imported
Import terminated successfully without warnings.

 

 

### import 옵션

 

## 원하는 사용자에게 import
만약 sys 사용자로 import 한다면
fromuser=(scott,damin) 이런식으로 원하는 사용자에게만 import 가능 


## 테이블 소유자 변경도 가능
fromuser=scott
touser=damin 

## index는 import 하지 않기 
indexes=n