Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:sql_groesse_tabelle

Größe einer Tabelle analysieren

Oft ist es notwendig, sich neben der reinen Größe einer Tabelle auch den Füllgrade anzeigen zu lassen:

(Die aktuelle Version liegt immer hier OraPowerShell SQL Library - space.sql )

space.sql
SET pagesize 300
SET linesize 250
SET verify off
 
define lnum  = "format 9G999G999D99"
define num   = "format 99G999"
define snum  = "format 9G999"
 
ttitle LEFT  "Space Usage of a table" skip 2
 
define ENTER_OWNER='&TABLE_OWNER'
define ENTER_TABLE='&TABLE_NAME'
 
COLUMN segment_name format A20
COLUMN owner        format A10
COLUMN Size_MB      &&lnum
COLUMN count_blk    &&num
COLUMN count_ext    &&snum
COLUMN count_part   &&snum
 
  SELECT segment_name
        ,owner
        ,ROUND (SUM (bytes) / 1024 / 1024, 3) AS Size_MB
        ,SUM (blocks) AS count_blk
        ,SUM (EXTENTS) AS count_ext
        ,COUNT (*) AS count_part
    FROM dba_segments
   WHERE     UPPER (segment_name) LIKE UPPER ('&ENTER_TABLE.%')
         AND UPPER (owner) = UPPER ('&ENTER_OWNER.')
GROUP BY segment_name, owner
/
 
/*
Parameter            Description
-----------------------------------------------------------------------
segment_owner        Schema name of the segment to be analyzed
segment_name        Name of the segment to be analyzed
partition_name        Partition name of the segment to be analyzed
segment_type        Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
unformatted_blocks    Total number of blocks that are unformatted
unformatted bytes    Total number of bytes that are unformatted
fs1_blocks            Number of blocks that has at least 0 to 25% free space
fs1_bytes            Number of bytes that has at least 0 to 25% free space
fs2_blocks            Number of blocks that has at least 25 to 50% free space
fs2_bytes            Number of bytes that has at least 25 to 50% free space
fs3_blocks            Number of blocks that has at least 50 to 75% free space
fs3_bytes            Number of bytes that has at least 50 to 75% free space
fs4_blocks            Number of blocks that has at least 75 to 100% free space
fs4_bytes            Number of bytes that has at least 75 to 100% free space
ful1_blocks            Total number of blocks that are full in the segment
full_bytes            Total number of bytes that are full in the segment
 
 
----------------------------
 
total_blocks                Returns total number of blocks in the segment.
total_bytes                    Returns total number of blocks in the segment, in bytes.
unused_blocks                Returns number of blocks which are not used.
unused_bytes                Returns, in bytes, number of blocks which are not used.
last_used_extent_ file_id    Returns the file ID of the last extent which contains data.
last_used_extent_ block_id    Returns the starting block ID of the last extent which contains data.
last_used_block                Returns the last block within this extent which contains data.
 
*/
 
SET serveroutput ON
 
/* Formatted on 24.7.2013. 21:49:56 (QP5 v5.185.11230.41888) */
DECLARE
   unf                    NUMBER;
   unfb                   NUMBER;
   fs1                    NUMBER;
   fs1b                   NUMBER;
   fs2                    NUMBER;
   fs2b                   NUMBER;
   fs3                    NUMBER;
   fs3b                   NUMBER;
   fs4                    NUMBER;
   fs4b                   NUMBER;
   FULL                   NUMBER;
   fullb                  NUMBER;
 
   total_blocks           NUMBER;
   total_bytes            NUMBER;
   unused_blocks          NUMBER;
   unused_bytes           NUMBER;
   lastextf               NUMBER;
   last_extb              NUMBER;
   lastusedblock          NUMBER;
 
   v_file_name            dba_data_files.file_name%TYPE;
   v_tablespace_name      dba_segments.TABLESPACE_NAME%TYPE;
   v_segment_management   dba_tablespaces.SEGMENT_SPACE_MANAGEMENT%TYPE;
 
   cursor c_seg
   IS
      SELECT segment_name, owner, SEGMENT_TYPE
        FROM dba_segments
       WHERE     UPPER (segment_name) LIKE UPPER ('&ENTER_TABLE.%')
             AND UPPER (owner) = UPPER ('&ENTER_OWNER.');
BEGIN
   FOR rec IN c_seg
   loop
      BEGIN
         DBMS_OUTPUT.put_line (
               'Info -- Call dbms_space.space_usage for table ( Type:'
            || rec.segment_type
            || ' ) ::'
            || rec.segment_name);
         DBMS_OUTPUT.put_line (
            'Info ------------------------------------------------------------------');
 
         DBMS_SPACE.space_usage (rec.owner
                                ,rec.segment_name
                                ,rec.segment_type
                                ,unf
                                ,unfb
                                ,fs1
                                ,fs1b
                                ,fs2
                                ,fs2b
                                ,fs3
                                ,fs3b
                                ,fs4
                                ,fs4b
                                ,FULL
                                ,fullb);
 
         DBMS_OUTPUT.put_line (
               'Info -- Total Count of blocks that are unformatted              :'
            || unf
            || ' Bytes :'
            || unfb);
         DBMS_OUTPUT.put_line (
               'Info -- Total Count of blocks that are full in the segment      :'
            || FULL
            || ' Bytes :'
            || fullb);
 
         DBMS_OUTPUT.put_line ('Info -- ');
 
         DBMS_OUTPUT.put_line (
               'Info -- Count of blocks that has at least 0  to 25%  free space :'
            || fs1
            || ' Bytes :'
            || fs1b);
         DBMS_OUTPUT.put_line (
               'Info -- Count of blocks that has at least 25 to 50%  free space :'
            || fs2
            || ' Bytes :'
            || fs2b);
         DBMS_OUTPUT.put_line (
               'Info -- Count of blocks that has at least 50 to 75%  free space :'
            || fs3
            || ' Bytes :'
            || fs3b);
         DBMS_OUTPUT.put_line (
               'Info -- Count of blocks that has at least 75 to 100% free space :'
            || fs4
            || ' Bytes :'
            || fs4b);
 
         DBMS_OUTPUT.put_line (
            'Info ------------------------------------------------------------------');
      exception
         WHEN others
         THEN
            DBMS_OUTPUT.put_line ('Error --');
            DBMS_OUTPUT.put_line ('Error -- ' || SQLERRM);
            DBMS_OUTPUT.put_line (
               'Error --   +This procedure can be used only on segments in  tablespaces with AUTO SEGMENT SPACE MANAGEMENT');
            DBMS_OUTPUT.put_line (
               'Error --   +Action:  Recheck the segment name and type and re-issue the statement');
 
            SELECT s.TABLESPACE_NAME, t.SEGMENT_SPACE_MANAGEMENT
              INTO v_tablespace_name, v_segment_management
              FROM dba_segments s, dba_tablespaces t
             WHERE     UPPER (s.segment_name) LIKE UPPER ('&ENTER_TABLE.%')
                   AND UPPER (s.owner) = UPPER ('&ENTER_OWNER.')
                   AND s.TABLESPACE_NAME = t.TABLESPACE_NAME;
 
            DBMS_OUTPUT.put_line (
                  'Error --   +Tablespace for the table &ENTER_TABLE.:: '
               || v_tablespace_name
               || ' - Segment Management for this tablespace:: '
               || v_segment_management);
            DBMS_OUTPUT.put_line ('Error --');
      END;
 
      BEGIN
         DBMS_OUTPUT.put_line (
               'Info -- Call dbms_space.UNUSED_SPACE for table  ( Type:'
            || rec.segment_type
            || ' ) ::'
            || rec.segment_name);
 
         DBMS_SPACE.UNUSED_SPACE (rec.owner
                                 ,rec.segment_name
                                 ,rec.segment_type
                                 ,total_blocks
                                 ,total_bytes
                                 ,unused_blocks
                                 ,unused_bytes
                                 ,lastextf
                                 ,last_extb
                                 ,lastusedblock);
 
         DBMS_OUTPUT.put_line (
            'Info ------------------------------------------------------------------');
         DBMS_OUTPUT.put_line (
               'Info -- Used total_blocks                           :'
            || total_blocks);
         DBMS_OUTPUT.put_line (
               'Info -- Used total_bytes                            :'
            || total_bytes);
         DBMS_OUTPUT.put_line (
               'Info -- Unused block                                :'
            || unused_blocks);
         DBMS_OUTPUT.put_line (
               'Info -- Unused byte                                 :'
            || unused_bytes);
         DBMS_OUTPUT.put_line (
               'Info -- File ID of the last extent with data        :'
            || lastextf);
 
         SELECT file_name
           INTO v_file_name
           FROM dba_data_files
          WHERE FILE_ID = lastextf;
 
         DBMS_OUTPUT.put_line (
               'Info -- File Name last extent with data             :'
            || v_file_name);
 
         DBMS_OUTPUT.put_line (
               'Info -- Starting block ID of the last extent        :'
            || last_extb);
         DBMS_OUTPUT.put_line (
               'Info -- Last block within this extent               :'
            || lastusedblock);
         DBMS_OUTPUT.put_line (
            'Info ------------------------------------------------------------------');
      exception
         WHEN others
         THEN
            DBMS_OUTPUT.put_line ('Error ---' || SQLERRM);
      END;
   END loop;
END;
/
 
SET verify ON
ttitle off

Wie wird die Tabelle nun aber wieder kleiner?

Guter Vorschlag für die Reorganisation von Tabellen und Tablespaces:

Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
dba/sql_groesse_tabelle.txt · Zuletzt geändert: 2013/10/10 08:34 von Gunther Pippèrr