下面是我写的一段程序,如果想自己生成代码,把声明的游标部分,放到SQLPLUS里执行一下,就可以了。
希望直接做这件事,那么修改文件中的两个参数,
old_tbs := 'DATA'; --需要移出的表空间
new_tbs := 'MONITOR_DATA'; --准备移入的表空间
就可以运行了,然后查看看你定义的两个表空间内的对象是不是全的。注意::我的代码是把闪回区对象清空处理的(清回回收站),所以统计的时候,要注意这个情况!
/* Formatted on 2013/4/3 23:30:58 (QP5 v5.114.809.3010) */
/******************************************************************************PURPOSE: 移动表空间
DATE CREATED: 2013-4-3
CREATED BY: 黄越勇
******************************************************************************/
DECLARE
--设置表可移动
CURSOR c_moveable (
old_tbs VARCHAR2
)
IS
SELECT DISTINCT
'alter table '
|| owner
|| '.'
|| segment_name
|| ' enable row movement'
cmd
FROM dba_extents
WHERE tablespace_name = old_tbs AND segment_type = 'TABLE';
--释放表空间,针对delete
CURSOR c_shrink (
old_tbs VARCHAR2
)
IS
SELECT DISTINCT
'alter table '
|| owner
|| '.'
|| segment_name
|| ' shrink space CASCADE'
cmd
FROM dba_extents
WHERE tablespace_name = old_tbs AND segment_type = 'TABLE';
--移动表到新的表空间
CURSOR c_move (
old_tbs VARCHAR2,
new_tbs VARCHAR2
)
IS
SELECT DISTINCT
'alter table '
|| owner
|| '.'
|| segment_name
|| ' move tablespace "'
|| new_tbs
|| '"'
cmd
FROM dba_extents
WHERE tablespace_name = old_tbs AND segment_type = 'TABLE';
--重建索引到新表空间
CURSOR c_index (
old_tbs VARCHAR2,
new_tbs VARCHAR2
)
IS
SELECT DISTINCT
'alter index '
|| owner
|| '.'
|| segment_name
|| ' rebuild tablespace "'
|| new_tbs
|| '"'
cmd
FROM dba_extents
WHERE tablespace_name = old_tbs AND segment_type = 'INDEX';
--移动大对象到新的表空间
CURSOR c_lob (
old_tbs VARCHAR2,
new_tbs VARCHAR2
)
IS
SELECT 'alter table '
|| owner
|| '.'
|| table_name
|| ' move lob('
|| column_name
|| ')store as (tablespace '
|| new_tbs
|| ')'
cmd
FROM dba_lobs
WHERE segment_name IN (SELECT segment_name
FROM dba_extents
WHERE tablespace_name = old_tbs);
--清空表空间的闪回区
CURSOR c_recycle (old_tbs VARCHAR2)
IS
SELECT 'purge table ' || owner || '."' || object_name || '"' cmd
FROM dba_recyclebin
WHERE type='TABLE' and ts_name= old_tbs;
old_tbs VARCHAR2 (30);
new_tbs VARCHAR2 (30);
BEGIN
old_tbs := 'DATA'; --需要移出的表空间
new_tbs := 'MONITOR_DATA'; --准备移入的表空间
DBMS_OUTPUT.put_line ('--设置表可移动');
FOR v_moveable IN c_moveable (old_tbs)
LOOP
DBMS_OUTPUT.put_line (v_moveable.cmd||';');
BEGIN
EXECUTE IMMEDIATE v_moveable.cmd;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
END;
END LOOP;
DBMS_OUTPUT.put_line ('--释放表空间,针对delete');
FOR v_shrink IN c_shrink (old_tbs)
LOOP
DBMS_OUTPUT.put_line (v_shrink.cmd||';');
BEGIN
EXECUTE IMMEDIATE v_shrink.cmd;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
END;
END LOOP;
DBMS_OUTPUT.put_line ('--移动表到新的表空间');
FOR v_move IN c_move (old_tbs, new_tbs)
LOOP
DBMS_OUTPUT.put_line (v_move.cmd||';');
BEGIN
EXECUTE IMMEDIATE v_move.cmd;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
END;
END LOOP;
DBMS_OUTPUT.put_line ('--重建索引到新表空间');
FOR v_index IN c_index (old_tbs, new_tbs)
LOOP
DBMS_OUTPUT.put_line (v_index.cmd||';');
BEGIN
EXECUTE IMMEDIATE v_index.cmd;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
END;
END LOOP;
DBMS_OUTPUT.put_line ('--移动大对象到新的表空间');
FOR v_lob IN c_lob (old_tbs, new_tbs)
LOOP
DBMS_OUTPUT.put_line (v_lob.cmd||';');
BEGIN
EXECUTE IMMEDIATE v_lob.cmd;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
END;
END LOOP;
DBMS_OUTPUT.put_line ('--清空表空间的闪回区');
FOR v_recycle IN c_recycle (old_tbs)
LOOP
DBMS_OUTPUT.put_line (v_recycle.cmd||';');
BEGIN
EXECUTE IMMEDIATE v_recycle.cmd;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('--这句出错了'||sqlerrm);
END;
END LOOP;
END;