CPU vs Core Vs Socket

Physical CPU It is the physical Processor on the server.Each CPU is assigned to one socket Core Each Physical CPU consist of single or Multiple Processing unit which are called Core of the Physical CPU. A Physical CPU could be dual core,quad core .It is the core which execute all the program  instructions. In multiple core processor,multiple programs can be executed in parallel providing better … Continue reading CPU vs Core Vs Socket »

What is a Bind Variable in Oracle

Last updated on October 8th, 2019 at 05:47 pmWhat is a Bind Variable? -It is a placeholder for a value .It is replaced by actual value when the SQL statement is executed -In PL/SQL it is called a local or global variable. DECLARE l_empl_id DEPT.emp_id%TYPE; BEGIN select first_name  from DEPT  where id = l_empl_id; END; -in SQL*Plus it is a variable variable a number BEGIN :a … Continue reading What is a Bind Variable in Oracle »

How to Install Oracle Express Edition for SQL Practice

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

Last updated on October 8th, 2019 at 05:43 pmWhen 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 … Continue reading Oracle Data types »

ORA-00054: resource busy and acquire with NOWAIT specified

Last updated on September 24th, 2019 at 05:35 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

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

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

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)

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

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

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

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 »