03-15 03:38 阅读 206

Oracle 高水位查询和处理方法汇总

查询高水位方法：

1

2

3

4

一查询dba_tables(user_tables)

二 dump SEGMENT HEADER block

三 Segment Advisor

四 show_space

高水位处理方法：

1

2

3

4

5

一 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)

1 2	`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 收集表统计信息

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

1

2

3

4

5

6

7

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

1 2	`SQL> alter system dump datafile 17 block 130;` `System altered.`

1

2

3

4

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.

1 2	`##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

1 2	`如果表里数据可以全部清空，可以通过truncate降低高水位` `truncate table t1;`

二 MOVE

1

2

3

4

5

6

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 table <table_name> shrink space [ <null> | 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为原表。

1

2

3

4

例如：

create table t2 as select * from t1;

drop table t1 purge;

rename t2 to t1;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2760416/，如需转载，请注明出处，否则将追究法律责任。