-
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
-
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.
-
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)
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|