mysql数据泵导入导出_oracle数据泵导入导出步骤

第1步：创建临时表空间

create temporary tablespace mytemp

tempfile '/data3/oracle/oradata/mytemp.dbf'

size 20m

autoextend on

next 10m maxsize 20m

extent management local;

第2步：创建表空间

create tablespace mydata

logging

datafile '/data3/oracle/oradata/mydata.dbf'

size 20m

autoextend on

next 10m maxsize 20m

extent management local;

第3步：创建用户并指定表空间

create user myuser identified by mypass

default tablespace mydata

temporary tablespace mytemp;

第4步：给用户授予权限

grant connect,resource to myuser;

select * from dba_role_privs where GRANTEE='myuser';

mysql myuser/mypass@

第5步：建表

CREATE TABLE mytb

(

userName VarChar(20) NOT NULL

);

第6步：插入数据

INSERT INTO mytb(userName) Values('张三');

INSERT INTO mytb(userName) Values('李四22');

select * from mytb

第7步：expdp 导出用户模式myuser

1)创建目录

先建立导出用的"目录"，并授权给testuser用户做导出

sqlplus / as sysdba

SQL> create directory expdir as '/data3/oracle/oradata/expdata';

Directory created.

SQL> grant read,write on directory expdir to myuser;

SQL> execute dbms_metadata_util.load_stylesheets;

2) expdap

expdp myuser/mypass dumpfile=myexpdp.dmp directory=expdir logfile=myexpdp.log schemas=myuser;

3) 删除表空间和用户

SQL> drop tablespace mydata including contents and datafiles;

SQL> drop tablespace mytemp including contents and datafiles;

SQL> drop user myuser cascade;

4) 重建表空间，用户

create temporary tablespace mytemp

tempfile '/data3/oracle/oradata/mytemp1.dbf'

size 20m

autoextend on

next 10m maxsize 20m

extent management local;

create tablespace mydata

logging

datafile '/data3/oracle/oradata/mydata2.dbf'

size 20m

autoextend on

next 10m maxsize 20m

extent management local;

create user myuser identified by mypass

default tablespace mydata

temporary tablespace mytemp;

grant connect,resource to myuser; #授权

8 恢复操作

数据泵导入所需的目录

SQL> create directory impdir as '/data3/oracle/oradata/expdata'

SQL> grant read,write on directory impdir to myuser;

impdp myuser/mypass dumpfile=myexpdp.dmp directory=impdir logfile=myexpdp.log schemas=myuser;

8.1 exec dbms_metadata_util.load_stylesheets;

9 总结

导入前得先确认原数据库用户的权限是多少,导入用户的权限一定要大于以前导出用户的权限。

10 其它常用的命令

10.1 数据文件，表空间对应查询

col file_name for a50;

col tablespace_name for a20;

select tablespace_name,file_name from dba_data_files;

10.2 用户权限被授予的角色

select * from dba_role_privs where grantee='MYUSER'; #注意值要用大写

Select Privilege from ROLE_SYS_PRIVS where ROLE='DBA'; #查看角色被赋予的系统权限

10.3 显示用户被授予的系统权限

select grantee,privilege from dba_sys_privs where grantee='MYUSER';