批量修改oracle 用户的表空间

来源：互联网发布：红杉树网络会议系统编辑：程序博客网时间：2024/05/16 12:07

/* Formatted on 2012-10-11 14:17:04 (QP5 v5.115.810.9015) */
--进入到该用户下进行操作

1.修改表
DECLARE
   i_count   INT := 0;
   execsql   VARCHAR2 (1000);
   CURSOR c_mysql
   IS
      SELECT   'alter table '
               || table_name
               || ' move tablespace UUMS'
               mysql
        FROM   user_all_tables;
        WHERE tablespace_name = 'YCTS';
BEGIN
   FOR r_mysql IN c_mysql
   LOOP
      DBMS_OUTPUT.put_line (r_mysql.mysql);
      EXECUTE IMMEDIATE r_mysql.mysql;
      i_count := i_count + 1;
   END LOOP;
   DBMS_OUTPUT.put_line ('i_count: ' || i_count);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         '异常:' || 'sqlcode:' || SQLCODE || ' sqlerrm : ' || SQLERRM
      );
END;

2.修改索引

DECLARE
   i_count INT := 0;
   CURSOR c_mysql IS
     SELECT 'alter index YCTS.' || index_name ||
            ' rebuild tablespace UUMS' mysql
       FROM dba_INDEXES
      WHERE owner = 'UUMS'
        and tablespace_name = 'YCTS'
        and index_type <>'LOB';
BEGIN
   FOR r_mysql IN c_mysql LOOP
     DBMS_OUTPUT.put_line(r_mysql.mysql);
     EXECUTE IMMEDIATE r_mysql.mysql;
     i_count := i_count + 1;
   END LOOP;
     DBMS_OUTPUT.put_line('i_count: ' || i_count);
END;

3.手动修改blob

ALTER TABLE 表名称 MOVE
TABLESPACE 新表空间
LOB (表中类型为lob的字段名) STORE AS lobsegment
(TABLESPACE 新表空间 );

0 0