cscripts

An inventory of Oracle SQL scripts sorted by job, from latency checks like la.sql to space reporting and container commands. Each entry pairs a script file with a one-line description of what it does.

This repository is Carlos Sierra Scripts, an inventory of scripts organized by type and dated in 2023. The categories cover latency, load, SQL performance, SQL plan baselines, SQL profiles, SQL patches, sessions, blocked sessions, locks, space reporting, space maintenance, and container commands. Each script gets named along with the job it does, which makes the list usable as a lookup table.

The latency group starts with la.sql, which reports current SQL latency measured as elapsed time over executions, and lr.sql, which reports SQL latency across a time range. The load group includes ta.sql, for the top active SQL from Active Sessions History over the last minute, and aa.sql, described as a poor man's version of ASH analytics.

p.sql gives basic SQL performance metrics for a given SQL ID, and pp.sql returns the same metrics with top keys added. The plan group lists cs planx.sql for execution plans and SQL performance. SQL plan baselines, profiles, and patches each come with create, drop, and evolve scripts, so the optimizer artifacts can be managed end to end.

a.sql shows active sessions with their SQL text and execution plan, while am.sql pulls ASH samples from memory. Kill scripts cover killing a single user session, killing sessions running a specific SQL ID, killing root blockers, and killing sessions from a machine. Blocked sessions reports and lock summaries with wait chains round out the section.

Space reporting scripts draw disk filesystem utilization charts, a time series chart of top PDB disk size utilization, and tablespace usage metrics across all PDBs. Maintenance scripts handle tablespace resize, table redefinition with an optional silent mode and purge, and schema level redefinition. Container scripts connect into the CDB root and list PDBs.

The whole thing reads like a menu. Pick a category, read the short purpose next to each script file, and run the one that fits the task at hand.

• Source 1 · Project repository
• Source 2 · README