关于使用SHOW_SPACE()

grassbell · 发表于 2004-7-5 19:59

看到一些朋友经常提到怎么使用SHOW_SPACE()来分析空间使用情况。我在这里将他们贴出来，以方便大家借助这个工具，参与讨论！

grassbell · 发表于 2004-7-5 20:00

只适用于非ASSM:

create or replace
procedure show_space
( p_segname in varchar2,
   p_owner in varchar2 default user,
   p_type in varchar2 default 'TABLE' )
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
   dbms_space.free_blocks
   ( segment_owner    => p_owner,
      segment_name    => p_segname,
      segment_type    => p_type,
      freelist_group_id => 0,
      free_blks       => l_free_blks );
   dbms_space.unused_space
   ( segment_owner    => p_owner,
      segment_name    => p_segname,
      segment_type    => p_type,
      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( '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;
/

grassbell · 发表于 2004-7-5 20:04

例：
SQL> create table donny(id char(1024));

表已创建。

SQL> set serveroutput on

SQL> exec show_space('DONNY')
Free Blocks.............................0
Total Blocks............................5
Total Bytes.............................40960
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................8
Last Used Ext BlockId...................27
Last Used Block.........................1

PL/SQL 过程已成功完成。

SQL>

Fenng · 发表于 2004-7-7 22:05

Tom写的这个东西其实前后好几个版本的

差别不小

mugen · 发表于 2004-7-7 22:17

我换个表名就不行了,只能显示donny?

lau · 发表于 2004-7-8 07:13

换了表名不行,是因为show_space时,默认表名为大写的缘故.

grassbell · 发表于 2004-7-8 09:28

恩，是有很多版本的。如果发现更好用的，共享出来哦。
mugen，问题解决了？

mugen · 发表于 2004-7-8 09:47

搞定，原来一定要大写阿

piner · 发表于 2004-7-9 08:58

create or replace procedure print_table
( p_query in varchar2,
  p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )

-- this utility is designed to be installed ONCE in a database and used
-- by all.  Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
AUTHID CURRENT_USER
is
l_theCursor    integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status       integer;
l_descTbl    dbms_sql.desc_tab;
l_colCnt       number;
l_cs          varchar2(255);
l_date_fmt    varchar2(255);

-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them
procedure restore
is
begin
   if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
   then
         execute immediate
         'alter session set cursor_sharing=exact';
   end if;
   if ( p_date_fmt is not null )
   then
         execute immediate
            'alter session set nls_date_format=''' || l_date_fmt || '''';
   end if;
   dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default, the
-- format mask I use includes that.  In order to be "friendly"
-- we save the date current sessions date format and then use
-- the one with the date and time.  Passing in NULL will cause
-- this routine just to use the current date format
if ( p_date_fmt is not null )
then
   select sys_context( 'userenv', 'nls_date_format' )
      into l_date_fmt
      from dual;

   execute immediate
   'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;

-- to be bind variable friendly on this ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar, if not, set it so when we parse -- literals
-- are replaced with binds
if ( dbms_utility.get_parameter_value
      ( 'cursor_sharing', l_status, l_cs ) = 1 )
then
      if ( upper(l_cs) not in ('FORCE','SIMILAR'))
      then
         execute immediate
         'alter session set cursor_sharing=force';
      end if;
end if;

-- parse and describe the query sent to us.  we need
-- to know the number of columns and their names.
dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
      if ( l_descTbl(i).col_type not in ( 113 ) )
      then
         dbms_sql.define_column
         (l_theCursor, i, l_columnValue, 4000);
      end if;
end loop;

-- execute the query, so we can fetch
l_status := dbms_sql.execute(l_theCursor);

-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
      for i in 1 .. l_colCnt loop
         if ( l_descTbl(i).col_type not in ( 113 ) )
         then
            dbms_sql.column_value
            ( l_theCursor, i, l_columnValue );
            dbms_output.put_line
            ( rpad( l_descTbl(i).col_name, 30 )
            || ': ' ||
            substr( l_columnValue, 1, 200 ) );
         end if;
      end loop;
      dbms_output.put_line( '-----------------' );
end loop;

-- now, restore the session state, no matter what
restore;
exception
when others then
      restore;
      raise;
END;

糖醋兔子 · 发表于 2004-7-9 12:50

呵呵，我偷了个懒，在ASSM下，其实只是
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
不能用，我就把它去掉了

[php]
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 )
as
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

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( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
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;
/

[/php]

[Tips] 关于使用SHOW_SPACE()