dba topics: 2020

SQL Query to Find Out Session wise PGA Usage

SQL Query

set lines 2000 Pages 200
col program for a40
col module for a40

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

#Jobs 21 Sep 2020: Flutter Developer

Flutter Developer

Job Description:

• Cross-platform mobile app developers who have developed mobile apps with a familiarity of Flutter

#Jobs 21 Sep 2020: Full Stack Developer

Full Stack Developer

Design client-side and server-side architecture

Build the front-end of applications through appealing visual design

#Jobs 21 Sep 2020: Front End Developer (Angular / React JS / Vue JS)

Front End Developer (Angular / React JS / Vue JS)

Location : calicut

Full Job Description:

#Jobs 21 Sep 2020: Senior Electrical Engineer with 9+ years

We are looking for a Senior Electrical Engineer with 9+ years experience.

Work Location :Cochin, Kerala

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 Check Disk Group & Disks Details

SQL Script

set pages 1000 lines 333
col PATH for a50

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

SQL Script

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@TESTBOX CDB]$ netca

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

SQL 1.
SQL to check the RMAN backups status

set pages 1000 
set lines 222
col STATUS format a9

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

SQL Query

set lines 250 
set pages 9999 
column path format a50

Execute Oracle SQLs from Linux Command Prompt and Exit

Method 1
Execute single SQL command from the prompt

[oracle@TESTBOX exp]$ echo 'select count(*) from tab;' | sqlplus / as sysdba

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.

SQL to Create 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;

How 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

set pages 1000 lines 222
col sql_id for a17
col inst_id for '99'
col sql_text for a60

SQL 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:-

set pages 1000 lines 220
col SQL_ID for a 15
select

– Number of Processing Units in the server which is a main component of a server.

Archive