DBA SCRIPTS

DB MONITORING

find current running sqls


Find active sessions in oracle database
Find waitevents in database
Find sessions generating undo
Find the temp usage of the sessions
Find sessions generating lot of redo
Monitor tablespace usage
Script to Monitor undo tablespace usage
Monitor TEMP tablespace usage
Find blocking sessions
Find long running operations
Find locks present in database
Find queries triggered from a procedure
Get sid from os pid
Kill all sessions of a sql_id
kill all session of a user
get parallel query detail
Kill snipped session in db
Top Query with high elapsed time
Monitor parallel queries
Find the locked objects
Check open cursors
Session login history from ASH
Buffer Cache hit ratio
Find top disk_reads by an user
Get os pid from sid
Get active sid of a pl/sql object
Find buffer cache usage
Monitor rollback transations
Find column usage statistics
Get background process details
oracle db is 32bit or 64 bit?
oracle license usage info
db optimizer processing rate
Purge recyclebin in database

DB MONITORING

xplain plan of sql_id from cursor


xplain plan of sql_id from AWR
Get sql_text from sid
xplain plan of a sql statement
xplain plan of a sql baseline
Get bind values of a sql_id
Flush a sql query from cursor
Enable trace for a sql_id
10053 OPTIMIZER TRACE
Enable trace for a session
Tracing all session of a user
Enable tracing for a listener
execution detail of a sql_id in cursor
Pga usage by sessions
segments with high physical read
I/O usage of each tempfile
Current SGA usage
Top running queries from ASH
Find blocking sessions from ASH
Top cpu consuming sessions
Sessions holding library cache lock
Objects locked by library cache
Sessions accessing an object
Sqls doing full table scan
Dictionary cache hit ratio
Top sql queries using literal values
Objects causing flushing of shared pool
Latch type and sql hash value
Objects causing latch contention
Queries causing high physical read
Mutex sleep in database
Sql tuning advisor for sql_id from cursor
run sga target advisory
Run shared pool advisory
Generate addm report

DATABASE INFO

Get redo log member info


Get DDL of all tablespaces
Get DDL of all privileges granted to user
Get size of the database
View hidden parameter setting
Get ACL details in database
Archive generation per hour
Find active transactions in db
Find who locked your account
Find duplicate rows in table
Database growth per month
generate resize datafile script without ORA-03297 error
Get database uptime
Scn to timestamp and viceversa
Disable/enable all triggers of schema
Ger row_count of all the tables of a schema
Spool sql query output to HTML
Monitor index usage
Get installed sqlpatches in db
Cleanup orphaned datapump jobs
Get Alert log location in db
Installed RDBMS components
Characterset info of database
View/modify AWR retention
Find optimal undo retention size
Purge old awr snapshots
Modify moving window size
Open database link information
utilization of current redo log ( in % )
Generate multiple AWR report
Table not having index on fk column
Get cpu memory info of db server
Get database incarnation info
View timezone info in db



ASM

Get asm disk info


Get ASM diskgroup details
drop an asm disk
Monitor ASM disk rebalance
execute runcluvfy.sh for RAC precheck
copy asm file to remote asm instance
Mount/dismount ASM diskgroups
Drop ASM diskgroup
Clock Synchronization status in RAC
Create ASM disk in Linux using oracleasm
Change asm rebalance power
Create password file in ASM DG
Stop/start cluster in rac standalone.
Modify asm user password
Monitor asm diskgroup i/o
Enable tracing for asmcmd
How to Change ASM sys password

SRVCTL COMMANDS

Stop and start db using srvctl


add/remove db using srvctl
Add/remove instance using srvctl
Stop and start instance using srvctl
Enable/disable db/instance using srvctl
Relocate a service
Add/remove a service
stop/start a service
Manage MGMTDB in 12c RAC
Set env variables using srvctl
Enable trace for srvctl commands

CRSCTL & RAC

Enable/Disable autorestart of crs


Find the cluster name in RAC
Stop and start CRS
Find OCR and VD location
Find the grid version
check cluster component status
Get cluster_interconnect details
Manual backup of ocr and list backups
Move voting disk to new diskgroup
get disktimeout values
get node info using olsnodes
Get interface info in RAC
Get OLR info in RAC



SQL PROF/BASELINE

create baselines for all sqls of a schema


Create sql baseline from cursor cache
drop a sql baseline
drop a sql profile
Get sql_profile of a sql_id
Disable/enable sql profile
Find sql baseline info from sql_id
Alter/disable a sql plan baseline

PARTITIONING

Adding partitions 11g/12c


Dropping partition 11g/12c
Truncate partitions
merge partition
Make a partition ready only(12CR2)
Split partition online(12cR2 only)
Non-partitioned to partitioned online(12CR2 only)
Rename a partition
Get row_count of partitions of a table
Find the table partition keys
Move partition to new tablespace

STATISTICS

Gather stats for schema


Gather stats for a table
Lock/unlock statistics
Export import statistics
Check stale stats
Table statistics history
Publish Pending stats
Get statistics preference setting
View/modify stats retention in db
Space used to store stats
Enable incremental stats collection
Delete statistics
Upgrade statistics in db



FLASHBACK TECH

Flashback a table to point in time


Recover a dropped table
Flashback query as of timestamp