oracle数据库复制并迁移至新的库
-- 第一步，创建表空间
declare
strTemp VARCHAR2(1024);
begin
strTemp:='CREATE TABLESPACE "JDEMESTBS" LOGGING DATAFILE ''D:\Microsoft\oradata\SMS\JDEMESTBS.ora'' SIZE 500M AUTOEXTEND
ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO';
execute immediate strTemp;
End;
-- 第二步 创建用户
declare
intNum NUMBER(3);
strTemp VARCHAR2(1024);
strPath VARCHAR2(256);
strUserName VARCHAR2(32);
strPassWord VARCHAR2(32);
strInitSize VARCHAR2(8);
strAddSize VARCHAR2(8);
strInitSize1 VARCHAR2(8);
strAddSize1 VARCHAR2(8);
intNewUser NUMBER(1);
begin
strUserName:='PPMS'; --创建用户的名称，需要修改，必须是大写用户名
strPassWord:='abc123456'; --创建用户的密码，需要修改
strInitSize:='500'; --TIPLM工作空间的初始大小，以M为单位
strAddSize:='10'; --TIPLM工作空间的增量大小，以M为单位
strInitSize1:='100'; --其它工作空间的初始大小，以M为单位
strAddSize1:='10'; --其它工作空间的增量大小，以M为单位
intNewUser:=1; --用户存在时，删除用户后再重建为1，只更新为0，用户不存在时都新建用户
strUserName:=upper(strUserName);
select count(*) into intNum from ALL_USERS where USERNAME = strUserName;
if intNum>0 and intNewUser>0 then
strTemp:='drop user "'||strUserName||'" cascade';
execute immediate strTemp;
end if;
if intNum=0 or intNewUser>0 then
strTemp:='CREATE USER "'||strUserName||'" PROFILE "DEFAULT" IDENTIFIED BY "'||strPassWord||'"
DEFAULT TABLESPACE "ERPSPACE" QUOTA UNLIMITED ON "ERPSPACE"
ACCOUNT UNLOCK';
execute immediate strTemp;
strTemp:='GRANT CREATE ANY PROCEDURE TO "'||strUserName||'"';
execute immediate strTemp;
strTemp:='GRANT CREATE ANY TABLE TO "'||strUserName||'"';
execute immediate strTemp;
strTemp:='GRANT CREATE ANY VIEW TO "'||strUserName||'"';
execute immediate strTemp;
strTemp:='GRANT SELECT ANY DICTIONARY TO "'||strUserName||'"';
execute immediate strTemp;
strTemp:='GRANT "CONNECT" TO "'||strUserName||'"';
execute immediate strTemp;
strTemp:='GRANT "EXP_FULL_DATABASE" TO "'||strUserName||'"';
execute immediate strTemp;
strTemp:='GRANT "IMP_FULL_DATABASE" TO "'||strUserName||'"';
execute immediate strTemp;
else
strTemp:='ALTER USER "'||strUserName||'" QUOTA UNLIMITED ON "ERPSPACE"';
execute immediate strTemp;
end if;
End;
-- 第三步 用PL/SQL导出数据表
-- 第四步 用PL/SQL导入数据表
