Create, delete, and migrate data across more than 200 tablespaces at the same time through Pl/sql

Function: Create stored procedures, create/delete 200 + table spaces/Users through plsql, data migration

Scope of application:

Table space is more, data is larger than data migration. This database will gradually be put into a separate instance. There is already a library of more than 1TB of data, such as csb221. To migrate this library to another set of library csb461 on another system.

SYS SYSDBA Landing Plsql

--0, creating another set of libraries Databaselink

If you can execute a statement, you do not have to create link.

Select Upper (UserID) as UserID frommymis_main.xtdw@csb48

Create a link statement

Create DATABASE link CSB48 connect to Mymis_main identified by Mymis_main using ' CSB48 ';

CREATE TABLE XTDW as SELECT * from MYMISMAIN.XTDW;

--1, deleting users

Begin

For I in (Select Upper (UserID) as UserID from XTDW) loop

Execute immediate ' drop user ' | | i.userid| | ' Cascade ';

End Loop;

End;

--2, deleting a table space

Begin

For I in (Select Upper (UserID) as UserID from XTDW) loop

Execute immediate ' drop tablespace ' | | i.userid| | ' including contents and datafiles ';

End Loop;

End;

--3, creating table spaces

Begin

For I in (Select Upper (UserID) as UserID from XTDW) loop

Execute immediate ' CREATE tablespace ' | | i.userid| | ' DataFile ' C:\ORACLE\PRODUCT\10.2.0\ORADATA\JZMS\JZMS\ ' | | i.userid| | '. ORA ' SIZE 350M autoextend on NEXT 20M MAXSIZE Unlimited ';

End Loop;

End;

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

--4, creating users

C:\ORACLE\PRODUCT\10.2.0\ORADATA\JZMS\JZMS\MYMIS0101. 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 ' | | 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, generating Import statements

Select ' Imp ' | | userid| | ' /'|| userid| | ' @jzmsFile =c:\oracle\product\10.2.0\oradata\jzms\jzms\ ' | | userid| | '. DMP Log=c:\oracle\product\10.2.0\oradata\jzms\jzms\ ' | | UserID | | ' Imp.log ignore=y full=y ' as Impsql from MYMISMAIN.XTDW

After execution, copy to the batch file for execution