- 发布时间: 2014/04/28 18:46
- 阅读: 1546
- 收藏: 3
- 点赞: 0
- 评论: 0
2014-04-28
1.查找文件
[oracle@oracle53 ~]$ which expdp
/u01/app/oracle/11.2.0/db_1/bin/expdp
[oracle@oracle53 ~]$ locate expdp
/u01/app/oracle/11.2.0/db_1/bin/expdp
/u01/app/oracle/11.2.0/db_1/bin/expdpO
[oracle@oracle53 ~]$ whereis expdp
2.数据泵导出
##hr用户
shell>expdp hr/hr directory=DATA_PUMP_DIR dumpfile=cc.cc tables=hr.jobs
ORA-39087: directory name DATA_PUMP_DIR is invalid
##系统用户
shell>expdp system/322815 directory=DATA_PUMP_DIR dumpfile=cc.cc tables=hr.jobs
**********************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/cc.cc
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 09:09:02
##也可以不指定目录
shell>expdp system/322815 dumpfile=cc.cc tables=hr.jobs
##基于scott用户
shell>expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=scott.dmp schemas=scott
3.创建目录对象 --需要先创建/u01/expdp目录
SQL> create directory exp_home as '/u01/expdp';
SQL> grant read,write on directory exp_home to scott;
SQL>select * from DBA_DIRECTORIES;
4.全库导出和导入 --需要EXP_FULL_DTABASE,IMP_FULL_DTABASE角色
导出:expdp scott/tiger directory=exp_dir full=y dumpfile=full_db.dmp
导入:impdp scott/tiger directory=exp_dir full=y dumpfile=full_db.dmp
5.表空间的导出和导入
导出:expdp scott/tiger directory=exp_home dumpfile=tbs_01.dmp tablespaces=tbs_01;
//如果删除表空间后恢复表空间数据,需要先创建表空间再恢复表空间数据
SQL> drop tablespace tbs_01 including contents; --不会物理删除磁盘上的datafile
##查看datafile并删除
shell>cd /u01/app/oracle/oradata/orcl/
shell>rm tbs_01.dbf
##导入
[oracle@ora243 expdp]$ impdp scott/tiger directory=exp_home dumpfile=tbs_01.dmp tablespaces=tbs_01
错误显示:ORA-00959: tablespace 'TBS_01' does not exist
##创建表空间
SQL>create tablespace tbs_01 datafile '/u01/app/oracle/oradata/orcl/tbs_01.dbf' size 100m;
##导入
impdp scott/tiger directory=data_pump_dir dumpfile=tbs_01.dmp tablespaces=tbs_01
6.schema的导入和导出
$ expdp scott/tiger directory=exp_home dumpfile=hr.dmp schemas=hr
$ impdp scott/tiger directory=exp_home dumpfile=hr.dmp schemas=hr
7.表的导入和导出
expdp scott/tiger directory=exp_home dumpfile=tble_emp.dmp tables=scott.emp reuse_dumpfiles=y
impdp scott/tiger directory=exp_home dumpfile=tble_emp.dmp tables=emp
8.数据泵导出参数
reuse_dumpfiles:默认不覆盖转储文件,覆盖用resue_dumpfiles=y
compression:指定压缩那些数据,在导出数据写入转储文件前.data_only只压缩所有数据,metadata_only,只压缩所有元数据(表结构)
//扩展
##查看角色权限
SQL>select * from role_sys_privs where role='EXP_FULL_DTABASE';
SQL>select * from role_sys_privs where role='CONNECT'
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
metalink:oracle内部的付费账号
9.导出hr.employees用户中JOB_ID=IT_PROG,薪水大于8000的雇员信息 --加query参数
shell>expdp hr/hr directory=data_pump_file dumpfile=emp.dmp tables=hr.employees query=hr.employees:"where job_id='IT_PROG' and salary>8000"
出错信息:
LRM-00101: unknown parameter name 'job_id'
正解:
expdp scott/tiger directory=data_pump_dir dumpfile=emp.dmp tables=hr.employees query=hr.employees:\"where JOB_ID\=\'IT_PROG\' and SALARY\>8000\"
10.SCN系统更改号
数据泵:精确到行记录,只能还原到备份的状态
rman:最小粒度为文件,和归档日志结合,可以还原到任何时间点.
##查看当前SCN
sql>select current_scn from v$database;
CURRENT_SCN
-----------
2280109
##插入数据
sql>insert into dept values(22,'ha','luoyang');
##查看更改过的SCN
sql>select current_scn from v$database;
CURRENT_SCN
-----------
2280147