DECLARE

   CURSOR C_DBFILE
   IS
        SELECT F.TABLESPACE_NAME,
               F.FILE_NAME,
               F.FILE_ID,
               F.BLOCKS
          FROM DBA_DATA_FILES F, DBA_TABLESPACES T
         WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME AND T.STATUS = 'ONLINE'
      ORDER BY F.TABLESPACE_NAME, F.FILE_ID;

   CURSOR C_FREESPACE (V_FILE_ID IN NUMBER)
   IS
        SELECT BLOCK_ID, BLOCK_ID + BLOCKS MAX_BLOCK
          FROM DBA_FREE_SPACE
         WHERE FILE_ID = V_FILE_ID
      ORDER BY BLOCK_ID DESC;

   /* variables to check settings/values */
   BLOCK_CORRECTION   NUMBER;
   BLOCK_SIZE         NUMBER;

   /* running variable to show (possible) end-of-file */
   FILE_MIN_BLOCK     NUMBER;
BEGIN
   SELECT VALUE
     INTO BLOCK_SIZE
     FROM V$PARAMETER
    WHERE NAME = 'db_block_size';

   /* main loop */
   FOR C_FILE IN C_DBFILE
   LOOP
      /* initialization of loop variables */
      FILE_MIN_BLOCK := C_FILE.BLOCKS;

      BEGIN
        
         FOR C_FREE IN C_FREESPACE (C_FILE.FILE_ID)
         LOOP
            /* if blocks is an uneven value there is a need to correct with -1 to compare with end-of-file which is even */
            BLOCK_CORRECTION := (0 - MOD (C_FREE.MAX_BLOCK, 2));

            IF FILE_MIN_BLOCK = C_FREE.MAX_BLOCK + BLOCK_CORRECTION
            THEN
               /* free extent is at end so file can be resized */
               FILE_MIN_BLOCK := C_FREE.BLOCK_ID;
            ELSE
               /* no more free extent at end of file, file cannot be further resized */
               EXIT ;
            END IF;
         END LOOP;
      END;

      /* check if file can be resized, minimal size of file 16 blocks */
      IF (FILE_MIN_BLOCK = C_FILE.BLOCKS) OR (C_FILE.BLOCKS <= 16)
      THEN
         DBMS_OUTPUT.
          PUT_LINE (
               'Tablespace: '
            || C_FILE.TABLESPACE_NAME
            || ' Datafile: '
            || C_FILE.FILE_NAME);
         DBMS_OUTPUT.put_line ('cannot be resized no free extents found');
         DBMS_OUTPUT.put_line ('.');
      ELSE
         /* file needs minimal no of blocks which does vary over versions */
         IF FILE_MIN_BLOCK < 16
         THEN
            FILE_MIN_BLOCK := 16;
         END IF;

         DBMS_OUTPUT.
          PUT_LINE (
               'Tablespace: '
            || C_FILE.TABLESPACE_NAME
            || ' Datafile: '
            || C_FILE.FILE_NAME);
         DBMS_OUTPUT.
          PUT_LINE (
               'current size: '
            || (C_FILE.BLOCKS * BLOCK_SIZE) / 1024
            || 'K'
            || ' can be resized to: '
            || ROUND ( (FILE_MIN_BLOCK * BLOCK_SIZE) / 1024)
            || 'K (reduction of: '
            || ROUND (
                  ( (C_FILE.BLOCKS - FILE_MIN_BLOCK) / C_FILE.BLOCKS) * 100,
                  2)
            || ' %)');
         DBMS_OUTPUT.
          PUT_LINE (
               'SQL> alter database datafile '''
            || C_FILE.FILE_NAME
            || ''' resize '
            || ROUND ( (FILE_MIN_BLOCK * BLOCK_SIZE) / 1024)
            || 'K;');
         DBMS_OUTPUT.put_line ('.');
      END IF;
   END LOOP;
END;
/