Oracle PL/SQL Tutorial/Table/Table Storage

Материал из SQL эксперт

Перейти к: навигация, поиск

Measure table storage

SQL> SET echo ON
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE t ( x int, y char(1000) DEFAULT "x" );
TABLE created.
SQL>
SQL> CREATE OR REPLACE procedure measure_usage
  2  AS
  3      l_free_blks                 number;
  4      l_total_blocks              number;
  5      l_total_bytes               number;
  6      l_unused_blocks             number;
  7      l_unused_bytes              number;
  8      l_LastUsedExtFileId         number;
  9      l_LastUsedExtBlockId        number;
 10      l_LAST_USED_BLOCK           number;
 11
 12      procedure get_data
 13      IS
 14      begin
 15          dbms_space.free_blocks
 16          ( segment_owner     =>  USER,
 17            segment_name      => "T",
 18            segment_type      => "TABLE",
 19            FREELIST_group_id => 0,
 20            free_blks         => l_free_blks );
 21
 22          dbms_space.unused_space
 23          ( segment_owner     => USER,
 24            segment_name      => "T",
 25            segment_type      => "TABLE",
 26            total_blocks      => l_total_blocks,
 27            total_bytes       => l_total_bytes,
 28            unused_blocks     => l_unused_blocks,
 29            unused_bytes      => l_unused_bytes,
 30            LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 31            LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 32            LAST_USED_BLOCK => l_last_used_block ) ;
 33
 34
 35          dbms_output.put_line( L_free_blks || " on FREELIST, " ||
 36                                to_number(l_total_blocks-l_unused_blocks-1 ) ||
 37                                " used by table" );
 38      end;
 39  begin
 40      FOR i IN 0 .. 10
 41      loop
 42          dbms_output.put( "insert " || to_char(i,"00") || " " );
 43          get_data;
 44          INSERT INTO t (x) VALUES ( i );
 45          commit ;
 46      end loop;
 47
 48
 49      FOR i IN 0 .. 10
 50      loop
 51          dbms_output.put( "update " || to_char(i,"00") || " " );
 52          get_data;
 53          UPDATE t SET y = NULL WHERE x = i;
 54          commit;
 55      end loop;
 56  end;
 57  /
SP2-0804: Procedure created WITH compilation warnings
SQL>
SQL> exec measure_usage
INSERT  00 0 ON FREELIST, 0 used BY TABLE
INSERT  01 1 ON FREELIST, 1 used BY TABLE
INSERT  02 1 ON FREELIST, 1 used BY TABLE
INSERT  03 1 ON FREELIST, 1 used BY TABLE
INSERT  04 1 ON FREELIST, 1 used BY TABLE
INSERT  05 1 ON FREELIST, 1 used BY TABLE
INSERT  06 1 ON FREELIST, 1 used BY TABLE
INSERT  07 1 ON FREELIST, 1 used BY TABLE
INSERT  08 1 ON FREELIST, 2 used BY TABLE
INSERT  09 1 ON FREELIST, 2 used BY TABLE
INSERT  10 1 ON FREELIST, 2 used BY TABLE
UPDATE  00 1 ON FREELIST, 2 used BY TABLE
UPDATE  01 1 ON FREELIST, 2 used BY TABLE
UPDATE  02 1 ON FREELIST, 2 used BY TABLE
UPDATE  03 1 ON FREELIST, 2 used BY TABLE
UPDATE  04 2 ON FREELIST, 2 used BY TABLE
UPDATE  05 2 ON FREELIST, 2 used BY TABLE
UPDATE  06 2 ON FREELIST, 2 used BY TABLE
UPDATE  07 2 ON FREELIST, 2 used BY TABLE
UPDATE  08 2 ON FREELIST, 2 used BY TABLE
UPDATE  09 2 ON FREELIST, 2 used BY TABLE
UPDATE  10 2 ON FREELIST, 2 used BY TABLE
PL/SQL procedure successfully completed.
SQL>
SQL> DROP TABLE t;
TABLE dropped.