Oracle DBA SQL Queries & Scripts

A comprehensive collection of Oracle DBA SQL queries and scripts for database administration, monitoring, performance tuning, troubleshooting, RAC, ASM, Data Guard, RMAN, GoldenGate, and production database management.

Oracle Database Information

  • Check Oracle Database Name
  • Check Oracle Database Version
  • Check Oracle Release and Edition
  • Check Oracle Database Status
  • Check Database Open Mode
  • Check Database Role
  • Check Database ID and DBID
  • Check Database Creation Date
  • Check Database Platform
  • Check Oracle Home
  • Check Oracle Instance Name
  • Check Oracle Instance Status

Oracle Tablespace Queries and Scripts

  • Check tablespace usage
  • Check tablespace free space
  • Check tablespace percentage used
  • Find tablespaces above 80%
  • Find tablespaces above 90%
  • Find tablespaces above 95%
  • Find tablespaces with low free space
  • Check tablespace status
  • Check tablespace type
  • Check locally managed tablespaces
  • Check dictionary managed tablespaces
  • Check permanent tablespaces
  • Check temporary tablespaces
  • Check undo tablespaces
  • Check read-only tablespaces
  • Check offline tablespaces
  • Check bigfile tablespaces
  • Check autoextend status
  • Check maximum datafile size
  • Find datafiles for a tablespace
  • Find tablespace for a datafile
  • Add datafile
  • Resize datafile
  • Enable autoextend
  • Disable autoextend
  • Check datafile growth
  • Generate datafile resize commands
  • Generate add-datafile commands

Oracle Instance & Server Queries

  • Check instance status
  • Check instance name
  • Check hostname
  • Check Oracle home
  • Check Oracle SID
  • Check startup time
  • Check instance uptime
  • Check number of sessions
  • Check active sessions
  • Check inactive sessions
  • Check background processes
  • Check Oracle processes
  • Check process utilization
  • Check session utilization
  • Check resource limits
  • Check CPU-related statistics
  • Check server information

Oracle User & Schema Queries

  • List all database users
  • Check user status
  • Find locked users
  • Find expired users
  • Find expired & locked users
  • Check user creation date
  • Check last login
  • Check default tablespace
  • Check temporary tablespace
  • Check user profile
  • Check user quota
  • Check schema size
  • Find largest schemas
  • Find objects owned by a schema
  • Find invalid objects by schema
  • Find users with DBA privileges
  • Find users with system privileges
  • Find users with object privileges
  • Find roles assigned to users
  • Find privileges granted through roles

Datafile Queries and Scripts

  • List all datafiles
  • Check datafile size
  • Check datafile status
  • Check datafile location
  • Check autoextend
  • Check maximum size
  • Find largest datafiles
  • Find datafiles with autoextend enabled
  • Find datafiles nearing maximum size
  • Check datafile offline/online status
  • Generate datafile resize commands
  • Generate autoextend commands
  • Generate datafile addition commands

TEMP Tablespace Queries and Scripts

  • Check TEMP usage
  • Check TEMP free space
  • Find TEMP-consuming sessions
  • Find sessions using TEMP
  • Find SQL consuming TEMP
  • Check TEMP files
  • Check TEMP file size
  • Check TEMP autoextend
  • Find TEMP space usage by session
  • Find TEMP usage by SQL
  • Generate TEMPFILE resize commands
  • Add TEMPFILE
  • Resize TEMPFILE

UNDO Queries and Scripts

  • Check undo tablespace
  • Check undo usage
  • Check undo free space
  • Check active undo segments
  • Check undo retention
  • Check UNDO_RETENTION
  • Check undo transactions
  • Find sessions consuming undo
  • Find large transactions
  • Find long-running transactions
  • Check undo statistics
  • Investigate ORA-01555
  • Find SQL causing excessive undo
  • Check undo segment status

Redo Log Queries and Scripts

  • List redo log groups
  • Check redo log status
  • Check redo log size
  • Check redo log members
  • Check redo log sequence
  • Check current redo log
  • Find inactive redo logs
  • Find active redo logs
  • Find redo log members
  • Check redo log multiplexing
  • Check redo log switch frequency
  • Find frequent log switches
  • Add redo log group
  • Add redo log member
  • Drop redo log group
  • Drop redo log member
  • Resize redo logs
  • Generate redo log commands

Archive Log Queries and Scripts

  • Check archive log mode
  • Check archive destinations
  • Check archive destination status
  • Check archive destination errors
  • Check archive log sequences
  • Check latest archived log
  • Check archive log generation
  • Find archive gaps
  • Check archive process status
  • Check archive destination usage
  • Check archive log retention
  • Find missing archive logs
  • Find archive logs by sequence
  • Check archived logs by thread
  • Generate archive log cleanup commands

Session Queries

  • List all sessions
  • List active sessions
  • List inactive sessions
  • Find sessions by username
  • Find sessions by machine
  • Find sessions by program
  • Find sessions by SQL ID
  • Find sessions by SID
  • Find sessions by serial#
  • Find long-running sessions
  • Find idle sessions
  • Find blocked sessions
  • Find blocking sessions
  • Find sessions waiting
  • Find sessions by event
  • Find sessions by module
  • Find sessions by service
  • Find sessions by OS user
  • Find sessions connected from a particular machine
  • Find sessions consuming CPU
  • Find sessions consuming TEMP
  • Find sessions consuming undo

Blocking & Lock Queries

  • Find blocking sessions
  • Find blocked sessions
  • Find blocker and waiter
  • Find locked objects
  • Find sessions locking tables
  • Find DML locks
  • Find DDL locks
  • Find row-level locks
  • Find enqueue waits
  • Find lock type
  • Find lock mode
  • Find blocking SID
  • Find blocking SQL
  • Find blocked SQL
  • Find blocking session for a table
  • Generate ALTER SYSTEM KILL SESSION
  • Generate ALTER SYSTEM DISCONNECT SESSION
  • Find historical blocking sessions

SQL Performance Queries and Scripts

  • Find currently running SQL
  • Find expensive SQL
  • Find top SQL by CPU
  • Find top SQL by elapsed time
  • Find top SQL by executions
  • Find top SQL by logical reads
  • Find top SQL by physical reads
  • Find top SQL by buffer gets
  • Find SQL consuming TEMP
  • Find SQL consuming PGA
  • Find SQL with high execution count
  • Find long-running SQL
  • Find SQL by SQL ID
  • Find SQL by username
  • Find SQL by module
  • Find SQL by machine
  • Find SQL text
  • Find SQL plan hash value
  • Find SQL execution statistics
  • Find SQL causing high CPU
  • Find SQL causing high I/O
  • Find SQL with full table scans
  • Find SQL with poor performance
  • Find invalid SQL
  • Generate SQL tuning queries

Database Performance Queries

  • Check database performance
  • Check database CPU
  • Check database I/O
  • Check logical reads
  • Check physical reads
  • Check buffer cache hit ratio
  • Check library cache statistics
  • Check parse statistics
  • Check hard parses
  • Check soft parses
  • Check redo generation
  • Check transaction rate
  • Check commits
  • Check rollbacks
  • Check wait events
  • Check top wait events
  • Check system statistics
  • Check database load
  • Check resource utilization

Execution Plan Queries and Scripts

  • Display execution plan
  • Display SQL execution plan by SQL ID
  • Find plan hash value
  • Compare plan hash values
  • Find changed execution plans
  • Find SQL with multiple plans
  • Find SQL using full table scan
  • Find SQL using index scan
  • Find expensive execution plans
  • Find SQL plan statistics
  • Check estimated vs actual rows
  • Find optimizer cost
  • Find SQL plan history
  • Generate DBMS_XPLAN scripts

Memory Parameters Queries

  • SGA
  • Check SGA size
  • Check SGA components
  • Check buffer cache
  • Check shared pool
  • Check large pool
  • Check Java pool
  • Check streams pool
  • Check SGA target
  • Check SGA max size
  • PGA
  • Check PGA usage
  • Check PGA target
  • Check PGA aggregate limit
  • Check PGA over-allocation
  • Find sessions consuming PGA
  • Find top PGA consumers

Database Object Queries

  • List tables
  • List indexes
  • List views
  • List sequences
  • List synonyms
  • List procedures
  • List functions
  • List packages
  • List triggers
  • List materialized views
  • List database links
  • List directories
  • List types
  • List object dependencies
  • Find invalid objects
  • Count objects by schema
  • Find recently created objects
  • Find recently modified objects

Invalid Object Queries

  • Find invalid objects
  • Find invalid objects by schema
  • Find invalid packages
  • Find invalid procedures
  • Find invalid functions
  • Find invalid views
  • Find invalid triggers
  • Generate recompilation commands
  • Recompile schema
  • Recompile database
  • Check compilation errors
  • Find dependent invalid objects

Oracle Index Queries

  • List indexes
  • Check index status
  • Find unusable indexes
  • Find invalid indexes
  • Find indexes by table
  • Find indexes by schema
  • Find duplicate indexes
  • Find redundant indexes
  • Find large indexes
  • Find index size
  • Find index columns
  • Find index type
  • Find function-based indexes
  • Find invisible indexes
  • Find unused indexes
  • Rebuild index
  • Generate rebuild commands

Oracle Table Queries

  • List tables
  • Find table size
  • Find largest tables
  • Find tables by schema
  • Find recently created tables
  • Find partitioned tables
  • Find tables without indexes
  • Find tables with high growth
  • Find table statistics
  • Find stale statistics
  • Find table row count
  • Generate table size report

Table Partition Queries

  • List partitioned tables
  • List table partitions
  • Check partition size
  • Find largest partitions
  • Find partition status
  • Find high-value partition
  • Find partition growth
  • Check partition indexes
  • Check local indexes
  • Check global indexes
  • Find unusable partition indexes
  • Generate partition maintenance commands

Oracle Statistics Queries

  • Check optimizer statistics
  • Find stale statistics
  • Find tables without statistics
  • Find indexes without statistics
  • Check statistics gathering date
  • Find recently analyzed tables
  • Find stale object statistics
  • Generate statistics gathering commands
  • Gather table statistics
  • Gather schema statistics
  • Gather database statistics
  • Check auto statistics job

Oracle Database Links

  • List database links
  • Check database link status
  • Find database links by schema
  • Test database link
  • Find public database links
  • Find private database links
  • Generate database link scripts

Scheduler & Jobs

  • List DBMS Scheduler jobs
  • Check running jobs
  • Check failed jobs
  • Check disabled jobs
  • Check job history
  • Find long-running jobs
  • Find failed scheduler jobs
  • Check job next run time
  • Check job owner
  • Check job action
  • Generate job enable commands
  • Generate job disable commands
  • Generate job execution commands

Privileges & Security Queries

  • List system privileges
  • List object privileges
  • List role privileges
  • Find DBA users
  • Find users with SELECT ANY TABLE
  • Find users with powerful privileges
  • Find privileges granted to PUBLIC
  • Find roles
  • Find users assigned to roles
  • Find direct privileges
  • Find privilege hierarchy
  • Audit user privileges
  • Generate privilege reports

Password & Profile Queries

  • List profiles
  • Check password policies
  • Check password lifetime
  • Check failed login attempts
  • Check password lock time
  • Find users with default profiles
  • Find users with expired passwords
  • Find locked users
  • Find expired users
  • Generate password/profile audit report

Alert Log / Diagnostic Queries

  • Check diagnostic destination
  • Check ADR information
  • Find alert log location
  • Query alert log
  • Find ORA errors
  • Find ORA-00600
  • Find ORA-07445
  • Find ORA-01555
  • Find ORA-04031
  • Find ORA-00060
  • Find database errors
  • Find recent errors
  • Find incident information

Database Parameter Queries

  • Show all parameters
  • Find parameter value
  • Find modified parameters
  • Find hidden parameters
  • Find parameters changed from default
  • Find memory parameters
  • Find optimizer parameters
  • Find archive parameters
  • Find Data Guard parameters
  • Find RAC parameters
  • Find audit parameters
  • Generate parameter report

Control File Queries

  • List control files
  • Check control file status
  • Check control file location
  • Check control file records
  • Check control file multiplexing
  • Find control file size
  • Generate control-file information report

SPFILE / PFILE Queries

  • Check SPFILE location
  • Check whether database uses SPFILE
  • Display SPFILE parameters
  • Compare SPFILE and PFILE
  • Generate PFILE from SPFILE
  • Generate SPFILE from PFILE
  • Find changed initialization parameters

Flashback Queries

  • Check Flashback Database status
  • Check Flashback logs
  • Check FRA usage
  • Check flashback retention
  • Find guaranteed restore points
  • Find restore points
  • Create restore point
  • Drop restore point
  • Check SCN
  • Check timestamp-to-SCN
  • Check SCN-to-timestamp

Fast Recovery Area or FRA Queries

  • Check FRA size
  • Check FRA usage
  • Find FRA percentage used
  • Find FRA files
  • Check recovery area
  • Find archive logs consuming FRA
  • Find flashback logs consuming FRA
  • Find backup files consuming FRA
  • Generate FRA usage report

RMAN-Related SQL Queries

  • Check RMAN configuration
  • Check backup status
  • Check backup pieces
  • Check backup sets
  • Check backup dates
  • Find failed backups
  • Find running RMAN jobs
  • Find RMAN sessions
  • Find backup duration
  • Find backup size
  • Find obsolete backups
  • Find expired backups
  • Check backup retention
  • Check backup location
  • Check archive backup status
  • Generate RMAN backup commands

Data Guard SQL Queries

  • Check database role
  • Check primary/standby status
  • Check Data Guard configuration
  • Check transport status
  • Check apply status
  • Check redo transport
  • Check archive destination
  • Check archive gap
  • Check apply lag
  • Check transport lag
  • Check standby lag
  • Check MRP status
  • Check RFS status
  • Check managed recovery
  • Check standby database status
  • Check standby redo logs
  • Check broker status
  • Check Data Guard errors
  • Find missing archive logs
  • Generate Data Guard monitoring report

RAC DBA SQL Queries

  • Check RAC instances
  • Check instance status
  • Check RAC services
  • Check RAC sessions
  • Check GV$ views
  • Check instance distribution
  • Find sessions by instance
  • Find SQL by instance
  • Find blocking sessions across RAC
  • Find global locks
  • Check GC waits
  • Check interconnect
  • Check RAC services
  • Check service distribution
  • Check instance load
  • Check RAC database status
  • Check cluster-related database information

ASM SQL Queries

  • Check ASM instances
  • List ASM diskgroups
  • Check diskgroup usage
  • Check diskgroup free space
  • Check ASM disks
  • Check ASM disk status
  • Check ASM disk paths
  • Find failed ASM disks
  • Find offline disks
  • Check ASM redundancy
  • Check ASM allocation unit
  • Check ASM rebalance
  • Check ASM clients
  • Check ASM diskgroup attributes
  • Check ASM files
  • Find ASM space usage
  • Generate ASM monitoring report

Oracle Audit Queries

  • Check auditing status
  • Check audit policies
  • Find audited users
  • Find login audit records
  • Find failed login attempts
  • Find DDL audit records
  • Find DML audit records
  • Find privileged activity
  • Find audit trail size
  • Find Unified Audit records
  • Find audit policy status
  • Generate audit reports

Oracle Database Growth Queries

  • Database size
  • Database growth
  • Monthly database growth
  • Daily database growth
  • Tablespace growth
  • Schema growth
  • Table growth
  • Index growth
  • Datafile growth
  • Archive log growth
  • Segment growth
  • Find fastest-growing tables
  • Find fastest-growing schemas
  • Generate database growth report

Top / Largest Object Queries

  • Top 10 largest tables
  • Top 20 largest tables
  • Largest indexes
  • Largest schemas
  • Largest segments
  • Largest tablespaces
  • Largest datafiles
  • Largest partitions
  • Top CPU SQL
  • Top I/O SQL
  • Top TEMP-consuming SQL
  • Top PGA-consuming sessions
  • Top undo-consuming sessions
  • 39. Long-Running Queries
  • Find long-running sessions
  • Find long-running SQL
  • Find SQL running for more than 1 hour
  • Find SQL running for more than 2 hours
  • Find long-running transactions
  • Find long-running RMAN jobs
  • Find long-running Data Pump jobs
  • Find long-running Scheduler jobs

Data Pump Queries & Scripts

  • Check Data Pump jobs
  • Check Data Pump sessions
  • Check Data Pump job status
  • Find failed Data Pump jobs
  • Find running Data Pump jobs
  • Generate expdp commands
  • Generate impdp commands
  • Generate schema export commands
  • Generate table export commands
  • Generate full database export commands
  • Generate metadata-only export
  • Generate data-only export
  • Generate remap-schema commands
  • Generate remap-tablespace commands

Database Startup & Shutdown Scripts

  • Startup database
  • Shutdown database
  • Startup mount
  • Startup nomount
  • Open database
  • Open read-only
  • Open restricted
  • Shutdown immediate
  • Shutdown transactional
  • Shutdown abort
  • Check startup status

Common DBA Daily Health Check Scripts

  • One script can report:
  • Database Status
  • Instance Status
  • Database Role
  • Database Version
  • Tablespace Usage
  • TEMP Usage
  • UNDO Usage
  • FRA Usage
  • Archive Destination
  • Redo Log Status
  • Invalid Objects
  • Blocking Sessions
  • Active Sessions
  • Long Running SQL
  • Top CPU SQL
  • Top I/O SQL
  • Failed Jobs
  • RMAN Backup Status
  • Data Guard Status
  • ASM Status
  • Alert Errors

Oracle DBA Troubleshooting Scripts

  • Tablespace full script
  • TEMP full script
  • UNDO full script
  • FRA full script
  • Blocking session script
  • Deadlock investigation script
  • High CPU investigation script
  • High I/O investigation script
  • Long-running SQL script
  • High TEMP usage script
  • High PGA usage script
  • High SGA usage script
  • Invalid object script
  • Failed job script
  • Archive gap script
  • Data Guard lag script
  • ASM space script
  • RMAN backup failure script
  • Database health-check script

Database Startup / Shutdown

  • Check whether database is running
  • Check database startup time
  • Check instance startup time
  • Check database open mode
  • Check restricted mode
  • Check mount status
  • Check read-only status
  • Check database role
  • Check instance status
  • Check all RAC instances
  • Check database services
  • Check startup parameters
  • Check shutdown status
  • Check last database restart
  • Find unexpected database restarts

Oracle Version & Patch Queries

  • Check Oracle database version
  • Check Oracle release
  • Check Oracle edition
  • Check Oracle home
  • Check Oracle inventory
  • Check OPatch version
  • Check installed patches
  • Check SQL patch registry
  • Check Java component version
  • Check database component versions
  • Check invalid components
  • Check PSU/RU information
  • Find latest applied RU
  • Compare database patch levels
  • Generate Oracle version report

Oracle Home & Environment

  • Check ORACLE_HOME
  • Check ORACLE_SID
  • Check Oracle base
  • Check inventory location
  • Check database home
  • Check listener home
  • Check environment variables
  • Check Oracle binary location
  • Check SQL*Plus version
  • Check TNS_ADMIN
  • Check ADR home
  • Check diagnostic destination

Oracle Database Configuration

  • List database parameters
  • Search specific parameter
  • Find parameters changed from default
  • Find hidden parameters
  • Find deprecated parameters
  • Find obsolete parameters
  • Find static parameters
  • Find dynamic parameters
  • Find modified parameters
  • Find parameters requiring restart
  • Compare parameters between databases
  • Generate parameter report
  • Generate initialization parameter script

NLS Queries

  • Check NLS database settings
  • Check NLS instance settings
  • Check NLS session settings
  • Check NLS date format
  • Check NLS timestamp format
  • Check NLS language
  • Check NLS territory
  • Check character set
  • Check national character set
  • Find sessions with different NLS settings

Character Set Queries

  • Check database character set
  • Check national character set
  • Check character set parameters
  • Find character columns
  • Find NVARCHAR2 columns
  • Find CLOB columns
  • Find potential character conversion issues
  • Check character set compatibility

Schema Size & Growth

  • Find schema size
  • Find largest schemas
  • Find smallest schemas
  • Find schema growth
  • Find schema object count
  • Find schema segment count
  • Find schema tables
  • Find schema indexes
  • Find schema LOBs
  • Find schema partitions
  • Find schema growth by month
  • Generate schema size report

Oracle Segment Queries

  • Find largest segments
  • Find segments by schema
  • Find segments by tablespace
  • Find table segments
  • Find index segments
  • Find LOB segments
  • Find partition segments
  • Find temporary segments
  • Find segment size
  • Find segment growth
  • Find segment type
  • Find segments consuming most space

Oracle LOB Queries

  • Find LOB columns
  • Find LOB segments
  • Find largest LOBs
  • Find LOB size
  • Find LOB tablespace
  • Find SecureFile LOBs
  • Find BasicFile LOBs
  • Find LOB indexes
  • Find LOB storage settings
  • Find LOB growth
  • Find tables with large LOBs

Oracle Constraint Queries

  • List primary keys
  • List foreign keys
  • List unique constraints
  • List check constraints
  • Find disabled constraints
  • Find invalid constraints
  • Find constraints by table
  • Find constraints by schema
  • Find foreign-key relationships
  • Find tables without primary keys
  • Find duplicate constraints

Oracle Trigger Queries

  • List triggers
  • Find disabled triggers
  • Find invalid triggers
  • Find triggers by schema
  • Find triggers by table
  • Find trigger status
  • Find trigger creation date
  • Find trigger source
  • Generate enable-trigger commands
  • Generate disable-trigger commands

Oracle View Queries

  • List views
  • Find invalid views
  • Find views by schema
  • Find views referencing a table
  • Find tables referenced by a view
  • Find view definitions
  • Find dependent views
  • Find views with compilation errors

Oracle Package / Procedure / Function Queries

  • List packages
  • List procedures
  • List functions
  • Find invalid packages
  • Find invalid procedures
  • Find invalid functions
  • Find package source
  • Find procedure source
  • Find function source
  • Find object dependencies
  • Find objects referenced by packages
  • Find packages referencing tables
  • Find recently modified PL/SQL objects

Oracle Dependency Queries

  • Find object dependencies
  • Find dependent objects
  • Find objects referenced by a table
  • Find objects referencing a table
  • Find package dependencies
  • Find procedure dependencies
  • Find view dependencies
  • Find trigger dependencies
  • Find invalid dependency chains
  • Generate dependency report

Oracle Sequence Queries

  • List sequences
  • Find sequence current value
  • Find sequence increment
  • Find sequence cache
  • Find sequence cycle
  • Find sequence maximum value
  • Find sequences nearing maximum value
  • Find sequences by schema
  • Find recently created sequences

Oracle Synonym Queries

  • List synonyms
  • Find public synonyms
  • Find private synonyms
  • Find synonyms by schema
  • Find synonym target object
  • Find invalid synonyms
  • Find synonyms pointing to another database
  • Find duplicate synonyms

Oracle Materialized View Queries

  • List materialized views
  • Check materialized view status
  • Check last refresh
  • Find failed refreshes
  • Find stale materialized views
  • Find materialized view logs
  • Find refresh groups
  • Find materialized view size
  • Find materialized views by schema
  • Generate refresh commands

Database Directory Queries

  • List database directories
  • Check directory paths
  • Find directory owners
  • Find users with directory privileges
  • Find objects using directories
  • Find invalid directory paths

Oracle External Table Queries

  • List external tables
  • Find external table locations
  • Check external table definitions
  • Find external table directories
  • Find external table errors
  • Find schemas using external tables

Scheduler Advanced Queries

  • List scheduler jobs
  • Find running jobs
  • Find failed jobs
  • Find disabled jobs
  • Find broken jobs
  • Find jobs by schema
  • Find job execution history
  • Find job duration
  • Find job failure reason
  • Find job next run
  • Find job repeat interval
  • Find job program
  • Find job schedule
  • Find jobs consuming database resources
  • Generate scheduler enable/disable scripts

Legacy DBMS_JOB Queries

  • List DBMS jobs
  • Check job status
  • Find broken jobs
  • Find failed jobs
  • Find next execution
  • Find job interval
  • Find job owner
  • Generate DBMS_JOB commands
  • Remove obsolete jobs

Resource Manager Queries

  • Check Resource Manager status
  • List resource plans
  • List resource consumer groups
  • Find sessions by consumer group
  • Check CPU allocation
  • Check resource plan directives
  • Find sessions throttled by Resource Manager
  • Generate Resource Manager reports

Oracle Service Queries

  • List database services
  • Find sessions by service
  • Find SQL by service
  • Find service status
  • Find preferred instances
  • Find available instances
  • Find service distribution
  • Find service-related waits
  • Check service configuration

Oracle Listener / Connectivity SQL Queries

  • Check database service registration
  • Check listener-related parameters
  • Check LOCAL_LISTENER
  • Check REMOTE_LISTENER
  • Check service names
  • Check instance registration
  • Find connection-related sessions
  • Check dedicated/shared server configuration

Shared Server Queries

  • Check shared server status
  • Check dispatcher configuration
  • Check shared server sessions
  • Find shared server processes
  • Check dispatcher statistics
  • Find shared server waits
  • Check circuit information

Oracle Parallel Execution Queries

  • Check parallel parameters
  • Find parallel sessions
  • Find parallel SQL
  • Find parallel processes
  • Check parallel degree
  • Find PX waits
  • Find parallel execution servers
  • Find SQL using parallel execution
  • Check parallel statement history

Oracle Wait Event Queries

  • Find top wait events
  • Find current wait events
  • Find sessions waiting
  • Find sessions by wait event
  • Find CPU vs wait time
  • Find I/O waits
  • Find lock waits
  • Find enqueue waits
  • Find network waits
  • Find RAC waits
  • Find commit waits
  • Find log file sync waits
  • Find db file sequential read
  • Find db file scattered read
  • Find direct path reads
  • Find buffer busy waits
  • Find library cache waits
  • Find latch waits

Oracle Latch Queries

  • Check latch statistics
  • Find latch waits
  • Find latch contention
  • Find top latch events
  • Find sessions waiting on latches
  • Find latch sleep statistics
  • Find latch misses

Oracle Enqueue Queries

  • Find enqueue waits
  • Find enqueue types
  • Find TX locks
  • Find TM locks
  • Find HW enqueue
  • Find SQ enqueue
  • Find library cache locks
  • Find blocking enqueue sessions

Oracle Transaction Queries

  • Find active transactions
  • Find long-running transactions
  • Find open transactions
  • Find transaction start time
  • Find transaction duration
  • Find transaction by SID
  • Find large transactions
  • Find rollback activity
  • Find transaction-related waits

Oracle Commit / Rollback Queries

  • Check commits per second
  • Check rollbacks per second
  • Find sessions doing excessive commits
  • Find sessions doing excessive rollbacks
  • Find commit wait events
  • Find log file sync
  • Find transaction throughput

Oracle CPU Troubleshooting

  • Find top CPU SQL
  • Find top CPU sessions
  • Find CPU-intensive users
  • Find CPU by service
  • Find CPU by module
  • Find CPU by SQL ID
  • Find CPU-intensive jobs
  • Find CPU-intensive parallel SQL
  • Generate high CPU report

Oracle I/O Troubleshooting

  • Find top physical reads
  • Find top physical writes
  • Find top I/O SQL
  • Find top I/O sessions
  • Find datafile I/O
  • Find tablespace I/O
  • Find disk I/O
  • Find I/O by service
  • Find I/O by module
  • Find I/O-intensive SQL

Oracle Wait & Performance Investigation

  • High CPU
  • High I/O
  • High TEMP
  • High UNDO
  • High PGA
  • High SGA
  • Blocking
  • Deadlocks
  • Slow SQL
  • Long-running SQL
  • High Parse Rate
  • High Hard Parse
  • Library Cache Contention
  • Buffer Cache Problems
  • Redo Contention
  • Commit Contention
  • RAC Cache Fusion
  • 80. Deadlock Investigation
  • Find deadlock sessions
  • Find deadlock objects
  • Find blocking SQL
  • Find blocked SQL
  • Find transaction information
  • Find deadlock history
  • Find trace file information
  • Generate deadlock investigation report

Oracle Deadlock Investigation

  • Find deadlock sessions
  • Find deadlock objects
  • Find blocking SQL
  • Find blocked SQL
  • Find transaction information
  • Find deadlock history
  • Find trace file information
  • Generate deadlock investigation report

Database Migration Queries

  • List schemas
  • List objects
  • List table sizes
  • List index sizes
  • List LOBs
  • List partitions
  • List database links
  • List directories
  • List users
  • List privileges
  • List tablespaces
  • List datafiles
  • Generate migration inventory
  • Generate pre-migration validation report
  • Generate post-migration validation report

Pre-Migration Validation Scripts

  • Check invalid objects
  • Check tablespace capacity
  • Check TEMP
  • Check UNDO
  • Check database parameters
  • Check users
  • Check privileges
  • Check database links
  • Check scheduler jobs
  • Check services
  • Check Data Guard
  • Check RMAN backup
  • Check archive destination
  • Check ASM capacity

Post-Migration Validation Scripts

  • Compare object counts
  • Compare schema sizes
  • Compare table counts
  • Compare index counts
  • Compare invalid objects
  • Compare users
  • Compare privileges
  • Compare tablespaces
  • Compare database parameters
  • Compare database links
  • Compare jobs
  • Compare data counts
  • Compare application connectivity

Oracle DBA Comparison Scripts

  • Compare DEV vs TEST
  • Compare TEST vs PROD
  • Compare Primary vs Standby
  • Compare RAC instances
  • Compare Oracle parameters
  • Compare users
  • Compare privileges
  • Compare objects
  • Compare tablespaces
  • Compare database versions
  • Compare patch levels
  • Compare initialization parameters

Security Audit Scripts

  • Find DBA users
  • Find powerful users
  • Find users with ANY privileges
  • Find users with SYSDBA
  • Find users with CREATE ANY
  • Find PUBLIC privileges
  • Find default passwords
  • Find expired users
  • Find locked users
  • Find password policy
  • Find inactive users
  • Find unused accounts
  • Generate database security report

Capacity Planning Queries

  • Database growth per day
  • Database growth per week
  • Database growth per month
  • Tablespace growth
  • Datafile growth
  • Archive log growth
  • FRA growth
  • ASM growth
  • Schema growth
  • Table growth
  • Index growth
  • Estimate storage required
  • Estimate future tablespace requirement

Oracle GoldenGate SQL Queries

  • Find source database information
  • Find target database information
  • Check supplemental logging
  • Check force logging
  • Check archive mode
  • Check LogMiner-related settings
  • Check GoldenGate users
  • Check GoldenGate privileges
  • Check source tables
  • Check table counts
  • Compare source and target row counts
  • Find tables without primary keys
  • Generate GoldenGate prerequisite SQL
  • Generate GoldenGate validation SQL

Oracle Exadata SQL Queries

  • Check database running on Exadata
  • Check Exadata-related parameters
  • Find Smart Scan SQL
  • Find offload processing
  • Find cell physical I/O
  • Find storage-related waits
  • Find high I/O SQL
  • Find SQL using Smart Scan
  • Find cell offload efficiency
  • Find Exadata wait events
  • Find storage cell performance

OCI Oracle Database SQL

  • Check cloud database information
  • Check database version
  • Check database role
  • Check services
  • Check storage
  • Check tablespace usage
  • Check backup status
  • Check Data Guard
  • Check CPU utilization
  • Check sessions
  • Check performance
  • Check database parameters

AWS For Oracle DBA SQL Queries

  • Oracle database inventory
  • RDS Oracle database information
  • Tablespace monitoring
  • Session monitoring
  • SQL performance
  • Backup validation
  • Data Guard validation
  • Database links
  • Migration validation
  • Pre-migration checks
  • Post-migration checks

Azure For Oracle DBA SQL Queries

  • Oracle database inventory
  • Azure-hosted Oracle checks
  • Tablespace monitoring
  • Session monitoring
  • SQL performance
  • Backup validation
  • Migration validation
  • Pre-migration checks
  • Post-migration checks
  • Database health checks

Scenario-Based

  • The production database is slow—what SQL do you run?
  • Tablespace is 99% full—what do you check?
  • One session is blocking 100 sessions—how do you investigate?
  • CPU suddenly reaches 100%—how do you identify SQL?
  • TEMP reaches 100%—how do you find the culprit?
  • The database generates excessive archive logs—how do you investigate?
  • RMAN backup is slow—what SQL do you check?
  • Data Guard has high apply lag—what do you investigate?
  • One RAC instance is overloaded—what queries do you run?
  • Application reports ORA-01555—how do you troubleshoot?