How to Install Oracle Express Edition for SQL Practice

Comment

We have to practice the sql statement to become well versed with syntax, restrictions and expression. Oracle Express Edition for windows is a good environment to do practice on your laptop or Desktop . I am going to provide you the step by step installation guide for that. Step by Step Oracle Express Edition Installation  Download the 18c Express edition from below link https://www.oracle.com/database/technologies/xe-downloads.html Choose … Continue reading How to Install Oracle Express Edition for SQL Practice »

Oracle Data types

Comment

When you create an Oracle table, you have to specify a datatype for each column, which is associated with a specific storage format, constraints, and a valid range of values Oracle provides the following categories of built-in datatypes: Character Datatypes Numeric Datatypes DATE Datatype LOB Datatypes RAW and LONG RAW Datatypes ROWID and UROWID Datatypes Character datatype -CHAR, NCHAR, VARCHAR2 & NVARCHAR2. The CHAR datatype … Continue reading Oracle Data types »

ORA-00054: resource busy and acquire with NOWAIT specified

Comment

Last updated on September 14th, 2019 at 01:53 pmORA-00054: resource busy and acquire with NOWAIT specified is a common error seen in Oracle Database Reference: Oracle documentation This generally happens when you try to execute a DDL on the table which is locked by transaction .It also happens if  select for update statement is executed with NOWAIT option Example SQL> alter table emp add (middlename varchar2(15)); … Continue reading ORA-00054: resource busy and acquire with NOWAIT specified »

ORA-01017: invalid username/password; logon denied Tips

Comment

Last updated on September 11th, 2019 at 06:03 pmORA-01017  is one of the common error seen while connecting to oracle database. Here is what documentation says about this error Here are the checklist to run to resolve the ORA-01017: invalid username/password The main issue with an ORA-01017 error is an invalid user ID and passwords combination.  You have to make sure ,you are entering the … Continue reading ORA-01017: invalid username/password; logon denied Tips »

how to check degree of parallelism in oracle & Default Parallelism

Comment

Last updated on September 1st, 2019 at 05:38 pmhow to check degree of parallelism in oracle Suppose we execute the below query in Oracle database select /*+ PARALLEL(JN) */ count(*) from ar.RA_INTERFACE_LINES_ALL jn; Here we can see , we have not specified the degree of the parallelism.Now there are two cases i. if we have defined degree at the table level, then that degree will … Continue reading how to check degree of parallelism in oracle & Default Parallelism »

Top 10 Parallel Processing Views and Monitoring Queries in Oracle Database

Comment

Last updated on September 1st, 2019 at 06:01 pmParallel Processing Data Dictionary views In this section we are trying to give a glimpse of Parallel Processing Data Dictionary views Oracle keep changing the view name,but below query should be able to give all the views SELECT table_name FROM dict WHERE table_name LIKE ‘V%PQ%’ OR table_name like ‘V%PX%‘; Examples V$PX_SESSION V$PX_SESSTAT V$PX_PROCESS V$PX_PROCESS_SYSSTAT Some important views … Continue reading Top 10 Parallel Processing Views and Monitoring Queries in Oracle Database »

How to grant access to v$ views (v$session ,v$instance)

Comment

Last updated on September 2nd, 2019 at 05:52 pmOne can’t grant direct access V$session as v$session is a synonym. Same is true for v$instance In fact this is true for all the v$ views are they are all synonym’s SQL> GRANT SELECT ON v$session TO test_user; grant select on v$session to test_user * ERROR at line 1: ORA-02030: can only select from fixed tables/views Query … Continue reading How to grant access to v$ views (v$session ,v$instance) »

How to apply patches in Oracle weblogic in Linux & windows

Comment

Here in this post we will see how to check patches applied in weblogic in 10.3.6 version , 12c (12.1.1  and 12.1.2 and later ). how to apply patches in weblogic in 10.3.6 version and up to 12.1.1 Weblogic Smart Update utility (bsu)  is used to apply patches in 10.3.6 and 12.1.1 version of weblogic. Weblogic Smart Update utility (bsu) lies in $MW_HOME/utils/bsu/ BSU Utility … Continue reading How to apply patches in Oracle weblogic in Linux & windows »

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 »