exp导出
--创建表空间
create tablespace PRO
logging
datafile '/tmp/oracle/obak_pro_01.dbf'
size 1500m
autoextend on
next 100m maxsize 10000m
extent management local;
--创建用户并且绑定表空间
create user dba_user identified by abcd1234 DEFAULT TABLESPACE PRO;
--给用户赋权限
grant connect,resource,dba,CTXAPP,create view,debug any procedure,debug connect session to dba_user;
登陆用户就可以使用了
导出
exp dba_user/abcd1234@SJZXBJT file=/tmp/oracle_base/test00120201211.dmp tables=class001
```sql
exp 用户名/密码@实例名 file=dmp文件存放路径 tables=那张表
exp导入
恢复表结构
--创建表空间
create tablespace PRO
logging
datafile '/tmp/oracle/obak_pro_01.dbf'
size 1500m
autoextend on
next 100m maxsize 10000m
extent management local;
--创建用户并且绑定表空间
create user dba_user identified by abcd1234 DEFAULT TABLESPACE PRO;
--给用户赋权限
grant connect,resource,dba,CTXAPP,create view,debug any procedure,debug connect session to dba_user;
--导入
mv test00120201211.dmp /oracle/11g/admin/SJZXBJT/dpdump/
chown -R oracle:oinstall htglsjzxdb20201109.dmp
imp dba_user/abcd1234 file=test00120201211.dmp full=y ignore=n
expdp
先创建备份目录
create or replace directory zyy as '/tmp/oracle_base';
expdp dba_user/abcd1234@SJZXBJT TABLES=class002 directory=zyy dumpfile=2020_12_11_DIDP.dmp logfile=2020_12_11_DIDP.log;
expdp 用户/密码@数据库
TABLES=表名
directory=备份目录名
dumpfile=导出文件名
logfile=导出日志
有时候会遇到这种错误
--重新创建备份目录
drop directory zyy ;
create or replace directory zyy as '/tmp/oracle_base';
grant read,write on directory zyy to public ;
--修改备份目录的属主和属组
mkdir tmp/oracle_base
chown -R oracle:oinstall tmp/oracle_base
全库备份
并行进程parallel
expdp center_user/abcd1234@SJZXBJT dumpfile=data_pump_dir:SJZXBJT$(date +%Y%m%d).dmp parallel=2
dumpfile=data_pump_dir:SJZXBJT.dmp与
directory=zyy dumpfile=2020_12_11_DIDP.dmp用法相同
//1:“full=y”,全量导出数据库;
expdp user1/password@orcl dumpfile=expdp.dmp directory=dump_dir full=y logfile=expdp.log;
//2:schemas按用户导出;
expdp user1/password@orcl schemas=user1 dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;
//3:按表空间导出;
expdp sysdba/password@orcl tablespace=tbs1,tbs2 dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;
//4:导出表;
expdp user1/password@orcl tables=table1,table2 dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;
//5:按查询条件导;
expdp user1/password@orcl tables=table1='where number=123' dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;
impdp
--创建表空间
create tablespace PRO
logging
datafile '/tmp/oracle/obak_pro_01.dbf'
size 1500m
autoextend on
next 100m maxsize 10000m
extent management local;
--创建用户并且绑定表空间
create user dba_user identified by abcd1234 DEFAULT TABLESPACE PRO;
--给用户赋权限
grant connect,resource,dba,CTXAPP,create view,debug any procedure,debug connect session to dba_user;
--导入
mv 2020_12_11_DIDP.dmp /oracle/11g/admin/SJZXBJT/dpdump/
chown -R oracle:oinstall 2020_12_11_DIDP.dmp
impdp dba_user/abcd1234@SJZXBJT dumpfile=data_pump_dir:2020_12_11_DIDP.dmp
全库导入
impdp center_user/abcd1234@SJZXBJT directory=dump_dir dumpfile=SJZXBJT full=y;
//1:“full=y”,全量导入数据库;
impdp A/password@orcl directory=dump_dir dumpfile=expdp.log full=y;
//2:同名用户导入,从用户A导入到用户A;
impdp A/password@orcl schemas=A directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log;
//3:从A用户中把表table1和table2导入到B用户中;
impdp A/password@orcl tables=A.table1,A.table2 remap_schema=A:B directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log;
//4:导入表空间;
impdp sysdba/password@orcl tablespaces=tbs1 directory=dump_dir dumpfile=expdp.dmp logfile=impdp.log;
//5:追加数据;
impdp sysdba/password@orcl directory=dump_dir dumpfile=expdp.dmp schemas=system table_exists_action=replace logfile=impdp.log;
//table_exists_action:导入对象已存在时执行的操作。有效关键字:SKIP,APPEND,REPLACE和TRUNCATE
//等待导入结束