[Err] ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'

List tablespaces, size, free space, and percent free:

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;



Another example query to check free and used space per tablespace: 

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;


Подсчет необходимого размера табличного пространства UNDO при текущей нагрузке:

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';


List datafiles, tablespace names, and size in MB:

SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;


You should resize the UNDO tablespace size. Execute this as sysdba.

alter database datafile '/usr/lib/oracle/xe/oradata/XE/undo.dbf' 
AUTOEXTEND ON MAXSIZE 1423M;


--
Oracle PL/SQL: TABLESPACE:  List tablespaces, files, allocated, and free space - Listing tablespace data, such as associated files, the tablespace's allocated space, free space, and
Oracle: Мониторинг использования табличного пространства UNDO - Программные продукты - Статьи
Tablespace - Oracle FAQ
Yumani's Blog: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'
Resize Undo Tablespace after ORA-03297 file contains used data beyond... | Beyond Oracle
Ural URAL's Oracle Blog: Resizing Undo Tablespaces
Теги: , ,