重做数据库与导入导出总结

drop user biuser cascade;--删除原有数据

--创建数据库用户

create user biuser profile default identified by biuser--创建用户biuser密码biuser

default tablespace bidata--表空间

quota unlimited on bidata

account unlock;

grant alter any index to biuser;--给权限

grant create any index to biuser;

grant create procedure to biuser;

grant create any view to biuser;

grant drop any index to biuser;

grant drop any view to biuser;

grant connect to biuser;

commit;

 

--分配权限

grant connect ,resource,imp_full_database to biuser;

 

--导入前准备  先查dump所在位置切换路径到dump所在  

oraclebitest-> sqlplus /nolog

查看测试环境对应的directory。

SQL> conn /as sysdba

Connected.

SQL> select * from dba_directories;

--导入操作

impdp \'/ as sysdba\'  directory=dump dumpfile=biuser20180426.dmp version=10.2.0.3.0

 

--导出  reuse_dumpfiles=y 表示导出文件存在 覆盖操作  content=metadata_only 只导出表定义不需要表数据

expdp \'/ as sysdba\'  directory=dump SCHEMAS=dmuser dumpfile=dmuser.dmp reuse_dumpfiles=y CONTENT=METADATA_ONLY version=10.2.0.3.0

--导出

expdp \'/ as sysdba\'  directory=dump SCHEMAS=dmuser dumpfile=dmuser.dmp reuse_dumpfiles=y version=10.2.0.3.0