Top Oracle Interview Questions

thVY67IZRU

Dear Readers,

This post contains all the basic and advanced topics that are mostly being asked in Interviews. Although this contains a long list of questions , but after reading this you will be fully prepared and confident to crack any interview regarding Oracle PL/SQL.

we will be uploading a pdf that will contain all the answers of the given below topics very soon and it will be deliver to your e-mail address. kindly subscribe us to avail the pdf.

 

10g Enhancements 

Normalization 

  • What is Normalization?
  • Why Normalization needed?
  • Types of normalization.
  • Difference between 1nf, 2nf, 3nf and BCNF.
  • In which form we add primary key to table?

Tables and constraints 

  • How many columns can be added in columns?
  • Difference between create or replace and drop & create ?
  • What is Primary key?
  • Difference between primary and unique key?
  • How many primary key can be created on table?
  • How many unique keys can be created on tables?
  • How many null values allowed for unique key constraint?
  • How many check constraints can be created on table?
  • Syntax of adding primary key, unique key, check constraint, foreign key.
  • Can you drop a table if its primary key is referred in some other table? If yes, what happens? If no, why?
  • How many long columns allowed in table?
  • Can you add not null column in table? If yes, How?
  • What is global temporary table?
  • Syntax of global temporary table.
  • Explain Delete rows on commit and Preserve rows on commit clause.
  • If you truncate global temporary table in one session, will it truncate table from all session?
  • What is importance of storage clause in table definition?

Clusters 

  • What is cluster?
  • Advantage of cluster.
  • Can you create index on cluster.
  • Performance benefit of cluster.
  • If cluster is created and table is assigned a cluster, can you drop cluster directly?
  • Does optimizer show cluster information in explain plan? If yes, how?

Indexes 

  • What is index?
  • Types of indexes.
  • When you create primary key which index is created on table?
  • When you create unique key which index is created on table?
  • Difference between unique and non-unique index.
  • When non-unique index created on table.
  • What is B-tree index?
  • What is Bitmap index?
  • When we should bitmap index?
  • What is function based index?
  • Bitmap join index
  • How data managed in b-tree index structure?
  • How data managed in bitmap index structure?
  • How many indexes can be created on one column?
  • What is B stands for in B-tree index?
  • Advantage of index.
  • Disadvantage of index.
  • What is reverse key index?
  • How oracle maintains indexes?
  • What is composite index?
  • What is Index organization table? Advantages?

Views 

  • What are views?
  • How to create read only view?
  • Can you update view if created using more than 1 table? If yes, How?
  • When view cannot be updated?
  • What are with check option views?
  • What are inline views?
  • What is key-preserved table?
  • Can you create trigger on view?

Materialized view 

  • What is materialized view?
  • Advantage of materialized view.
  • Difference between view and materialized view?
  • Types of materialized view.
  • Refresh types of materialized view.
  • What is materialized view log?
  • Default type of materialized view?
  • What are updatable materialized views?
  • What are writable materialized views?
  • What you need to do to make it updatable?
  • Which package can be used to make materialized view updatable?
  • Primary key materialized views
  • Row ID materialized view
  • Complex materialized view
  • Can you created index on materialized view?
  • What is query rewrite option in materialized view?
  • What is build immediate and build deferred option in materialized view?
  • How can you go for incremental refresh for materialized view?

External Tables 

  • What is external table?
  • Syntax of external table.
  • Types of access drivers of external table.
  • What is the function of bad file, log file, discard file?
  • What Load when clause used for in external table?
  • What is skip clause?
  • Can you load 3 files at the same using oracle_loader access driver?
  • What parallel clause used for?
  • What is reject clause?
  • Can you unload data from one dump file to another dump file? How?
  • Can you create index on external table?
  • Can you create trigger on external table?
  • Can external table’s statistics gathered?
  • What privileges needed to create external table?
  • How can you improve performance for external table?
  • Difference between sql loader and external table.
  • Is DML operations allowed on external table?
  • Can you create temporary table on external table?

Partitions 

  • What is partition?
  • Types of partition.
  • Use of partition.
  • How many partitions can be created on cluster?
  • Can indexes created on partitions?
  • Syntax of creating index on partition.
  • What is range partitioning?
  • What is hash partitioning?
  • What is list partitioning?
  • Does optimizer show partition information in explain plan? If yes, how?
  • How would you know if particular value is picked from particular partition?
  • Can you partition existing table? If yes, how?

Collections 

  • What is collection?
  • What are difference types of collections available in oracle?
  • What is difference between plsql table, nested table and varrays?
  • Initialization required for plsql table?
  • What happens if you try to element from collection and it is not present in collection?
  • Difference types of exceptions related to collection.
  • What is exits method in case of collection? What is its return type?
  • What is use of extend method?
  • What is trim method?
  • Can you delete particular element from varrays?
  • How would you decide which collection to be used for your requirement?
  • Plsql table vs nested table.
  • Nested table vs varrays.
  • Plsql table vs varrays.

Analytical functions 

  • What are analytical functions?
  • Examples.
  • Rank vs Dense_rank.
  • What is partition by clause?
  • What is over clause?
  • LAG and LEAD

Aggregate functions 

  • What are Aggregate functions?
  • Difference between analytical and aggregate functions?
  • Rollup
  • Cube
  • Grouping
  • Grouping_id

Sub queries, co related sub queries 

  • What is sub query?
  • What is co related sub query?
  • When co related sub query should be use?

Cursors, ref cursors 

  • What are Cursors?
  • How cursors work?
  • Types of cursors
  • Cursor attributes
  • Difference between implicit and explicit cursors.
  • Difference between cursor and collection.
  • What are ref cursors?
  • What are the types of ref cursors
  • Syntax of ref cursors.
  • How to declare cursor globally?

Triggers 

  • What are triggers? Purpose of triggers.
  • Different types of triggers.
  • How many triggers can be created on table?
  • Statement level vs row level trigger?
  • What is mutating table error? How to avoid that?
  • Can you commit inside trigger?
  • Can you perform DDL operations inside triggers?
  • Can you perform DML operations inside triggers?
  • What are two virtual columns?

 

Packages vs functions vs procedures 

  • Difference between packages vs functions vs procedures
  • Advantage of packages.
  • What is package specification?
  • What is package body?
  • Can perform DML operations inside function?
  • Can you use IN OUT parameter in function? If yes, can you use that function in SELECT query?
  • Can we use return clause in procedure?
  • What return; statement will do in procedure?
  • What happens when you write return 1; statement in procedure?
  • Can you use procedure in SELECT query?
  • Packaged programs are loaded in which part of memory?
  • What are global variables and where we can declare them?
  • Can you create bodiless package?
  • Can you create package body without specification?
  • What are pipelined functions?

Pragma 

  • What is Pragma?
  • Different types of Pragmas.
  • What is Pragma exception_init?
  • What is Pragma restrict_references?
  • What is Pragma Autonomous_transaction?
  • Pragma serially_reusable
  • Pragma inline.
  • Syntax of all Pragmas.

Exceptions 

  • What is exception?
  • Types of exceptions.
  • Types of oracle exceptions
  • Examples of user defined exceptions.
  • How can you handle exception when operating on bulk DML operations?

Performance tuning 

  • What is tuning?
  • What are the types of various techniques of tuning?
  • What is tkprof?
  • Where does trace file reside in system?
  • Dbms_profiler.
  • Tables related to Dbms_profiler.
  • Procedure related to Dbms_profiler.
  • What is Explain plan?
  • How to generate and display explain plan?
  • Dbms_stats.
  • What are different types of statics can be gathered using Dbms_stats?
  • When to index the table?
  • When to partition the table?
  • What append hint does?
  • Which is better IN or EXISTS?
  • What is AWR?
  • What is snapshot? How frequently it’s taken by optimizer?
  • What ADDM?
  • Yesterday my query was running fine, but today it’s taking lot of time. What could be probable reason and work around?
  • What is Cost based optimizer?
  • What is Rule based Optimizer?
  • Different types of Optimizer modes.
  • While generating any execution plan, what operations are performed by optimizer?
  • What are hints?
  • How to add hint to your DML statements?

Miscellaneous 

  • With clause vs global temporary table
  • IN,ANY,ALL predicates
  • Joins - left outer join, right out join, self-join, equi join
  • Difference between delete, truncate and drop.
  • Execute immediate?
  • Bulk collect
  • Bulk collect LIMIT clause
  • FORALL
  • What is the use of INDICES OF and VALUES OF?
  • Query: How can I select only the even/odd rows from an Oracle table?
  • Query: Find out duplicate records from table?
  • Query: Find out missing numbers from number column in a table.
  • Query: Find 3rd highest salary of employees.
  • Query: Find 3rd highest salary without analytical function.
  • Query: Find cumulative sum of salary order by employee name.
  • Query: Find max,avg salary of department along with emp_id,emp_name,dept_name,salary
  • Query: Use Row_number to get top N salary.
  • What is output: Select 1 id1, 2 id2 from dual UNION select 1 id2 , 2 id1 from dual;
  • What is output: Select * from ALL_OBJECTS where ROWNUM between 50 and 100;
  • What is output: Select SYSDATE from DUAL where null is null;
  • What is output: Select * from dual where null=null;
  • What is output: Select * from dual where dummy =’X’;
  • Total length of ROWID?  ROWID needs 10 bytes of storage on disk and is displayed by using 18 characters

Oracle Architecture 

  • What is difference between oracle SID and Oracle service name?
  • What is an Oracle Instance?
  • What information is stored in Control File?
  • When you start an Oracle DB which file is accessed first?
  • What is the Job of SMON, PMON processes?
  • What is Instance Recovery?
  • What is written in Redo Log Files?
  • How do you control number of Datafiles one can have in an Oracle database?
  • How many Maximum Datafiles can there be in an Oracle Database?
  • What is a Tablespace?
  • What is the purpose of Redo Log files?
  • Which default Database roles are created when you create a Database?
  • What is a Checkpoint?
  • Which Process reads data from Datafiles?
  • Which Process writes data in Datafiles?
  • Can you make a Datafile auto extendible. If yes, how?
  • What is PCT Increase setting?
  • What is PCTFREE and PCTUSED Setting?
  • What is 01555 – Snapshot Too Old error and how do you avoid it?
  • Can you audit SELECT statements?
  • What is a Locally Managed Tablespace?
  • A table is having few rows, should you create indexes on this table?
  • A Column is having many repeated values which type of index you should create on this column, if you have to?
  • When should you rebuilt indexes?
  • What will be size of 4th extent?
  • Can you change SHARED_POOL_SIZE online?
  • Can you redefine a table Online?
  • You want users to change their passwords every 2 months. How do you enforce this?
  • What is Automatic Management of Segment Space setting?
  • What is COMPRESS and CONSISTENT setting in EXPORT utility?
  • What is the difference between Direct Path and Convention Path loading?
  • What is a Global Index and Local Index?
  • What is difference between Multithreaded/Shared Server and Dedicated Server?
  • How do see how much space is used and free in a tablespace?
  • What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation?
  • What are bind variables?
  • What is the difference between data block/extent/segment?
  • What is the difference between PGA and UGA?
  • What is SGA? Define structure of shared pool component of SGA?
  • What is the difference between SMON and PMON processes?
  • What is a system change number (SCN)?
  • What is the main purpose of ‘CHECKPOINT’ in oracle database? How do you automatically force the oracle to perform a checkpoint?
  • What happens when we fire SQL statement in Oracle?
  • What is the use of large pool, which case you need to set the large pool?
  • What does database do during the mounting process?
  • What are logfile states?
  • What is log switch?
  • How to check Oracle database version?
  • Explain Oracle Architecture?
  • Oracle Instance:
  • Oracle server:
  • Oracle database:
  • Instance memory Structures:
  • System Global Area (SGA):
  • SGA Memory structures:
  • Shared Pool :
  • Library Cache:
  • Data Dictionary Cache :
  • Database Buffer Cache:
  • Redo Log Buffer :
  • User process:
  • Server process:
  • Program Global Area (PGA):
  • Background processes:
  • DBWn writes when:
  • Log Writer (LGWR) writes:
  • System Monitor (SMON) Responsibilities:
  • Checkpoint (CKPT) Responsible for:
  • Why do you run orainstRoot and ROOT.SH once you finalize the Installation?
  • Oracle Database 11g New Feature for DBAs?
  • What is the Difference Between Local Inventory and Global Inventory?
     What is oraInventory ?
  • What is Global Inventory ?
  • What is Local Inventory ?
  • What is Oracle Home Inventory?
  • Can I have multiple Global Inventory on a machine ?
  • What to do if my Global Inventory is corrupted ?
  • What is RESULT Cache?
  • 11G Backgroung Processes?
  • If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted ?
  • What is SGA_TARGET and SGA_MAX_SIZE ?
  • What happens when you run ALTER DATABASE OPEN RESETLOGS ?
  • Difference between RESETLOGS and NORESETLOGS ?
  • What is Database Incarnation ?
  • How to view Database Incarnation history of Database ?
  • What is difference between Restoring and Recovery of database?
  • What is the difference between complete and incomplete recovery?
  • What is the benefit of running the DB in archivelog mode over no archivelog mode?
  • If an oracle database is crashed? How would you recover that transaction which is not in backup?
  • What is the difference between HOTBACKUP and RMAN backup?
  • Can we use Same target database as Catalog database?
  • Why RMAN incremental backup fails even though full backup exists?
  • Can we perform RMAN level 1 backup without level 0?
  • How to put Manual/User managed backup in RMAN?
  • How to check RMAN version in oracle?
  • What happens actually in case of instance Recovery?
  • ORACLE PATCHING,CLONING & UPGRADE
  • In which months oracle release CPU patches?
  • Is it possible to apply OPATCH without downtime?
  • You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from it?
  • If both CPU and PSU are available for given version which one, you will prefer to apply?
  • PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?
  • How to Download Patches, Patchset or Opatch from metalink?
  • What is the recent Patch applied?
  • What is OPatch?
  • How to Apply Opatch in Oracle?
  • Patching Oracle Software with OPatch ?
  • What is Opactch in Oracle?
  • What is ASM in Oracle?
  • Advantages of ASM in Oracle?
  • What is ASM instance in Oracle?
  • What are ASM Background Processes in Oracle?
  • What are the components of components of ASM are disk groups?
  • Advantages of ASM in Oracle?
  • Why should we use separate ASM home?
  • How many ASM instances should one have?
  • How many diskgroups should one have?
  • What is Oracle RAC One Node?
  • What is a virtual IP address or VIP?
  • What is the use of VIP?
  • What is voting disk?
  • How many voting disks are you maintaining ?
  • Why we need to keep odd number of voting disks ?
  • What are Oracle RAC software components?

Oracle Performance Tuning 

  • Application user is complaining the database is slow.How would you find the performance issue of SQL queries?
  • What is the use of iostat/vmstat/netstat command in Linux?
  • If you are getting high “Busy Buffer waits”, how can you find the reason behind it?
  • What to Look for in AWR Report and STATSPACK Report?
  • What is the difference between DB file sequential read and DB File Scattered Read?
  • Which factors are to be considered for creating index on Table? How to select column for index?
  • Is creating index online possible?
  • How to recover password in oracle 10g?
  • What is Secure External password Store (SEPS)?
  • What is the difference between Redo, Rollback and Undo?
  • You have more than 3 instances running on the Linux server? How can you determine which shared memory and semaphores are associated with which instance?
  • Why drop table is not going into Recycle bin?
  • Temp Tablespace is 100% FULL and there is no space available to add data files to increase temp tablespace. What can you do in that case to free up TEMP tablespace?
  • What are Row Chaning and Row Migration?
  • How to find out background processes ?

 

Top Oracle Interview Questions
4.64 (92.86%) 14 votes