오라클 유저생성/삭제 덤프 임포트

DROP USER [삭제할 유저명] CASCADE;

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME = '[타겟이 될 계정명]';

ALTER SYSTEM KILL SESSION '[SID],[SERIAL]';

sid와 serial을 ,로 구분해서 함께 넣어야 한다.

테이블 스페이스 이름 조회

SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

테이블 스페이스 생성(NEW_USER_DATA)

CREATE TABLESPACE NEW_USER_DATA

DATAFILE '/somedir/userdata_data.dbf' size 1500M AUTOEXTEND ON NEXT 200M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO




유저 생성

CREATE USER NEW_USER identified BY NEW_USER_PASSWD
DEFAULT tablespace NEW_USER_DATA
TEMPORARY tablespace TEMP
profile DEFAULT QUOTA unlimited On NEW_USER_DATA;

alter user NEW_USER quota unlimited on NEW_USER_DATA;

GRANT CONNECT TO NEW_USER;
GRANT resource TO NEW_USER;
GRANT DBA TO NEW_USER;
GRANT Unlimited tablespace TO NEW_USER;

imp userid=new_user/new_user file='test.dmp' fromuser=test  touser=new_user  ignore='y'










테이블스페이스명이 변경되어야 하는 경우 아래의 링크를 참고해서 변경이 가능하다.

http://firedev.tistory.com/entry/Oracle-%EC%98%A4%EB%9D%BC%ED%81%B4-Imp%ED%95%A0-%EB%95%8C-%ED%85%8C%EC%9D%B4%EB%B8%94-%EC%8A%A4%ED%8E%98%EC%9D%B4%EC%8A%A4-%EB%B3%80%EA%B2%BD%ED%95%B4%EC%84%9C-%EC%98%AC%EB%A6%AC%EA%B8%B0 

혹시 몰라 본문을 복해서 붙여넣는다.

출처 : database.sarang.net

<질문 내용>
test 테이블스페이스에 있는내용을
exp userid=test/test file=test.dmp
위와 같이 받은뒤

다른서버의 새로 생성된 테이블 스페이스에다가
 imp userid=test2/test2 file='test.dmp' fromuser=test  touser=test2  ignore='y'
적용하였더니....
데이터는 제대로 들어가는데 테이블 스페이스가
이전 test 로 되어있습니다.

새로 들어가는 test2로 적용이 되게 하고 싶은데 어떡해야 하나요?
부탁드립니다.

<답변 내용 1>
안녕하세요.. 이런 건이 있다니 반갑네요 ..
저도 이런 프로젝트가 있어서.. 해봤었거든요..

테이블스페이스에 쿼터뺏고, 그런거 다 안됩니다. 상식적으로 생각해봐도 안되죠.. ㅋㅋㅋ...  저도 삽질해 봤습니다.. .

또, show=y .. 이거 안됩니다.. 그렇게 해서 스크립트를 뽑으면, 제대로된 스크립트가 안나오죠...  많이 고쳐야 됩니다.

아래 내용은 제가 싸이트에서 해본 내용입니다. 1000% 보장합니다.

옵션 중에 indexfile 라는 게 있습니다. 요건 역시 스크립트만 뽑아주는 건데요.. 테이블과 인덱스 생성스크립트 만 뽑아줍니다.
프로시저라던가, 시퀀스, 뷰  등등은. .. index 나 테이블에 딸려있는 오브젝트 들이죠.. 하여.. 테이블과 인덱스만 틀을 갖춰 놓으면, import 시 ignore=y 옵션으로 데이타만 때려넣으면 알아서 다 들어갑니다.


그럼, 방법을 설명드리지요..

먼저, 이관해야할 DB에서 export 로 dmp 파일을 생성합니다.
그리고, export 받은 dmp 파일에서 스크립트를 뽑아내는 것이죠.
export 받은  dmp 파일을 intranet.dmp 라고 합시다.

imp system/manager file=intranet.dmp fromuser=intranet touser=intra  indexfiles=index.sql constraints=y full=y  log=intra.log

이렇게 하면,  index.sql 이라는 스크립트를 하나 떨궈줍니다. 실제로 improt 를 하는 게 아니라 스크립트를 만드는 겁니다.
index.sql 을 열어보면. 테이블과 인덱스를 생성하는 스크립트가 주욱 있습니다. 그곳에다가 오브젝트가 위치할 테이블스페이스 명을 다 바꿔줍니다. 오브젝트 앞의 유저이름도 주의하세요. intranet.employee 같은 거요. 유저명도 바꿔주셔야 되지요.. .
 더블쿼테이션(") 은.. vi 같은데서 일괄적으로 지워 주시고요. row 몇건 이런건 앞에 -- 로 주석처리 해줍니다. editplus 같은데서 하시면 편해요 ^^;;

이런 것도 알려드려야 되나?? 알고 계실듯 하지만..
:1,$s/"//g   이런식으로요..
:1,$s/row/--row/g  대충이런식으로 바꾸면, 처리되지요..
그리고 스크립트만 남겠지요. 물론 테이블스페이스를 모두 바꾼...

그리고, import 할 유저로 로긴해서, index.sql 을 돌려줍니다.
그럼 테이블과 인덱스가 생성되겠지요. 원하시는 테이블스페이스에 말이죠. 물론, 미리 테이블스페이스는 다 만들어 놓으셨겠지요..

그리고, 마지막으로 intranet.dmp 파일에서 데이타만 때려 넣으면 되겠지요...

imp system/manager file=intranet.dmp fromuser=intranet touser=intra constraints=y commit=y ignore=y log=intra.log  direct=y

commit 옵션은 롤백세그먼트가 작다면, 써주시구요.. ,direct 옵션은 써주면 빠르더라구요..
ignore 옵션이 가장 중요합니다. 데이타만 때려넣어야 되니까요..
확인해 보시면 스크립트에 변경한 테이블스페이스로 제대로 들어가 있을 겁니다.

도움이 되셨길 빕니다..

<답변 내용 2>

import할때 스크립트 옵션을 사용하여 일단 스트립트만 받은다음 스크립트를 열어 테이블 스페이스를 수정한후 스크립트를 수행합니다.(껍데기만 생성됨) 그후에 ignore=y로 한번더 import하여 자료를 붓습니다.

1. 원본 exp받기
    # exp aaa/aaa123 file=aaa.dmp log=aaa.log buffer=40960
   
2. index script 생성하기
    - indexfile Option을 이용하여 index script 생성
    # imp aaa/aaa123 file=aaa.dmp indexfile=index.sql  fromuser=aaa touser=aaa(tablespace 부분 수정)
3. 수정한 sql문을 실행시켜서 테이블 만들기/index만들기   
4. exp받아둔 파일로 진짜 import 하기/ignore=y하여 에러안나게
   껍데기만 만들어진 상태에서 ignore없이하면 또 만들려다 에러남
   # imp aaa/aaa123 file=aaa.dmp buffer=40960 log=imp0522.log commit=y fromuser=aaa touser=aaa ignore=y

유성호(ysh74)님이 2003-09-29 22:52:07에 작성한 댓글입니다.

또 다른 방법으로는

새로운 서버에 test2 라는 유저계정을 만드신후 default tablespace를 새로운 tablespace 로 정합니다.   그리고 alter user test2 quota 0M on test 라고 해 주시면 모든 테이블이 test2 테이블 스페이스로 들어 갑니다.

단 기존의 테이블에 LONG 이나 RAW 같은 데이타 타입이 들어있으면 안됩니다.

[출처] [database,펌]오라클 Imp할 때 테이블 스페이스 변경해서 올리기|작성자 abouttabs



주의사항 : 오라클 11g 부터는 expdp로 익스포트와 임포트를 진행해야한다.