OracleNotes

Category: Script

  • Sequences Nearing Limit

    Happy New Year 2012! 

    During the holidays we hit a production issue on the EBS instance. It was one of the Sequences reaching the maximum limit, but took a lot of time for our admins and Oracle support to figure out where the issue was… If we had the following SQL as part of the monitoring jobs, could have avoided the issue altogether…

    This was added to monitoring tool immediately… 🙂

    SELECT sequence_owner,
sequence_name,
last_number,
max_value,
cache_size
FROM dba_sequences
WHERE last_number > max_value - CASE
WHEN max_value > 50000 THEN 10000
ELSE 200
END
AND cycle_flag = 'N'
AND max_value != -1;

     

  • Tablespace Free Space and Max Size

    Earlier, provided a small script to find the maximum growable size of a tablespace… Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk…All size is in MB.

    SELECT a.tablespace_name TSNAME, 
        ROUND(SUM(a.growable)/1048576) growable, 
        ROUND(SUM(a.tots)/1048576) Tot_Size,
        ROUND(SUM(a.sumb)/1048576) Tot_Free,
        ROUND(SUM(a.sumb)*100/sum(a.tots)) Pct_Free,
        ROUND(SUM(a.largest)/1048576) Large_Ext
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, 0 growable
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY rollup(a.tablespace_name)
ORDER by a.tablespace_name

    Enjoy!