Stored procedure (show_space)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Stored procedure (show_space)

Hybrid 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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Connected as SCOTT:

    Code:
    SQL> show errors
    Errors for PROCEDURE SHOW_SPACE:
    
    LINE/COL ERROR
    -------- -----------------------------------------------
    23/12    PL/SQL: SQL Statement ignored
    25/55    PL/SQL: ORA-00942: table or view does not exist
    SQL> 
    SQL> 
    SQL> desc dba_tablespaces
    ERROR:
    ORA-04043: object "SYS"."DBA_TABLESPACES" does not exist
    As SYS:
    Code:
     ...
    60  p( 'Last Used Block', l_LAST_USED_BLOCK );
     61  end; 
     62  /
    
    Procedure created.
    As SCOTT, with sys having granted select on dba_tablespaces and dba_segments:
    Code:
     ...
    60  p( 'Last Used Block', l_LAST_USED_BLOCK );
     61  end; 
     62  /
    
    Procedure created.
    Last edited by stecal; 12-13-2010 at 02:40 PM.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    Not sure what your trying to tell me in your previous post. can you be a bit
    more specific.

    As you can see I can desc the view.

    Thanks

    SQL> show user;
    USER is "STU"
    SQL> desc dba_tablespaces;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TABLESPACE_NAME NOT NULL VARCHAR2(30)
    BLOCK_SIZE NOT NULL NUMBER
    INITIAL_EXTENT NUMBER
    NEXT_EXTENT NUMBER
    MIN_EXTENTS NOT NULL NUMBER
    MAX_EXTENTS NUMBER
    PCT_INCREASE NUMBER
    MIN_EXTLEN NUMBER
    STATUS VARCHAR2(9)
    CONTENTS VARCHAR2(9)
    LOGGING VARCHAR2(9)
    FORCE_LOGGING VARCHAR2(3)
    EXTENT_MANAGEMENT VARCHAR2(10)
    ALLOCATION_TYPE VARCHAR2(9)
    PLUGGED_IN VARCHAR2(3)
    SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
    DEF_TAB_COMPRESSION VARCHAR2(8)
    RETENTION VARCHAR2(11)
    BIGFILE VARCHAR2(3)

  4. #4
    Join Date
    Jul 2006
    Posts
    195
    Thanks, I got it!!

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