Oracle数据库备份与还原命令
sqlplus /nolog
conn / as sysdba
一,imp/exp 导入导出
create tablespace MY_TABLESPACE logging datafile 'D:\DMP\MY_TABLESPACE.DBF' SIZE 100M autoextend on next 100M;
drop user MY_TABLESPACE cascade;
create user MY_TABLESPACE identified by mypwd default tablespace MY_TABLESPACE;
grant connect, resource, create session to MY_TABLESPACE;
imp MY_TABLESPACE/mypwd@orcl file=D:\DMP\mydmp.dmp commit=no full=yes grants=yes ignore=yes indexes=yes rows=yes show=no constraints=yes STATISTICS=NONE;
select 'alter table ' || table_name || ' allocate extent;' from user_tables where num_rows = 0 or num_rows is null;
exp MY_TABLESPACE/mypwd@127.0.0.1:1521/orcl file=D:\DMP\mydmp.dmp owner=MY_TABLESPACE COMPRESS=N;
二,im/ex pdp 数据泵导入导出
create tablespace MY_TABLESPACE logging datafile 'D:\DMP\MY_TABLESPACE.DBF' SIZE 100M autoextend on next 100M;
drop user MY_TABLESPACE cascade;
create user MY_TABLESPACE identified by mypwd default tablespace MY_TABLESPACE;
grant connect, resource, imp_full_database to MY_TABLESPACE;
select * from dba_directories;
create or replace directory datafile_my as 'D:\DMP';
grant read, write on directory datafile_my to MY_TABLESPACE;
impdp MY_TABLESPACE/mypwd@orcl DIRECTORY=datafile_my dumpfile=mydmp.dmp logfile=mydmp.log FULL=y remap_schema=XXX:MY_TABLESPACE remap_tablespace=XXX:MY_TABLESPACE
expdp MY_TABLESPACE/mypwd@orcl directory=datafile_my dumpfile=mydmp.dmp schemas=MY_TABLESPACE logfile=mydmp.log