TOPIC 1. SCOTT의 데이타를 LARRY로 옮기는 방법
scott의 데이타를 larry 로 옮기려면 export/import 를 이용해야 한다.
larry가 만들어져 있지 않다면 다음과 같이 만든다.
$ sqlplus system/manager
SQL> create user larry
identified by lion
default tablespace users
temporary tablespace temp
quota unlimited on users;
default tablespace, temporary tablespace 는 시스템에 따라 알맞게 설정한다.
다음과 같이 larry에게 권한을 부여한다.
sql> grant connect, resource to larry
sql> revoke unlimited tablespace from larry
물론 users 테이블스페이스는 이미 존재한다고 가정한다.
scott로 export를 받고 larry로 import한다.
$ exp scott/tiger file=scott.dmp
$ imp larry/lion file=scott.dmp fromuser=scott touser=larry
만약 scott 가 dba 권한을 갖고 있었다면 다음과 같이 import를 해야 한다.
Using imp:
To import the full database exported in the example above.
imp SYSTEM/password FULL=y FIlE=dba.dmp
To import just the dept and emp tables from the scott schema
imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)
To import tables and change the owner
imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)
To import just the scott schema exported in the example above
imp <user>/<password> FIlE=scott.dmp
$ imp system/manager file=scott.dmp fromuser=scott touser=larry
만약 import 도중 연속된 저장 공간이 부족해서 에러가 발생한다면 익스포트를
받을 때 compress=n 옵션을 사용하며, import 시 롤백 세그먼트 문제가 발생
한다면 import 시 commit=y 옵션을 사용하면 쉽게 해결이 가능하다.
---
-- 테이블삭제
drop table SC_AUTHORITY cascade constraints;
drop table SC_BRANCH cascade constraints;
drop table SC_BRANCHDESCRIPTION cascade constraints;
drop table SC_BRANCHPHOTO cascade constraints;
drop table SC_BUSINESSDATEMASTER cascade constraints;
drop table SC_COMMONCODE cascade constraints;
drop table SC_CONNECTION_HISTORY cascade constraints;
drop table SC_FAVORITE cascade constraints;
drop table SC_LARGECOMMONCODE cascade constraints;
drop table SC_MANAGEMENTSALESYSTEM cascade constraints;
drop table SC_MENU cascade constraints;
drop table SC_PAYMENTMETHODACCOUNT cascade constraints;
drop table SC_PAYMENTMETHODMAPPING cascade constraints;
drop table SC_POSTCODE cascade constraints;
drop table SC_ROLE cascade constraints;
drop table SC_SALEACCOUNT cascade constraints;
drop table SC_STORE cascade constraints;
drop table SC_USER cascade constraints;
drop table SC_USER_ROLE cascade constraints;
drop table SC_USER_SESSION cascade constraints;
drop table SC_USE_LOG cascade constraints;
-- 테이블덤프 추가(import)
imp shresort_fo/shcenter FILE=shresort-20080621.dmp TABLES=SC_AUTHORITY,SC_BRANCH,SC_BRANCHDESCRIPTION,SC_BRANCHPHOTO,SC_BUSINESSDATEMASTER,SC_COMMONCODE,SC_CONNECTION_HISTORY,SC_FAVORITE,SC_LARGECOMMONCODE,SC_MANAGEMENTSALESYSTEM,SC_MENU,SC_PAYMENTMETHODACCOUNT,SC_PAYMENTMETHODMAPPING,SC_POSTCODE,SC_ROLE,SC_SALEACCOUNT,SC_STORE,SC_USER,SC_USER_ROLE,SC_USER_SESSION,SC_USE_LOG
-- 테이블스페이스 기본 설정값 확인
select username, default_tablespace, temporary_tablespace from dba_users where username = 'SHRESORT_FO'
-- 테이블스페이스 변경
alter table SC_AUTHORITY move tablespace SHRESORT_FO;
alter table SC_BRANCH move tablespace SHRESORT_FO;
alter table SC_BRANCHDESCRIPTION move tablespace SHRESORT_FO;
alter table SC_BRANCHPHOTO move tablespace SHRESORT_FO;
alter table SC_BUSINESSDATEMASTER move tablespace SHRESORT_FO;
alter table SC_COMMONCODE move tablespace SHRESORT_FO;
alter table SC_CONNECTION_HISTORY move tablespace SHRESORT_FO;
alter table SC_FAVORITE move tablespace SHRESORT_FO;
alter table SC_LARGECOMMONCODE move tablespace SHRESORT_FO;
alter table SC_MANAGEMENTSALESYSTEM move tablespace SHRESORT_FO;
alter table SC_MENU move tablespace SHRESORT_FO;
alter table SC_PAYMENTMETHODACCOUNT move tablespace SHRESORT_FO;
alter table SC_PAYMENTMETHODMAPPING move tablespace SHRESORT_FO;
alter table SC_POSTCODE move tablespace SHRESORT_FO;
alter table SC_ROLE move tablespace SHRESORT_FO;
alter table SC_SALEACCOUNT move tablespace SHRESORT_FO;
alter table SC_STORE move tablespace SHRESORT_FO;
alter table SC_USER move tablespace SHRESORT_FO;
alter table SC_USER_ROLE move tablespace SHRESORT_FO;
alter table SC_USER_SESSION move tablespace SHRESORT_FO;
alter table SC_USE_LOG move tablespace SHRESORT_FO;