第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';