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