每日点滴之 dbms_space包

DBMS_SPACE

这个包可以分析段的增长和需要的空间
需要使用sus用户权限执行，执行管线给了public，用户必须在对象上有analyze的权限。

使用这个包我们可以获得以下字节数
--即将创建的索引的空间  强烈依赖于统计信息的收集
--即将创建的表的大小
--一个对象中空闲块的信息
--分配给对象的段的列表
--及时反映在特定时间点某个表的函数对对象行数的使用率
--返回自动段空间管理的段的空闲块的信息
--对象中未使用的空间的信息

数据结构：
定义了一个record类型和一个table类型

 

Table 98-2 DBMS_SPACE Package Subprograms

Subprogram Description
ASA_RECOMMENDATIONS Function
 Returns recommendations/findings of segment advisor run automatically by the system or manually invoked by the user

CREATE_INDEX_COST Procedure
 Determines the cost of creating an index on an existing table

CREATE_TABLE_COST Procedures
 Determines the size of the table given various attributes
 
FREE_BLOCKS Procedure
 Returns information about free blocks in an object (table, index, or cluster)

OBJECT_DEPENDENT_SEGMENTS Function
 Returns the list of segments that are associated with the object

OBJECT_GROWTH_TREND Function
 A table function where each row describes the space usage of the object at a specific point in time

SPACE_USAGE Procedure
 Returns information about free blocks in an auto segment space managed segment

UNUSED_SPACE Procedure
 Returns information about unused space in an object (table, index, or cluster)

使用方法举例：
以下资料转自itpub北在南方 http://space.itpub.net/22664653/viewspace-677328
帖子标题：【oracle 】如何估算即将创建的索引大小
SQL> set serveroutput on
SQL> declare
  2  l_index_ddl varchar2(1000);
  3  l_used_bytes number;
  4  l_allocated_bytes number;
  5  begin
  6  dbms_space.create_index_cost (
  7 ddl =>'create index idx_t on t(id) ', --执行的ddl
  8  used_bytes => l_used_bytes,
  9  alloc_bytes => l_allocated_bytes );
 10  dbms_output.put_line ('used= ' || l_used_bytes || 'bytes'
 11  || '  allocated= ' || l_allocated_bytes || 'bytes');
 12  end;
 13  /
used= 345098bytes  allocated= 720896bytes                                     
PL/SQL 过程已成功完成。
说明:  used_bytes  给出索引数据实际表现的字节数。
         allocated 是当实际创建索引时在表空间中实际占用的字节数。

联机文档的官方解释：
CREATE_INDEX_COST Procedure
This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.

Syntax

DBMS_SPACE.CREATE_INDEX_COST (
   ddl             IN    VARCHAR2,
   used_bytes      OUT   NUMBER,
   alloc_bytes     OUT   NUMBER,
   plan_table      IN    VARCHAR2 DEFAULT NULL);
Pragmas

pragma restrict_references(create_index_cost,WNDS);
Parameters

Table 98-4 CREATE_INDEX_COST Procedure Parameters

Parameter Description
ddl   The create index DDL statement
used_bytes  The number of bytes representing the actual index data
alloc_bytes  Size of the index when created in the tablespace
plan_table  Which plan table to use, default NULL

Usage Notes

The table on which the index is created must already exist.

The computation of the index size depends on statistics gathered on the segment.

It is imperative that the table must have been analyzed recently.

In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.

 

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

转载于:http://blog.itpub.net/24799772/viewspace-677683/