DATABASE
Administrator (DBA) Interview Questions and Answers
Are you a
Database Administrator? Need to update DBA technical knowledge or need to
prepare for a job interview? Check out this collection of DATABASE
Administrator (DBA) Interview Questions and Answers...
DATABASE
Administrator (DBA) Interview Questions and Answers
- What is a DBA?
- What DBA activities did you
to do today?
- What are the different modes
of mounting a Database with the Parallel Server?
- What are the advantages of
operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG
mode?
- Do you consider yourself a
development DBA or a production DBA and why?
- What are the Large object
types suported by Oracle?
- Diffrence between a �where� clause and a �having� claus
- Shall we create procedures
to fetch more than one record?
- Do View contain Data?
- What are the Referential
actions supported by FOREIGN KEY integrity constraint?
- What are the type of
Synonyms?
- Where would you look for
errors from the database engine?
- Compare and contrast
TRUNCATE and DELETE for a table.
- Give the reasoning behind
using an index.
- Give the two types of tables
involved in producing a star schema and the type of data they hold.
- What type of index should
you use on a fact table?
- What is a Segment?
- 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?
- Explain the difference
between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and
disadvantages to each.
- What is a Sequence?
- Give the stages of instance
startup to a usable state where normal users may access it.
- What is a Synonym?
- How would you go about
generating an EXPLAIN plan?
- How would you go about
increasing the buffer cache hit ratio?
- Explain an ORA-01555
- Explain the difference
between $ORACLE_HOME and $ORACLE_BASE.
- How would you determine the
time zone under which a database was operating?
- Explain the use of setting
GLOBAL_NAMES equal to TRUE.
- What command would you use
to encrypt a PL/SQL application?
- Explain the difference between
a FUNCTION, PROCEDURE and PACKAGE.
- Explain the use of table
functions.
- Name three advisory
statistics you can collect.
- Where in the Oracle
directory tree structure are audit traces placed?
- Explain materialized views
and how they are used.
- What does a Control file
Contain?
- What is difference between
UNIQUE constraint and PRIMARY KEY constraint?
- What is the effect of
setting the value �ALL_ROWS� for OPTIMIZER_GOAL
parameter of the ALTER SESSION command?
- Describe what redo logs are.
- How would you force a log
switch?
- What are the different
Parameter types?
- What does coalescing a
tablespace do?
- What is the difference
between a TEMPORARY tablespace and a PERMANENT tablespace?
- Name a tablespace
automatically created when you create a database.
- When creating a user, what
permissions must you grant to allow them to connect to the database?
- How do you add a data file
to a tablespace?
- How do you resize a data
file?
- What view would you use to
look at the size of a data file?
- What view would you use to
determine free space in a tablespace?
- How would you determine who
has added a row to a table?
- How can you rebuild an
index?
- Explain what partitioning is
and what its benefit is.
- You have just compiled a
PL/SQL package but got errors, how would you view the errors?
- How can you gather
statistics on a table?
- How can you enable a trace for
a session?
- What is the difference
between the SQL*Loader and IMPORT utilities?
- What is a Schema?
- What is a cluster Key?
- What?s the command to change
the SQL prompt name?
- What is Parallel Server?
- How do I eliminate the
duplicate rows ?
- How do I display row number
with records?
- Display the records between
two range
- I know the nvl function only
allows the same data type(ie. number or char or date Nvl(comm, 0)), if
commission is null then the text ?Not Applicable? want to display, instead
of blank space. How do I write the query?
- Oracle cursor : Implicit
& Explicit cursors
- Explicit Cursor attributes
- Implicit Cursor attributes
- Find out nth highest salary
from emp table
- To view installed Oracle
version information
- Display the number value in
Words
- Display Odd/ Even number of
records
- Which date function returns
number value?
- Any three PL/SQL Exceptions?
- What are PL/SQL Cursor
Exceptions?
- Other way to replace query
result null value with a text
- What are the more common
pseudo-columns?
- What is the output of SIGN
function?
- What is the maximum number
of triggers, can apply to a single table?
- What is a database instance?
Explain.
- What is Parallel Server?
- What is a schema?
- What are the options
available to refresh snapshots?
- What is a SNAPSHOT LOG?
- What is Distributed
database?
- What are the basic element
of base configuration of an Oracle database?
- What is a deadlock? Explain.
- What is Privilege Auditing?
- What is Object Auditing?
- Physical DB Structure
- Logical DB Structure
- Tablespaces
- Online & Offline TS
- Schema
- Index Clusters
- Database Links
- Data Blocks
- For a field in a repeating
frame, can the source come from the column which does not exist in the
data group which forms the base for the frame?
- Can a field be used in a
report without it appearing in any data group?
- The join defined by the
default data link is an outer join yes or no?
- What UNIX command will
control the default file permissions when files are created?
- Explain the read, write, and
execute permissions on a UNIX directory.
- the difference between a
soft link and a hard link?
- Give the command to display
space usage on the UNIX file system.
- Explain iostat, vmstat and
netstat.
- What is new_form built-in?
- What is the �LOV of Validation� Property of an item? - What
is the use of it?
- What is the diff. when Flex
mode is mode on and when it is off?
- What is ROWID?
- What is a correlated
subquery?
- Explain UNION, MINUS, UNION
ALL and INTERSECT?
- What are the types of SQL
statement?
- What is a pseudo column.
Give some examples?
- What is difference between
CHAR and VARCHAR2?
- What do you know about subqueries?
- What is a database link?
- How to drop the index ?
- What are the different types
of SQL statements?
- What are the uses of
rollback segment?
- What is a deadlock and
Explain?
- State any three mouse events
system variables?
- What other parts of your
organization do you interact with and how?
- How do you display console
on a window?
- In exception handling we
have some NOT_FOUND and OTHERS. In inner layer we have some NOT_FOUND and
OTHERS. While executing which one whether outer layer or inner layer will
check first?
- What are the different types
of joins?
- How will you copy the
structure of a table without copying the data?
- What is a VIEW? How to get
script for a view?
- What is a �trigger�?
- Explain the difference
between a hot backup and a cold backup and the benefits associated with
each.
- What cursor type do you use
to retrieve multiple recordsets?
- What is the difference among
�dropping a table�, �truncating a table� and �deleting all records� from a table.
- Difference between �ORACLE� and �MICROSOFT ACCESS� databases.
- How to remove duplicate
records from a table?
- Are you a nuts-n-bolts DBA
or a tools-n-props DBA
- How to create a database link?
- What is SQL*Loader?
- How to run SQL script from a
Unix Shell?
- Talk about �Exception Handling� in PL/SQL?
- Give some examples of
Analytical functions
- What is Log Switch?
- What is On-line Redo Log?
- Which parameter specified in
the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after
creating the tablespace?
- What are the steps involved
in Database Startup?
- What are the steps involved
in Instance Recovery?
- Can Full Backup be performed
when the database is open?
- You have just had to restore
from backup and do not have any control files. How would you go about
bringing up this database?
- What are the steps involved
in Database Shutdown?
- What is Archived Redo Log?
- What is Restricted Mode of
Instance Startup?
- What is Partial Backup?
- What is Mirrored on-line
Redo Log?
- What is Full Backup?
- Can a View based on another
View?
- Can a Tablespace hold
objects from different Schemes?
- Can objects of the same Schema
reside in different tablespaces?
- What is the use of Control
File?
- What is your typical day
like?
- How do you switch from an
init.ora file to a spfile?
- Explain the difference
between a data block, an extent and a segment.
- Give two examples of how you
might determine the structure of the table DEPT.
- What is a Redo Log?
- What is an Index Segment?
- Explain the relationship
among Database, Tablespace and Data file.?
- What are the different type
of Segments?
- What are Clusters?
- What is an Integrity
Constrains?
- What is an Index?
- What is an Extent?
- What is a View?
- What is Table?
- What column differentiates
the V$ views to the GV$ views and how?
- What command would you use
to create a backup control file?
- Give two examples of
referential integrity constraints.
- What is schema?
- Describe Referential
Integrity?
- What is Hash Cluster?
- What is a Private Synonyms?
- What is Database Link?
- What is a Tablespace?
- What is Rollback Segment?
- What are the Characteristics
of Data Files?
- How to define Data Block
size?
- When a user process fails,
what background process cleans up after it?
- What background process
refreshes materialized views?
- How would you determine what
sessions are connected and what resources they are waiting for?
- What is the effect of
setting the value �CHOOSE� for OPTIMIZER_GOAL,
parameter of the ALTER SESSION Command?
- What is the function of
Optimizer?
- What is Execution Plan?
- What are the different
approaches used by Optimizer in choosing an execution plan?
- What does ROLLBACK do?
- What is SAVE POINT?
- What are the values that can
be specified for OPTIMIZER MODE Parameter?
- What is COST-based approach
to optimization?
- What does COMMIT do?
- What is RULE-based approach
to optimization?
- What are the values that can
be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command?
- Define Transaction?
- What is Read-Only
Transaction?
- What is a deadlock?
- Name two files used for
network connection to a database.
- What?s the command to see the
current user name?
- How do you switch to DOS
prompt from SQL prompt?
- What are the basic element
of Base configuration of an oracle Database?
- What is clusters?
- What is an Index? - How it
is implemented in Oracle Database?
- What is a Database instance?
- What is the use of ANALYZE
command?
- What is default tablespace?
- What are the system
resources that can be controlled through Profile?
- What is Tablespace Quota?
- What are the different
Levels of Auditing?
- What is Statement Auditing?
- What are the database
administrators utilities available?
- How can you enable automatic
archiving?
- What are roles?
- How can we implement roles?
- What are the use of Roles?
- Auditing
- Audit Trial
- What is Auditing?
- What are the
responsibilities of a Database Administrator?
- What is a trace file and how
is it created?
- What is a profile?
- How will you enforce
security using stored procedures?
- What are the dictionary
tables used to monitor a database spaces?
- What are the roles and user
accounts created automatically with the database?
- What are the minimum
parameters should exist in the parameter file (init.ora)?
- How can we specify the
Archived log file name format and destination?
- What is user Account in
Oracle database?
- What dynamic data
replication?
- What is Two-Phase Commit?
- How can you Enforce
Referential Integrity in snapshots?
- What is a SQL * NET?
- What is a SNAPSHOT?
- What is the mechanism
provided by ORACLE for table replication?
- What is snapshots?
- What are the various type of
snapshots?
- Describe two phases of
Two-phase commit?
- What is snapshot log?
- What are the benefits of
distributed options in databases?
- What is an index? How it is
implemented in Oracle database?
- What are clusters?
- What is a cluster key?
- How can we reduce the
network traffic?
- Differentiate simple and
complex, snapshots?
- What are the Built-ins used
for sending Parameters to forms?
- Can you have more than one
content canvas view attached with a window?
- Is the After report trigger
fired if the report execution fails?
- Does a Before form trigger
fire when the parameter form is suppressed?
- Is it possible to split the
print reviewer into more than one region?
- Is it possible to center an
object horizontally in a repeating frame that has a variable horizontal
size?
- How do you list the files in
an UNIX directory while also showing hidden files?
- How do you execute a UNIX
command in the background?
- Can a formula column
referred to columns in higher group?
- Can a formula column be
obtained through a select statement?
- If a parameter is used in a
query without being previously defined, what diff. exist between report
2.0 and 2.5 when the query is applied?
- What are the SQL clauses
supported in the link property sheet?
- What is trigger associated
with the timer?
- What are the trigger
associated with image items?
- What are the different
windows events activated at runtimes?
- When do you use data
parameter type?
- What is difference between
open_form and call_form?
- How would you change all
occurrences of a value using VI?
- Give two UNIX kernel
parameters that effect an Oracle install
- Briefly, how do you install
Oracle software on UNIX.
- What is the diff. when
confine mode is on and when it is off?
- What are visual attributes?
- Which of the two views
should objects according to possession?
- What are the two types of
views available in the object navigator (specific to report 2.5)?
- What are the vbx controls?
- What is the use of
transactional triggers?
- How do you create a new
session while open a new form?
- What are the ways to monitor
the performance of the report?
- If two groups are not linked
in the data model editor, What is the hierarchy between them?
- An open form can not be
execute the call_form procedure if you chain of called forms has been
initiated by another open form?
- Explain about horizontal,
Vertical tool bar canvas views?
- What is the purpose of the
product order option in the column property sheet?
- What is the use of
image_zoom built-in?
- How do you reference a
parameter indirectly?
- Is it possible to insert
comments into sql statements return in the data model editor?
- Is it possible to disable
the parameter from while running the report?
- When a form is invoked with
call_form, Does oracle forms issues a save point?
- Can a property clause itself
be based on a property clause?
- What is a timer?
- What are the two phases of
block coordination?
- What are Most Common types
of Complex master-detail relationships?
- What is a text list?
- What is term?
- What is use of term?
- What is pop list?
- What is the maximum no of
chars the parameter can store?
- What are the default
extensions of the files created by library module?
- What are the Coordination
Properties in a Master-Detail relationship?
- What is an index and How it
is implemented in Oracle database?
- Lot of users are accessing
select sysdate from dual and they getting some millisecond differences. If
we execute SELECT SYSDATE FROM EMP; what error will we get. Why?
- Give two methods you could
use to determine what DDL changes have been made.
- What are the types of
calculated columns available?
- Explain about stacked canvas
views?
- What are the built_ins used
the display the LOV?
- What is the difference between
SHOW_EDITOR and EDIT_TEXTITEM?
- What are the built-ins that
are used to Attach an LOV programmatically to an item?
- How do you call other Oracle
Products from Oracle Forms?
- What is the main diff. bet.
Reports 2.0 & Reports 2.5?
- What are the different file
extensions that are created by oracle reports?
- What is strip sources
generate options?
- What is the basic data
structure that is required for creating an LOV?
- What is the Maximum allowed
length of Record group Column?
- Which parameter can be used
to set read level consistency across multiple queries?
- What are the different types
of Record Groups?
- From which designation is it
preferred to send the output to the printed?
- what are difference between
post database commit and post-form commit?
- What are the different
display styles of list items?
- Which of the above methods
is the faster method?
- With which function of
summary item is the compute at options required?
- What are parameters?
- What are the three types of
user exits available?
- How many windows in a form
can have console?
- If the maximum record
retrieved property of the query is set to 10 then a summary value will be
calculated?
- What are the two repeating
frame always associated with matrix object?
- What are the master-detail
triggers?
- What are the different
objects that you cannot copy or reference in object groups?
- What is an OLE?
- Is it possible to modify an
external query in a report which contains it?
- Does a grouping done for
objects in the layout editor affect the grouping done in the data model
editor?
- Can a repeating frame be
created without a data group as a base?
- If a break order is set on a
column would it affect columns which are under the column?
- Is it possible to set a
filter condition in a cross product group in matrix reports?
- Do user parameters appear in
the data modal editor in 2.5?
- Can you pass data parameters
to forms?
- Is it possible to link two
groups inside a cross products after the cross products group has been
created?
- What are the different
modals of windows?
- What are modal windows?
- What are the different
default triggers created when Master Deletes Property is set to
Non-isolated?
- What are the different
default triggers created when Master Deletes Property is set to isolated?
- What are the different
default triggers created when Master Deletes Property is set to Cascade?
- What is the diff. bet.
setting up of parameters in reports 2.0 reports2.5?
- What are the difference
between lov & list item?
- What is the advantage of the
library?
- What is lexical reference?
- What is
system.coordination_operation?
- What is synchronize?
- What use of command line
parameter cmd file?
- What is a Text_io Package?
- What is forms_DDL?
- How is link tool operation
different bet. reports 2 & 2.5?
- What are the different
styles of activation of ole Objects?
- How do you reference a
Parameter?
- What is the difference
between object embedding and linking in Oracle forms?
- Name of the functions used
to get/set canvas properties?
- What are the built-ins that
are used for setting the LOV properties at runtime?
- What are the built-ins used
for processing rows?
- What are built-ins used for
Processing rows?
- What are the built-in used
for getting cell values?
- What are the built-ins used
for Getting cell values?
- A tleast how many set of
data must a data model have before a data model can be base on it?
- To execute row from being
displayed that still use column in the row which property can be used?
- What are different types of
modules available in oracle form?
- What is the remove on exit
property?
- What is WHEN-Database-record
trigger?
- What is a difference between
pre-select and pre-query?
- What are built-ins
associated with timers?
- What are the built-ins used
for finding object ID functions?
- What are the built-ins used
for finding Object ID function?
- Any attempt to navigate
programmatically to disabled form in a call_form stack is allowed?
- Use the Add_group_row
procedure to add a row to a static record group 1. true or false?
- Use the add_group_column
function to add a column to record group that was created at a design
time?
- What are the various sub
events a mouse double click event involves?
- How can a break order be
created on a column in an existing group?
- What is the use of place
holder column?
- What is the use of hidden
column?
- What is the use of break
group?
- What is an anchoring object
and what is its use?
- What are the various sub
events a mouse double click event involves?
- What are the default
parameter that appear at run time in the parameter screen?
- What are the built-ins used
for Creating and deleting groups?
- What are different types of
canvas views?
- What are the different types
of Delete details we can establish in Master-Details?
- What is relation between the
window and canvas views?
- What is a User_exit?
- How is it possible to select
generate a select set for the query in the query property sheet?
- How can values be passed
between precompiler exits & Oracle call interface?
- How can a square be drawn in
the layout editor of the report writer?
- How can a text file be
attached to a report while creating in the report writer?
- How can I message to passed
to the user from reports?
- How is possible to restrict
the user to a list of values while entering values for parameters?
- How can a button be used in
a report to give a drill down facility?
- How can a cross product be
created?
- What are different types of
images?
- What is the difference
between boiler plat images and image items?
- What is bind reference and
how can it be created?
- What are the triggers
available in the reports?
- Give the sequence of
execution of the various report triggers?
- Why is a Where clause faster
than a group filter or a format trigger?
- Why is it preferable to
create a fewer no. of queries in the data model?
- Where is the external query
executed at the client or the server?
- Where is a procedure return
in an external pl/SQL library executed at the client or at the server?
- What is coordination Event?
- What is the difference
between OLE Server & OLE Container?
- What is an object group?
- What is an LOV?
- At what point of report
execution is the before Report trigger fired?
- What are the built -ins used
for Modifying a groups structure?
- What is an user exit used
for?
- What is the User-Named
Editor?
- What are the Built-ins to
display the user-named editor?
- What is a Static Record
Group?
- What is a record group?
- How many number of columns a
record group can have?
- What is a Query Record
Group?
- What is a property clause?
- What is a physical page?
What is a logical page?
- What does the term panel
refer to with regarda to pages?
- What is a master detail
relationship?
- What is a library?
- How can a group in a cross
products be visually distinguished from a group that does not form a cross
product?
- What is the frame &
repeating frame?
- What is a combo box?
- What are three panes that
appear in the run time pl/SQL interpreter?
- What are the two panes that
Appear in the design time pl/SQL interpreter?
- What are the two ways by
which data can be generated for a parameters list of values?
- What are the various methods
of performing a calculation in a report?
- What are the default
extensions of the files created by menu module?
- It is possible to use raw
devices as data files and what is the advantages over file system files?
- What are disadvantages of
having raw devices?
- What is the significance of
having storage clause?
- What is the use of INCTYPE
option in EXP command?
- What is the use of FILE
option in IMP command?
- What is a Shared SQL pool?
- What is hot backup and how
it can be taken?
- List the Optional Flexible
Architecture (OFA) of Oracle database? How can we organize the tablespaces
in Oracle database to have maximum performance?
- How to implement the
multiple control files for an existing database?
- What is advantage of having
disk shadowing/ Mirroring?
- How will you force database
to use particular rollback segment?
- Give one method for
transferring a table from one schema to another
- What is the purpose of the
IMPORT option IGNORE? What is it?s default setting?
- You have a rollback segment
in a version 7.2 database that has expanded beyond optimal, how can it be
restored to optimal?
- If the DEFAULT and TEMPORARY
tablespace clauses are left out of a CREATE USER command what happens? Is
this bad or good? Why?
- What are some of the Oracle
provided packages that DBAs should be aware of?
- What happens if the
constraint name is left out of a constraint clause?
- What happens if a tablespace
clause is left off of a primary key constraint clause?
- What is the proper method
for disabling and re-enabling a primary key constraint?
- What happens if a primary
key constraint is disabled and then enabled without fully specifying the
index clause?
- (On UNIX) When should more
than one DB writer process be used? How many should be used?
- You are using hot backup
without being in archivelog mode, can you recover in the event of a
failure? Why or why not?
- What causes the
"snapshot too old" error? How can this be prevented or
mitigated?
- How can you tell if a
database object is invalid?
- A user is getting an
ORA-00942 error yet you know you have granted them permission on the
table, what else should you check?
- 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?
- If you have an example
table, what is the best way to get sizing data for the production table
implementation?
- How can you find out how
many users are currently logged into the database? How can you find their
operating system id?
- A user selects from a
sequence and gets back two values, his select is:
- How can you determine if an
index needs to be dropped and rebuilt?
- A tablespace has a table
with 30 extents in it. Is this bad? Why or why not.
- How do you set up
tablespaces during an Oracle installation?
- You see multiple fragments
in the SYSTEM tablespace, what should you check first?
- What are some indications
that you need to increase the SHARED_POOL_SIZE parameter?
- What is the general
guideline for sizing db_block_size and db_multi_block_read for an
application that does many full table scans?
- What is the fastest query
method for a table?
- Explain the use of TKPROF?
What initialization parameter should be turned on to get full TKPROF
output?
- When looking at v$sysstat
you see that sorts (disk) is high. Is this bad or good? If bad -How do you
correct it?
- When should you increase
copy latches? What parameters control copy latches?
- 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?
- 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?
- Discuss row chaining, how
does it happen? How can you reduce it? How do you correct it?
- 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?
- If you see contention for
library caches how can you fix it?
- If you see statistics that
deal with "undo" what are they really talking about?
- If a tablespace has a
default pctincrease of zero what will this cause (in relationship to the
smon process)?
- If a tablespace shows
excessive fragmentation what are some methods to defragment the
tablespace? (7.1,7.2 and 7.3 only)
- How can you tell if a
tablespace has excessive fragmentation?
- You see the following on a
status report: redo log space requests 23; redo log space wait time 0; Is
this something to worry about? What if redo log space wait time is high?
How can you fix this?
- What can cause a high value
for recursive calls? How can this be fixed?
- 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?
- If you see the value for
reloads is high in the estat library cache report is this a matter for
concern?
- 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?
No comments:
Post a Comment