【PL/SQL】show_space

简介: SQL> create table objects as select * from dba_objects; 表已创建 SQL> @f:\sql\show_space SQL> set serveroutput onSQL> exec show_space('objects','auto');Total Blocks.

SQL> create table objects as select * from dba_objects;

表已创建

SQL> @f:\sql\show_space

SQL> set serveroutput on
SQL> exec show_space('objects','auto');
Total Blocks............................1024                                   
Total Bytes.............................8388608                                
Unused Blocks...........................8                                      
Unused Bytes............................65536                                  
Last Used Ext FileId....................1                                      
Last Used Ext BlockId...................90377                                  
Last Used Block.........................120                                    
PL/SQL 过程已成功完成。

SQL> create index i_ojectid on objects(object_id);

索引已创建。
                                
SQL> exec show_space('i_ojectid','auto','i');
Total Blocks............................256                                    
Total Bytes.............................2097152                                
Unused Blocks...........................101                                    
Unused Bytes............................827392                                 
Last Used Ext FileId....................1                                      
Last Used Ext BlockId...................90633                                  
Last Used Block.........................27                                     

PL/SQL 过程已成功完成。
SQL> analyze table objects compute statistics;
表已分析。

SQL> exec show_space('objects','auto','T','Y');
Total Blocks............................1024                                   
Total Bytes.............................8388608                                
Unused Blocks...........................8                                      
Unused Bytes............................65536                                  
Last Used Ext FileId....................1                                      
Last Used Ext BlockId...................90377                                  
Last Used Block.........................120                                    
BEGIN show_space('objects','auto','T','Y'); END;

*
第 1 行出现错误:
ORA-10614: Operation not allowed on this segment
ORA-06512: 在 "SYS.DBMS_SPACE", line 190
ORA-06512: 在 "SYS.SHOW_SPACE", line 92
ORA-06512: 在 line 1

SQL> select segment_space_management from dba_tablespaces
  2  where tablespace_name=
  3  (select tablespace_name from user_tables where table_name ='OBJECTS');

SEGMEN                                                                         
------                                                                         
MANUAL                                                                         


SQL> grant execute on dbms_space to public;
授权成功。
SQL> create public synonym show_space for sys.show_space;
同义词已创建。

SQL> grant execute on show_space to public;
授权成功。

SQL> conn scott/yang
已连接。
SQL> exec show_space('emp','auto');

PL/SQL 过程已成功完成。

SQL> set serveroutput on
SQL> exec show_space('emp','auto');
Total Blocks............................8                                      
Total Bytes.............................65536                                  
Unused Blocks...........................0                                      
Unused Bytes............................0                                      
Last Used Ext FileId....................4                                      
Last Used Ext BlockId...................25                                     
Last Used Block.........................8                                      

PL/SQL 过程已成功完成。

 

目录
相关文章
|
1月前
|
SQL 缓存 BI
在 SQL Server 中使用 SPACE 函数
【8月更文挑战第5天】
89 6
在 SQL Server 中使用 SPACE 函数
|
SQL 关系型数据库 Oracle
|
存储 索引
学习show_space存储过程
今天学习和介绍一个有用的工具,来自TOM大神的show_space,其实这就是一个存储过程,用他可以统计一些段的用度,非常方便,网上流传着不同的版本。 首先我们看下原版的脚本,https://asktom.
1070 0