Oracle导入导出数据库
首先配置好DATA_PUMP_DIR
将以下内容写入bat脚本中运行
导出bat:
SET USR_Name=USR
SET PWD=PASSWORD
SET FILE_NAME=%USR_Name%
EXPDP %USR_Name%/%PWD% directory=DATA_PUMP_DIR dumpfile=%FILE_NAME%.dmp logfile=%FILE_NAME%.log schemas=%USR_Name%
导入bat:
SET USR_Name=USR
SET PWD=PASSWORD
SET FILE_NAME=%USR_Name%
IMPDP %USR_Name%/%PWD% directory=DATA_PUMP_DIR dumpfile=%FILE_NAME%.dmp logfile=%FILE_NAME%.log schemas=%USR_Name% table_exists_action=replace
删除当前用户下的所有数据库的脚本bat:
SET USR_Name=USR
SET PWD=PASSWORD
sqlplus %USR_Name%/%PWD%<Drop.sql
Drop.sql内容如下:
CREATE OR REPLACE PROCEDURE DROP_OBJECTS
IS
------------------------------------------------------------------------------------------------
-- This procedure will drop all the database objects for the current user.
------------------------------------------------------------------------------------------------
--
CURSOR DBMSJOb_Cur IS
SELECT Job
FROM USER_JOBS
ORDER BY 1;
--
CURSOR DBMS_Scheduler_Cur IS
SELECT Job_Name
FROM USER_SCHEDULER_JOBS
ORDER BY 1;
--
CURSOR Procedures_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'PROCEDURE'
AND Object_Name NOT IN ( 'DROP_OBJECTS'
,'CSI_ENTERPRISE_TEST_SUITE' )
ORDER BY 1;
--
CURSOR Functions_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'FUNCTION'
ORDER BY 1;
--
CURSOR Package_Body_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'PACKAGE BODY'
ORDER BY 1;
--
CURSOR Packages_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'PACKAGE'
ORDER BY 1;
--
CURSOR Sequences_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'SEQUENCE'
ORDER BY 1;
--
CURSOR Views_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'VIEW'
ORDER BY 1;
--
CURSOR Synonym_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'SYNONYM'
ORDER BY 1;
--
CURSOR DBLink_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'DATABASE LINK'
ORDER BY 1;
--
CURSOR OTAB_Type_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'TYPE'
AND Object_Name LIKE 'OTAB_%'
ORDER BY 1;
--
CURSOR OTYP_Type_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'TYPE'
AND Object_Name LIKE 'OTYP_%'
ORDER BY 1;
--
CURSOR MaterializedView_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'MATERIALIZED VIEW'
ORDER BY 1;
--
CURSOR Tables_Cur IS
SELECT *
FROM USER_OBJECTS
WHERE Object_Type = 'TABLE'
ORDER BY 1;
--
n_JObRunning NUMBER := 0;
n_ObjectCount NUMBER := 0;
n_ErrLocator NUMBER;
--
v_InstanceName VARCHAR2(256);
v_HostName VARCHAR2(30);
v_DBVersion VARCHAR2(30);
--
BEGIN
--
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Start - '||TO_CHAR(SYSDATE,'MM/DD/RRRR HH24:MI:SS'));
--
n_ErrLocator := 5;
--
SELECT UPPER(Instance_Name)
,UPPER(Host_Name)
,Version
INTO v_InstanceName
,v_HostName
,v_DBVersion
FROM V$INSTANCE;
--
DBMS_OUTPUT.PUT_LINE('Instance Name: '||v_InstanceName);
DBMS_OUTPUT.PUT_LINE('Host Name: '||v_HostName);
DBMS_OUTPUT.PUT_LINE('Database Version: '||v_DBVersion);
--
-- If the current user is SYS or SYSTEM, exit the program.
--
IF USER IN ( 'SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP' ) THEN
--
DBMS_OUTPUT.PUT_LINE('You are logged in as "'||USER||'". This script cannot be executed as "'||USER||'"');
DBMS_OUTPUT.PUT_LINE('Drop User Objects - Failed');
DBMS_OUTPUT.PUT_LINE('End - '||TO_CHAR(SYSDATE,'MM/DD/RRRR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
--
RETURN;
--
END IF;
--
DBMS_OUTPUT.PUT_LINE('Logged in as "'||USER||'".' );
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
-- Drop all the DBMS Jobs
--
n_ErrLocator := 10;
--
FOR CurrDBMSJob IN DBMSJob_Cur LOOP
--
EXECUTE IMMEDIATE 'BEGIN DBMS_JOB.REMOVE('||CurrDBMSJob.Job||'); END;';
--
DBMS_OUTPUT.PUT_LINE('DBMS Job '||CurrDBMSJob.Job||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('DBMS Jobs dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
-- Drop all the DBMS_SCHEDULER Jobs
--
n_ErrLocator := 15;
--
FOR CurrRow IN DBMS_Scheduler_Cur LOOP
--
SELECT COUNT(Job_Name)
INTO n_JobRunning
FROM USER_SCHEDULER_RUNNING_JOBS
WHERE Job_Name = CurrRow.Job_Name;
--
IF ( n_JobRunning > 0 )
THEN
--
EXECUTE IMMEDIATE 'BEGIN DBMS_SCHEDULER.STOP_JOB ('''||CurrRow.Job_Name||''',TRUE); END;';
--
END IF;
--
EXECUTE IMMEDIATE 'BEGIN DBMS_SCHEDULER.DROP_JOB ('''||CurrRow.Job_Name||''',TRUE); END;';
--
DBMS_OUTPUT.PUT_LINE('DBMS Scheduler Job '||CurrRow.Job_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('DBMS Scheduler Jobs dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Stored procedures
--
n_ErrLocator := 20;
--
FOR CurrProcedure IN Procedures_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP PROCEDURE '||CurrProcedure.Object_Name;
DBMS_OUTPUT.PUT_LINE('Procedure '||CurrProcedure.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Procedures dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Functions
--
n_ErrLocator := 25;
--
FOR CurrFunction IN Functions_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP FUNCTION '||CurrFunction.Object_Name;
DBMS_OUTPUT.PUT_LINE('Function '||CurrFunction.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Functions dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Packages
--
n_ErrLocator := 30;
--
FOR CurrPackages IN Packages_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP PACKAGE '||CurrPackages.Object_Name;
DBMS_OUTPUT.PUT_LINE('Package '||CurrPackages.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Packages dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Sequences
--
n_ErrLocator := 35;
--
FOR CurrSequence IN Sequences_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP SEQUENCE '||CurrSequence.Object_Name;
DBMS_OUTPUT.PUT_LINE('Sequence '||CurrSequence.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Sequences dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Views
--
n_ErrLocator := 40;
--
FOR CurrView IN Views_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP VIEW '||CurrView.Object_Name;
DBMS_OUTPUT.PUT_LINE('View '||CurrView.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Views dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Synonyms
--
n_ErrLocator := 45;
--
FOR CurrSynonym IN Synonym_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP SYNONYM '||CurrSynonym.Object_Name;
DBMS_OUTPUT.PUT_LINE('Synonym '||CurrSynonym.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Synonyms dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Database Links
--
n_ErrLocator := 50;
--
FOR CurrDBLink IN DBLink_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP DATABASE LINK '||CurrDBLink.Object_Name;
DBMS_OUTPUT.PUT_LINE('Database Link '||CurrDBLink.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Database Link dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Sub typed Table Object Types
--
n_ErrLocator := 55;
--
FOR CurrType IN OTAB_Type_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP TYPE '||CurrType.Object_Name;
DBMS_OUTPUT.PUT_LINE('Database Link '||CurrType.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Sub Typed Table Object types dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Base typed Table Object Types
--
n_ErrLocator := 60;
--
FOR CurrType IN OTYP_Type_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP TYPE '||CurrType.Object_Name;
DBMS_OUTPUT.PUT_LINE('Database Link '||CurrType.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Base Typed Table Object types dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Materialized Views
--
n_ErrLocator := 65;
--
FOR CurrMV IN MaterializedView_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW "'||CurrMV.Object_Name||'"';
DBMS_OUTPUT.PUT_LINE('Materialized View '||CurrMV.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Materialized Views dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
n_ObjectCount := 0;
--
-- Drop all the Tables
--
n_ErrLocator := 70;
--
FOR CurrTable IN Tables_Cur LOOP
--
EXECUTE IMMEDIATE 'DROP TABLE "'||CurrTable.Object_Name||'"';
DBMS_OUTPUT.PUT_LINE('Table '||CurrTable.Object_Name||' dropped');
--
n_ObjectCount := n_ObjectCount + 1;
--
END LOOP;
--
DBMS_OUTPUT.PUT_LINE('Tables dropped: '||n_ObjectCount);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
--
DBMS_OUTPUT.PUT_LINE('Drop Camstar Objects - Successful');
DBMS_OUTPUT.PUT_LINE('End - '||TO_CHAR(SYSDATE,'MM/DD/RRRR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
--
EXCEPTION
WHEN OTHERS THEN
--
DBMS_OUTPUT.PUT_LINE('Error dropping '||USER||'''s objects - ErrLoc: '||n_ErrLocator||' ErrMsg: '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Drop Camstar Objects - Failed');
DBMS_OUTPUT.PUT_LINE('End - '||TO_CHAR(SYSDATE,'MM/DD/RRRR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
--
RAISE_APPLICATION_ERROR(-20001,'Error dropping '||USER||'''s objects - ErrLoc: '||n_ErrLocator||' ErrMsg: '||SQLERRM);
--
END;
/
SPOOL Drop.log
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINESIZE 300
SET PAGES 20000
PURGE RECYCLEBIN
/
BEGIN
--
DROP_OBJECTS;
--
END;
/
DROP PROCEDURE DROP_OBJECTS
/
PURGE RECYCLEBIN
/
SPOOL OFF