签到成功

知道了

CNDBA社区CNDBA社区

Oracle Show_space 过程 使用示例 与 注释

2016-11-25 15:38 3043 0 原创 Oracle 11g
作者： dave
本文链接：http://www.cndba.cn/dave/article/410


 

一．Show_space 过程源码

 

1.1 源码

Tom 大师的show_space过程，经pub某位同学完善之后，脚本如下：

 

CREATE OR REPLACE PROCEDURE show_space (

   p_segname_1     IN VARCHAR2,

   p_type_1        IN VARCHAR2 DEFAULT 'TABLE',

p_space         IN VARCHAR2 DEFAULT'MANUAL',

   p_analyzed      IN VARCHAR2 DEFAULT 'N',

p_partition_1   IN VARCHAR2 DEFAULTNULL,

   p_owner_1       IN VARCHAR2 DEFAULT USER)

   AUTHID CURRENT_USER

AS

   p_segname              VARCHAR2 (100);

   p_type                 VARCHAR2 (30);

   p_owner                VARCHAR2 (30);

   p_partition            VARCHAR2 (50);

 

   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;

 http://www.cndba.cn/dave/article/410

   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;

 

   PROCEDURE p (p_label IN VARCHAR2,p_num IN NUMBER)

   IS

   BEGIN

      DBMS_OUTPUT.put_line (RPAD(p_label, 40, '.') || p_num);

   END;

BEGIN

   p_segname := UPPER (p_segname_1);

   p_owner := UPPER (p_owner_1);

   p_type := p_type_1;

   p_partition := UPPER(p_partition_1);

 

   IF (p_type_1 = 'i' OR p_type_1 ='I')

   THEN

      p_type := 'INDEX';

   END IF;

 

   IF (p_type_1 = 't' OR p_type_1 ='T')

   THEN

      p_type := 'TABLE';

   END IF;

 

   IF (p_type_1 = 'tp' OR p_type_1 ='TP')

   THEN

      p_type := 'TABLE PARTITION';

   END IF;

 

   IF (p_type_1 = 'ip' OR p_type_1 = 'IP')

   THEN

      p_type := 'INDEX PARTITION';

   END IF;

 

   IF (p_type_1 = 'c' OR p_type_1 ='C')

   THENhttp://www.cndba.cn/dave/article/410

      p_type := 'CLUSTER';

   END IF;

 

   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,

http://www.cndba.cn/dave/article/410

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

 

   IF p_space = 'MANUAL' OR (p_space<> 'auto' AND p_space <> 'AUTO')

   THEN

      DBMS_SPACE.FREE_BLOCKS (segment_owner       => p_owner,

                             segment_name        =>p_segname,

                              segment_type        => p_type,

                             partition_name      =>p_partition,

http://www.cndba.cn/dave/article/410

                             freelist_group_id   => 0,

                             free_blks           =>l_free_blks);

 

      p ('Free Blocks', l_free_blks);

   END IF;

 

   p ('Total Blocks',l_total_blocks);

   p ('Total Bytes', l_total_bytes);

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

 

   /*IF the segment is analyzed */

   IF p_analyzed = 'Y'

   THEN

      DBMS_SPACE.SPACE_USAGE(segment_owner        => p_owner,

                             segment_name         => p_segname,

                             segment_type         => p_type,

                             partition_name       =>p_partition,

                              unformatted_blocks   => l_unformatted_blocks,

                             unformatted_bytes    =>l_unformatted_bytes,

                             fs1_blocks           =>l_fs1_blocks,

                             fs1_bytes            =>l_fs1_bytes,

                             fs2_blocks           =>l_fs2_blocks,

                              fs2_bytes            => l_fs2_bytes,

                             fs3_blocks           =>l_fs3_blocks,

                             fs3_bytes            =>l_fs3_bytes,

                             fs4_blocks           =>l_fs4_blocks,

                              fs4_bytes            => l_fs4_bytes,

                             full_blocks          =>l_full_blocks,

                             full_bytes           =>l_full_bytes);

      DBMS_OUTPUT.put_line (RPAD ('', 50, '*'));

      DBMS_OUTPUT.put_line ('Thesegment is analyzed');

      p ('0% -- 25% free spaceblocks', l_fs1_blocks);

      p ('0% -- 25% free spacebytes', l_fs1_bytes);

      p ('25% -- 50% free spaceblocks', l_fs2_blocks);

      p ('25% -- 50% free spacebytes', l_fs2_bytes);

      p ('50% -- 75% free spaceblocks', l_fs3_blocks);

      p ('50% -- 75% free spacebytes', l_fs3_bytes);

      p ('75% -- 100% free spaceblocks', l_fs4_blocks);

      p ('75% -- 100% free spacebytes', l_fs4_bytes);

      p ('Unused Blocks', l_unformatted_blocks);

      p ('Unused Bytes',l_unformatted_bytes);

      p ('Total Blocks',l_full_blocks);

      p ('Total bytes',l_full_bytes);

   END IF;

END;

 

1.2 使用示例

 

1.2.1 MSSM 管理表空间下示例

 

SQL> create tablespace mssm

 2  datafile'/u01/app/oracle/oradata/dave/mssm01.dbf' 

 3  size 100m 

 4  extent management local

 5  segment space managementmanual; 

 

Tablespace created.

 

SQL> create table t_mssm tablespace mssmas select * from dba_objects;

Table created.

 

SQL> create index idx_mssm ont_mssm(object_id) tablespace mssm;

Index created.

 

SQL> set serveroutputon

SQL> execshow_space('T_MSSM','T','MANUAL');

FreeBlocks.............................0   --由DBMS_SPACE.FREE_BLOCKS输出

TotalBlocks............................1152  --以下由DBMS_SPACE.UNUSED_SPACE输出

TotalBytes.............................9437184

Unused Blocks...........................81

UnusedBytes............................663552

Last Used Ext FileId....................6

Last Used ExtBlockId...................1152

Last Used Block.........................47

 

PL/SQL procedure successfully completed.

 

SQL> execshow_space('IDX_MSSM','I','MANUAL');

Free Blocks.............................0

Total Blocks............................256

TotalBytes.............................2097152

Unused Blocks...........................87

UnusedBytes............................712704

Last Used Ext FileId....................6

Last Used Ext BlockId...................1408

Last Used Block.........................41

 

PL/SQL procedure successfully completed.

 

SQL> execshow_space('T_MSSM','T');

Free Blocks.............................0

TotalBlocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................81

UnusedBytes............................663552

Last Used Ext FileId....................6

Last Used ExtBlockId...................1152

Last Used Block.........................47

 

PL/SQL procedure successfully completed.

 

 

 

1.2.2 ASSM  管理表空间下示例

 

SQL> create tablespace assm

 2  datafile'/u01/app/oracle/oradata/dave/assm01.dbf' 

 3  size 100m 

 4  extent management local

 5  segment space managementauto; 

 

Tablespace created.

 

SQL> create table t_assm tablespace assmas select * from dba_objects;

 

Table created.

 http://www.cndba.cn/dave/article/410

SQL> create index idx_assm ont_assm(object_id) tablespace assm;

 

Index created.

 

SQL> execshow_space('T_ASSM','AUTO','T');

TotalBlocks............................1152

Total Bytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

 

PL/SQL procedure successfully completed.

 

SQL> exec show_space('IDX_ASSM','AUTO','I');

Total Blocks............................256

TotalBytes.............................2097152

Unused Blocks...........................76

UnusedBytes............................622592

Last Used Ext FileId....................7

Last Used ExtBlockId...................1408

Last Used Block.........................52

 

PL/SQL procedure successfully completed.

 

注意：脚本里并显示我们DBMS_SPACE.SPACE_USAGE的内容。因为我们之前有一个判断。

 

SQL> execshow_space('T_ASSM','AUTO','T',NULL,'Y');

TotalBlocks............................1152

TotalBytes.............................9437184

UnusedBlocks...........................54

Unused Bytes............................442368

Last Used ExtFileId....................7

Last Used ExtBlockId...................1152

Last UsedBlock.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free spacebytes..............0

25% -- 50% free spaceblocks............0

25% -- 50% free spacebytes.............0

50% -- 75% free spaceblocks............0

50% -- 75% free spacebytes.............0

75% -- 100% free space blocks...........0

75% -- 100% free spacebytes............0

UnusedBlocks...........................0

UnusedBytes............................0

TotalBlocks............................1072

Totalbytes.............................8781824

 

PL/SQL procedure successfullycompleted.

 

--删除部分数据后，在测试：

SQL> delete fromt_assm where rownum<100;

 

99 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> execshow_space('T_ASSM','T','AUTO','Y',NULL);

TotalBlocks............................1152

TotalBytes.............................9437184http://www.cndba.cn/dave/article/410

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........1

75% -- 100% free spacebytes............8192

Unused Blocks...........................0

Unused Bytes............................0

TotalBlocks............................1071

Total bytes.............................8773632

 

PL/SQL procedure successfully completed.

 

--对表t_assm 收集统计信息：

SQL> execdbms_stats.gather_table_stats(ownname =>'&owner',tabname=>'&tablename',estimate_percent => &est_per ,method_opt =>'forall columns size 1',degree=>&degree,cascade => true);

Enter value for owner: sys

Enter value for tablename: t_assm

Enter value for est_per: 10

Enter value for degree: 8

 

PL/SQL procedure successfully completed.

 

SQL> exec show_space('T_ASSM','AUTO','T',NULL,'Y');

TotalBlocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........1

75% -- 100% free spacebytes............8192

Unused Blocks...........................0

Unused Bytes............................0

TotalBlocks............................1071

Total bytes.............................8773632

 

PL/SQL procedure successfully completed.

--两次收集信息一致。

 

 

再次使用analyze 分析：

 

SQL> delete from t_assm;

 

75155 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> execshow_space('T_ASSM','T','AUTO','Y',NULL);

Total Blocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free spaceblocks...........1072

75% -- 100% free spacebytes............8781824

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................0

Total bytes.............................0

 

PL/SQL procedure successfully completed.

 

SQL> analyzetable t_assm compute statistics;

 

Table analyzed.

 

SQL> exec show_space('T_ASSM','T','AUTO','Y',NULL);

TotalBlocks............................1152

TotalBytes.............................9437184

Unused Blocks...........................54

UnusedBytes............................442368

Last Used Ext FileId....................7

Last Used ExtBlockId...................1152

Last Used Block.........................74

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free spaceblocks...........1072

75% -- 100% free spacebytes............8781824

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................0

Total bytes.............................0

 

PL/SQL procedure successfully completed.

 

我这里测试，两者值相同，但实际上，Analyze 会收集如下信息：EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT，而gather_table_stats则不会收集。

 

 

--测试分区表,查看某个分区的信息：

SQL> create table pt_assm

 2  (

 3  object_id number,

 4  created date

 5  )  tablespace assm

 6  partition by hash(object_id)

 7  (

 8  partition part_01,

 9  partition part_02,

 10 partition part_03

 11  );

Table created.

 

SQL> insert into pt_assm select object_id,createdfrom dba_objects;

75259 rows created.

 

SQL> commit;

Commit complete.

 

SQL> Create index idx_pt_assm onpt_assm(object_id) tablespace assm local;

Index created.

 

SQL> execshow_space('PT_ASSM','TP','AUTO','Y','PART_01');

TotalBlocks............................1024

TotalBytes.............................8388608

Unused Blocks...........................896

UnusedBytes............................7340032

Last Used Ext FileId....................7

Last Used Ext BlockId...................1536

Last Used Block.........................128

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............1

50% -- 75% free spacebytes.............8192

75% -- 100% free space blocks...........15

75% -- 100% free spacebytes............122880

Unused Blocks...........................48

UnusedBytes............................393216

Total Blocks............................46

Totalbytes.............................376832

 

PL/SQL procedure successfully completed.

 

 

SQL> execshow_space('IDX_PT_ASSM','IP','AUTO','Y','PART_01');

Total Blocks............................48

TotalBytes.............................393216

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................7

Last Used ExtBlockId...................4648

Last Used Block.........................8

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............1

25% -- 50% free spacebytes.............8192

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........0

75% -- 100% free space bytes............0

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................42

Totalbytes.............................344064

 

PL/SQL procedure successfully completed.

 

 

 

二． 相关知识点说明

 

2.1 表空间管理模式说明

    在看show_space过程里函数之前，需要先了解Oracle 表空间的管理模式。

 

    LogicalSpace Management分：Locally  managed tablespaces (default) 和 Dictionary-managed tablespaces。 其中Locallymanaged tablespaces 又分ASSM和MSSM。

   

自动段空间管理（ASSM），在ASSM中链接列表freelist(MSSM)被位图所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB 段）。 

 

从使用区段空间管理自动参数创建tablespace开始： 

create tablespace DAVE 

datafile '/u01/data/dave01.dbf ' 

size 5m 

EXTENT MANAGEMENT LOCAL -- Turn on LMT  

SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM; 

 

    一旦你定义好了tablespace，那么表和索引就能够使用各种方法很容易地被移动到新的tablespace里，带有ASSM的本地管理tablespace会略掉任何为PCTUSED、NEXT和FREELISTS所指定的值。 

 

    当表格或者索引被分配到这个tablespace以后，用于独立对象的PCTUSED的值会被忽略，而Oracle9i会使用位图数组来自动地管理tablespace里表和索引的freelist。

 

    对于在LMT的tablespace内部创建的表格和索引而言，这个NEXT扩展子句是过时的，因为由本地管理的tablespace会管理它们。但是，INITIAL参数仍然是需要的，因为Oracle不可能提前知道初始表格加载的大小。对于ASSM而言，INITIAL最小的值是三个块。 

 

新的管理机制(ASSM)用位图来跟踪或管理每个分配到对象的块，每个块有多少剩余空间根据位图的状态来确定，如>75%,50%-75%,25%-50%和<25%，也就是说位图其实采用了四个状态位来代替以前的pctused，什么时候该利用该数据块则由设定的pctfree来确定。 

 http://www.cndba.cn/dave/article/410

    使用ASSM的一个巨大优势是，位图free list(MSSM)肯定能够减轻缓冲区忙等待（buffer busy wait）的负担，这个问题在Oracle9i以前的版本里曾是一个严重的问题。

 

 

关于Oracle 表空间的更多说明，参考：

Oracle 自动段空间管理(ASSM:auto segment space management)

http://blog.csdn.net/tianlesoftware/article/details/4958989

 

 

几个过程都是出自DBMS_SPACE包，Oracle 11gR2 官方文档中的说明，参考如下链接：

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_space.htm

 

 

 

2.2 DBMS_SPACE.UNUSED_SPACE 过程

Returns information about unused space in anobject (table, index, or cluster)

 

语法：

DBMS_SPACE.UNUSED_SPACE (

   segment_owner              IN  VARCHAR2,

  segment_name               IN  VARCHAR2,

  segment_type               IN  VARCHAR2,

  total_blocks               OUTNUMBER,

  total_bytes                OUTNUMBER,

  unused_blocks              OUTNUMBER,

  unused_bytes               OUTNUMBER,

  last_used_extent_file_id   OUTNUMBER,

  last_used_extent_block_id  OUTNUMBER,

  last_used_block            OUTNUMBER,

  partition_name             IN  VARCHAR2 DEFAULT NULL);

 

参数说明：

Parameter

Description

segment_owner

Schema name of the segment to be analyzed

segment_name

Segment name of the segment to be analyzed

segment_type

Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER):

TABLE

TABLE PARTITION

TABLE SUBPARTITION

INDEX

INDEX PARTITION

INDEX SUBPARTITION

CLUSTER

LOB

LOB PARTITION

LOB SUBPARTITION

total_blocks

Returns total number of blocks in the segment

total_bytes

Returns total number of blocks in the segment, in bytes

unused_blocks

Returns number of blocks which are not used

unused_bytes

Returns, in bytes, number of blocks which are not used

last_used_extent_file_id

Returns the file ID of the last extent which contains data

last_used_extent_block_id

Returns the starting block ID of the last extent which contains data

last_used_block

Returns the last block within this extent which contains data

partition_name

Partition name of the segment to be analyzed.

This is only used for partitioned tables; the name of subpartition should be used when partitioning is compose.

 http://www.cndba.cn/dave/article/410

 

 

2.3. DBMS_SPACE.FREE_BLOCKS 过程（只用MSSM）

This procedure returns information  about free blocks in an object (table, index,or cluster).  该过程只适用非ASSM 管理的对象（MSSM）。

 

用法：

DBMS_SPACE.FREE_BLOCKS (

  segment_owner     IN  VARCHAR2,

  segment_name      IN  VARCHAR2,

  segment_type      IN  VARCHAR2,

http://www.cndba.cn/dave/article/410

  freelist_group_id IN  NUMBER,

  free_blks         OUT NUMBER,

  scan_limit        IN  NUMBER DEFAULT NULL,

  partition_name    IN  VARCHAR2 DEFAULT NULL);

 

参数说明：

Parameter

Description

segment_owner

Schema name of the segment to be analyzed

segment_name

Segment name of the segment to be analyzed

segment_type

Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER):

TABLE

TABLE PARTITION

TABLE SUBPARTITION

INDEX

INDEX PARTITION

INDEX SUBPARTITION

CLUSTER

LOB

LOB PARTITION

LOB SUBPARTITION

freelist_group_id

Freelist group (instance) whose free list size is to be computed

free_blks

Returns count of free blocks for the specified group

scan_limit

Maximum number of free list blocks to read (optional).

Use a scan limit of X you are interested only in the question, "Do I have X blocks on the free list?"

partition_name

Partition name of the segment to be analyzed.

This is only used for partitioned tables. The name of subpartition should be used when partitioning is composite.

 

 

 

2.4. DBMS_SPACE.SPACE_USAGE过程（只用ASSM）

 

The first form of the procedure shows the space usage of datablocks under the segment High Water Mark. You cancalculate usagefor LOBs, LOB PARTITIONSand LOB SUBPARTITIONS. This procedure can only be used on tablespaces that arecreated with auto segment space management（该过程返回ASSM 管理的对象的free blocks信息（ASSM））. The bitmap blocks, segment header,and extent map blocks are not accounted for by this procedure. Notethat this overload cannot be used on SECUREFILE LOBs.

 

The second formof the procedure returns information about SECUREFILE LOB spaceusage. It will return the amount of space in blocks being used by all the SECUREFILE LOBsin the LOB segment. The procedure displays the space actively used bythe LOB column, freed space that has retention expired, and freed space thathas retention unexpired. Note that this overload can be used onlyon SECUREFILE LOBs.

 

语法：

DBMS_SPACE.SPACE_USAGE(

  segment_owner           IN  VARCHAR2,

  segment_name            IN  VARCHAR2,

  segment_type            IN  VARCHAR2,

  unformatted_blocks      OUTNUMBER,

  unformatted_bytes       OUTNUMBER,

  fs1_blocks              OUTNUMBER,

  fs1_bytes               OUTNUMBER,

  fs2_blocks              OUTNUMBER,

  fs2_bytes               OUTNUMBER,

  fs3_blocks              OUTNUMBER,

  fs3_bytes               OUTNUMBER,

  fs4_blocks              OUTNUMBER,

  fs4_bytes               OUTNUMBER,

  full_blocks             OUTNUMBER,

  full_bytes              OUTNUMBER,

  partition_name          IN  VARCHAR2 DEFAULT NULL);

 

或者：

DBMS_SPACE.SPACE_USAGE(

  segment_owner           IN    VARCHAR2,

  segment_name            IN    VARCHAR2,

  segment_type            IN    VARCHAR2,

  segment_size_blocks     OUT   NUMBER,

  segment_size_bytes      OUT   NUMBER,

  used_blocks             OUT  NUMBER,

  used_bytes              OUT   NUMBER,

  expired_blocks          OUT   NUMBER,

  expired_bytes           OUT   NUMBER,

  unexpired_blocks        OUT   NUMBER,

  unexpired_bytes         OUT   NUMBER,

  partition_name          IN    VARCHAR2 DEFAULT NULL);

 

参数说明：

Parameterhttp://www.cndba.cn/dave/article/410

Description

segment_owner

Schema name of the segment to be analyzed

segment_name

Name of the segment to be analyzed

partition_name

Partition name of the segment to be analyzed

segment_type

Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER):

TABLE

TABLE PARTITION

TABLE SUBPARTITION

INDEX

INDEX PARTITION

INDEX SUBPARTITION

CLUSTER

LOB

LOB PARTITION

LOB SUBPARTITION

unformatted_blocks

Total number of blocks unformatted

unformatted bytes

Total number of bytes unformatted

fs1_blocks

Number of blocks having at least 0 to 25% free space

fs1_bytes

Number of bytes having at least 0 to 25% free space

fs2_blocks

Number of blocks having at least 25 to 50% free space

fs2_bytes

Number of bytes having at least 25 to 50% free space

fs3_blocks

Number of blocks having at least 50 to 75% free space

fs3_bytes

Number of bytes having at least 50 to 75% free space

fs4_blocks

Number of blocks having at least 75 to 100% free space

fs4_bytes

Number of bytes having at least 75 to 100% free space

ful1_blocks

Total number of blocks full in the segment

full_bytes

Total number of bytes full in the segment

segment_size_blocks

Number of blocks allocated to the segment

segment_size_bytes

Number of bytes allocated to the segment

used_blocks

Number blocks allocated to the LOB that contains active data

used_bytes

Number bytes allocated to the LOB that contains active data

expired_blocks

Number of expired blocks used by the LOB to keep version data

expired_bytes

Number of expired bytes used by the LOB to keep version data

unexpired_blocks

Number of unexpired blocks used by the LOB to keep version data

unexpired_bytes

Number of unexpired bytes used by the LOB to keep version data

partition_name

Name of the partition (NULL if not a partition)

 

 

 

2.5 ANALYZED 分析

在DBMS_SPACE.SPACE_USAGE收集信息之前，做了一个ANALYZED的判断，因为Analyze 会收集以下信息，而gather_table_stats 则不会收集： 

EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT

 

更多区别参考：

OracleDBMS_STATS 包 和 Analyze 命令的区别

http://blog.csdn.net/tianlesoftware/article/details/7055233

 

 

 

 

 

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

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Skype:    tianlesoftware

QQ:       tianlesoftware@gmail.com

Email:    tianlesoftware@gmail.com

Blog:     http://blog.csdn.net/tianlesoftware

Weibo:    http://weibo.com/tianlesoftware

Twitter:  http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware


版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle 脚本

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2239
    原创
  • 3
    翻译
  • 547
    转载
  • 186
    评论
  • 访问：6612204次
  • 积分：4249
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ