Oracle备份数据到新的数据库

Oracle备份数据到一个新的数据库的脚本

1.创建对应的表空间

2.创建用户授予权限

3.使用impdp命令从备份里面导入数据都新的数据库

 1 CREATE TABLESPACE POSITIVE50A
 2  DATAFILE 'D:\ora_data\POSITIVE50A.dbf'
 3  SIZE 20232M
 4  DEFAULT STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 20);
 5 
 6 CREATE TEMPORARY TABLESPACE POSITIVE50A_TMP
 7  TEMPFILE 'D:\ora_data\POSITIVE50A_TMP.dbf'
 8  SIZE 500M;
 9 
10 CREATE TABLESPACE PO50A
11  DATAFILE 'D:\ora_data\PO50A.dbf'
12  SIZE 20232M
13  DEFAULT STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 20);
14 
15 CREATE TEMPORARY TABLESPACE PO50A_TMP
16  TEMPFILE 'D:\ora_data\PO50A_TMP.dbf'
17  SIZE 500M;
18 
19 CREATE TABLESPACE POSITIVE_MYNUM
20  DATAFILE 'D:\ora_data\POSITIVE_MYNUM.dbf'
21  SIZE 20232M
22  DEFAULT STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 20);
23 
24 CREATE TEMPORARY TABLESPACE POSITIVE_MYNUM_TMP
25  TEMPFILE 'D:\ora_data\POSITIVE_MYNUM_TMP.dbf'
26  SIZE 500M;
27  
28 create or replace directory dmp_0623_50a as 'd:\dmp_0623_50a'
29 --DROP directory dmp_0623_50a
30 SELECT T.* FROM all_directories T;
31 
32 --01
33 CREATE USER PSTV50A_MC_01_21 IDENTIFIED BY BRAIN;
34 GRANT ALL PRIVILEGES TO PSTV50A_MC_01_21;
35 CREATE OR REPLACE TYPE PSTV50A_MC_01_21.RANGEDATE_TBL_TYPE AS TABLE OF DATE;
36 
37 create user PSTV50A_MC_NCL_01_21 identified by BRAIN;
38 grant all privileges to PSTV50A_MC_NCL_01_21;
39 create or replace TYPE PSTV50A_MC_NCL_01_21.RANGEDATE_TBL_TYPE AS TABLE OF DATE;
40 
41 impdp system/Abcd1234@HAYABUSA directory=dmp_0623_50a dumpfile=FULL.DMP SCHEMAS=(PSTV50A_MC_21,PSTV50A_MC_NCL_21) REMAP_SCHEMA=PSTV50A_MC_21:PSTV50A_MC_01_21 REMAP_SCHEMA=PSTV50A_MC_NCL_21:PSTV50A_MC_NCL_01_21 TABLE_EXISTS_ACTION=TRUNCATE
42 CREATE OR REPLACE EDITIONABLE SYNONYM "PSTV50A_MC_01_21"."NCL_MYNUM_FILE_MS" FOR "PSTV50A_MC_NCL_01_21"."WEB_TN9000002_M";
43 GRANT ALL ON PSTV50A_MC_NCL_01_21.WEB_TN9000002_M TO PSTV50A_MC_01_21;

 

转载于:https://www.cnblogs.com/xqhu/p/5981516.html