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

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



RAC & 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

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 COMMANDS

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



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

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
Enable flashback for database
Create/drop flashback restore point
Flashback db using restore point
Flashback a procedure/package
How far we can flashback
Flashback area usage info
Enable archivelog mode in standalone db

RMAN SCRIPTS

rman full db backup run block script


RMAN incremental db backup run block script
rman tablespace backup run block
RMAN datafile(s) backup run block
delete archivelog older than 1 day using RMAN
backup archivelogs using RMAN
Copy archive log from ASM to Mount point
backup archive between 2 sequence number
Enable trace for RMAN
Recover dropped table with RMAN 12c
Monitor rman backup progress
Restore archivelog from rman tape
Enable block change tracking

USER MANAGEMENT

Create user in oracle


Alter an user
Change default tablespace of user
Tablespace quota for a user
View Privileges granted to an user
grant table/column privilege to user
Connect to user without knowing password
Common user/role in CDB
User creation details in user$ table
Create /alter profile in database
Default users in oracle 12c



TABLESPACE & DATAFILE

Create tablespace in oracle db


Rename tablespace in oracle db
Drop tablespace in oracle db
Add/alter datafile
Add/drop Tempfile
Rename/move a datafile
Checkpoint time of datafiles
Occupants usage in sysaux tablespace

MULTITENANT(CDB-PDB)

Status of PDBS in multitenant


Tablespace info in Multitenant
Temp tablespace details in Multitenant
show History of PDBS
currently connected PDB name
stop and start pluggable db:
Drop a pluggable database
Check undo mode in Multitenant db (oracle 12.2)

SCHEDULER & JOBS

Manage dbms_schedulerjobs


Create and scheduler a scheduler job
Drop a schedule
scheduler shell script in dbms_scheduler
Monitor scheduler jobs
All scheduler windows
View all scheduler schedules
history of all scheduler job runs
log information for all Scheduler jobs
Get DDL of a scheduler job
Scheduler job detail in CDB
Copy scheduler job from one user to other
Definition of job in dbms_jobs