/* 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 新表空间 );