功能:DBMS_SPACE用于分析段增长和空间需求。
DBMS_SPACE常用的子方法:
* create_index_cost 计算表上创建索引时的空间需求
* create_table_cost 计算创建表的的空间需求
* free_blocks 返回table,index,cluster空闲块的信息
* object_dependent_segments 返回段的关联对象
* object_growth_trend 特定时间点的空间使用
* space_usage 返回自动段空间管理空闲块的信息
* unused_space 返回table,index,cluster已使用空间的信息
实验:
SQL> create user zwunix identified by zwunix;
User created.
SQL> grant dba to zwunix;
Grant succeeded.
SQL> conn zwunix/zwunix;
Connected.
SQL> create table t as select * from dba_objects;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
9719
SQL> set serveroutput on;
SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost('create index i_t_obj on t(object_id)',:used_bytes,:alloc_bytes);
PL/SQL procedure successfully completed.
SQL> print :used_bytes
USED_BYTES
----------
142298
SQL> print :alloc_bytes
ALLOC_BYTES
-----------
327680
SQL> create index i_t_obj on t(object_id);
Index created.
SQL> select bytes from user_segments where segment_name='I_T_OBJ';
BYTES
----------
262144
空间分配字节数估计量大致等于实际计算量。
DBMS_SPACE常用的子方法:
* create_index_cost 计算表上创建索引时的空间需求
* create_table_cost 计算创建表的的空间需求
* free_blocks 返回table,index,cluster空闲块的信息
* object_dependent_segments 返回段的关联对象
* object_growth_trend 特定时间点的空间使用
* space_usage 返回自动段空间管理空闲块的信息
* unused_space 返回table,index,cluster已使用空间的信息
实验:
SQL> create user zwunix identified by zwunix;
User created.
SQL> grant dba to zwunix;
Grant succeeded.
SQL> conn zwunix/zwunix;
Connected.
SQL> create table t as select * from dba_objects;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
9719
SQL> set serveroutput on;
SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost('create index i_t_obj on t(object_id)',:used_bytes,:alloc_bytes);
PL/SQL procedure successfully completed.
SQL> print :used_bytes
USED_BYTES
----------
142298
SQL> print :alloc_bytes
ALLOC_BYTES
-----------
327680
SQL> create index i_t_obj on t(object_id);
Index created.
SQL> select bytes from user_segments where segment_name='I_T_OBJ';
BYTES
----------
262144
空间分配字节数估计量大致等于实际计算量。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29225845/viewspace-775587/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29225845/viewspace-775587/