oracle碎片整理

ThinkerFuther

已于 2024-05-20 14:22:48 修改

阅读量287

点赞数 9

文章标签： oracle 数据库

于 2024-05-20 13:07:38 首次发布

本文链接：https://blog.csdn.net/lc547913923/article/details/139062538

版权

1、move碎片整理

1）

DECLARE
tmp_val VARCHAR2 (500);
BEGIN
FOR REC IN (SELECT TABLE_NAME FROM USER_TABLES )
LOOP
tmp_val:='ALTER TABLE '|| REC.TABLE_NAME ||' MOVE';
BEGIN
EXECUTE IMMEDIATE tmp_val;
DBMS_OUTPUT.ENABLE(buffer_size => null);
DBMS_OUTPUT.put_line (tmp_val);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error: ' || tmp_val || '!');
END;
END LOOP;
END;

2）

DECLARE
tmp_val VARCHAR2 (500);
BEGIN
FOR REC IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = 'T_ORDER' )
LOOP
tmp_val:='ALTER TABLE T_ORDER move partition '|| REC.partition_name ||'' ;
BEGIN
EXECUTE IMMEDIATE tmp_val;
DBMS_OUTPUT.ENABLE(buffer_size => null);
DBMS_OUTPUT.put_line (tmp_val);
EXCEPTION
WHEN OTH