Oracle导库
问题:针对生产库与开发库的版本不一致导致开发库无法复现生产问题,所以需要导库来解决。
总得思路:把用户的脚本全部删除,然后将备份的dmp文件保存到本地用语句导入即可,具体思路如下:
(1)清空当前登录用户的所有对象的脚本(去PL/SQL中执行如下sql)
declare
v_sql varchar2(128);
BEGIN
FOR objectRow IN (SELECT OBJECT_NAME, OBJECT_TYPE FROM user_objects) LOOP
IF objectRow.OBJECT_TYPE = 'TABLE' THEN
v_sql := 'DROP TABLE ' || objectRow.OBJECT_NAME ||
' cascade constraints';
execute immediate v_sql;
ELSIF objectRow.OBJECT_TYPE = 'TYPE' THEN
v_sql := 'DROP ' || objectRow.OBJECT_TYPE || ' ' ||
objectRow.OBJECT_NAME || ' force';
execute immediate v_sql;
ELSIF objectRow.OBJECT_TYPE = 'VIEW' THEN
v_sql := 'DROP ' || objectRow.OBJECT_TYPE || ' ' ||
objectRow.OBJECT_NAME || ' force';
execute immediate v_sql;
ELSIF objectRow.OBJECT_TYPE = 'SEQUENCE' OR
objectRow.OBJECT_TYPE = 'PACKAGE' OR
objectRow.OBJECT_TYPE = 'PROCEDURE' OR
objectRow.OBJECT_TYPE = 'FUNCTION' THEN
v_sql := 'DROP ' || objectRow.OBJECT_TYPE || ' ' ||
objectRow.OBJECT_NAME;
execute immediate v_sql;
END IF;
END LOOP;
END;
/
PURGE RECYCLEBIN;
/
(2)新建directory的语法
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
For Example:
create or replace directory HH_DUMP_DIR as 'D:\myTempFile';
查看是否创建成功:
select * from dba_directories;
(3)指令导入
imp 用户名/密码@实例名 file=导入的dmp文件路径
For Example:
impdp test/11111111@192.168.1.1:1521/NSDEV dumpfile=myTest.dmp full=y
--如果当导出的时候有多个dmp文件中间用逗号隔开即可
dumpfile=myTest01.dmp,myTest02.dmp,myTest03.dmp
补充:导出
exp 用户名/密码@实例名 file=导出的dmp文件存放路径 log=导出日志存放路径
For Example:
exp test/test@192.168.1.1:1521/TEST file=D:\myTempFile\myTest.dmp log=D:\myTempFile\myTest.log
如果dmp文件由dba用户导出,而导入的用户不是dba,那么会报错
IMP-00013:only a DBA can import a file exported by another DBA
IMP-00000:Import terminated unsuccessfully
只需将dba权限赋值给需要导入的用户后,重新导入即可:
赋值语句:grant dba to test