OracleInstance & database
Processes and sessions against their limits
Current and peak usage of processes, sessions and transactions since startup. A PEAK % near 100 means the next connection storm will hit ORA-00020.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN resource_name HEADING "RESOURCE" FORMAT A224COLUMN current_util HEADING "CURRENT" FORMAT 999,9905COLUMN max_util HEADING "PEAK" FORMAT 999,9906COLUMN limit_value HEADING "LIMIT" FORMAT A107COLUMN max_pct HEADING "PEAK %" FORMAT 990.98 9SELECT resource_name,10 current_utilization AS current_util,11 max_utilization AS max_util,12 TRIM(limit_value) AS limit_value,13 CASE WHEN TRIM(limit_value) NOT IN ('UNLIMITED', '0')14 THEN ROUND(100 * max_utilization / TO_NUMBER(TRIM(limit_value)), 1) END AS max_pct15FROM v$resource_limit16WHERE resource_name IN ('processes', 'sessions', 'transactions', 'parallel_max_servers')17ORDER BY resource_name;Save it as ora-resource-limit.sql and run it with SQL> @ora-resource-limit.
Open in denrepoRelated tool: Init parameter reference
Helps with
- ORA-00018: maximum number of sessions exceeded
- ORA-00020: maximum number of processes (%s) exceeded
- ORA-12519: TNS:no appropriate service handler found
- ORA-12537: TNS:connection closed
Part of these runbooks
More Oracle scripts: Instance & database
- Instance status on every nodeHost, instance, version, status, whether logins are allowed, startup time and archiver state for every instance. A quick RAC-wide check after a…
- Working with the spfileShows which spfile the instance is using, where parameter files live and the order Oracle searches for them, and what SCOPE = MEMORY, SPFILE and BOTH…
- Database and instance overviewOne row per instance: host, version, uptime, role, open mode, archive log mode and whether it's a CDB. The first thing to run when you land on an…
- Non-default initialization parametersEvery parameter that has been changed from its default. Handy for comparing two databases or documenting a build.
- Installed components and their statusEverything in DBA_REGISTRY. Anything not VALID after a patch or upgrade needs attention before you hand the database back.
- Applied patches (datapatch history)Release updates and one-off patches applied through datapatch, newest first. Confirms the SQL side of a patch actually ran.