Many people knows the show_space procedure of Tom KYTE. The procedure shows the space issues for an object, given in parameters. Execution of procedure is simple, and statistics are good. But, in AskTom, the show_space procedure has many versions :) and it's hard to know which is the last version. Also, the procedures can thow some errors in some cases and it is sometimes hard to clarify the error. In this topic, I will deal with them and give the code of last-version, running show_space code :)
First, let's create the test stuff:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 12:22:17 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@bilalEE as sysdba;
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user testspace identified by test;
User created.
SQL> grant resource, connect to testspace;
Grant succeeded.
SQL> conn testspace/test@bilalEE
Connected.
SQL> create table tspace as select * from all_objects;
Table created.
SQL> create table tspace_p partition by hash (owner) as select * from all_objects;
Table created.
SQL>
Download Code
So far, we have created a test user called testsapce and two tables in its schema, first, a normal table tspace and a partitioned table tspace_t.
Let's start with the oldest version of show_space that I found on AskTom:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 12:28:20 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@bilalEE as sysdba;
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create or replace
2 procedure show_space
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE',
6 p_free in BOOLEAN default true )
7 as
8 l_free_blks number;
9
10 l_total_blocks number;
11 l_total_bytes number;
12 l_unused_blocks number;
13 l_unused_bytes number;
14 l_LastUsedExtFileId number;
15 l_LastUsedExtBlockId number;
16 l_LAST_USED_BLOCK number;
17 procedure p( p_label in varchar2, p_num in number )
18 is
19 begin
20 dbms_output.put_line( rpad(p_label,40,'.') ||
21 p_num );
22 end;
23 begin
24 if (p_free) then
25 dbms_space.free_blocks
26 ( segment_owner => p_owner,
27 segment_name => p_segname,
28 segment_type => p_type,
29 freelist_group_id => 0,
30 free_blks => l_free_blks );
31 end if;
32 dbms_space.unused_space
33 ( segment_owner => p_owner,
34 segment_name => p_segname,
35 segment_type => p_type,
36 total_blocks => l_total_blocks,
37 total_bytes => l_total_bytes,
38 unused_blocks => l_unused_blocks,
39 unused_bytes => l_unused_bytes,
40 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
41 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
42 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
43
44 p( 'Free Blocks', l_free_blks );
45 p( 'Total Blocks', l_total_blocks );
46 p( 'Total Bytes', l_total_bytes );
47 p( 'Unused Blocks', l_unused_blocks );
48 p( 'Unused Bytes', l_unused_bytes );
49 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
50 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
51 p( 'Last Used Block', l_LAST_USED_BLOCK );
52 end;
53 /
Procedure created.
SQL>
SQL> grant execute on show_space to public;
Grant succeeded.
SQL>
SQL> create or replace public synonym show_space for sys.show_space;
Synonym created.
SQL>
Download Code
I created the procedure on sys schema and grant execute to public plus a public synonym.
Can you see the problems in this code?
First, we have a security issue. By executing this procedure, all users can be able to see the statistical information about the tables on sys schema *without having any grant*. Let's see:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 12:34:53 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@bilalEE as sysdba;
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t1 as select object_name from all_objects;
Table created.
SQL> conn testspace/test@bilalEE
Connected.
SQL> exec show_space('T1','SYS');
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec show_space('T1','SYS');
Free Blocks.............................0
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................1
Last Used Ext BlockId...................60553
Last Used Block.........................75
PL/SQL procedure successfully completed.
SQL>
How?? The procedure is actually in sys schema, and unless you specify, it will run with the rights of the procedure's owner. (Look at the AUTHID section at: http://www.psoug.org/reference/procedures.html)
So, we have to specify AUTHID CURRENT_USER pragma.
Also, another problem with the above procedure is, it does not handle the segments in tablespaces with AUTO SEGMENT SPACE MANAGEMENT, that is default, so we will usually get this error:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 13:21:24 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn testspace/test@bilalEE
Connected.
SQL> exec show_space('TSPACE');
BEGIN show_space('TSPACE'); END;
*
ERROR at line 1:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 152
ORA-06512: at "SYS.SHOW_SPACE", line 24
ORA-06512: at line 1
SQL>
Also, this procedure does not support partitioned tables.
The second version of the show_space procedure is like this:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 13:24:59 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@bilalEE as sysdba
Enter password:
Connected.
SQL> create or replace
2 procedure show_space
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE',
6 p_partition in varchar2 default NULL )
7 authid current_user
8 as
9 l_free_blks number;
10
11 l_total_blocks number;
12 l_total_bytes number;
13 l_unused_blocks number;
14 l_unused_bytes number;
15 l_LastUsedExtFileId number;
16 l_LastUsedExtBlockId number;
17 l_LAST_USED_BLOCK number;
18 procedure p( p_label in varchar2, p_num in number )
19 is
20 begin
21 dbms_output.put_line( rpad(p_label,40,'.') ||
22 p_num );
23 end;
24 begin
25 for x in ( select tablespace_name
26 from dba_tablespaces
27 where tablespace_name = ( select tablespace_name
28 from dba_segments
29 where segment_type = p_type
30 and segment_name = p_segname
31 and SEGMENT_SPACE_MANAGEMENT <> 'AUTO'
32 and rownum=1 )
33 )
34 loop
35 dbms_space.free_blocks
36 ( segment_owner => p_owner,
37 segment_name => p_segname,
38 segment_type => p_type,
39 partition_name => p_partition,
40 freelist_group_id => 0,
41 free_blks => l_free_blks );
42 end loop;
43
44 dbms_space.unused_space
45 ( segment_owner => p_owner,
46 segment_name => p_segname,
47 segment_type => p_type,
48 partition_name => p_partition,
49 total_blocks => l_total_blocks,
50 total_bytes => l_total_bytes,
51 unused_blocks => l_unused_blocks,
52 unused_bytes => l_unused_bytes,
53 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
54 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
55 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
56
57 p( 'Free Blocks', l_free_blks );
58 p( 'Total Blocks', l_total_blocks );
59 p( 'Total Bytes', l_total_bytes );
60 p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
61 p( 'Unused Blocks', l_unused_blocks );
62 p( 'Unused Bytes', l_unused_bytes );
63 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
64 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
65 p( 'Last Used Block', l_LAST_USED_BLOCK );
66 end;
67 /
Procedure created.
SQL>
Download Code
This version solves the partition problem and security problem. But we already don't have support for AUTO SEGMENT SPACE MANAGEMENT:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 13:29:21 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn testspace/test@bilalEE
Connected.
SQL> exec show_space('T1','SYS');
BEGIN show_space('T1','SYS'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.SHOW_SPACE", line 24
ORA-06512: at line 1
SQL>
And the last, the most reliable version of show_space is like this one:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 13:31:54 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@bilalEE as sysdba
Enter password:
Connected.
SQL> create or replace procedure show_space
2 ( p_segname in varchar2,
3 p_owner in varchar2 default user,
4 p_type in varchar2 default 'TABLE',
5 p_partition in varchar2 default NULL )
6 -- this procedure uses authid current user so it can query DBA_*
7 -- views using privileges from a ROLE and so it can be installed
8 -- once per database, instead of once per user that wanted to use it
9 authid current_user
10 as
11 l_free_blks number;
12 l_total_blocks number;
13 l_total_bytes number;
14 l_unused_blocks number;
15 l_unused_bytes number;
16 l_LastUsedExtFileId number;
17 l_LastUsedExtBlockId number;
18 l_LAST_USED_BLOCK number;
19 l_segment_space_mgmt varchar2(255);
20 l_unformatted_blocks number;
21 l_unformatted_bytes number;
22 l_fs1_blocks number; l_fs1_bytes number;
23 l_fs2_blocks number; l_fs2_bytes number;
24 l_fs3_blocks number; l_fs3_bytes number;
25 l_fs4_blocks number; l_fs4_bytes number;
26 l_full_blocks number; l_full_bytes number;
27
28 -- inline procedure to print out numbers nicely formatted
29 -- with a simple label
30 procedure p( p_label in varchar2, p_num in number )
31 is
32 begin
33 dbms_output.put_line( rpad(p_label,40,'.') ||
34 to_char(p_num,'999,999,999,999') );
35 end;
36 begin
37 -- this query is executed dynamically in order to allow this procedure
38 -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
39 -- via a role as is customary.
40 -- NOTE: at runtime, the invoker MUST have access to these two
41 -- views!
42 -- this query determines if the object is a ASSM object or not
43 begin
44 execute immediate
45 'select ts.segment_space_management
46 from dba_segments seg, dba_tablespaces ts
47 where seg.segment_name = :p_segname
48 and (:p_partition is null or
49 seg.partition_name = :p_partition)
50 and seg.owner = :p_owner
51 and seg.tablespace_name = ts.tablespace_name'
52 into l_segment_space_mgmt
53 using p_segname, p_partition, p_partition, p_owner;
54 exception
55 when too_many_rows then
56 dbms_output.put_line
57 ( 'This must be a partitioned table, use p_partition => ');
58 return;
59 end;
60
61
62 -- if the object is in an ASSM tablespace, we must use this API
63 -- call to get space information, else we use the FREE_BLOCKS
64 -- API for the user managed segments
65 if l_segment_space_mgmt = 'AUTO'
66 then
67 dbms_space.space_usage
68 ( p_owner, p_segname, p_type, l_unformatted_blocks,
69 l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
70 l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
71 l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
72
73 p( 'Unformatted Blocks ', l_unformatted_blocks );
74 p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
75 p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
76 p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
77 p( 'FS4 Blocks (75-100)', l_fs4_blocks );
78 p( 'Full Blocks ', l_full_blocks );
79 else
80 dbms_space.free_blocks(
81 segment_owner => p_owner,
82 segment_name => p_segname,
83 segment_type => p_type,
84 freelist_group_id => 0,
85 free_blks => l_free_blks);
86
87 p( 'Free Blocks', l_free_blks );
88 end if;
89
90 -- and then the unused space API call to get the rest of the
91 -- information
92 dbms_space.unused_space
93 ( segment_owner => p_owner,
94 segment_name => p_segname,
95 segment_type => p_type,
96 partition_name => p_partition,
97 total_blocks => l_total_blocks,
98 total_bytes => l_total_bytes,
99 unused_blocks => l_unused_blocks,
100 unused_bytes => l_unused_bytes,
101 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
102 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
103 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
104
105 p( 'Total Blocks', l_total_blocks );
106 p( 'Total Bytes', l_total_bytes );
107 p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
108 p( 'Unused Blocks', l_unused_blocks );
109 p( 'Unused Bytes', l_unused_bytes );
110 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
111 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
112 p( 'Last Used Block', l_LAST_USED_BLOCK );
113 end;
114 /
Procedure created.
SQL> conn testspace/test@bilalEE
Connected.
SQL> exec show_space('TSPACE');
BEGIN show_space('TSPACE'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.SHOW_SPACE", line 44
ORA-06512: at line 1
SQL>
Download Code
Ups! We got an error? Actually, the error was about this query, in the code: "Line 45: 'select ts.segment_space_management from dba_segments seg, dba_tablespaces ts ..." We must give the grant to this dictionary tables to public, if we want to run this procedure. This is for determining whether the segment space management is auto or manual. Also it is another security issue, but not so critical if this is not producton system. (Don't give any user the right to create or execute the show_space procedure on your production system)
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 13:44:56 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@bilalEE as sysdba
Enter password:
Connected.
SQL> grant select on dba_segments to testspace;
Grant succeeded.
SQL> grant select on dba_tablespaces to testspace;
Grant succeeded.
SQL> conn testspace/test@bilalEE
Connected.
SQL> set serveroutput on
SQL> exec show_space('TSPACE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 568
Total Blocks............................ 640
Total Bytes............................. 5,242,880
Total MBytes............................ 5
Unused Blocks........................... 54
Unused Bytes............................ 442,368
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 21,257
Last Used Block......................... 74
PL/SQL procedure successfully completed.
SQL>
At last, we can see a show_space output :)
Free Blocks...... Number of blocks on the freelist
Total Blocks..... Total blocks allocated to the table
Total Bytes...... Total bytes allocated to the table
Unused Blocks.... Blocks that have never contained data
Unused Bytes..... The above in bytes
This output is for a automatically managed segment(automatic segment space management). For a manually managed segment, we will have an output like this:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 14:29:32 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@bilalEE as sysdba
Enter password:
Connected.
SQL> set serveroutput on
SQL> exec show_space('T1');
Free Blocks............................. 0
Total Blocks............................ 256
Total Bytes............................. 2,097,152
Total MBytes............................ 2
Unused Blocks........................... 53
Unused Bytes............................ 434,176
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 60,553
Last Used Block......................... 75
PL/SQL procedure successfully completed.
SQL>
And for a partitioned table, we can get space statistics like this:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 14:33:47 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn testspace/test@bilalEE
Connected.
SQL> exec show_space('TSPACE_P');
BEGIN show_space('TSPACE_P'); END;
*
ERROR at line 1:
ORA-14107: partition specification is required for a partitioned object
ORA-06512: at "SYS.DBMS_SPACE", line 175
ORA-06512: at "SYS.SHOW_SPACE", line 67
ORA-06512: at line 1
SQL> select partition_position, partition_name
2 from user_tab_partitions
3 where table_name = 'TSPACE_P';
PARTITION_POSITION PARTITION_NAME
------------------ ------------------------------
1 SYS_P21
SQL> set serveroutput on
SQL> exec show_space('TSPACE_P','TESTSPACE','TABLE PARTITION','SYS_P21');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 568
Total Blocks............................ 640
Total Bytes............................. 5,242,880
Total MBytes............................ 5
Unused Blocks........................... 54
Unused Bytes............................ 442,368
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 21,897
Last Used Block......................... 74
PL/SQL procedure successfully completed.
SQL>
Also, you can use this one for partitioned tables. This package may be more useful:
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 14:56:50 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys@bilalEE as sysdba
Enter password:
Connected.
SQL> create or replace type show_space_type
2 as object
3 ( owner varchar2(30),
4 segment_name varchar2(30),
5 partition_name varchar2(30),
6 segment_type varchar2(30),
7 free_blocks number,
8 total_blocks number,
9 unused_blocks number,
10 last_used_ext_fileid number,
11 last_used_ext_blockid number,
12 last_used_block number
13 )
14 /
Type created.
SQL> create or replace type show_space_table_type
2 as table of show_space_type
3 /
Type created.
SQL>
SQL>
SQL> create or replace
2 function show_space_for
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE',
6 p_partition in varchar2 default NULL )
7 return show_space_table_type
8 authid CURRENT_USER
9 PIPELINED
10 as
11 pragma autonomous_transaction;
12 type rc is ref cursor;
13 l_cursor rc;
14
15 l_free_blks number;
16 l_total_blocks number;
17 l_total_bytes number;
18 l_unused_blocks number;
19 l_unused_bytes number;
20 l_LastUsedExtFileId number;
21 l_LastUsedExtBlockId number;
22 l_last_used_block number;
23 l_sql long;
24 l_conj varchar2(7) default ' where ';
25 l_owner varchar2(30);
26 l_segment_name varchar2(30);
27 l_segment_type varchar2(30);
28 l_partition_name varchar2(30);
29
30 procedure add_predicate( p_name in varchar2, p_value in varchar2 )
31 as
32 begin
33 if ( instr( p_value, '%' ) > 0 )
34 then
35 l_sql := l_sql || l_conj || p_name || ' like ''' ||
upper(p_value) || '''';
36 l_conj := ' and ';
37 elsif ( p_value is not null )
38 then
39 l_sql := l_sql || l_conj || p_name || ' = ''' ||
upper(p_value) || '''';
40 l_conj := ' and ';
41 end if;
42 end;
43 begin
44 l_sql := 'select owner, segment_name, segment_type, partition_name
45 from dba_segments ';
46
47 add_predicate( 'segment_name', p_segname );
48 add_predicate( 'owner', p_owner );
49 add_predicate( 'segment_type', p_type );
50 add_predicate( 'partition', p_partition );
51
52 execute immediate 'alter session set cursor_sharing=force';
53 open l_cursor for l_sql;
54 execute immediate 'alter session set cursor_sharing=exact';
55
56 loop
57 fetch l_cursor into l_owner, l_segment_name, l_segment_type,
l_partition_name;
58 dbms_output.put_line( l_segment_name || ',' || l_segment_type );
59 exit when l_cursor%notfound;
60 begin
61 dbms_space.free_blocks
62 ( segment_owner => l_owner,
63 segment_name => l_segment_name,
64 segment_type => l_segment_type,
65 partition_name => l_partition_name,
66 freelist_group_id => 0,
67 free_blks => l_free_blks );
68
69 dbms_space.unused_space
70 ( segment_owner => l_owner,
71 segment_name => l_segment_name,
72 segment_type => l_segment_type,
73 partition_name => l_partition_name,
74 total_blocks => l_total_blocks,
75 total_bytes => l_total_bytes,
76 unused_blocks => l_unused_blocks,
77 unused_bytes => l_unused_bytes,
78 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
79 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
80 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
81
82 pipe row(show_space_type( l_owner, l_segment_name, l_partition_name,
83 l_segment_type, l_free_blks, l_total_blocks,
l_unused_blocks, l_lastUsedExtFileId,
84 l_LastUsedExtBlockId, l_last_used_block ) );
85 exception
86 when others then null;
87 end;
88 end loop;
89 close l_cursor;
90
91 return;
92 end;
93 /
Function created.
Download Code
|
Set as Homepage
Add to Favourites
Recommend
Contact
