c:> sqlplus /nolog SQL> conn sys/root123 as sysdba
使用EXPDP命令导出数据(可以按照表导出,按照用户模式导出,按照表空间导出和全库导出),使用IMPDP命令导入数据(可以按照表导入,按照用户模式导入,按照表空间导出和全库导入)。
1.1expdp备份
1.1.1导出表
导出scott用户的emp、dept表
创建一个操作目录 SQL> create directory dump_dir as 'C:appAdministratororadataorcldump est' 授权用户操作dump_dir目录的权限 SQL> grant read,write on directory dump_dir to scott; SQL> col directory_name for a20 SQL> col directory_path for a60 SQL> col owner for a8 SQL> select * from dba_directories;
创建测试用户tom并授权
SQL> create user tom identified by 123456; SQL> grant connect,resource to tom; SQL> grant read,write on directory dump_dir to tom;
导出scott用户下的emp和dept表
c:> expdp scott/123456 directory=dump_dir dumpfile=scotttab.dmp tables=emp,dept
1.1.2导入指定表
导入scott用户emp表
用户scott删除emp表
SQL> conn scott/123456 SQL> drop table emp;
导入emp表
c:> impdp scott/123456 directory=dump_dir dumpfile=scotttab.dmp tables=emp
1.1.3用户表导入到指定用户
scott用户下的emp表导入到tom用户下
C:UsersAdministrator> impdp system/root123 directory=dump_dir dumpfile=scotttab.dmp tables=scott.emp,scott.dept REMAP_SCHEMA=SCOTT:TOM
1.3.1导出表空间
创建表空间,在表空间上创建一个表,并为表插入记录
SQL> show user USER 为 "SYS" SQL> create tablespace xx datafile 'c: empxx.dbf' size 100m; SQL> alter user scott account unlock; SQL> conn scott/root123; SQL> create table aa(id int,name varchar2(10)) tablespace xx; SQL> insert into aa values(1,'tom1'); SQL> commit;
C:UsersAdministrator>expdp system/root123 directory=dump_dir dumpfile=xx.dmp tablespaces=xx
删除表空间及数据文件
SQL> show user; SQL> drop tablespace xx including contents and datafiles;
查看表
SQL> conn scott/root123 SQL> select * from tab; aa表已被删除
//先创建表空间 SQL> create tablespace xx datafile 'c: empxx.dbf' size 100m; //导入表空间 c:> impdp system/root123 directory=dump_dir dumpfile=xx.dmp tablespaces=xx
验证
SQL> conn scott/oracle; Sql> select * from tab;
1.5导出全库
创建目录,授权用户
SQL> create directory xxx as 'C:appAdministratororadataxxx'; SQL> grant read,write on directory xxx to system;
查看目录;目录权限
SQL> select * from dba_directories; SQL> SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;
导出全库
C:> expdp system/root123 directory=xxx dumpfile=full04281057.dmp full=y logfile=expdp.log ... 作业 "SYSTEM"."SYS_EXPORT_FULL_01" 已经完成, 但是有 2 个错误 (于 10:54:40 完成)
-
出现两个错误:
-
1.客户端字符集
-
2.system权限不够(ORA-39181: Only partial table data may be exported due to fine grain access control on "OE"."PURCHAS)
-
解决方法
-
设置字符集
客户端修改字符集: 开始》》运行》》regedit》》确定: hkey_local_machinesoftwareoraclehomexx ls_lang 原来的设置为 simplified chinese_china.zhs16gbk 改为 american_america.al32utf8
-
添加 exempt access policy to system 豁免访问权限
SQL> grant exempt access policy to system;
-
1.6导入全库
-
table_exists_action关键字
-
skip:如果表已存在,则跳过并处理写一个对象
-
append:是为了表增加数据
-
truncate:是截断表,然后为其增加新数据
-
replace是删除已存在表,重新建表并追加数据
-
-
remap_schema=A:B
-
A导出的数据,导入到B中
-
-
SCHEMAS 要导入的方案的列表(导入到指定用户)
-
owner 改变表的owner:remap_schema=scott:system;
-
tablespaces 导入表空间
-
full 导入数据库
-
追加数据: impdp system/root123 directory=my_data dumpfile=expdp.dmp schemas=system table_exists_action=append;
C:UsersAdministrator>impdp system/root123 directory=dump_dir dumpfile=full.dmp schemas=sys table_exists_action=replace
1.7directory目录操作
创建
create [or replace] directory xxx as 'c:aaaxxx'
SQL> create directory xxx as 'c:aaaxxx'; SQL> create or replace directory xxx as 'c:aaaxxx'; //or replace 重建
授权
grant read[,write] on directory directory to username;
SQL> grant read,write on directory directory to username;
查
SQL> select * from dba_directories;
删
SQL> drop directory xxx;