检查oracle表和索引可回收空间!!

不错的文章~~工作中很常用。
记录学习。
交流群127591054
原帖地址:http://www.3lian.com/edu/2013/11-29/111321.html

对表进行大量删除后,可能会有很多空闲空间可以回收,相关计算方法参考如下:


  更新统计信息

  Analyze table compute statistics ;

  计算碎片空间

  SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)

  "Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;

  ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

  SELECT table_name,num_rows,blocks,empty_blocks

  FROM user_tables

  WHERE table_name='BIG_EMP1';

  SELECT COUNT (DISTINCT

  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||

  DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"

  FROM big_emp1;

  SELECT segment_name,segment_type,blocks

  FROM dba_segments

  WHERE segment_name='BIG_EMP1';

  对于索引

  校验结构

  analyze index validate structure;

  检查

  column name format a15

  column blocks heading "ALLOCATED|BLOCKS"

  column lf_blks heading "LEAF|BLOCKS"

  column br_blks heading "BRANCH|BLOCKS"

  column Empty heading "UNUSED|BLOCKS"

  select name,

  blocks,

  lf_blks,

  br_blks,

  blocks-(lf_blks+br_blks) empty

  from index_stats;

  或者

  select name, btree_space, used_space, pct_used from index_stats;

  回收空间方法

  'Compatible' 必须 >=10.0

  1. Enable row movement for the table.

  SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

  2. Shrink table but don't want to shrink HWM (High Water Mark).

  SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

  3. Shrink table and HWM too.

  SQL> ALTER TABLE scott.emp SHRINK SPACE;

  4. Shrink table and all dependent index too.

  SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

  5. Shrink table under MView.

  SQL> ALTER TABLE

  SHRINK SPACE;

  6. Shrink Index only.

  SQL> ALTER INDEX SHRINK SPACE;

  验证

  SQL> set serveroutput on

  SQL> declare

  2 v_unformatted_blocks number;

  3 v_unformatted_bytes number;

  4 v_fs1_blocks number;

  5 v_fs1_bytes number;

  6 v_fs2_blocks number;

  7 v_fs2_bytes number;

  8 v_fs3_blocks number;

  9 v_fs3_bytes number;

  10 v_fs4_blocks number;

  11 v_fs4_bytes number;

  12 v_full_blocks number;

  13 v_full_bytes number;

  14 begin

  15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,

  16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

  17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

  18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

  19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

  20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

  21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

  22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

  23 dbms_output.put_line('Full Blocks = '||v_full_blocks);

  24 end;

  25 /

  Unformatted Blocks = 0

  FS1 Blocks = 0

  FS2 Blocks = 0

  FS3 Blocks = 0

  FS4 Blocks = 2

  Full Blocks = 1
<think>好的,我现在需要帮助用户了解如何清理Oracle数据库中USERS空间的日志文件。首先,用户的问题可能存在一点误解,因为USERS空间通常存储用户数据,而日志文件一般属于重做日志或归档日志,由数据库自动管理。不过,可能用户指的是清理USERS空间中的旧数据或归档日志,需要先明确这一点。 首先,我应该确认USERS空间的用途。根据引用[3],USERS是Oracle自带的空间之一,用于存放用户数据,如用户创建的索引。而日志文件属于重做日志或归档日志,存放在指定的日志目录,由系统自动管理,与空间无关。可能用户误将数据文件与日志文件混淆了。 接下来,用户可能想清理的是USERS空间中的历史数据,比如旧或归档数据。引用[5]提到清理USERS空间时,需要确定哪些可以清理,比如按时间范围删除数据。此时,正确的步骤应该是:先识别占用空间的对象,再制定清理策略,如删除旧数据、归档或迁移,最后释放空间。 同时,需要考虑空间空间管理。如果用户确实需要清理数据文件,根据引用[1],不建议直接向SYSTEM或SYSAUX空间添加数据文件,但USERS空间可能允许添加或调整。不过,清理通常指删除数据而非文件。此外,引用[4]提到大文件空间的管理方式,但USERS默认可能不是大文件空间。 另外,用户可能需要了解如何回收空间。删除数据后,高水位线不会自动下降,需要使用ALTER TABLE SHRINK SPACE或重建来释放空间。也可以使用数据泵导出导入重组空间。 最后,要提醒用户备份检查,避免误删重要数据。同时,归档日志的清理应通过RMAN或配置保留策略,而不是直接操作USERS空间。 总结步骤:确认清理目标(数据而非日志)、识别大对象、删除或归档数据、释放空间、处理归档日志(如果需要)。需要引用相关的引用资料,特别是关于空间管理清理的部分。</think>### 清理Oracle数据库USERS空间的方法 #### 1. **明确清理目标** - **USERS空间的作用**:USERS空间Oracle默认的用户数据空间,用于存储用户创建的索引等数据对象[^3]。 - **日志文件的归属**:Oracle的日志文件(重做日志、归档日志)由独立的日志管理系统维护,与空间无直接关联[^4]。若需清理日志文件,需通过RMAN或归档日志管理命令操作。 #### 2. **清理USERS空间中的数据** 若需释放USERS空间空间,需按以下步骤操作: - **步骤1:识别占用空间的对象** ```sql SELECT owner, segment_name, segment_type, bytes/1024/1024 AS "SIZE_MB" FROM dba_segments WHERE tablespace_name = 'USERS' ORDER BY bytes DESC; ``` 通过此查询找到占用空间较大的索引[^2]。 - **步骤2:清理历史数据** - 若为时间范围数据(如日志),可通过删除旧数据: ```sql DELETE FROM <table_name> WHERE <date_column> < TO_DATE('2023-01-01', 'YYYY-MM-DD'); COMMIT; ``` - 若需彻底释放空间,执行收缩: ```sql ALTER TABLE <table_name> ENABLE ROW MOVEMENT; ALTER TABLE <table_name> SHRINK SPACE; ``` - **步骤3:重建或迁移对象** 对大索引进行重建以释放碎片: ```sql ALTER INDEX <index_name> REBUILD TABLESPACE USERS; ``` #### 3. **处理归档日志(若需清理日志文件)** - 使用RMAN清理过期归档日志: ```sql RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; -- 删除7天前的归档日志 ``` - 配置归档日志保留策略: ```sql RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; ``` #### 4. **注意事项** - **备份**:清理前务必备份关键数据。 - **高水位线**:删除数据后需手动释放空间(如`ALTER TABLE ... SHRINK SPACE`)。 - **监控**:定期检查空间使用率: ```sql SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics; ``` --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值