Oracle 백업

= exp ==

1.tablespace data위치 확인

sql> select t.name, d.status, d.name from v$tablespace t, v$datafile d where t.ts#=d.ts#;

 

2.user 확인

sql> select tablespace_name from dba_tablespaces;

 

3.exp 백업

# exp your_user/your_pass file='백업파일명'

 

== imp ==

1.user / tablespace 확인. 있을시 삭제

sql> drop user your_user cascade;

--> user접속으로 인하여 삭제 안될때 확인 : select sid, serial# from v$session where username='your_user';

sql> drop tablespace your_tablespace_data including contents;

sql> drop tablespace your_tablespace_idx including contents;

sql> drop tablespace your_tablespace_temp including contents;

 

2.tablespace dbf 파일 삭제(확인된 위치의 tablespace 삭제)

sql> select t.name, d.status. d.name from v$tablespace t, v$datafile d where t.ts# = d.ts#;

 

3.tablespace 생성

sql> create tablespace your_tablespace_data datafile '/oracle/oradata/your_tablespace_data.dbf' size 300m autoextend on;

sql> create tablespace your_tablespace_idx datafile '/oracle/oradata/your_tablespace_idx.dbf' size 200m autoextend on;

sql> create tablespace your_tablespace_temp datafile '/oracle/oradata/your_tablespace_temp.dbf' size 200m autoextend on;

 

4.user 생성

sql> create user your_user identified by your_pass default tablespace your_tablespace_data temporary tablespace your_tablespace_temp;

 

5.user 권한

sql> grant connect, dba, resource, exp_full_database, imp_full_database to your_user;

 

6.imp

# imp your_user/your_pass file='백업파일명'

출처 : http://blog.naver.com/galer?Redirect=Log&logNo=130078085965

----------------------------------------------------사용 쿼리 및 커맨드---------------------------

sql>create tablespace TS_APPADM_TAB datafile '/oracle/product/10.2.0/oradata/APPADM.TBL' size 10M autoextend on next 10M default storage (initial 4k next 128k minextents 1 pctincrease 0);

sql>create user APPADM identified by admin88 default tablespace TS_APPADM_TAB;

sql>grant resource, connect, exp_full_database, imp_full_database to APPADM;

c:\>imp userid=pp/admin88 file='c:\PP_201010131417.dmp' full=y