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

Flashback PDB in Oracle Database 12c Release 2

Oracle database 12c Release 2 provides flashback capability at the individual PDB level. Lets discuss the new feature. we can learn about Flashback database in general from below links Flashback Database explained and limitation For the oracle database to be flashback, archive log mode and flashback mode should be on How to put database in archive log mode sqlplus “/ as sysdba” SHUTDOWN IMMEDIATE STARTUP … Continue reading Flashback PDB in Oracle Database 12c Release 2 »

REGEXP string Manipulation

String manipulation and searching contribute to a large percentage of the logic within a Webbased application. Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. Usage ranges from the simple (“Find the word SAN in the text”) to the more complex (“Extract all URLs from the text”) to the archaic (“Find all words in which every second character … Continue reading REGEXP string Manipulation »

How to use Optimizer hints

What is Optimizer? When a query is submitted in Oracle server. optimizer attempts to generate the best execution plan for a SQL statement. The best execution plan is defined as the plan with the lowest cost among all considered candidate plans. The cost computation accounts for factors of query execution such as I/O, CPU, and communication. The best method of execution depends on myriad conditions … Continue reading How to use Optimizer hints »

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

It is very common that you want to know / see the available index on a 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 off echo off col table_name head ‘Table Name’ format … 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 »