【
Oracle】-【show_space和show_space_asm】-执行存储过程show_space和show_space_asm报错问题
Sys建立show_space()和show_space_asm()。
Bisal执行提示：
SQL> exec show_space('MY_OBJECTS', 'BISAL');
BEGIN show_space('MY_OBJECTS', 'BISAL'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SHOW_SPACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Sys用户执行：
grant execute on show_space to bisal，bisal还是无法执行。
Sys用户执行：
SQL> create public synonym show_space for sys.show_space; Synonym created. SQL> grant execute on show_space to public ; Grant succeeded.
Bisal执行：
SQL> exec show_space('MY_OBJECTS', 'BISAL');
UNFORMATTED BLOCKS ..................... 0
FS1 BLOCKS (0-25) ...................... 0
FS2 BLOCKS (25-50) ..................... 0
FS3 BLOCKS (50-75) ..................... 18
FS4 BLOCKS (75-100)..................... 122
FULL BLOCKS ............................ 0
TOTAL BLOCKS............................ 256
TOTAL BYTES............................. 2,097,152
TOTAL MBYTES............................ 2
UNUSED BLOCKS........................... 104
UNUSED BYTES............................ 851,968
LAST USED EXT FILEID.................... 5
LAST USED EXT BLOCKID................... 137
LAST USED BLOCK......................... 24
PL/SQL procedure successfully completed.
SQL> exec show_space_assm('MY_OBJECTS', 'BISAL');
free space 0-25% Blocks:................0
free space 25-50% Blocks:...............0
free space 50-75% Blocks:...............18
free space 75-100% Blocks:..............122
Full Blocks:............................0
Unformatted blocks:.....................0
PL/SQL procedure successfully completed.
附TOM大神的show_space和show_space_assm：
-- --------------------------------------------
-- Author : Tom Kyte
-- Description : Displays free and unused space for the specified object.
-- Call Syntax : EXEC Show_Space('Tablename');
-- Requirements : SET SERVEROUTPUT ON
-- Last Modified: June 22, 2010
-- This enhance version has all the fixes for ASSM, LMT, partitions etc (Oracle version 10gr2 +)
-- -----------------------------------------------------------------------------------
set define off
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4