oracle瘦身（lob字段空间释放存储过程）
5墨值下载
--当执行用户不是管理员时需要先给用户授权
grant select any dictionary to 用户名;
--创建存储过程
create or replace PROCEDURE OUTPUTOFLOB(v_tablespace_name IN VARCHAR2)
Authid Current_User AS
v_table_name VARCHAR2(2000);
v_column_name VARCHAR2(2000);
v_OWNER VARCHAR2(2000);
v_index_name VARCHAR2(2000);
CURSOR CUR_SQLS IS
select d.TABLE_NAME as TABLE_NAME,
d.COLUMN_NAME as COLUMN_NAME,
d.OWNER as OWNER,
s.Segment_Name as Segment_Name,
Sum(s.bytes) / 1024 / 1024 as n
from dba_lobs d
left join User_Extents s
on s.SEGMENT_NAME = d.SEGMENT_NAME
where s.SEGMENT_NAME LIKE 'SYS_LOB%'
and d.TABLESPACE_NAME = v_tablespace_name
group by d.TABLE_NAME, d.COLUMN_NAME, d.OWNER, s.Segment_Name
having Sum(s.bytes) / 1024 / 1024 > 100
order by Sum(bytes) / 1024 / 1024 desc;
begin
DBMS_OUTPUT.ENABLE(buffer_size => null); --输出的字符不限制
FOR CV_SQL IN CUR_SQLS LOOP
v_table_name := CV_SQL.table_name;
v_column_name := CV_SQL.column_name;
v_OWNER := CV_SQL.OWNER;
begin
DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || v_table_name ||
' MOVE TABLESPACE ' || v_tablespace_name ||
' LOB(' || v_column_name ||
') STORE AS (TABLESPACE ' || v_tablespace_name ||
' );');
FOR CV_INDEX IN (SELECT s.index_name as index_name
from dba_indexes s
where s.table_name = v_table_name
and s.owner = v_owner
and s.index_type = 'NORMAL') LOOP
v_index_name := CV_INDEX.index_name;
begin
DBMS_OUTPUT.PUT_LINE('ALTER INDEX "' || v_index_name ||
'" rebuild;');
end;
end LOOP;
end;
end loop;
end OUTPUTOFLOB;
--执行存储过程
call OUTPUTOFLOB('表空间的名字');
of 1
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论