Oracle Database Monitoring and Administration - Queries

-: Database Monitoring :-                                                             

Query to check active/inactive session details                                               

Query to get sid with session details using spid

Dynamic killing session scripts in Oracle Database       

Top Tablespace Usage                                                    

Temporary Tablespace Usage                                
DC-DR Sync

Query to get bind variable values for sql_id in oracle - dba_hist_sqlbind

Query to check table columns statistics in Oracle

How to execute sql query without output to get only elapsed time of the query in Oracle

Query to check Table Partition details in Oracle

sql query text from sql_id in Oracle

Query to check Index Partition details in Oracle

Query to check if ASM or Automatic Storage Management is configured or not in Oracle

Query to check if Fine-grained Auditing is configured or not in Oracle database

Query to check if Unified Auditing is configured or not in Oracle database

Query to check if Oracle RAC or Real Application Cluster is configured or not

Query to check Oracle Database Vault is configured or not in Oracle database

Query to check Oracle Label Security is configured or not in Oracle database

Query to check Dataguard is configured or not in Oracle database

Query to check timezone in Oracle database

Query to check registry component in Oracle database

Query to check Default Permanent Tablespace in Oracle

Query to check Global Database Name of Oracle database

Query to check default temporary tablespace in Oracle database

Query to check NLS parameters in Oracle database

Query to check applied patches details from database in Oracle

Query to check database role in Oracle database

Query to check control file type in Oracle database

Query to check protection mode in Oracle database

Query to check temp files in Oracle database

Query to check control files in Oracle database

Query to check data files in Oracle database

How to check database release number and version number in Oracle database

How to check if database is open in restricted mode

How to check if database is running in archivelog mode or noarchivelog mode

How to check if force_logging mode is enabled in Oracle database

How to check if flashback is enabled in Oracle database

Query to check switchover status in oracle database

Query to check protection mode in oracle database i.e. maximum performance or availability or protection mode

Query to check DBID of database in Oracle

Query to check the database creation date of oracle database

How to check if control file type is current or standby control file in oracle database

How to check if database is pluggable database or container database or CDB in Oracle

How to find if database is Primary or Standby in Oracle

How to check if Oracle database is running or not

Query to check logical directories in Oracle database

Script to monitor oracle processes on server at OS level

Query to check database size in Oracle                                       

               

                              

-: Administration :-   

Starting and shutting down Oracle Single Instance Database

Starting and Shutting Oracle RAC database

How to resize datafile

How to add datafile

How to disable/enable default maintenance jobs

How to resize redo logs in Oracle

How to enable/disable archivelog mode

Adding redo log groups in one command in Oracle

Dropping redo log groups in one command in Oracle

Creating service using ORADIM in Oracle Windows

invisible indexes in Oracle database

How to fix unusable indexes in Oracle database

Setting Default UNDO Tablespace in Oracle Database

-: Database Info :-                                                          

Object DDL                                                                         

Object Details                                                                   

Query to check invalid objects                                                                                             

Query to check index details in Oracle database                   

Query to check indexes on column of the table

Query check object size in Oracle Database

Query to check last modified time and created time of the Object in Oracle Database

                                                                                  

-: User and Profile Management :-

Query to check user details

How to check locked users

How to lock/unlock users

How to change User's password

Profile Management

Query to check default or internal users and non-default or application users in Oracle database

AWR/ASH/ADDM reports

Scripts to capture AWR/ASH/ADDM Reports

- ashrpt.sql

- addmrpt.sql 

- awrddrpt.sql   

- awrrpti.sql    

- ashrpti.sql    

- addmrpti.sql   

- awrgrpt.sql    

- awrgrpti.sql   

- awrgdrpt.sql   

- awrextr.sql    

- awrinfo.sql     

- awrsqrpt.sql 

How to create AWR snapshot manually in Oracle database

How to manually purge AWR snapshots in Oracle database

dbms_workload_repository.add_colored_sql and remove_colored_sql in Oracle Database

DBMS_WORKLOAD_REPOSITORY.CONTROL_RESTRICTED_SNAPSHOT in Oracle database

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS in Oracle Database

What is moving window baseline in Oracle AWR ?

-: RMAN :-                                                                  

RMAN useful commands

RMAN Configuration Parameters

Creating Physical Standby on Same Server by RMAN Duplicate from Active Database

Creating Physical Standby database using rman duplicate for standby method

Oracle DB cloning from 2-node RAC into single instance by rman active database

RMAN duplicate on different server with backup from 2-node RAC into single instance

-: RAC :-

crsctl query crs commands in Oracle RAC

Stopping and Starting Cluster Services in Oracle - crsctl commands

Disable/Enable automatic startup configuration of Oracle High Availability Services

How to check Oracle Clusterware Status - crsctl commands

crsctl get commands - Oracle 12c RAC

crsctl check commands - Oracle RAC

How to disable/enable HAS in Oracle RAC

srvctl commands - Oracle RAC

How to stop/start scan in Oracle RAC

-: ASM :-                                                                            

Query to check ASM disk group details

Query to check disks belong to which disk groups

How to check free space in ASM disk group without logging to ASM instance - asmcmd lsdg

Difference between asmsmd and asmcmd -p option - Oracle ASM

How to check asmcmd version number in Oracle - asmcmd -V

asmcmd --privilege option in Oracle ASM

lsattr in asmcmd

lsdsk in asmcmd Oracle

-: Dataguard :-

Query to check sync between Primary and Standby databases

Query to check if MRP process is running in Oracle database

How to start/stop MRP process in Oracle Standby

Standby redo logs in oracle dataguard

LNS NSS NSA TT processes in Oracle Dataguard

Creating Standby Redo Logs or SRLs in Oracle database

                  

                          

-: Export/Import :-

How to list all export import commands in Oracle

export import from Oracle 12c to Oracle 11g database using version parameter

export table from Oracle 11g and import into Oracle 12c database

How to create logical directory in Oracle database

Oracle data pump export of table in Oracle database

Oracle data pump export of schema or user in Oracle database

Full database export by data pump in Oracle

Tablespace export by data pump in Oracle database

How to take export backup to multiple directories in Oracle

Parallel data pump export in Oracle

estimate_only parameter in Oracle expdp

Default expdp directory/dumpfile/logfile in Oracle database

exclude=statistics in expdp Oracle

exclude in expdp Oracle

include in expdp Oracle

content parameter in datapump Oracle

logtime in expdp oracle 12c

metrics=y in expdp Oracle

Data Pump performance Issue Troubleshooting - LOGTIME=ALL with METRICS=y

How to use Network_link parameter in Oracle database

REUSE_DUMPFILES keyword in expdp - oracle database

SAMPLE parameter in expdp Oracle

compression parameter in expdp Oracle

COMPRESSION_ALGORITHM parameter in expdp oracle