|
|
|
Welcome to Oracle FAQs and Interview Quesitons and Answers section.
The Oracle Interview Questions listed below have been collected through
various forums, websites, blogs and other resources. We have included the
references to the originators where we can but if you feel that you should be
credited for a given problem please contact us.
If you have a product or service that you would like to be included in the FAQ
section, please contact us at FAQs@megasolutions.net
|
|
|
| 1.1 Describe the
difference between a procedure, function and anonymous pl/sql block.
|
| 1.2 What is a
mutating table error and how can you get around it?
|
| 1.3 Describe the use
of %ROWTYPE and %TYPE in PL/SQL
|
| 1.4 What packages
(if any) has Oracle provided for use by developers?
|
1.5 Describe the use
of PL/SQL tables
|
| 1.6 When is a
declare statement needed ?
|
| 1.7 In what order
should a open/fetch/loop set of commands in a PL/SQL block be implemented if
you use the %NOTFOUND cursor variable in the exit when statement? Why?
|
| 1.8 What are SQLCODE
and SQLERRM and why are they important for PL/SQL developers?
|
| 1.9 How can you find
within a PL/SQL block, if a cursor is open?
|
| 1.10 How can you
generate debugging output from PL/SQL?
|
| 1.11 What are the
types of triggers?
|
|
|
| 2.1 Give one method for
transferring a table from one schema to another
|
| 2.2 What is the purpose
of the IMPORT option IGNORE? What is it?s default setting?
|
| 2.3 You have a rollback
segment in a version 7.2 database that has expanded beyond optimal, how can it
be restored to optimal?
|
| 2.4 If the DEFAULT and
TEMPORARY tablespace clauses are left out of a CREATE USER command what
happens? Is this bad or good? Why?
|
| 2.5 What are some of the
Oracle provided packages that DBAs should be aware of?
|
| 2.6 What happens if the
constraint name is left out of a constraint clause?
|
| 2.7 What happens if a
tablespace clause is left off of a primary key constraint clause?
|
| 2.8 What is the proper
method for disabling and re-enabling a primary key constraint?
|
| 2.9 What happens if a
primary key constraint is disabled and then enabled without fully specifying
the index clause?
|
| 2.10 (On UNIX)
When should more than one DB writer process be used? How many should be used?
|
| 2.11 You are
using hot backup without being in archivelog mode, can you recover in the event
of a failure? Why or why not?
|
| 2.12 What causes the
"snapshot too old" error? How can this be prevented or mitigated?
|
| 2.13 How can you tell
if a database object is invalid?
|
| 2.14 A user is getting
an ORA-00942 error yet you know you have granted them permission on the table,
what else should you check?
|
| 2.15 A developer is
trying to create a view and the database won?t let him. He has the "DEVELOPER"
role which has the "CREATE VIEW" system privilege and SELECT grants on the
tables he is using, what is the problem?
|
| 2.16 If you have an
example table, what is the best way to get sizing data for the production table
implementation?
|
| 2.17 How can you find
out how many users are currently logged into the database? How can you find
their operating system id?
|
| 2.18 A user selects
from a sequence and gets back two values, his select is: SELECT pk_seq.nextval
FROM dual; What is the problem?
|
| 2.19 How can you
determine if an index needs to be dropped and rebuilt?
|
|
|
| 3.1 GHow can
variables be passed to a SQL routine?
|
| 3.2 You want to
include a carriage return/linefeed in your output from a SQL script, how can
you do this?
|
| 3.3 How can you call
a PL/SQL procedure from SQL?
|
| 3.4 How do you
execute a host operating system command from within SQL?
|
| 3.5 You want to use
SQL to build SQL, what is this called and give an example
|
| 3.6 What SQLPlus
command is used to format output from a select?
|
| 3.7 You want to
group the following set of select returns, what can you group on?
|
| 3.8 What special
Oracle feature allows you to specify how the cost based system treats a SQL
statement?
|
| 3.9 You want to
determine the location of identical rows in a table before attempting to place
a unique index on the table, how can this be done?
|
| 3.10 What is a
Cartesian product?
|
| 3.11 You are
joining a local and a remote table, the network manager complains about the
traffic involved, how can you reduce the network traffic?
|
3.12 . What is the
default ordering of an ORDER BY clause in a SELECT statement?
|
| 3.13 What is tkprof
and how is it used?
|
| 3.14 What is
explain plan and how is it used?
|
| 3.15 . How do you
set the number of lines on a page of output? The width?
|
| 3.16 How do you
prevent output from coming to the screen?
|
| 3.17 How do you
prevent Oracle from giving you informational messages during and after a SQL
statement execution?
|
| 3.18 How do you
generate file output from SQL?
|
|
|
| 4.1 A tablespace has
a table with 30 extents in it. Is this bad? Why or why not.
|
| 4.2 How do you set up
tablespaces during an Oracle installation?
|
| 4.3 You see multiple
fragments in the SYSTEM tablespace, what should you check first?
|
| 4.4 What are some
indications that you need to increase the SHARED_POOL_SIZE parameter?
|
| 4.5 What is the
general guideline for sizing db_block_size and db_multi_block_read for an
application that does many full table scans?
|
| 4.6 What is the
fastest query method for a table?
|
| 4.7 Explain the use
of TKPROF? What initialization parameter should be turned on to get full TKPROF
output?
|
| 4.8 When looking at
v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How
do you correct it?
|
| 4.9 When should you
increase copy latches? What parameters control copy latches?
|
| 4.10 Where can you
get a list of all initialization parameters for your instance? How about an
indication if they are default settings or have been changed?
|
| 4.11 Describe hit
ratio as it pertains to the database buffers. What is the difference between
instantaneous and cumulative hit ratio and which should be used for tuning?
|
| 4.12 . Discuss row
chaining, how does it happen? How can you reduce it? How do you correct it?
|
| 4.13 When looking at
the estat events report you see that you are getting busy buffer waits. Is this
bad? How can you find what is causing it?
|
| 4.14 If you see
contention for library caches how can you fix it?
|
| 4.15 If you see
statistics that deal with "undo" what are they really talking about?
|
| 4.16 If a tablespace
has a default pctincrease of zero what will this cause (in relationship to the
smon process)?
|
| 4.17 If a tablespace
shows excessive fragmentation what are some methods to defragment the
tablespace? (7.1,7.2 and 7.3 only)
|
| 4.18 How can you
tell if a tablespace has excessive fragmentation?
|
| 4.19What can cause a
high value for recursive calls? How can this be fixed?
|
| 4.20 If you see a
pin hit ratio of less than 0.8 in the estat library cache report is this a
problem? If so, how do you fix it?
|
| 4.21 If you see the
value for reloads is high in the estat library cache report is this a matter
for concern?/a>
|
| 4.22 You look at the
dba_rollback_segs view and see that there is a large number of shrinks and they
are of relatively small size, is this a problem? How can it be fixed if it is a
problem?
|
| 4.23 You look at the
dba_rollback_segs view and see that you have a large number of wraps is this a
problem?
|
| 4.24 You see
multiple extents in the temporary tablespace. Is this a problem?
|
|
|
| 5.1 Define OFA.
|
| 5.2 How do you
set up your tablespace on installation?
|
| 5.3 . What
should be done prior to installing Oracle (for the OS and the disks)?
|
| 5.4 You have
installed Oracle and you are now setting up the actual instance. You have been
waiting an hour for the initialization script to finish, what should you check
first to determine if there is a problem?
|
| 5.5 When
configuring SQLNET on the server what files must be set up?
|
| 5.6 When
configuring SQLNET on the client what files need to be set up?
|
| 5.7 What must
be installed with ODBC on the client in order for it to work with Oracle?
|
| 5.8 You have
just started a new instance with a large SGA on a busy existing server.
Performance is terrible, what should you check for?
|
| 5.9 What OS
user should be used for the first part of an Oracle installation (on UNIX)?
|
| 5.10 When
should the default values for Oracle initialization parameters be used as is?
|
| 5.11 . How
many control files should you have? Where should they be located?
|
| 5.12 How many
redo logs should you have and how should they be configured for maximum
recoverability?
|
| 5.13 WYou have
a simple application with no "hot" tables (i.e. uniform IO and access
requirements). How many disks should you have assuming standard layout for
SYSTEM, USER, TEMP and ROLLBACK tablespaces?
|
|
|
| 6.1 Describe
third normal form?
|
| 6.2 .Is the
following statement true or false: "All relational databases must be in third
normal form" Why or why not?
|
6.3
What is an ERD?
|
| 6.4 Why are
recursive relationships bad? How do you resolve them?
|
| 6.5 What does a
hard one-to-one relationship mean (one where the relationship on both ends is
"must")?
|
| 6.6 How should
a many-to-many relationship be handled?
|
| 6.7 What is an
artificial (derived) primary key? When should an artificial (or derived)
primary key be used?
|
| 6.8 When should
you consider denormalization?
|
|
|
| 7.1 How can
you determine if an Oracle instance is up from the operating system level?
|
| 7.2 Users
from the PC clients are getting messages indicating :
|
| 7.3 . Users
from the PC clients are getting the following error stack:
|
| 7.4 How can
you determine if the SQLNET process is running for SQLNET V1? How about V2?
|
| 7.5 What
file will give you Oracle instance status information? Where is it located?
|
| 7.6 Users
aren?t being allowed on the system. The following message is received:
|
| 7.7 Where
would you look to find out if a redo log was corrupted assuming you are using
Oracle mirrored redo logs?
|
| 7.8 You
attempt to add a datafile and get:
|
| 7.9 You look
at your fragmentation report and see that smon hasn?t coalesced any of you
tablespaces, even though you know several have large chunks of contiguous free
extents. What is the problem?
|
|
|
| 8.1 Explain
the difference between a hot backup and a cold backup and the benefits
associated with each.
|
| 8.2 You have
just had to restore from backup and do not have any control files. How would
you go about bringing up this database?
|
| 8.3 How do
you switch from an init.ora file to a spfile?
|
| 8.4 Explain
the difference between a data block, an extent and a segment.
|
| 8.5 Give two
examples of how you might determine the structure of the table DEPT
|
| 8.6 Where
would you look for errors from the database engine?
|
| 8.7 Compare
and contrast TRUNCATE and DELETE for a table.
|
| 8.8 Give the
reasoning behind using an index.
|
| 8.9 Give the
two types of tables involved in producing a star schema and the type of data
they hold.
|
| 8.10 What
type of index should you use on a fact table?
|
| 8.11Give
two examples of referential integrity constraints.
|
| 8.12 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?
|
| 8.13 Explain
the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits
and disadvantages to each.
|
| 8.14 What
command would you use to create a backup control file?
|
| 8.15 Give
the stages of instance startup to a usable state where normal users may access
it.
|
| 8.16 What
column differentiates the V$ views to the GV$ views and how?
|
| 8.17 How
would you go about generating an EXPLAIN plan?
|
| 8.18 How
would you go about increasing the buffer cache hit ratio?
|
| 8.19 .
Explain an ORA-01555
|
| 8.20 Explain
the difference between $ORACLE_HOME and $ORACLE_BASE.
|
|
|