DBA scripts

158 scripts for Oracle, SQL Server, PostgreSQL and MySQL, formatted to run as-is. Oracle scripts are tagged with version, license and where to run them.

Oracle: 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.
• Processes and sessions against their limitsCurrent and peak usage of processes, sessions and transactions since startup. A PEAK % near 100 means the next connection storm will hit ORA-00020.
• 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.
• SGA components and PGA usageCurrent size of each SGA pool, then PGA target against what's actually allocated. PGA allocated well above target points at sort- or hash-heavy…
• Listener registration and servicesWhich listeners the instance registers with and which services it offers on every node. When clients get ORA-12514, the service they ask for is…
• Listener status and services (lsnrctl)What the listener is doing and which services it knows about, from the database server. Then test the path from a client machine. Pairs with Listener…

Oracle: Sessions

• Long operations with time remaining (all instances)Long operations still running on any instance, with percent done and elapsed, remaining and estimated total minutes. RMAN is left out because it has…
• SQL active for 30 minutes or more (all instances)Active application sessions whose current call has run for at least 1,800 seconds, with instance, program, OS process ID, SQL_ID, plan hash value and…
• Active user sessionsUser sessions doing work right now on every instance: who, from which OS user and machine, the module, what they're waiting on, the SQL_ID, when they…
• Session count by user and machineWho is connected, from where, and how many are active. The quickest way to spot a connection pool that has run away.
• Everything about one sessionPrompts for a SID and shows the user, OS details, program, current and previous SQL_ID, wait event and the OS process ID behind it.
• Find the session behind an OS process IDWhen top or ps shows an Oracle process burning CPU, enter its PID to see which session and SQL it belongs to.
• Long-running operations in progressFull scans, sorts, RMAN and index builds that Oracle tracks in V$SESSION_LONGOPS, with percent done and estimated seconds remaining.
• Generate kill statements for a user's sessionsPrompts for a username and prints one ALTER SYSTEM KILL SESSION per session, including the instance on RAC. Nothing is killed until you run the…
• Kill a sessionNeeds both SID and SERIAL# from V$SESSION; the session and blocking scripts show them together. Add the instance ID as a third value on RAC.

Oracle: Blocking & locks

• Locked objects across all instances (three views)Three looks at DML locks from GV$LOCKED_OBJECT: which objects are locked and by whom, the lock mode per object, and the same with the OS process ID…
• Blocked sessions and their blockersSessions waiting on another session, joined to the blocker. Both sides show as SID,SERIAL#, ready to paste into a kill. On RAC, also check…
• Blocking treeThe whole chain as an indented tree, starting from the root blocker. When ten sessions are stuck, the one at the left margin is the one to deal with.
• Locked objects and who holds themEvery object with a DML lock, the session holding it and the lock mode in plain words. Exclusive locks from sessions that are INACTIVE usually mean…

Oracle: Performance

• Load right now (last 60 seconds)Host CPU, average active sessions, transactions, redo and I/O rates from V$SYSMETRIC. Compare average active sessions with your CPU count to judge…
• What active sessions are doing right nowActive user sessions grouped by wait event, with CPU shown separately. A quick live picture before you reach for ASH.
• Top wait events since startupThe 15 non-idle waits with the most total time since the instance started, with average wait in milliseconds. Averages matter: 'db file sequential…
• Top 20 SQL by elapsed timeFrom V$SQLSTATS, which is cheaper to query than V$SQL. Feed the SQL_ID into the execution plan script to see how it runs.
• Top sessions by CPU usedConnected sessions ranked by total CPU seconds since they logged on. Good for finding the one batch job or report hammering the box.

Oracle: SQL tuning

• Execution plan from the cursor cachePrompts for a SQL_ID and prints every cached child plan. For actual row counts, run the statement with the GATHER_PLAN_STATISTICS hint and change…
• Full SQL text for a SQL_IDThe complete statement, not the first 1,000 characters. Reads the cursor cache; an optional AWR lookup for aged-out statements is included, commented…
• SQL running with more than one planStatements in the cursor cache with several plan hash values, showing the best and worst average time. A wide gap is the classic sign of plan…
• Plan history for a SQL_ID from AWREach AWR snapshot where the statement ran, with its plan hash value and average time. Shows exactly when a plan flipped and what it cost.
• Every historical plan for a SQL_IDPrints all plans AWR has captured for the statement, so you can compare a good plan with a bad one side by side.
• Literal SQL that should use bind variablesGroups statements that are identical apart from literals. Hundreds of copies of the same shape means hard parsing and shared pool churn; show the…
• SQL plan baselines and SQL profilesWhat's pinning plans in this database. Baselines come with Enterprise Edition; SQL profiles are created by the SQL Tuning Advisor, which needs the…
• Purge one statement from the shared poolForces a single SQL_ID to hard parse again, usually to pick up new statistics or drop a bad plan, without flushing the whole shared pool.

Oracle: ASH & AWR

• Run an AWR report (one instance or all)Oracle's AWR report scripts. The first reports on the instance you're connected to; the second is the global report across all RAC instances. Each…
• Top SQL in the last hour (ASH)Statements with the most sampled activity in the last hour, split into CPU, user I/O and other waits. Each sample is roughly one second of database…
• Top wait events in the last hour (ASH)Where the database spent its time over the last hour, with CPU counted as its own line.
• What was running between two times (AWR ASH)For "it was slow at 2 a.m." questions. Prompts for a start and end time (YYYY-MM-DD HH24:MI) and ranks SQL and events from AWR's history, where each…
• AWR settings and recent snapshotsSnapshot interval and retention, then every snapshot from the last 24 hours. You need the snapshot IDs to run an AWR report.
• Generate AWR, ASH, ADDM and SQL reportsOracle's own report scripts. Each one prompts for the format, snapshot range and file name, then writes the report to your current directory.
• Take an AWR snapshot nowTakes a manual snapshot so an AWR report can cover exactly the window of a test or a batch run. Take one before and one after.

Oracle: Storage

• Datafile usage with a bar graphEvery datafile with its size, used space, max size and a ten-character usage bar (X = used, - = free). Free_MB is the largest free extent in the…
• Database size three ways: allocated, used and totalAllocated datafile size, space actually used by segments, and the overall footprint including temp files, online redo logs and control files. All in…
• Database size summary: total, used and freeOne line with the database name and its total size (datafiles, temp files and redo logs), used space and free space, rounded to whole GB.
• Size of one schemaTotal segment size in GB for the schema you enter.
• Tablespace usageUsed and free space in GB against the maximum each tablespace can autoextend to, with its type (permanent, temporary or undo), fullest first.
• Datafiles with size and autoextend limitsEvery datafile, its current size, whether it can grow, and how far. Files with AUTOEXTEND NO in a busy tablespace are the ones that page you at night.
• Top 20 largest segmentsThe biggest tables, indexes, LOBs and partitions in the database.
• Space used per schemaTotal segment size per owner, largest first.
• How far each datafile can be shrunkCompares each file's size with its high-water mark to show space you can give back with a RESIZE. Reads DBA_EXTENTS, so it can take a while on large…
• Add space: resize or add a datafileTemplates for the three usual fixes. The resize runs as written; the other two are commented out. Change names and sizes, and uncomment the one you…

Oracle: Temp & undo

• Temporary tablespace usageSize, allocated and free space for each temporary tablespace.
• Sessions using temp spaceWho is using temp right now and for what (sort, hash, LOB), largest first. Run it when you see ORA-01652.
• Undo usage and ORA-01555 riskUndo extents by status, then the longest query and any snapshot-too-old or out-of-space errors from V$UNDOSTAT (roughly the last four days).
• Open transactions and the undo they holdEvery open transaction with its session, undo size and start time. An old transaction from an idle session is usually someone who forgot to commit.

Oracle: Redo & archiving

• Online redo log groups and membersEvery redo log group with its thread, sequence, archived flag, status, member file and size. More than one CURRENT per thread, or members missing…
• Log switches per hour, last 7 daysA day-by-hour grid of log switches. Aim for roughly four an hour at peak; consistently more means the redo logs are too small.
• Archived redo generated per dayCount and size of archived logs for the last 14 days, from the local destination only so standby copies aren't double-counted. Useful for sizing the…
• Redo generated per hourActual redo volume per hour for the last 7 days, from archived log sizes on the local destination, so early log switches and Data Guard copies don't…
• Sessions generating the most redoConnected sessions ranked by redo generated since logon. Finds the job behind a sudden flood of archive logs.

Oracle: Backup & recovery

• RMAN backup history with run time in hoursRun in the target database as SYSDBA, not the recovery catalog. The first report lists every RMAN job (full, incremental and archivelog) with start,…
• RMAN backup jobs in the last 7 daysStatus, duration and output size of each RMAN job, newest first.
• Running RMAN job progressPercent complete and time remaining for each RMAN channel that's working right now.
• Datafiles without a recent backupDatafiles with no backup of any kind in the last two days, or none at all. A new datafile added after the last full backup shows up here first.
• RMAN configuration (non-default settings)The persistent CONFIGURE settings stored in the control file, the same list SHOW ALL marks as changed. Check retention and control file autobackup…
• Fast Recovery Area usageFRA size, used and reclaimable space, then what's using it by file type. Real used % is the number that matters: at 100% the database stops archiving…
• Flashback status and restore pointsWhether flashback is on, how far back you can go, and every restore point. A forgotten guaranteed restore point will keep filling the FRA until it's…
• Guaranteed restore point for a releaseCreate one before a risky change so you can flash the whole database back. Drop it as soon as the change is signed off.
• Corrupt blocks found by RMANBlocks RMAN has flagged as corrupt. The view is filled by backups and by RMAN VALIDATE, so run a validate first if you haven't recently.

Oracle: Data Guard

• Transport and apply lagHow far behind the standby is in receiving and applying redo. A growing apply lag with no transport lag points at the apply process, not the network.
• Redo apply and transport processesWhich Data Guard processes are running and what they're doing. No MRP0 row means redo is arriving but not being applied. Before 12.2, query…
• Redo destinations and transport errorsEvery active archive destination with its status, gap state and last error. Any text in the ERROR column means redo isn't reaching that standby.
• Archive gaps and last applied sequenceMissing log sequences the standby is waiting for, then the last applied sequence per thread. Compare with the current sequence on the primary.
• Data Guard errors and warnings, last 24 hoursMessages Data Guard wrote to V$DATAGUARD_STATUS, filtered to warnings and errors. Run on both sides.
• Standby redo log configurationStandby redo logs by thread. You want one more group per thread than you have online log groups, all the same size as the online logs.
• Broker health checks (DGMGRL)The broker's own view of the configuration. VALIDATE DATABASE is the best single readiness check before a switchover.

Oracle: RAC & ASM

• Stop, start and check a RAC database (srvctl)Clusterware commands to stop the database, start it read-only, check its status, and start a single instance in MOUNT mode. Run one line at a time;…
• ASM disk group spaceSize, free and usable space per disk group. Usable GB already accounts for mirroring, so it's the number to alert on. Works from the ASM or database…
• ASM disks and their statusEvery disk ASM can see, including CANDIDATE disks not yet in a group. Check MODE and STATE after storage maintenance.
• ASM rebalance progressRebalance operations in flight with power level and estimated minutes left. No rows means nothing is rebalancing.
• Sessions per instance and serviceHow connections are spread across RAC nodes and services. A lopsided spread usually means a service isn't running where you think it is.
• Global cache (interconnect) waits by instanceCluster-class waits per instance since startup. High average times on gc cr or gc current events point at the interconnect or at hot blocks shared…
• Cluster status commands (srvctl and crsctl)The Clusterware commands for checking a RAC database, its services and every cluster resource.

Oracle: Multitenant

• Pluggable databases and their stateEvery PDB with its open mode, whether it's restricted, when it opened and its size. RESTRICTED = YES usually means a plug-in violation.
• Tablespace usage across all PDBsThe tablespace report for every container at once, fullest first, so you don't have to switch into each PDB.
• Unresolved plug-in violationsWhy a PDB opened in restricted mode: patch mismatches, parameter conflicts and missing options, with Oracle's suggested fix in the message.
• Open PDBs and keep them open after restartOpens every PDB, then saves the state so they reopen automatically when the CDB restarts. The first query shows what's currently saved.

Oracle: Statistics

• Tables and partitions with stale statisticsApplication objects the optimizer considers stale. The flush at the top makes sure recent DML is counted before the check.
• Tables that have never been analyzedApplication tables with no statistics at all, which forces dynamic sampling or guesswork. Locked statistics are shown so you know which ones are…
• Automatic statistics job status and historyWhether the nightly optimizer stats job is enabled, and how each run in the last week went. A run that keeps hitting the window end isn't finishing…
• Gather statistics for a table or schemaTemplates for gathering stats by hand. New statistics can change execution plans, so do this at a quiet time and know which plans matter.

Oracle: Objects & schema

• Generate DDL for a table and an indexUses DBMS_METADATA to write the CREATE statements for a table and one of its indexes into ddl_list.sql in your current directory. Prompts for the…
• Invalid objectsEvery invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.
• Recompile invalid objectsRecompile one schema, or everything in the database with Oracle's utlrp script. Run the invalid objects check again afterwards.
• Unusable indexes and index partitionsIndexes marked UNUSABLE, typically after a partition operation or a direct-path load. Queries that need them will either fail or fall back to full…
• Foreign keys that are probably not indexedForeign keys whose columns aren't the leading columns of an index. Unindexed foreign keys cause table-level locks when the parent row is updated or…
• Disabled constraints and triggersConstraints and triggers someone switched off, often for a data load, and never switched back on.
• Objects changed in the last 24 hoursApplication objects with DDL in the last day. Grants also update LAST_DDL_TIME, so not every row is a code change.
• Recycle bin contents by ownerDropped objects still taking up space. Oracle reclaims it under space pressure, but a large recycle bin can make tablespace reports look worse than…

Oracle: Security & users

• Accounts locked, expired or expiring soonApplication accounts that aren't OPEN, or whose passwords expire in the next 14 days. Catch service accounts before they break an application.
• Who has DBA, SYSDBA and powerful system privilegesNon-Oracle accounts and roles holding the DBA role or high-risk ANY privileges, then everyone in the password file. Worth reviewing every audit cycle.
• CMU step 1: prepare Active DirectoryDone once by an AD administrator before any database work: create the service account the database binds with, install Oracle's password filter on…
• CMU step 2: export the AD root certificateThe database talks to AD over LDAPS, so its wallet must trust the certificate authority that issued the domain controllers' certificates. Export that…
• CMU step 3: create the walletBuilds the auto-login wallet the database reads at login: the service account's user name, DN and password, plus the AD root certificate. With PDBs,…
• CMU step 4: create dsi.oraTells the database which domain controllers to use. Put it in the same folder as the wallet from step 3. Use fully qualified host names, and list at…
• CMU step 5: point each PDB at its wallet (CMU_WALLET)Creates a directory object for the wallet folder from step 3 and sets the CMU_WALLET database property in the PDB, so CMU reads that PDB's wallet and…
• CMU step 6: turn on directory accessSwitches the database to Active Directory for global users. In a CDB, run it in each PDB that uses CMU, not in the root: setting it in the root only…
• CMU step 7: map AD users and groupsCreates database users and roles tied to AD, the way Oracle's 19c guide recommends: users log in through a shared schema mapped to an AD group, which…
• CMU step 8: check the setup and a loginShows the LDAP parameters, the CMU_WALLET location, the users and roles mapped to AD, and who the current session really is. Run it once as a DBA,…
• Roles granted to each user, by how they log inEvery role granted to each account that isn't Oracle-maintained, grouped by authentication type: PASSWORD, GLOBAL (directory users, such as through…
• All privileges for one userPrompts for a username and lists the roles, system privileges and object privileges granted directly to it.
• Failed logins in the last 24 hoursFrom the unified audit trail, which records failed logons by default through the ORA_LOGON_FAILURES policy. Return code 1017 is a wrong password;…
• Password profile settingsPassword rules for each profile: lifetime, reuse, failed attempts and the verify function. Shows why an account locked or expired.
• Unlock an account or reset its passwordChange the username and password before running. Quote the password so special characters are kept.

Oracle: Scheduler & jobs

• Scheduler job failures in the last 24 hoursEvery job run that didn't succeed, with the error number and the start of the error text.
• Scheduler jobs running nowJobs currently executing, the session running each one and how long it has been going.
• Application scheduler jobs and next runNon-Oracle scheduler jobs with their state, last and next run, and failure count. Disabled or BROKEN jobs stand out at a glance.
• Legacy DBMS_JOB jobsOld-style jobs, which still turn up in upgraded databases. From 19c they're implemented as scheduler jobs underneath, but DBA_JOBS still shows them…

Oracle: Maintenance

• Rotate the listener logTurns listener logging off, lets you rename the current listener.log, then turns logging back on so a fresh file starts. Run it from the OS prompt as…

Oracle: Diagnostics

• Alert log errors in the last 24 hoursORA- and TNS- errors plus checkpoint warnings from the alert log, read through SQL. It can be slow when the alert log is very large.
• Where the alert log and trace files areThe ADR home, alert log directory, trace directory and this session's own trace file.
• Trace another session with waits and bindsPrompts for a SID and serial number, turns on extended SQL trace, and shows the trace file name. Run the disable line when you've captured enough,…
• Review problems and incidents (ADRCI)ADRCI commands for critical errors like ORA-00600 and ORA-07445, and for packaging an incident to send to Oracle Support.

SQL Server: Sessions

• Currently running requestsEvery user request executing right now, with waits, blocker and the exact statement being run. First stop when someone says the server is slow.
• Kill a sessionEnds a session and rolls back its open transaction. A large rollback can take as long as the work did; check progress with KILL <id> WITH STATUSONLY.

SQL Server: Blocking & locks

• Blocking chainWho is blocked, by whom, for how long, and the last statement the blocker ran. A blocker whose SQL looks finished is usually an open transaction.

SQL Server: Performance

• Top 20 statements by CPUCached statements ranked by total CPU since they entered the plan cache. Compare avg_cpu_ms with execution_count to separate one heavy query from a…
• Top wait typesWhere the instance spends its time waiting since the last restart, with benign background waits filtered out. High signal wait share points at CPU…

SQL Server: Storage

• Data and log file size and free spaceSize, free space, growth setting and max size for each file in the current database. Run it in the database you're checking.
• Transaction log usage and why it can't truncateLog size and percent used for every database, then the reason each log can't be reused. LOG_BACKUP means log backups aren't running.

SQL Server: Backup & recovery

• Last full, differential and log backup per databaseReads backup history from msdb. Databases in FULL recovery with no recent log backup are the ones to chase first.

SQL Server: Indexes

• Missing index suggestionsThe optimizer's own index wish list, ranked by estimated benefit. Treat as leads, not instructions: overlapping suggestions are common.
• Fragmented indexes in the current databaseIndexes over 30% fragmented and larger than 1,000 pages, where a rebuild is worth considering. LIMITED mode keeps the scan cheap.

SQL Server: Security & users

• Members of the sysadmin roleEvery login with full control of the instance. Worth reviewing on a schedule and after any access request.

SQL Server: Scheduler & jobs

• SQL Agent job failures in the last 24 hoursFailed job steps with their error message, newest first. Good for a morning check.

PostgreSQL: Sessions

• Active queriesEvery non-idle backend with what it's waiting on and how long it's been running.
• Long-running and idle-in-transaction sessionsTransactions open longer than five minutes. Idle-in-transaction sessions hold locks and stop vacuum from cleaning up.
• Cancel a query or terminate a sessionTry cancel first: it stops the current statement and leaves the connection open. Terminate ends the backend and rolls back its transaction.

PostgreSQL: Blocking & locks

• Blocked and blocking sessionsPairs each waiting backend with the backend holding the lock it needs, using pg_blocking_pids (9.6+).

PostgreSQL: Performance

• Top statements by total timeNeeds the pg_stat_statements extension. Column names are for PostgreSQL 13 and later; on 12 and earlier use total_time and mean_time.
• Buffer cache hit ratio per databaseShare of block reads served from shared buffers. OLTP databases usually sit above 99%; a drop suggests memory pressure or a new large scan.

PostgreSQL: Storage

• Database sizesOn-disk size of every non-template database, largest first.
• Largest tables with index sizeTop 20 tables in the current database by total size, split into heap and index.

PostgreSQL: Replication

• Replication lag (run on the primary)Each connected standby with how far behind it is, in bytes and in time.

PostgreSQL: Indexes

• Unused indexesNon-unique indexes never scanned since statistics were last reset. Check replicas too before dropping, since stats are per server.

PostgreSQL: Security & users

• Superusers and role creatorsRoles with superuser or the ability to create other roles.

PostgreSQL: Maintenance

• Dead tuples and last vacuumTables with the most dead rows and when they were last vacuumed and analyzed. A high dead_pct with an old last_autovacuum needs attention.

MySQL: Sessions

• Running threadsAll non-sleeping connections, longest running first.
• Kill a query or connectionKILL QUERY stops the statement and keeps the connection; KILL closes the connection and rolls back its transaction.

MySQL: Blocking & locks

• InnoDB lock waitsUses the sys schema (5.7 and 8.0). sql_kill_blocking_query gives you the ready-made statement to stop the blocker.

MySQL: Performance

• Top statements by total latencyFrom the sys schema, which already sorts this view by total latency. Needs performance_schema enabled.

MySQL: Storage

• Database sizesData plus index size for each schema. Figures come from table statistics, so they're estimates.
• Largest tablesTop 20 user tables with data, index and reclaimable free space.

MySQL: Replication

• Replica status (run on the replica)Check Replica_IO_Running, Replica_SQL_Running and Seconds_Behind_Source. SHOW REPLICA STATUS needs 8.0.22+; older versions use SHOW SLAVE STATUS.

MySQL: Indexes

• Unused indexesIndexes with no recorded reads since the server started. Only meaningful after a representative period of uptime.

MySQL: Security & users

• Accounts with SUPER or GRANT privilegeAccounts that can administer the server or hand out privileges, plus whether they're locked.