In this blog, I gathered few Interview Questions asked on Oracle Database. It will be very useful for 2-4 years of Experience IT Professionals.1. When we should use Materialized view and non materialized view.2. Difference between Ref cursor and sys ref cursor.3. What are the different types of TRIGGERS.4. Difference between SUBSTRING and INSTRING function.5. If a query is taking more time to display results, What’s first step will you perform to check this issue.6. Exceptional handling in Oracle procedure.7. Name PSEUDO columns in Oracle.8. Difference between Schema and User…
Category: ORACLE
GET HIERARCHICAL VALUES PRESENT IN SAME COLUMN OF A TABLE
Lets discuss about the parent and child relationship present in the same column of a table. That means both exists in the same column and there is always a identifier which says whether a record belongs to parent value or it belongs to child value. See the below data .As it is clearly seen from the data, parent and child record can be easily identified from column c1. Here , we need to apply self join to get parent and child values in the same row. See the below Query.…
Check SQL History and Monitor Sessions in PL/SQL Developer Tool
Today, i will discuss about how to monitor Sessions and check what all queries executed in pl/sql Sessions. First, In order to Monitor Sessions which are active /inactive in particular Schema in pl/sql developer tool, Go to Tools->Monitor Sessions. See the below Screen Shot for the same. Second, In order to see what all queries executed in particular Session , go to View -> SQL History. See the below image for the same.
GET COLUMN NAMES ON WHICH INDEX IS CREATED IN ORACLE
Today, i will share the query which will give the column names along with table name on which index is created.Below is the Query for the same. select a.index_name,a.table_name,b.column_name from all_indexes a, all_ind_columns b where a.table_name=b.table_name and a.index_name=b.index_name and a.uniqueness=’UNIQUE’ and a.table_name in <LLIST OF TABLE NAMES> Query to give column names which are not null in the table. select table_name,column_name from all_tab_columns where table_name like ‘%ABC%’ and NULLABLE=’N’ and owner=<SCHEMA_NAME> order by table_name
CHECK WHETHER VALUES ARE UPPERCASE OR NOT IN DATABASE
Today, i will discuss about how to check which values in the table for a particular column are uppercase or not. I have designed a Query using dual to achieve the same. Below is the Query. select * from (select ‘A’ as col1, ‘B’ as col2 from dual union select ‘a’ col1,’D’ as col2 from dual) A, (select ‘A’ as col1 ,’B’ as col2 from dual union select ‘a’ col1 ,’D’ as col2 from dual)B where upper(B.col1)=A.col1 and B.col2=A.col2; I created table A and table B using dual command .…