检测表是否需要shrink,Segment advisor

本文提供了一个用于评估Oracle数据库表是否需要进行Shrink操作的SQL脚本,并通过一个示例展示了如何手动执行Segment Advisor来确定Shrink操作的潜在节省空间。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select a.owner || '.' || a.table_name "Table",a.num_rows,a.avg_row_len,b.inserts,b.deletes,a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0) total_rows,
round(a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0))/1024/1024,4) "Calculate_Sizle(Mb)",c.bytes/1024/1024 "Segment_Size(Mb)"
from dba_tables a left join dba_tab_modifications b
on a.owner=b.table_owner and a.table_name=b.table_name inner join dba_segments c on a.owner=c.owner and a.table_name=c.segment_name
where a.last_analyzed is not null and a.partitioned='NO' and a.owner not in
('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS','XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')
and (a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0)))/c.bytes<0.5;

上面的脚本(注意,该脚本只能检测被分析过的表,另外我也没有考虑分区表)可以初步判断数据库中所有表是否需要shrink,得到关于表的概要信息之后,我们可以利用10g中的Segment advisor来判断该表是否需要shrink,可以用oem来调用segment advisor,不过很多情况下oem并没有打开,所以这里介绍如何手动执行Segment advisor。

Segment advisor 例子:

SQL> show user

User is "robinson"

SQL> create table test as select * from dba_objects;

Table created

SQL> insert into test select * from dba_objects;

49792 rows inserted

SQL> /

49792 rows inserted

SQL> /

49792 rows inserted

SQL> /

49792 rows inserted

SQL> /

49792 rows inserted

SQL> /

49792 rows inserted

SQL> /

49792 rows inserted

SQL> /

49792 rows inserted

SQL> /

49792 rows inserted

SQL> /

49792 rows inserted

SQL> select owner,segment_name,segment_type,bytes/1024/1024 "Size(Mb)" from dba_segments where segment_name='TEST';

OWNER SEGMENT_NA SEGMENT_TYPE Size(Mb)

---------- ---------- ------------------ ----------

ROBINSON TEST TABLE 60

SQL> commit;

Commit complete

SQL> select count(*) from test;

COUNT(*)

----------

547712

SQL> delete from test where rownum<547712/2;

273855 rows deleted

SQL> commit;

Commit complete

SQL> variable task_id number;

SQL> begin

2 declare

3 object_id number;

4 name varchar2(100);

5 task_desc varchar2(100);

6 begin

7 name := 'test';

8 task_desc := 'Segment Advisor TEST';

9 dbms_advisor.create_task(advisor_name => 'Segment Advisor',

10 task_id => :task_id,

11 task_name => name,

12 task_desc => task_desc

13 );

14 dbms_advisor.create_object(task_name => name,

15 object_type => 'TABLE',

16 attr1 => 'ROBINSON',

17 attr2 => 'TEST',

18 attr3 => NULL,

19 attr4 => NULL,

20 attr5 => NULL,

21 object_id =>object_id

22 );

23 dbms_advisor.set_task_parameter(task_name => name,

24 parameter => 'recommend_all',

25 value => 'TRUE'

26 );

27 dbms_advisor.execute_task(task_name => name);

28 end;

29 end;

30 /

PL/SQL procedure successfully completed

task_id

---------

560

SQL> col task_name format a8

SQL> col segname format a8

SQL> col partition format a8

SQL> col type format a8

SQL> col message format a100

SQL> select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message

2 from dba_advisor_findings af, dba_advisor_objects ao

3 where ao.task_id = af.task_id

4 and ao.object_id = af.object_id

5 and ao.owner = 'ROBINSON';

TASK_NAM SEGNAME PARTITIO TYPE MESSAGE

-------- -------- -------- -------- ----------------------------------------------------------------------------------------------------

test TEST TABLE Enable row movement of the table ROBINSON.TEST and perform shrink, estimated savings is 28451785 byt

es.

SQL> select 28451785/1024/1024 from dual;

28451785/1024/1024

------------------

27.1337366104126

从Segment advisor里面得到如果对表进行shrink 可以节约27M的空间,根据例子的操作步骤也可以得出这个结论,因为我删除了一般的数据

,在未删除之前表test占用了60M。由此可见Segment advisor的结果还是比较准确的。

查看task信息:

select * from dba_advisor_tasks where advisor_name!='ADDM';

删除task:

SQL> exec dbms_advisor.delete_task(task_name => 'test');

PL/SQL procedure successfully completed

<think>好的,用户问的是Oracle中如何收缩含有大字段(LOB)的,也就是使用SHRINK命令处理有大字段的。首先,我得回忆一下Oracle的SHRINK SPACE操作。记得这个命令是用来回收碎片和释放未使用空间的,但LOB字段的处理可能和普通不同。 首先,要确认用户是否了解SHRINK的基本使用,比如普通的收缩步骤。可能需要先介绍常规操作,再讲LOB的特殊情况。因为LOB数据通常存储在单独的段中,可能使用ASSM(自动段空间管理),所以收缩时可能需要分开处理。 接下来,考虑用户可能的场景:他们的有大量LOB数据,导致空间占用过大,想要通过收缩来释放空间。但LOB字段可能无法像普通数据那样轻易移动,需要特定的方法,比如开启ROW MOVEMENT,或者单独处理LOB段。 然后,需要检查Oracle的版本是否支持LOB段的收缩。可能从10g或11g开始支持?需要确认具体的版本要求。另外,ALTER TABLE SHRINK SPACE CASCADE是否会自动处理LOB段,或者是否需要单独对LOB列进行操作。 还可能需要注意,收缩操作需要支持行移动,并且可能引起索引的重建,这会导致锁,影响业务。所以需要提醒用户在维护窗口操作,并做好备份。 另外,要提到DBA_LOBS视图,用来查询LOB字段所在的空间和段信息,帮助用户确认LOB的存储位置。可能还需要使用DBMS_REDEFINITION在线重定义,如果直接收缩不可行的话。 用户可能遇到的问题包括:收缩后空间未释放,可能是因为LOB数据未被有效处理,或者HWM(高水位线)未调整。需要解释HWM的概念,以及如何通过MOVE或SHRINK来降低HWM。 最后,整理步骤:先开启行移动,收缩级,再处理LOB段,可能需要CASCADE参数,或者单独执行ALTER TABLE MODIFY LOB (...) (SHRINK SPACE)。还要提醒空间是否返回给空间,取决于存储参数和是否启用了自动扩展。 需要确保回答结构清晰,分步骤说明,注意事项和可能的问题,帮助用户全面理解操作过程和潜在风险。</think>在Oracle数据库中,对大字段(LOB)进行空间收缩需要特殊处理。以下是分步骤说明: ### 1. 收缩前的准备工作 - **开启行移动**(Row Movement): ```sql ALTER TABLE 名 ENABLE ROW MOVEMENT; ``` 这是SHRINK操作的前提条件,允许Oracle物理重组行位置。 - **确认LOB存储类型**: ```sql SELECT table_name, column_name, tablespace_name, segment_name FROM dba_lobs WHERE table_name = '名'; ``` 确认LOB字段是否存储在独立段(BASICFILE/SECUREFILE)中。 ### 2. 普通收缩(非LOB字段) ```sql ALTER TABLE SHRINK SPACE COMPACT; -- 仅碎片整理 ALTER TABLE SHRINK SPACE; -- 释放空间并重置HWM ``` ### 3. LOB字段的特殊处理 - **方法1:级联收缩**(CASCADE) ```sql ALTER TABLE SHRINK SPACE CASCADE; ``` 该命令会级联收缩: - 段(TABLE) - 索引段(INDEX) - LOB段(LOBSEGMENT) - LOB索引段(LOBINDEX) - **方法2:单独收缩LOB列** ```sql ALTER TABLE 名 MODIFY LOB(LOB字段名) (SHRINK SPACE); ``` ### 4. 验证收缩效果 ```sql SELECT segment_name, bytes/1024/1024 MB FROM dba_segments WHERE segment_name IN ('名', 'LOB段名'); ``` ### 注意事项 1. **版本兼容性**: - 需要Oracle 11g及以上版本才支持LOB字段的SHRINK操作 - SECUREFILE类型LOB支持更高效的压缩 2. **锁机制**: - 操作期间会获取级排他锁(TM) - 建议在维护窗口操作 3. **空间回收限制**: - 无法回收已分配给空间的未使用空间(需配合数据文件resize操作) - LOB字段的碎片化空间可能无法完全回收 4. **性能影响**: - 会产生大量重做日志(建议开启NOLOGGING模式) - 可能触发全扫描和行迁移 ### 替代方案 对于超大型LOB,推荐使用: ```sql ALTER TABLE 名 MOVE TABLESPACE 新空间 LOB(字段名) STORE AS (TABLESPACE LOB空间); ``` 通过物理迁移重组存储结构,更彻底地回收空间。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值