SQL Query
set lines 2000 Pages 200 col program for a40 col module for a40
SQL Query to Find Out Session wise PGA Usage
SQL Query to Find Out Sessions Using More Temp Space
Query
set pages 1000 lines 222 col USERNAME for a15 col osuser for a8 col tablespace for a8 col MODULE for a10 SELECT so_us.TABLESPACE
Easy way to Connect and Query OCI Autonomous Database - SQL Developer Web
Oracle provides easy way to connect the OCI autonomous database. This is free SQL developer like tool which is in build in the OCI console itself. Here I am going to explain how to access and use it.
How to Create or Provision OCI Autonomous Database
Step 1.
Click on navigation menu and Click on Autonomous Data Warehousing or Autonomous Transaction Processing
Script to Check ASM Diskgroup Compatibility
COMPATIBLE.ASM (v$asm_diskgroup. COMPATIBILITY) – The minimum version of the ASM software that can access the disk group. In 11g,
Script to Check Disk group Freespace & Mount Status
SQL Script
set pages 1000 lines 120 col NAME for a16
SQL Script to Find out ASM Disk Details
Here is the SQL script to find out ASM Disk Details.
set pages 10000 lines 250 column path format a55
Direct Method to Create Database Link Without Using TNSnames.ora
Normal method – Using TNSnames service name
This method requires the TNS entry to be added in the TNSnames.ora file as mentioned below.
SQL Script to Check Oracle Logfile Size, Status and Location
This sql script provide you the logfile group, member, path and file size details for a Oracle database.
SQL Query
set pagesize 30 linesize 100 column member format a60 column status format a10 column size_MB format '999,999'
Database Creation using dbca failed with Oracle Net Services configuration failed. The exit code is 1
Error Description:
Database creation using dbca utility failed with following error.
netca command failed with ProfileException: Could not create Profile: TNS-04414: File error
Error Description:
Oracle netca command failed with following error.
Oracle netca command failed with following error.
ProfileException: Could not create Profile: TNS-04414: File error
Oracle Net Services configuration failed. The exit code is 1
India Driving License Renewal New Process Through sarathi.parivahan.gov.in/ Portal
There is a change in the driving license renewal process in all the states in India. It is became the common portal for all the states. Earlier there were individual portal for different states.
Scripts to Check RMAN Backup Status and Progress
In this post I wanted to give 2 SQL scripts to check the Oracle RMAN backup status.
Datapump expdp impdp New Feature – Print Content of Parfile to Logfile
From Oracle 12.2 release onwards, when you use the parfile option to expdp or impdp it will write the content of the parfile to the logfile in below format.
Connect to Oracle Database Running in Linux
Step 1. Identify the database running in the server
[oracle@TESTBOX exp]$ ps -ef|grep pmon|grep -v grep oracle 25167 1 0 Jul18 ? 00:00:10 ora_pmon_cdb
Script to Check ASM Disk Usage Details
This script provides you ASM disk usage details for the ASM database.
SQL Query
Execute Oracle SQLs from Linux Command Prompt and Exit
Method 1
Execute single SQL command from the prompt
Command to List the Oracle Databases Running on Linux Server
Generally we use ps -ef|grep pmon command to list the databases running on the database server.
In this post I am going to mention
Commands to Delete Old Aged Oracle Trace Files(.trc, .trm) and Audit(.aud) Files
In this post I am going to explain the command to delete old trace, Audit files. You have to periodically clean up these trace, audit file locations to avoid the file system get filled with files.
Create Restore and Drop Oracle Restore Point - Types and Benefits
Restore Point
It is a name associated with the SCN in the database. Using this restore point we can revert to the SCN which is pointing to the restore point even if you make some changes to the database.
RMAN: Dropped Table Recovery
In this post I am going to demonstrate the table recovery using RMAN. There are chances to drop tables accidentally by developer. In this cases DBA asked to recovery the dropped table from the backup.
Script to Analyze All Schema Tables and Indexes in Oracle Database
This script to generate the script to analyze all the tables in the specified schema(s)
Method 1.
Using dbms_stats
SQL to Set Tablespace Quota for Oracle User
Create a tablespace - USER_DATA
create tablespace user_data datafile '/u02/oradata/CDB/user_data_01.dbf' size 500m;
Script to Find out / Identify Trace File for the Current or Other Oracle User Session
In this post I am going to help you to identify the trace file name for the
current oracle session.
Method 1.
Using v$diag_info table
Trace Current Oracle Session – Different Methods
Method 1.
Using alter session command
ALTER SESSION SET sql_trace=TRUE; ALTER SESSION SET sql_trace=FALSE;
SQL Query to Find Blocking Sessions and SQL Text for Last One Day from History
In this post I wanted to give you one sql script to find blocking sessions for the last one day from the history.
SQL Query
set pages 1000 lines 222col sql_id for a17
col inst_id for '99'
col sql_text for a60SQL Query to Find SQLIDs Spend on CPU/Wait/IO
This query is used to find out the SQLIDs spend on the CPU, Wait and IO.
SQL Query
Script to Find Most Active Session in Oracle Database for Last One Hour
These SQLs to identify most active sessions in the oracle database for last one hour. Two queries mentioned here for the background and foreground processes.
SQL Query to Find I/O Intensive SQL and SQLText in Last One Hour
This can be used for finding out most CPU intensive query in last one hour. There are 3 data dictionary views involved in this query.
gv$active_session_history
gv$event_name
v$sqlarea
SQL Query to Find Out Oracle Session Details for a Past Time Period from the History tables
There are the 3 views can be used to achieve this goal. The columns are almost similar to the V$SESSION table except snap_id, sample_id, sample_time etc.. The AWR,
SQL Query to Find Out Pending Distributed Transaction in Oracle Database
A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database.
OCI-How to Generate SSH Keys for OCI instance connectivity
To connect OCI Unix instance using Putty from windows server or Unix server it requires public and private keys to establish the connection. To generate keys we can use Putty Keygen tool.
How to Setup Free Oracle Cloud Infrastructure (OCI) Account
Step 1
Go to the free account web link.
https://www.oracle.com/cloud/free/#free-cloud-trial
Click on "you can sign up on the Oracle Cloud Free Tier" sign up on the bottom of the page.
Go to the free account web link.
https://www.oracle.com/cloud/free/#free-cloud-trial
Click on "you can sign up on the Oracle Cloud Free Tier" sign up on the bottom of the page.
SQL Query to Get Oracle Database Server CPU, CPU CORE, CPU Sockets and Physical Memory
Some Definitions
CPU
– Number of Processing Units in the server which is a main component of a server.
RMAN / SQL query to Get Oracle Database Incarnation Details
What is Database Incarnation?
Database incarnation is effectively a new “version” of the database. A new version or Incarnation happens when you reset the online redo logs using “alter database open resetlogs;”.
SQL Query to Get Oracle DB TIMEZONE details
In this post I am adding some useful sql query to find out the oracle database TIMEZONE details.
How to Check, Disable and Enable Recyclebin in Oracle Database
Check whether recyclebin is enabled or not using below commands.
