SQL Query to Get Oracle Blocking Sessions

In this post I am going to explain you how to get the Oracle blocking session details. 

SQL 

Get details of which session is blocking and waiting.

SQL Script to Check the Open Cursors in the Oracle Database

SQL Query

set pages 1000 lines 120
col username for a15
SELECT 	OC.inst_id, 
		OC.user_name username,

SQL Script to Find out Total SGA and Current SGA Utilization

This query gives you the total SGA and its current usage by combining v$sga, v$sgastat and v$sga_dynamic_components dynamic views. 

SQL Script to Check the User wise Count in Oracle database

Here is the SQL script

set lines 120 pages 1000
break on report
compute SUM of tot on report

Script to Kill My Own SQLPLUS Session

Here I am giving you tip to kill your own sqlplus session connected to the oracle database. 

Syntax

How to Check When was the Table Insert, Update, Delete Done on Oracle Tables or Partitions

This is done with the help of the DBMS_STATS. FLUSH_DATABASE_MONITORING_INFO package. This package flushes in-memory monitoring information for all the tables to the dictionary.

SQL Query to Check Last Modified Time for an Oracle Object

SQL Query

select 	to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') 
from 		dba_objects 
where 	owner='&user' 
and 		object_name='&Object_name';

How Check Hardware type In Linux / Unix

AIX

uname -M  /   prtconf

Linux

cat /sys/devices/virtual/dmi/id/*	

ORA-01103: database name 'TEST' in control file is not 'CDB'

Problem Description: 

Alter database mount failed with following error after the dbname change using DBNEWID – NID utility.

DBNEWID – NID Utility – Change DBNAME and DBID

Using this command line utility we can make following 3 changes to the oracle database. 

1. Only the DBID of a database 

Flush SQLID or SQL Statement, Object from Shared Pool - DBMS_SHARED_POOL.PURGE

Package Name: DBMS_SHARED_POOL.PURGE

PROCEDURE PURGE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------

How to Delete Table, Index, Schema Statistics?

Here are the commands to delete the statistics. 
Table:-

EXEC dbms_stats.delete_table_stats('<TALE OWNER>', '<TABLE NAME>');

EXEC dbms_stats.delete_table_stats(OWNNAME=>'<TALE OWNER>', TABNAME=>'<TABLE NAME>');

DataPump EXPDP Estimate [ BLOCKS | STATISTICS ]Option

This parameter is used to estimate the amount of disk space used by each table in the export. This would be an approximate value. There are two options for this parameter. 

How to Check User Quota on Oracle Tablespace - DBA_TS_QUOTAS

The base dictionary view is DBA_TS_QUOTAS to fetch the specific tablespace user quota details. MAX_BYTES column value -1 means unlimited quota for the user for the particular tablespace.

DataPump Expdp KEEP_MASTER Option

Master Table:- Master table is the key table for the data pump jobs. It keep track of the respective datapump jobs. The table name is same as the job name of the export or import job.

Oracle 12c 18c 19c: How to find out the Listener.log file location?

In latest versions of Oracle the listener.log file placed under the ADR_BASE/diag folder.

DataPump Expdp ORA-39071: Value for EXCLUDE is badly formed

Problem Description: 

Datapump export command failed with following error.

Datapump COMPRESSION_ALGORITHM Option

This parameter determines the algorithm to compress the dump file data. 

Values:- COMPRESSION_ALGORITHM = {BASIC | LOW | MEDIUM | HIGH} 

DataPump Expdp ABORT_STEP Option

ABORT_STEP is the option with the DataPump export utility (expdp) to stop the job after the initialization. This is available from 12.1.0.2. 

Oracle CDB PDB: ORA-65096: invalid common user or role name; Create user Command failed

Problem Description:

SQL> create user "mytest" identified by mytest;
create user "mytest" identified by mytest
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL Script to Find out Current Running Active, Inactive Sessions Connected to the Database

This is the one of the most useful queries which is used by the DBAs on daily basis. Here I have provided multiple queries which can be used for finding out Active, Inactive with RAC and Non-RAC databases. 

How to Remove / Delete / Drop a Machine from Oracle VM Virtual Box?

Step 1.

Stop /power off the running VM by right clicking the running virtual Machine.

iPhone - How to Call Non-emergency Number from Locked iPhone Without Knowing Password?

I hope this would be an interesting feature to know. Suppose you lost your iPhone and somebody might have found it or in some emergency your friends or colleagues wanted to contact your family member,