【ORACLE】使用数据泵的生产环境impd,expdp数据迁移

**********************************************

** 原文： blog.csdn.net/clark_xu  徐长亮专栏

*********************************************

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

版权声明：本文博主原创文章。博客，未经同意不得转载。