查询高水位方法：
一 查询dba_tables(user_tables) 二 dump SEGMENT HEADER block 三 Segment Advisor 四 show_space
高水位处理方法：
一 truncate 二 SHRINK SPACE CASCADE 三 MOVE 四 exp/imp或expdp/impdp 五 复制要保留的数据到临时表T，DROP原表，然后RENAME临时表T为原表。
创建测试数据
SQL> conn / as sysdba Connected. SQL> create tablespace cjctbs datafile '/u01/app/oracle/oradata/chendb/cjctbs01.dbf' size 10M autoextend on; create user c##cjc identified by a default tablespace cjctbs; grant connect,resource,dba to c##cjc; conn c##cjc/a create table t1 as select * from dba_objects; insert into t1 select * from t1; / / / SQL> select count(*) from t1; COUNT(*) ---------- 881698 SQL> delete t1 where rownum<881688; Commit complete
查询高水位，方法一：查询dba_tables(user_tables)
How to find Objects Fragmented below High water mark (Doc ID 337651.1) Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]
1 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'C##CJC',tabname => 't1',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE,degree=>2);
2 查询高水位
set line 300 col table_name for a20 SELECT table_name, ROUND ( (blocks * 8), 2) "High_Water(K)", ROUND ( (num_rows * avg_row_len / 1024), 2) "USED_Space(K)", ROUND ( (blocks * 10 / 100) * 8, 2) "Reserve_Space(K)", ROUND ( ( blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100), 2) "RECOVERY_Space(K)" FROM user_tables WHERE table_name='T1' ORDER BY 5 DESC; TABLE_NAME High_Water(K) USED_Space(K) Reserve_Space(K) RECOVERY_Space(K) -------------------- ------------- ------------- ---------------- ----------------- T1 137896 1.33 13789.6 124105.07 SQL> select segment_name,bytes/1024 from user_segments where segment_name='T1'; SEGMENT_NA BYTES/1024 ---------- ---------- T1 139264
或通过如下脚本查询：
[oracle@cjcos02 ~]$ cat high_water.sql
REM This is an example SQL*Plus Script to find tables fragmentated below high water mark
REM set heading off verify off echo off
set line 300
col table_name for a20
REM The below queries gives information about the size of the table with respect to the High water Mark
REM note that BLOCKS*8192 is BLOCKS times the block size: 8192. Substitue your DB blocksize.
PROMPT Please enter the schema name
SELECT TABLE_NAME,BLOCKS*8192/1024/1024 MB FROM DBA_TABLES WHERE owner='&OWNER';
REM The below queries gives the actual size in MB used by the table in terms of data .
REM You can use the difference of the two sql statements specified above to get the table which
REM has fragementation below high water mark prompt Enter name(s) of schema for which you want to find fragemented object.
PROMPT Please enter the schema name
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;
SQL> @high_water.sql
Please enter the schema name
Enter value for owner: c##cjc
old 1: SELECT TABLE_NAME,BLOCKS*8192/1024/1024 MB FROM DBA_TABLES WHERE owner='&OWNER'
new 1: SELECT TABLE_NAME,BLOCKS*8192/1024/1024 MB FROM DBA_TABLES WHERE owner='c##cjc'
no rows selected
Please enter the schema name
Enter value for owner: c##cjc
old 2: "Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc
new 2: "Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('c##cjc') order by 2 desc
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 134.662766
查询高水位，方法二：dump SEGMENT HEADER block
set linesize 200 pagesize 200 col owner for a10 col segment_name for a10 select owner,segment_name,header_file,header_block,SEGMENT_TYPE from dba_segments where segment_name='T1'; OWNER SEGMENT_NA HEADER_FILE HEADER_BLOCK SEGMENT_TYPE ---------- ---------- ----------- ------------ ------------------ C##CJC T1 17 130 TABLE
SQL> alter system dump datafile 17 block 130; System altered.
SQL>select value from v$diag_info where name='Default Trace File'; VALUE --------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/chendb/chendb/trace/chendb_ora_26119.trc
[oracle@cjcos02 ~]$ vim /u01/app/oracle/diag/rdbms/chendb/chendb/trace/chendb_ora_26119.trc Trace file /u01/app/oracle/diag/rdbms/chendb/chendb/trace/chendb_ora_26119.trc Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417 ORACLE_HOME: /u01/app/oracle/product/19.0.0/dbhome_1 System name: Linux Node name: cjcos02 Release: 4.1.12-112.16.4.el7uek.x86_64 Version: #2 SMP Mon Mar 12 23:57:12 PDT 2018 Machine: x86_64 Instance name: chendb Redo thread mounted by this instance: 1 Oracle process number: 58 Unix process pid: 26119, image: oracle@cjcos02 (TNS V1-V3) ...... Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 88 #blocks: 17408 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x04404480 ext#: 87 blk#: 1024 ext size: 1024 #blocks in seg. hdr's freelists: 0 #blocks below: 17237 mapblk 0x00000000 offset: 87 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x04404480 ext#: 87 blk#: 1024 ext size: 1024 #blocks in seg. hdr's freelists: 0 #blocks below: 17237 mapblk 0x00000000 offset: 87 Level 1 BMB for High HWM block: 0x04404083 Level 1 BMB for Low HWM block: 0x04404083 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x04400081 Last Level 1 BMB: 0x04404083 Last Level II BMB: 0x04400081 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 88 obj#: 76295 flag: 0x10000000 Inc # 1
高水位=17237*8192/1024=137896KB
查询高水位，方法三：Segment Advisor
SQL> declare my_task_id number; obj_id number; my_task_name varchar2(100); my_task_desc varchar2(500); begin my_task_name :='advisor_test tab Advice'; my_task_desc :='Manual Segment Advisor Run'; -----step 1 /* 创建一个段顾问任务 */ dbms_advisor.create_task( advisor_name => 'Segment Advisor', task_id => my_task_id, task_name => my_task_name, task_desc =>my_task_desc); -----step 2 /* 为这个任务分配一个对象 */ dbms_advisor.create_object( task_name=>my_task_name, object_type=>'TABLE', --指定对象级别，假设为表对象则为'TABLE'，假设为表空间级别则为'TABLESPACE' attr1=>'C##CJC', ---假设在表对象级别执行，这个属性为username，表空间级别这个属性为表空间名字 attr2 => 'T1', ---假设在表对象级别执行，这个属性为表名，表空间级别这个属性为null attr3 => NULL, attr4=>null, attr5=>null, object_id=>obj_id); -----step 3 /* 设置任务參数 */ dbms_advisor.set_task_parameter( task_name => my_task_name, /* 设置段顾问执行參数"ecommend_all"的值,为TRUE则为全部类型的对象的生成建议，为FALSE则仅生成与空间相关的建议 */ /* 还有一个滚问执行參数"time_limit",制定顾问执行的时间限制，默认值为无限制 */ parameter=>'recommend_all', value=>'TRUE'); -----step 4 /* 运行这个任务 */ dbms_advisor.execute_task(my_task_name); end; / PL/SQL procedure successfully completed.
##3 删除 ###SQL> exec dbms_advisor.delete_task(task_name => 'advisor_test tab Advice');
查询建议
select /* "|chr(13)||chr(10)"为windows平台的换行符，假设是linux等其他平台，请用"chr(10)"取代 */ 'Task name :'||f.task_name||chr(13)||chr(10)|| 'Segment name :'||o.attr2 ||chr(13)||chr(10)|| 'Sement type :'||o.type ||chr(13)||chr(10)|| 'partition name:'||o.attr3 ||chr(13)||chr(10)|| 'Message :'||f.message ||chr(13)||chr(10)|| 'More info :'||f.more_info TASK_ADVICE from dba_advisor_findings f,dba_advisor_objects o where o.task_id=f.task_id and o.object_id=f.object_id and f.task_name = 'advisor_test tab Advice' order by f.task_name;
TASK_ADVICE --------------------------------------------------------------------------------------------------- Task name :advisor_test tab Advice Segment name :T1 Sement type :TABLE partition name: Message :Enable row movement of the table C##CJC.T1 and perform shrink, estimated savings is 142605304 bytes. More info :Allocated Space:142606336: Used Space:1032: Reclaimable Space :142605304:
查询高水位，方法四：show_space
创建show_space存储过程
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;
-- inline procedure to print out numbers nicely formatted
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is a ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;
-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, else we use the FREE_BLOCKS
-- API for the user managed segments
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p( 'Free Blocks', l_free_blks );
end if;
-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
SQL> exec show_space('T1');
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec show_space('T1');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 209
Full Blocks ..................... 17,028
Total Blocks............................ 17,408
Total Bytes............................. 142,606,336
Total MBytes............................ 136
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 17
Last Used Ext BlockId................... 16,512
Last Used Block......................... 1,024
PL/SQL procedure successfully completed.
高水位处理方法：
一 truncate
如果表里数据可以全部清空，可以通过truncate降低高水位 truncate table t1;
二 MOVE
ALTER TABLE TABLE_NAME MOVE; 1 会锁表 2 move是以block为单位重组数据，行的rowid都会跟着变化，索引会失效，需要重建索引。 3 需要准备两倍的空间。 3 MOVE之后,HWM降低了，空闲块也上去了。 但是分配的空间并没有改变，仍然是1280个BLOCKS。
三 SHRINK SPACE CASCADE
ALTER TABLE TABLE_NAME SHRINK SPACE; 在执行该指令之前必须开启行移动。 1 压缩segment,调整hwm,并马上释放空间 2 shrink是以行为单位重组数据，根据复杂算法从逻辑+物理重组数据 3 shrink的算法是从segment的底部开始，移动row到segment的顶部，移动的过程相当于delete/insert操作的组合，在这个过程中会产生大量的undo和redo信息。 4 耗时可能非常长，通常慢于move。 5 对于空间的要求，shrink不需要额外的空间。 ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT; alter tableshrink space [ | compact | cascade ]; alter table shrink space compact cascade; ALTER TABLE TABLE_NAME DISABLE ROW MOVEMENT; cascade: 缩小表及其索引，并移动高水位线，释放空间，这个参数是在shrink table的时候自动级联索引，相当于rebulid index。 compact: 仅仅是缩小表和索引，并不移动高水位线，不释放空间 加上compact选项仅重新整理segment 空间，并压缩表的记录在以后进行release空间。 但数据库并不调整hwm及释放空间. 为了释放空间.你必须再发布alter table shrink space --compact用于把一个长操作分割为两个较短的操作 如果在业务繁忙时做压缩，可以使用alter table shrink space compact来对表格进行碎片整理，而不调整高水位线，之后再次调用alter table table_name shrink space来释放空间。 也可以使用alter table table_name shrink space cascade来同时对索引都进行收缩，这等同于同时执行alter index idxname shrink space。
四 exp/imp或expdp/impdp
五 复制要保留的数据到临时表T，DROP原表，然后RENAME临时表T为原表。
例如： create table t2 as select * from t1; drop table t1 purge; rename t2 to t1;
###2021-03-01 22:10 chenjuchao###