【ORACLE】生产环境使用数据泵impd,expdp进行数据迁移相关教程
1 原数据库的操作
1.1 查看原有配置
1.1.1 查看用户权限
查看用户系统权限：
SQL> select * from user_sys_privs;
查看用户拥有的角色
SQL> select * from user_role_privs;
查看当前会话的权限
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
1.1.2 查看表
查看用户下所有的表
SQL>select * from user_tables;
查看某表的创建时间
select object_name,created from user_objects
查看某表的大小
select sum(bytes)/(1024*1024) size_M,segment_name from user_segments group by segment_name;
查看放在ORACLE的内存区里的表
SQL> select table_name,cache from user_tables where instr(cache,'Y')>0;
1.1.3 查看视图，索引
查看用户的视图
Select * from user_views;
查看用户的索引
SQL> select * from user_indexes;
查看索引个数和类别
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper(‘&index_name’);
查看索引的大小
SQL>select sum(bytes)/(1024*1024) as “size(M)” from user_segments
where segment_name=upper(‘&index_name’);
1.1.4 查看存储过程
查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type=’FUNCTION’;
SQL>select object_name,status from user_objects where object_type=’PROCEDURE’;
查看函数和过程的源代码
SQL>select text from all_source where owner=user and name=upper(‘&plsql_name’);
1.1.5查看序列号
select * from user_sequences;
1.1.6查看同义词
select * from user_synonyms;
1.1.7查看某表的约束条件
SQL> select constraint_name, constraint_type,search_condition, r_constraint_name
2 from user_constraints
3 where table_name ='FAMILY';
1.2 数据的导出
建立Dump路径
SQL> connect / as sysdba
Connected.
SQL> create directory dump_dir as '/oracle';
SQL> grant read,write on directory dump_dir to u1;
按照用户导出，可以导出存储过程，数据，视图，索引等。
expdp system/oracle schemas=u1 dumpfile='u1.dump' directory=dump_dir version=10.2.0.1.0
2 目标数据库操作
2.1 建立表空间和用户
SQL> create tablespace u1
2 logging
3 datafile '/home/oracle/oradata/db1/u1.dbf'
4 size 30m
5 autoextend on
6 next 32m
7 maxsize 100m
8 extent management local;
SQL> create user u1 identified by tiger default tablespace u1;
grant resource,connect to u1;
2.2 按照用户导入
建立Dump路径
SQL> connect / as sysdba
Connected.
SQL> create directory dump_dir as '/oracle';
SQL> grant read,write on directory dump_dir to u1;
impdp system/oracle schemas=u1 dumpfile='u1.dump' directory=dump
3. 其他参数
3.1 按照版本导出
[oracle@host2 ~]$ expdp system/oracle tablespaces=u1 dumpfile='u1_version.dump' directory=dump_dir version=10.2.0.1.0
[oracle@host2 oracle]$ impdp system/oracle tablespaces=u1 directory=dump dumpfile='u1_version.dump' version=10.2.0.1.0
3.2 导出定义
expdp system/oracle tablespaces=u1 dumpfile='u1_all.dump' directory=dump_dir version=10.2.0.1.0 CONTENT=ALL
3.3 只导出存储过程
PROCEDURE也可以换成其它对象如：INDEX，TABLE，FUNCTION，VIEW，DATABASE LINK，SYNONYM，PACKAGE BODY，SEQUENCE，LOB，PACKAGE，PROCEDURE
[oracle@host2 oracle]$ expdp system/oracle schemas=u1 dumpfile='u1_p.dump' directory=dump_dir version=10.2.0.1.0 include=PROCEDURE