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