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

How to find how much space is actually used by the lobsegments

Last updated on November 28th, 2015 at 12:35 pmLet us take the example of FND_LOBS tables in Oracle Apps Database.FND_LOBS has LOB datatype column FILE_DATA How to  find how much space is actually used by the lobsegments Query to find the actual bytes used by the lob datatype select sum(dbms_lob.getlength (FILE_DATA)) from FND_LOBS; SUM(DBMS_LOB.GETLENGTH(FILE_DATA)) ———————————- 13313 The total space allocated in the extents could be … Continue reading How to find how much space is actually used by the lobsegments »

How to correct error RMAN-20004

Last updated on December 21st, 2015 at 04:14 amProblem:When Recovery catalog has been configured.and Production Database has been cloned and a connection has been made from the clone to Catalog database using rman with out changing the DBID of the clone,then we are in some trouble Then when you try to backup the production instance with the catalog,The backup fails with following error RMAN-06004: ORACLE … Continue reading How to correct error RMAN-20004 »

How to delete duplicate rows from a table

Last updated on September 1st, 2016 at 04:22 amWe often find duplicate rows in the table due to many reasons in the database. We need to delete to clear off the data issues often. There are many days to to delete duplicate rows from a table.I would be showing off few  faster method to achieve it in this post. Please note that you must  identify all … Continue reading How to delete duplicate rows from a table »

Cluster command in Oracle clusterware 10g and 11g

Last updated on September 1st, 2016 at 05:21 amOracle clusterware is the clusterware technology from oracle to Manage Oracle RAC database on various platform.Oracle  has provide cluster utilities to manage it. Here are some of the useful Cluster command  in 10g and 11g  CRSCTL Cluster Control utility performs various administrative operation of oracle clusterware. It is located in $ORA_CRS_HOME/bin and must be executed by the … Continue reading Cluster command in Oracle clusterware 10g and 11g »

Result cache in 11g

Last updated on September 1st, 2016 at 05:21 amPrior to 10g, every query must be re-executed even if there is no change in the tables. This reexecution is unnecessary for few static tables and only caching using at client tools resolve this issue. But, in 11g, query results can be cached in SGA. Just to avoid confusion, this is much different from buffer cache caching … Continue reading Result cache in 11g »

Interconnect setup in RAC

Last updated on November 23rd, 2015 at 03:42 pmThe interconnect is a very important part of the cluster environment it is on of the aorta’s of a cluster environment. The interconnect is used as physical layer between the cluster nodes to perform heartbeats as well as the cache fusion is using it. The interconnect must be a private connection. Cross over cable is not support. … Continue reading Interconnect setup in RAC »

How to find optimizer underscore parameter

Last updated on November 23rd, 2015 at 03:31 pmHow to find optimizer underscore parameter SELECT x.ksppinm name, y.ksppstvl VALUE, ksppstcmnt update_comment FROM x$ksppi x, x$ksppcv y WHERE x.inst_id = userenv(‘Instance’) AND y.inst_id = userenv(‘Instance’) AND x.indx = y.indx AND x.ksppinm LIKE ‘%optim%%’; How to find background process information select nm, max(description) descript from (select regexp_replace( name,'[0-9a-z]’,’#’) nm, description from v$bgprocess) group by nm order by … Continue reading How to find optimizer underscore parameter »

Various Joins Method in Oracle

Last updated on September 15th, 2016 at 05:24 amPerformance is key for OLTP system and we should have good information about Joins Method in Oracle to increase performance for sql queries Here are the  Joins Method in Oracle available in Oracle database Nested Loops -For each row in the first row source access all the rows from the second row source. -Best for OLTP type transactions … Continue reading Various Joins Method in Oracle »

Optimizer Mode

Last updated on November 23rd, 2015 at 03:34 pmSet in the Init.Ora Can be changed via ALTER SESSION CHOOSE, FIRST_ROWS, ALL_ROWS = cost based optimization RULE = rule based optimization Optimizer_Mode = Rule Original optimizer developed by prior to 7 Applications prior to 11i must run as RULE to be supported Documentation has warned of its removal uses strict rules for determining the execution path … Continue reading Optimizer Mode »

What is Oracle Database Lite 10g

Last updated on September 15th, 2016 at 05:30 amOracle Database Lite 10g extends the power of grid computing to a mobile workforce with a complete, integrated, secure database for building, deploying, and managing mobile applications for a broad range of mobile devices. Oracle Database Lite 10g enables persistent access to critical information and applications without requiring continuous connectivity to back-end enterprise systems. As a result, … Continue reading What is Oracle Database Lite 10g »

Oracle DBA: Hanganalyze and system state dump

Last updated on August 31st, 2016 at 01:11 pmMany times Oracle database get hangs due to locks or latching issue and Nobody can login to the system. It becomes critical and  And Usually we do shutdown abort to immediately resolve the issue. But  it is good to take system state dump/hanganalyze  at 1 min interval to analyze the locking or latching  issue afterwards before doing … Continue reading Oracle DBA: Hanganalyze and system state dump »

how to perform relink in Oracle

Last updated on August 31st, 2016 at 01:30 pmSometimes Oracle components may start giving strange issues like coredump  or the Operating system is patched, In these cases it is always recommended to try oracle relink to resolve the issues.A Oracle relink is also necessary whenever you install a new release or a patchset or when there is a patch or upgrade to the OS environment. Oracle … Continue reading how to perform relink in Oracle »