3 Comments

  1. Thomas
    9/16/2008

    Hi Marco!
    Very interesting comparison! Looks like high compression brings quite some space savings!
    Could you add the queries you used to get the segments and database sizes? Thanks!
    Thomas

  2. 9/16/2008

    There is a dedicated presentation dealing with securefile during Oracle Open World (if you are going). Search in the scheduler on keyword “securefile”.

  3. 9/16/2008

    Scripting, segments:

    SQL> GET segment
      1  prompt
      2  PROMPT Segments:
      3  prompt
      4  COLUMN segment_name format a30         heading 'Segment'
      5  COLUMN segment_type format a15         heading 'Type'
      6  COLUMN bytes        format 99999999999 heading 'Bytes'
      7  COLUMN extents      format 9999999     heading 'Tot.Ext'
      8  COLUMN ini          format 99999999999 heading 'IniSize'
      9  COLUMN NEXT         format 99999999999 heading 'NextSize'
     10  COLUMN min_extents  format 9999        heading 'MinE'
     11  COLUMN max_extents  format 999999        heading 'MaxE'
     12  COLUMN owner        format a15         heading 'Owner'
     13  COLUMN pct          format 9999        heading '%Inc'
     14  COLUMN tbsname      format a20         heading 'Tablespace'
     15  break ON report
     16  compute SUM OF bytes ON report
     17  undefine segment_type
     18  undefine segment_name
     19  undefine tablespace_name
     20  undefine owner
     21  undefine extents
     22  SET LINESIZE 200
     23  SET PAGESIZE 5000
     24  SET FEEDBACK OFF
     25  SELECT   SEGMENT_NAME                      
     26  ,        SEGMENT_TYPE                      
     27  ,        BYTES                             
     28  ,        EXTENTS                   
     29  ,        INITIAL_EXTENT ini        
     30  ,        NEXT_EXTENT NEXT          
     31  ,        PCT_INCREASE pct
     32  ,        MIN_EXTENTS                       
     33  ,        MAX_EXTENTS
     34  ,        OWNER
     35  ,        tablespace_name                   
     36  FROM     sys.dba_segments
     37  WHERE    segment_name LIKE UPPER('%&Segment_name%')
     38  AND      segment_type LIKE UPPER('%&Segment_type%')
     39  AND      owner LIKE UPPER('%&Owner%')
     40  AND      extents >= to_number(nvl('&Extents','0'))
     41  AND      tablespace_name LIKE UPPER('%&Tablespace_name%')
     42  ORDER BY owner
     43  ,        extents DESC
     44  ,        segment_name
     45  ,        segment_type
     46  ,        bytes
     47  /
     48  SET linesize 132
     49  SET pagesize 36
     50  SET feedback ON
     51  undefine segment_type
     52  undefine segment_name
     53  undefine tablespace_name
     54  undefine owner
     55* undefine extents
     56

    If appropriate for detailed LOB info, I used:

     
    SQL> GET bytes
      1  SET SERVEROUTPUT ON
      2  PROMPT
      3  PROMPT
      4  UNDEFINE USERNAME
      5  UNDEFINE SEGMENTNAME
      6  UNDEFINE SEGMENTTYPE
      7  DECLARE
      8      l_segment_name          varchar2(30);
      9      l_segment_size_blocks   NUMBER;
     10      l_segment_size_bytes    NUMBER;
     11      l_used_blocks           NUMBER;
     12      l_used_bytes            NUMBER;
     13      l_expired_blocks        NUMBER;
     14      l_expired_bytes         NUMBER;
     15      l_unexpired_blocks      NUMBER;
     16      l_unexpired_bytes       NUMBER;
     17  BEGIN
     18  dbms_output.put_line(chr(10));
     19   dbms_space.space_usage (segment_owner           => UPPER('&&USERNAME'),
     20                           segment_name            => UPPER('&&SEGMENTNAME'),
     21                           segment_type            => UPPER('&&SEGMENTTYPE'),
     22                           partition_name          => NULL,
     23                           segment_size_blocks     => l_segment_size_blocks,
     24                           segment_size_bytes      => l_segment_size_bytes,
     25                           used_blocks             => l_used_blocks,
     26                           used_bytes              => l_used_bytes,
     27                           expired_blocks          => l_expired_blocks,
     28                           expired_bytes           => l_expired_bytes,
     29                           unexpired_blocks        => l_unexpired_blocks,
     30                           unexpired_bytes         => l_unexpired_bytes
     31                     );
     32     dbms_output.put_line('OBJECT_NAME = &&SEGMENTNAME');
     33     dbms_output.put_line('-----------------------------------------');
     34     dbms_output.put_line('segment_size_blocks       => '||  l_segment_size_blocks);
     35     dbms_output.put_line('segment_size_bytes        => '||  l_segment_size_bytes);
     36     dbms_output.put_line('used_blocks               => '||  l_used_blocks);
     37     dbms_output.put_line('used_bytes                => '||  l_used_bytes);
     38     dbms_output.put_line('expired_blocks            => '||  l_expired_blocks);
     39     dbms_output.put_line('expired_bytes             => '||  l_expired_bytes);
     40     dbms_output.put_line('unexpired_blocks          => '||  l_unexpired_blocks);
     41     dbms_output.put_line('unexpired_bytes           => '||  l_unexpired_bytes);
     42  END;
     43  /
     44  UNDEFINE USERNAME
     45  UNDEFINE SEGMENTNAME
     46* UNDEFINE SEGMENTTYPE
     47

Comments are closed.