【PL/SQL】show_space

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 過程已成功完成。