Category Archives: Oracle

ORACLE TUTORIALS

Oracle is most widely used Oracle database in the world. It has the capacity to handle large database and high transaction

Here I am presenting the Oracle tutorials on Various Oracle products

  1. Oracle database
  2. Oracle sql/plsql
  3. Oracle application
  4. Weblogic
  5. Performance tuning.

These articles will be  really help in understanding the oracle products and getting started on Oracle system

Local Undo in Oracle Database 12c R2(12.2)

Problem 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 or plug in operation. (If any uncommitted transactions … 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 »

How to move LOB segment to another tablespace

Here we are presenting ways to move/rebuild LOB segment First take the initial size of the LOB segment SELECT table_name, column_name, segment_name, a.bytes FROM dba_segments a JOIN dba_lobs b USING (owner, segment_name) WHERE b.table_name = ‘TABLE_NAME’; Now lets take a look at various ways to rebuild the lob 1) Alter table move command If you just want to move the table without impacting LOB Segments,then … Continue reading How to move LOB segment to another tablespace »

approx_count_distinct : Oracle Database 12c

Introduction approx_count_distinct is the new function included in 12c to estimate the distinct values in the column in a faster manner without deviating much from actual values. It returns approximate number of rows that contain distinct values of expression. approx_count_distinct Definition We know that it take considerable time to project desired output using traditional COUNT(DISTINCT) appraoch. With Oracle 12c (12.1.0.2), we have a function “APPROX_COUNT_DISTINCT” … Continue reading approx_count_distinct : Oracle Database 12c »

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 »

Basic elements(Application,Forms,functions,menu) of Oracle EBS Application

Here in this post,I am trying to explain the Basic elements(Application,Forms,functions,menu) of Oracle EBS Application Application -An Application is a collection of Forms,Reports and PL/SQL objects to meet a particular business objectives -Oracle Applications like 11.0,11i,R12 is a collection of predefined inter linked applications -A custom application can be designed and integrated with Oracle Applications -Any custom application has to be registered with Oracle Applications … Continue reading Basic elements(Application,Forms,functions,menu) of Oracle EBS Application »

Guest User password in 11i/R12

Here in this post,I am decoding all the facts about Guest User password in 11i/R12 –GUest user password is very important for the functioning of Application SSWA. Self service login used this password to verify the user password.Selfservice will not come up if this password is incorrect.Direct form login will still work if the password is incorrect -It is stored in following places in 11i $FND_TOP/secure/ … Continue reading Guest User password in 11i/R12 »

Important Oracle Forms Architecture Features in R12

We are explaining here Oracle Forms Architecture Features in R12/R12.2 Features of Forms in R12 -forms are deployed as a one One OC4J instance of 10.1.3 Oracle home -It uses the utlity from 10.1.2 Oracle home/bin -Forms.EAR 10.1.2 is deployed to the OC4J container in Application Server 10.1.3 -Run time form executabale f60webmx has been replaced by frmweb -Forms Servlet is default deployment / communication … Continue reading Important Oracle Forms Architecture Features in R12 »

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) »

How to rebuild WF_DEFERRED_TABLE_M Queue

Some times we need to rebuild the WF_DEFERRED_TABLE_M queue due to corruption. Here are the steps to perform it. First take a pre snapshot of all the objects related to that queue Login to server and source the EBS environment spool WF_DEFERRED_TABLE_M_objects_pre.lst select object_name, object_type, status, owner from dba_objects where object_name like ‘%WF_DEFERRED_TABLE_M%’; spool off 2) Now we will proceed with the steps to rebuild the … Continue reading How to rebuild WF_DEFERRED_TABLE_M Queue »