SELECT *
  FROM (  SELECT *
            FROM dba_segments
        ORDER BY bytes DESC)
 WHERE ROWNUM <= 10

SELECT * FROM ( SELECT * FROM dba_segments ORDER BY bytes DESC) WHERE ROWNUM <= 10

SELECT *
  FROM (  SELECT l.owner,
                 l.table_name,
                 l.column_name,
                 l.segment_name,
                 l.tablespace_name,
                 l.index_name,
                 ROUND (s.bytes / 1024 / 1024) "SIZE IN MB"
            FROM dba_lobs l, dba_segments s
           WHERE     s.segment_type = 'LOBSEGMENT'
                 AND l.segment_name = s.segment_name
        ORDER BY s.bytes DESC)
 WHERE ROWNUM <= 10;

SELECT * FROM ( SELECT l.owner, l.table_name, l.column_name, l.segment_name, l.tablespace_name, l.index_name, ROUND (s.bytes / 1024 / 1024) "SIZE IN MB" FROM dba_lobs l, dba_segments s WHERE s.segment_type = 'LOBSEGMENT' AND l.segment_name = s.segment_name ORDER BY s.bytes DESC) WHERE ROWNUM <= 10;

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

SELECT DECODE (
    end_block_1,
    NULL,    'ALTER TABLESPACE '
          || TABLESPACE_NAME 
          || ' DROP DATAFILE '''
          || FILE_NAME
          || ''';',
       'ALTER DATABASE DATAFILE '''
          || FILE_NAME
          || ''' RESIZE '
          || CEIL ( (NVL (END_BLOCK_1, 1) * 8192) / 1024 / 1024)
          || 'M;')
    RESIZE_COMMAND
FROM (SELECT FILE_NAME,
             TABLESPACE_NAME,
             FILE_ID,
             FILE_BLOCKS,
             SEGMENT_NAME SEGMENT_NAME_1,
             START_BLOCK START_BLOCK_1,
             END_BLOCK END_BLOCK_1,
             LEAD (SEGMENT_NAME, 1)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  SEGMENT_NAME_2,
             LEAD (START_BLOCK, 1)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  START_BLOCK_2,
             LEAD (END_BLOCK, 1)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  END_BLOCK_2,
             LEAD (SEGMENT_NAME, 2)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  SEGMENT_NAME_3,
             LEAD (START_BLOCK, 2)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  START_BLOCK_3,
             LEAD (END_BLOCK, 2)
                  OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC)
                  END_BLOCK_3,
             POSITION
         FROM (SELECT FILE_NAME,
                      TABLESPACE_NAME,
                      FILE_ID,
                      FILE_BLOCKS,
                      SEGMENT_NAME,
                      BLOCK_ID START_BLOCK,
                      BLOCK_ID + BLOCKS - 1 END_BLOCK,
                      POSITION
                   FROM (SELECT DISTINCT
                                DDF.TABLESPACE_NAME,
                                DDF.FILE_NAME,
                                DDF.FILE_ID,
                                DDF.BLOCKS FILE_BLOCKS,
                                DE.SEGMENT_NAME,
                                DE.BLOCK_ID,
                                DE.BLOCKS,
                                ROW_NUMBER ()
                                OVER (PARTITION BY DDF.FILE_NAME
                                    ORDER BY DE.BLOCK_ID DESC)
                                    POSITION
                             FROM DBA_DATA_FILES DDF, DBA_EXTENTS DE
                             WHERE   DDF.FILE_ID = DE.FILE_ID(+)
                                         AND DDF.TABLESPACE_NAME LIKE 'TS_%')
                   WHERE POSITION <= 3))
         WHERE POSITION = 1
         ORDER BY FILE_ID;

SELECT DECODE ( end_block_1, NULL, 'ALTER TABLESPACE ' || TABLESPACE_NAME || ' DROP DATAFILE ''' || FILE_NAME || ''';', 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL ( (NVL (END_BLOCK_1, 1) * 8192) / 1024 / 1024) || 'M;') RESIZE_COMMAND FROM (SELECT FILE_NAME, TABLESPACE_NAME, FILE_ID, FILE_BLOCKS, SEGMENT_NAME SEGMENT_NAME_1, START_BLOCK START_BLOCK_1, END_BLOCK END_BLOCK_1, LEAD (SEGMENT_NAME, 1) OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) SEGMENT_NAME_2, LEAD (START_BLOCK, 1) OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) START_BLOCK_2, LEAD (END_BLOCK, 1) OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) END_BLOCK_2, LEAD (SEGMENT_NAME, 2) OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) SEGMENT_NAME_3, LEAD (START_BLOCK, 2) OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) START_BLOCK_3, LEAD (END_BLOCK, 2) OVER (PARTITION BY FILE_NAME ORDER BY START_BLOCK DESC) END_BLOCK_3, POSITION FROM (SELECT FILE_NAME, TABLESPACE_NAME, FILE_ID, FILE_BLOCKS, SEGMENT_NAME, BLOCK_ID START_BLOCK, BLOCK_ID + BLOCKS - 1 END_BLOCK, POSITION FROM (SELECT DISTINCT DDF.TABLESPACE_NAME, DDF.FILE_NAME, DDF.FILE_ID, DDF.BLOCKS FILE_BLOCKS, DE.SEGMENT_NAME, DE.BLOCK_ID, DE.BLOCKS, ROW_NUMBER () OVER (PARTITION BY DDF.FILE_NAME ORDER BY DE.BLOCK_ID DESC) POSITION FROM DBA_DATA_FILES DDF, DBA_EXTENTS DE WHERE DDF.FILE_ID = DE.FILE_ID(+) AND DDF.TABLESPACE_NAME LIKE 'TS_%') WHERE POSITION <= 3)) WHERE POSITION = 1 ORDER BY FILE_ID;

ALTER DATABASE DATAFILE '/home/oracle/datenbanken/DB/TS_ABC/abc_1.dbf' RESIZE 2048M;
ALTER DATABASE DATAFILE '/home/oracle/datenbanken/DB/TS_ABC/abc_2.dbf' RESIZE 740M;

ALTER DATABASE DATAFILE '/home/oracle/datenbanken/DB/TS_ABC/abc_1.dbf' RESIZE 2048M; ALTER DATABASE DATAFILE '/home/oracle/datenbanken/DB/TS_ABC/abc_2.dbf' RESIZE 740M;