Tag Archives: ORA-03297

Useful scripts: Get a list of objects to be moved for a data file resize

Every DBA from time to time wants to reduce a size of a data file, thinking ‘There is a lot of free space in datafile, why don’t to resize it?’ But the secret is that shrinking tables is not very helpful in this case — datafile least size will be limited to the block, where the last table’s/index’s block resides.

So, the solution is to relocate used blocks to free gaps between blocks.

We can achieve this by several ways:

  • ALTER TABLE …. MOVE or INDEX REDUILD;
  • use DBMS_REDIFINITION package;
  • Export table, purge it and import again with the help of DataPump.

But prior we need to get a list of objects, which need to be relocated to make datafile reduce possible. The following query show such objects:

DECLARE
   V_FILE_ID       NUMBER;
   V_BLOCK_SIZE    NUMBER;
   V_RESIZE_SIZE   NUMBER;
BEGIN
   V_FILE_ID := &FILE_ID;
   V_RESIZE_SIZE := &RESIZE_FILE_TO;
   SELECT BLOCK_SIZE
     INTO V_BLOCK_SIZE
     FROM V$DATAFILE
    WHERE FILE# = V_FILE_ID;

   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE (
         'OBJECTS IN FILE '
      || V_FILE_ID
      || ' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '
      || V_RESIZE_SIZE
      || ' BYTES');
   DBMS_OUTPUT.PUT_LINE (
      '=======================================================================');
   FOR my_record
      IN (  SELECT DISTINCT
                   (   OWNER
                    || '.'
                    || SEGMENT_NAME
                    || ' - OBJECT TYPE = '
                    || SEGMENT_TYPE)
                      ONAME
              FROM DBA_EXTENTS
             WHERE (block_id + blocks - 1) * V_BLOCK_SIZE > V_RESIZE_SIZE
                   AND FILE_ID = V_FILE_ID
          ORDER BY 1)
   LOOP
      DBMS_OUTPUT.PUT_LINE (my_record.ONAME);
   END LOOP;
END;
/
Реклама
%d такие блоггеры, как: