ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

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

内容发布:风哥| 发布时间:2013-12-25 20:43:10
通过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

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




上一篇:在Oracle数据库中创建跟踪客户端IP地址的触发器
下一篇:【Oracle 表分区管理】Oracle partition表分区与分区索引几种方式测试过程
189070296,150201289

专业提供Oracle数据库服务、主机、存储、备份、中间件等相关技术支持服务,QQ号:176140749
关注ITPUX技术网微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux

加群分享视频教程部分如下:

1、公开课视频:Oracle/MySQL数据库工程师职业发展前景讲解(免费)
http://edu.51cto.com/course/7015.html

2、51CTO学院Oracle数据库高级工程师培训(高薪就业.课程介绍)
http://edu.51cto.com/px/train/131?xiaotu

3、Oracle DBA数据库高级工程师培训视频课程1.1(系列78套+七大阶段+上千案例)
套餐视频地址: http://edu.51cto.com/topic/1121.html

4、MySQL数据库(终身门徒)套餐:http://edu.51cto.com/sd/1e1a6

回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表