DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Stored procedure (show_space)

Threaded View

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    Stored procedure (show_space)

    I am trying to install Tom Kytes famous show_space SP.

    When I try installing it I get an error 942 (26:73) PL/SQL: ORA-00942
    table or view does not exist.

    I have DBA privs and can desc both dba_tablespaces and dba_segements as
    referenced in the query. I saw somebody had a similiar problem, but that
    link was unavailable.

    I am trying to install this in an Oracle 10.2.4.0 DB. If I preference
    the procedure name with the user dbsnmp that does work.

    Does anybody have any ideas on what the issue may be?

    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 )
    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;
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
    dbms_output.put_line( rpad(p_label,40,'.') || p_num );
    end;
    begin
    for x in ( select tablespace_name
    from dba_tablespaces
    where tablespace_name = ( select tablespace_name from dba_segments
    where segment_type = p_type and segment_name = p_segname )
    )
    loop
    dbms_space.free_blocks
    ( segment_owner => p_owner,
    segment_name => p_segname,
    segment_type => p_type,
    partition_name => p_partition,
    freelist_group_id => 0,
    free_blks => l_free_blks );
    end loop;

    dbms_space.unused_space
    ( segment_owner => p_owner,
    segment_name => p_segname,
    segment_type => p_type,
    partition_name => p_partition,
    total_blocks => l_total_blocks,
    total_bytes => l_total_bytes,
    unused_blocks => l_unused_blocks,
    unused_bytes => l_unused_bytes,
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
    LAST_USED_BLOCK => l_LAST_USED_BLOCK );

    p( 'Free Blocks', l_free_blks );
    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
    end;
    Last edited by BeefStu; 12-13-2010 at 02:36 PM. Reason: added more information

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width