Category Archives: Oracle Database

An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management.Many companies has implemented their data in Oracle. Most of them are mission critical. So Oracle database concept need to be very clear to manage these mission critical things. At the same time Oracle database needs to deliver high performance also. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

Below section I have tried to present all the important features in Oracle Database management

ASM best practice to add disk

Here are ASM best practice to add disk which should be always use to manipulate the ASM disks 0) First check the newly added disk is visible in ASM  disksselect header_status,path from v$asm_disk where path= ”; It should show candidate .1) Create a new temporary diskgroup: CREATE DISKGROUP TEST_DISK EXTERNAL REDUNDANCY DISK ”; 2) Check if the diskgroup is created and mounted: SELECT STATE, NAME FROM … Continue reading ASM best practice to add disk »

Local Undo in Oracle Database 12c R2(12.2)

Last updated on December 17th, 2017 at 05:20 pmProblem statement We have global or shared undo for the entire CDB in 12c R1. With shared undo, before performing operations such as (cold) clone or unplug, it is necessary for the database to check for any uncommitted transactions in the source PDB. This is to avoid problems with transactional consistency in the PDB after the clone … Continue reading Local Undo in Oracle Database 12c R2(12.2) »

Find indexes status and assigned columns for a table

Last updated on July 27th, 2018 at 04:56 pmIt is very common that you want to Find indexes status and assigned columns for a oracle table And also you sometime want to know the columns which are assigned to the indexes. Following views would be helpful in these cases dba_ind_columns all_ind_columns   Query to Find the indexes with assigned column is  set pagesize 50000 verify … Continue reading Find indexes status and assigned columns for a table »

Oracle Database Interactive Quick Reference Guide | 11g & 12c

The Oracle Database Interactive Quick Reference is a multimedia tool for various terms and concepts used in the Oracle Database . Built as a multimedia web page, this diagram provides descriptions of database architectural components, as well as references to relevant documentation. This is very helpful reference and cheat sheet for writing custom data dictionary scripts, locating views pertinent to a specific database component or … Continue reading Oracle Database Interactive Quick Reference Guide | 11g & 12c »

how to find the semaphore for the Database instance

a) When Oracle Instance is not running: /uXX/app/oracle/product/XX> sysresv IPC Resources for ORACLE_SID “TEST″ : Shared Memory ID KEY No shared memory segments used Semaphores: ID KEY No semaphore resources used Oracle Instance not alive for sid “TEST″ b)When Oracle Instance is running: /uXX/app/oracle/product/XX> sysresv IPC Resources for ORACLE_SID “TEST″ : Shared Memory: ID KEY 14437 0xe4efa8324dc Semaphores: ID KEY 1232330802 0x09d48331246 Oracle Instance alive … Continue reading how to find the semaphore for the Database instance »

New Optimizer Feature with 11g and 12c

New Optimizer Feature with 11g 1) Invisible indexes With 11g release,index can be made invisible. They will be maintained all the time and can be made visible any time alter index K invisible; Optimizer will not use the index if it is invisible alter index K visible If want one query to use invisible index,we can set this parameter in the session optimizer_use_invisible_index=true Then the … Continue reading New Optimizer Feature with 11g and 12c »

Oracle Performance Tuning Tools( Oracle Explain Plan,Auto-trace,tkprof)

We are presenting here All information about Oracle tuning tools like Oracle Explain Plan,Auto-trace,tkprof. We will take a deep dive into each of them Oracle Explain Plan -Oracle database internally creates a query execution plan in order to fetch the desired data from the physical tables. The query execution plan is nothing but a set of methods on how the database will access the data from … Continue reading Oracle Performance Tuning Tools( Oracle Explain Plan,Auto-trace,tkprof) »

Oracle Case Statement Explained with Tips and Examples

Last updated on December 24th, 2017 at 02:08 pmWe have seen working of Oracle Decode processing in the previous post Oracle sql decode processing Now let us see Case statement processing Table of content Case statement in Oracle Simple case statement Searchable Case statement Nested Oracle Case statement Important points about Simple and searchable Case statement difference between Case and Decode statement   Case statement … Continue reading Oracle Case Statement Explained with Tips and Examples »

Difference between v$system_parameter and v$parameter

In this post, we will discuss the Difference between v$system_parameter and v$parameter and other parameter view available in Oracle database. Lets first start with the definition of each of them V$PARAMETER It displays the information about initialization parameters that are currently in effect for the session. V$SYSTEM_PARAMETER is the view which shows instance level parameters (and these are what all new sessions inherit) V$SPPARAMETER It displays … Continue reading Difference between v$system_parameter and v$parameter »

How to change Hostname for a Grid Infrastructure Oracle Restart Standalone Configuration

We have a Grid Infrastructure Oracle Restart  configurtaion on one database server and Now we want to change the hostname of the server. Here we will be seeing the steps required to perform it without any loss of the database. Configuration Old Host Name: techgo New Host name:techgo-new Stop all the services $> <11.2 Grid Infrastructure Oracle Home>/bin/crsctl stop has Change the Hostname from techgo to … Continue reading How to change Hostname for a Grid Infrastructure Oracle Restart Standalone Configuration »

How to use google translate URL in Oracle plsql

Last updated on November 14th, 2017 at 04:46 amWe all know google translate which can help translate from one language to another. We will explore option to use google functionality in oracle plsql in oracle database to get translation done. First we need to create Network access list which is required from Oracle 11g   BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => ‘google_translatorapi.xml’, description => ‘Google Translator Access’, … Continue reading How to use google translate URL in Oracle plsql »

How to find the waitevent History of the Oracle session

In this post , we will look at the different queries to retrieve the waitevent history of the Oracle session. History of wait events in a specific session from start can be found using below query set lines 120 trimspool on col event head “Waited for” format a30 col total_waits head “Total|Waits” format 999,999 col tw_ms head “Waited|for (ms)” format 999,999.99 col aw_ms head “Average|Wait … Continue reading How to find the waitevent History of the Oracle session »