How to check Patch application in Oracle E-Business Suite instance R12.1/R12.2

Comment

We often need to find out what all patches are applied to the EBS instance. Now this vary as per the EBS instance version. We have different queries for R12.1 and R12.2. For R12.0/R12.1 ad_bugs tables contains the information for all the patches applied to the system. we can used below query to find out the patch application in the instance sqlplus apps/<APPS PASSWORD> SELECT DISTINCT RPAD(a.bug_number, … Continue reading How to check Patch application in Oracle E-Business Suite instance R12.1/R12.2 »

Oracle Performance tuning Glossary

Comment

Last updated on September 5th, 2018 at 04:26 amHere is the glossary on Oracle Performance tuning. It is quite complex and interesting topics. Oracle performance tuning need through analysis  and knowledge of all the aspect of Oracle database Oracle Parser It performs syntax analysis as well as semantic analysis of SQL statements for execution, expands views referenced in the query into separate query blocks, optimizing it … Continue reading Oracle Performance tuning Glossary »

How to use Oracle LISTAGG Function

Comment

Last updated on September 5th, 2018 at 04:37 amOracle LISTAGG function is an analytical function which allows us to concatenate the strings for measure_column for each GROUP based on the order_by_clause. This is present in Oracle from 11gR2 The syntax for the LISTAGG function in Oracle is LISTAGG (measure_column [, ‘delimiter’]) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)] Explanation of terms measure_column The column or expression whose … Continue reading How to use Oracle LISTAGG Function »

How to Solve ORA-00942 table or view does not exist

Comment

Last updated on September 4th, 2018 at 05:29 pmORA-00942 is one of the many errors which Oracle developer ,DBA often gets. Lets first look at the OERR output Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying … Continue reading How to Solve ORA-00942 table or view does not exist »

How to resolve the ORA-00936 missing expression

Comment

Last updated on July 12th, 2018 at 05:51 pmDescription : ORA-00936 missing expression is one of the common error everybody working in Oracle Sql must have faced some time. This generally happens when you omit important thing in the Sql statement i.e you left out an important chunk of what you were trying to run Reference : Oracle documentation Cause This Oracle error is mainly related … Continue reading How to resolve the ORA-00936 missing expression »

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

Comment

Last updated on August 8th, 2018 at 05:57 amORA-01017  is one of the common error seen in PLSQL programs. Here is what documentation says about this error Here are the checklist to run to resolve the ORA-01017 error 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 right password Incase … Continue reading ORA-01017: invalid username/password; logon denied Tips »

How to resolve ORA-29913 with external tables

1 Comment

Last updated on July 26th, 2018 at 09:25 amORA-29913 is one of the common error when working with external tables in Oracle database. We would be looking at various ways the error can be produced and what can be done to resolve the error External File not found or permission incorrect SQL> select * from Scott.example_external_table; select * from example_external_table * ERROR at line 1: … Continue reading How to resolve ORA-29913 with external tables »

Grep equivalent in Windows to search text

Comment

We use grep command to search for strings in the Unix Operation system. Now the same things on Windows can be done using these three free tools. These tools can do many work same as Grep command. Find command Lets check out some command tasklist|find “notepad” The below command searches for string in all the files ending .txt find “string name” *.txt findstr command It … Continue reading Grep equivalent in Windows to search text »

Sql Subqueries : Single, Multiple, Scalar and Correlated

Comment

Sql SubQueries 1) A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is a query within a query 2) A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. 3) Subqueries answer the queries that have multiple parts. … Continue reading Sql Subqueries : Single, Multiple, Scalar and Correlated »

What are Oracle Joins (Sql Joins)?

Comment

Last updated on August 22nd, 2018 at 11:28 amSometimes you need to display data from multiple tables. In order to perform that you need to link one table to another table.There are various ways in which we can obtain data from Multiple tables.Here I would be focusing on Oracle Joins with examples.This is valid for in general any Sql Joins also. I would be explaining … Continue reading What are Oracle Joins (Sql Joins)? »

Sql Set Operators: Union,Union All,Intersect and Minus

Comment

Using Sql Set Operators(Union,Union All,Intersect and Minus/Except) Sql Set operators can be used to select data from multiple tables. They combine the results of two or more queries. When using the Set operators a) Each SELECT clause must have the same number of column b) Column must be of same data-type c) Column should be specified in the same order in all the select clauses. … Continue reading Sql Set Operators: Union,Union All,Intersect and Minus »

what is materialized views in oracle?

1 Comment

Last updated on July 25th, 2018 at 05:05 pm What is materialized views in oracle A materialized view is a database object that contains the results of a query. It is different from simple view. These materialized view have data stored and when you query the materialized view,it returns data from the data stored. The FROM clause of the query can name tables, views, and … Continue reading what is materialized views in oracle? »