通过PLSQL同时对几百个Oracle表空间进行创建/删除/数据迁移

sys sysdba 登陆plsql

--0、创建另外一套库的databaselink
如果能执行语句就不用创建link。
Select upper(userid) as userid From mymis_CS.CSdw@csb48
创建link语句
create database link CSB48 connect to MYMIS_CS identified by MYMIS_CS using 'CSB48';

create table xtdw as select * from mymismai.xtdw;
--1、删除用户

Begin
For i In (Select upper(userid) as userid From CSdw) loop
execute immediate 'drop user '||i.userid||' Cascade';
End Loop;
End;

--2、删除表空间
Begin
For i In (Select upper(userid) as userid From XTCS) loop
execute immediate 'drop tablespace '||i.userid||' including contents and datafiles';
End Loop;
End;
--3、创建表空间
Begin
For i In (Select upper(userid) as userid From CSdw) loop
execute immediate 'CREATE TABLESPACE '||i.userid||' DATAFILE ''C:\ORACLE\PRODUCT\10.2.0\ORADATA\CSMS \CSMS \'||i.userid||'.ORA'' SIZE 350M AUTOEXTEND ON NEXT 20M MAXSIZE Unlimited';
End Loop;
End;

--4、创建用户
C:\ORACLE\PRODUCT\10.2.0\ORADATA\CSMS\CSMS\MYMISS0101.ORA

Begin
For i In (Select upper(userid) as userid From xtdw) loop
Execute Immediate 'CREATE USER ' ||i.userid|| ' PROFILE "DEFAULT" '
||'IDENTIFIED BY ' ||i.userid|| ' DEFAULT TABLESPACE ' ||i.userid|| ' '
||'TEMPORARY TABLESPACE "TEMP" '
||'ACCOUNT UNLOCK';
Execute Immediate 'GRANT ALTER ANY INDEX TO ' ||i.userid|| '';
Execute Immediate 'GRANT ALTER ANY PROCEDURE TO ' ||i.userid|| '';
Execute Immediate 'GRANT ALTER ANY SEQUENCE TO ' ||i.userid|| '';
Execute Immediate 'GRANT ALTER ANY TABLE TO ' ||i.userid|| '';
Execute Immediate 'GRANT ALTER ANY TRIGGER TO ' ||i.userid|| '';
Execute Immediate 'GRANT CREATE ANY INDEX TO ' ||i.userid|| '';
Execute Immediate 'GRANT CREATE ANY PROCEDURE TO ' ||i.userid|| '';
Execute Immediate 'GRANT CREATE ANY SEQUENCE TO ' ||i.userid|| '';
Execute Immediate 'GRANT CREATE ANY TABLE TO ' ||i.userid|| '';
Execute Immediate 'GRANT CREATE ANY TRIGGER TO ' ||i.userid|| '';
Execute Immediate 'GRANT CREATE ANY VIEW TO ' ||i.userid|| '';
Execute Immediate 'GRANT DEBUG ANY PROCEDURE TO ' ||i.userid|| '';
Execute Immediate 'GRANT DELETE ANY TABLE TO ' ||i.userid|| '';
Execute Immediate 'GRANT DROP ANY INDEX TO ' ||i.userid|| '';
Execute Immediate 'GRANT DROP ANY PROCEDURE TO ' ||i.userid|| '';
Execute Immediate 'GRANT DROP ANY SEQUENCE TO ' ||i.userid|| '';
Execute Immediate 'GRANT DROP ANY TABLE TO ' ||i.userid|| '';
Execute Immediate 'GRANT DROP ANY TRIGGER TO ' ||i.userid|| '';
Execute Immediate 'GRANT DROP ANY VIEW TO ' ||i.userid|| '';
Execute Immediate 'GRANT EXECUTE ANY PROCEDURE TO ' ||i.userid|| '';
Execute Immediate 'GRANT INSERT ANY TABLE TO ' ||i.userid|| '';
Execute Immediate 'GRANT SELECT ANY SEQUENCE TO ' ||i.userid|| '';
Execute Immediate 'GRANT SELECT ANY TABLE TO ' ||i.userid|| '';
Execute Immediate 'GRANT UNDER ANY TABLE TO ' ||i.userid|| '';
Execute Immediate 'GRANT UNDER ANY VIEW TO ' ||i.userid|| '';
Execute Immediate 'GRANT UPDATE ANY TABLE TO ' ||i.userid|| '';
Execute Immediate 'GRANT "CONNECT" TO ' ||i.userid|| '';
Execute Immediate 'GRANT "DBA" TO ' ||i.userid|| '';
Execute Immediate 'alter user ' ||i.userid|| ' quota unlimited on ' ||i.userid|| '';
End Loop;
End;

--5、生成导入语句
Select 'imp '|| userid||'/'|| userid||'@jzms File=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CSMS\JCSMS\'|| userid||'.dmp Log=C:\ORACLE\PRODUCT\10.2.0\ORADATA\CSMS\CSMS\'|| userid ||'imp.log Ignore=y Full=y' as impsql From mymisma.xtCS

执行后，拷贝到批处理文件中执行。