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

How to move LOB segment to another tablespace

Comment

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’; More queries can found at How to find space usage of lob segments in Oracle database Suppose you know the lob segment name and you want to know which … Continue reading How to move LOB segment to another tablespace »

RANK, DENSE_RANK and ROW_NUMBER :Oracle Analytic functions

Comment

Oracle Analytic functions compute an aggregate value based on a group of rows called window which determines the range of rows used to perform the calculations for the current row. Following are most used Analytic functions. – RANK, DENSE_RANK and ROW_NUMBER – LAG and LEAD – FIRST_VALUE and LAST_VALUE I would be discussing about RANK, DENSE_RANK and ROW_NUMBER analytics functions Here is the general syntax … Continue reading RANK, DENSE_RANK and ROW_NUMBER :Oracle Analytic functions »

How to apply the technology patches to EBS R12.2

Comment

We already know the general practice to apply application patches to R12.2 through online patching Adop(Ad online patching utility) explained R12.2 R12.2 Online patching cycle Summary Let’s explore the steps on how to apply database patches , weblogic patches , 10.1.2 patches , web-tier patches  to the R12.2 tech stack. We can apply the same online patching cycle for the below technology patches a. Weblogic … Continue reading How to apply the technology patches to EBS R12.2 »

how to perform relink in Oracle

Comment

Last updated on August 18th, 2019 at 05:52 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 »

LEAD and LAG : Oracle Analytic Functions

Comment

Last updated on August 18th, 2019 at 05:47 pmLead Function LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row. The general syntax of LEAD is shown below: LEAD (<expr>, <offset>, <default>) OVER (<analytic_clause>) <expr> is the expression to compute from the leading row. <offset> … Continue reading LEAD and LAG : Oracle Analytic Functions »

how to use XMLImporter and FndXdfCmp in Oracle EBS

Comment

Last updated on August 18th, 2019 at 05:53 pmXMLImporter and FndXdfCmp are the implement utilities in Oracle E business Suite for implementing new schema definition and changes Here are the steps on how to use XMLImporter and FndXdfCmp XMLImporter java -classpath “${CLASSPATH}” oracle.jrad.tools.xml.importer.XMLImporter $APPL_TOP/ahl/12.0.0/mds/prd/webui/WorkorderDetailsRN.xml -username apps -password apps -dbconnection “(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=exam.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TEST)))” -userId “1” -rootPackage /oracle/apps/ahl -rootdir $APPL_TOP/ahl/12.0.0/mds java -classpath “${CLASSPATH}” oracle.jrad.tools.xml.importer.XMLImporter $APPL_TOP/ahl/12.0.0/mds/prd/webui/WorkorderDocumentsRN.xml -username apps -password apps -dbconnection … Continue reading how to use XMLImporter and FndXdfCmp in Oracle EBS »

FNDCPASS and AFPASSWD

Comment

Last updated on August 18th, 2019 at 05:51 pmFNDCPASS is an important utility in EBS application and it is located in $FND_TOP/bin.  It is used to change password for apps schema, oracle EBS schema password and application user password change. $FNDCPASS FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS FNDCPASS apps/apps 0 Y system/manager ORACLE FNDCPASS apps/apps 0 Y system/manager USER When you execute FNDCPASS, it … Continue reading FNDCPASS and AFPASSWD »

How to List All Tables in Oracle

Comment

All the table and column information are stored in SYS.TAB$ and SYS.COL$ tables.  Oracle has provided data dictionary views  to get the information about table and columns There are three categories of  views To list all tables owned by the current user, select tablespace_name, table_name from user_tables; To list all tables in a Oracle database select owner,table_name,tablespace_name, from dba_tables; To list all tables accessible to … Continue reading How to List All Tables in Oracle »

Oracle DBA: Hanganalyze ,system state dump,v$wait_chains

Comment

Many 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 shutdown abort so that we can find the … Continue reading Oracle DBA: Hanganalyze ,system state dump,v$wait_chains »

How to identify corrupts blocks in the Oracle database and correct it

Comment

Last updated on July 28th, 2019 at 07:58 amThere are two type of corruption. a. Physical corruption: This happens generally due to media corruption. In this case,block does have invalid checksum.it may contains zero. This is generally the result of infrastructure issue like media,network ,memory. RMAN block recover can be used to remove physical corruption We see the error ORA-01578: ORACLE data block corrupted in … Continue reading How to identify corrupts blocks in the Oracle database and correct it »

What is Skew in Database?

Comment

Data skew primarily refers to a non uniform distribution in a data-set. A non-uniform distribution might impact the system if the proper execution plan is not selected depending on the data values. Data is generally said to be skewed if there is presence of significant popular values in the column.But it can happen with non-uniformity of range also. Let us go through example for both to … Continue reading What is Skew in Database? »