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

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 »

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 »

What you need to know about Oracle Flashback query

Last updated on November 14th, 2017 at 04:53 am Oracle has introduced Flashback Technology  that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. With flashback features, you can do the following: Perform queries that return past data Perform queries that return metadata that shows a detailed history of changes to the … Continue reading What you need to know about Oracle Flashback query »

Must Read Oracle Database Cloud Service Overview and various options

Introduction to Oracle Database Cloud Service Oracle database cloud service is an service of Cloud Category PaaS. It enables businesses to reap all the benefits of Platform as a Service, including subscription-based, self-service access to reliable, scalable, and elastic cloud environments. Earliar you need to purchase hardware ,install OS and then install Oracle software and create database for using the database. Now You can be … Continue reading Must Read Oracle Database Cloud Service Overview and various options »