How could I get distinct entries from a table?
The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table... Read more »
‘Oracle Interview Questions’ - KyaPoocha.com
How to get the results of a Query sorted in any order?
You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The... Read more »
How can I find the total number of records in a table?
You could use the COUNT keyword , example SELECT COUNT(*) FROM emp WHERE age>40 Read more »
What is GROUP BY?
The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without... Read more »
What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.
Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes Truncating: (Data alone deleted), Performs an automatic... Read more »
What are cursors?
Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information. Disadvantages... Read more »
What are triggers? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. Triggers can’t... Read more »
What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types... Read more »
What is a self join?
Self join is just like any other join, except that two instances of the same table will be joined in the query. Read more »
Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of... Read more »
Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous... Read more »
A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint. Read more »
Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to... Read more »
Give the stages of instance startup to a usable state where normal users may access it
STARTUP NOMOUNT - Instance startup. STARTUP MOUNT - The database is mounted. STARTUP OPEN - The database is opened Read more »
What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the information came from. Read more »
How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql. Use the explain plan set statement_id = ‘tst1? into plan_table for a SQL statement. Look at the explain plan with utlxplp.sql... Read more »
How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size... Read more »
Explain an ORA-01555.
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks.... Read more »
Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside. Read more »