Logo Background

Oracle System Tablespace Size

  • By on July 14, 2010 | No Comments

    Looking on how to check Oracle system tablespace size? Database Administrator should check the percentage of space available from time to time to avoid it from hitting full.

    Each sizing of Oracle system tablespace was determined by DBA. If the autoextend was set as ON, Oracle extends the tablespace automatically.

    But for those Oracle system tablespace set without autoextend, DBA should monitor it periodically otherwise it will ends with error as below in example.

    ORA-01653: unable TO extend TABLE GL.GL_JE_HEADERS BY 128 IN tablespace APPS_TS_TX_DATA

    Use the SQL command as below to determine the size and the percentage of available space.

    SELECT d.status "Status",
    d.tablespace_name "Name",
    d.contents "Type",
    d.extent_management "Extent Management",
    d.initial_extent "Initial Extent",
    TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
    TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (M)",
    TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",
    TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),'99,999,990.900') "MaxSize (M)",
    TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0), '990.00') "Used % of Max"
    FROM sys.dba_tablespaces d,
    (SELECT tablespace_name, 
    SUM(bytes) bytes, 
    SUM(decode(autoextensible,'NO',bytes,'YES',maxbytes))
    maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space 
    GROUP BY tablespace_name) f
    WHERE d.tablespace_name = a.tablespace_name(+)
    AND d.tablespace_name = f.tablespace_name(+)
    ORDER BY 10 DESC;
    Previous
    Next
    » Retry SFTP Connection In Shell Script
Leave a Comment