|
Oracle Interview Questions and Answers
What is the function of Optimizer ?
The goal of the optimizer is to choose the most
efficient way to execute a SQL statement.
What is Execution Plan ?
The combinations of the steps the optimizer chooses to
execute a statement is called an execution plan.
Can one resize tablespaces and data files? (for DBA)
One can manually increase or decrease the size of a
datafile from Oracle 7.2 using the command.
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
Because you can change the sizes of datafiles, you can
add more space to your database without adding more
datafiles. This is beneficial if you are concerned about
reaching the maximum number of datafiles allowed in your
database.
Manually reducing the sizes of datafiles allows you to
reclaim unused space in the database. This is useful for
correcting errors in estimations of space requirements.
Also, datafiles can be allowed to automatically extend
if more space is required. Look at the following
command:
CREATE TABLESPACE pcs_data_ts
DATAFILE 'c:\ora_apps\pcs\pcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE (INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;
What is SAVE POINT ?
For long transactions that contain many SQL statements,
intermediate markers or savepoints can be declared which
can be used to divide a transaction into smaller parts.
This allows the option of later rolling back all work
performed from the current point in the transaction to a
declared savepoint within the transaction.
What are the values that can be specified for OPTIMIZER
MODE Parameter ?
COST and RULE.
Can one rename a tablespace? (for DBA)
No, this is listed as Enhancement Request 148742.
Workaround:
Export all of the objects from the tablespace
Drop the tablespace including contents
Recreate the tablespace
Import the objects
What is RULE-based approach to optimization ?
Choosing an executing planbased on the access paths
available and the ranks of these access paths.
What are the values that can be specified for
OPTIMIZER_GOAL parameter of the ALTER SESSION Command ?
CHOOSE,ALL_ROWS,FIRST_ROWS and RULE.
How does one create a standby database? (for DBA)
While your production database is running, take an
(image copy) backup and restore it on duplicate
hardware. Note that an export will not work!!!
On your standby database, issue the following commands:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
ALTER DATABASE MOUNT STANDBY DATABASE;
RECOVER STANDBY DATABASE;
On systems prior to Oracle 8i, write a job to copy
archived redo log files from the primary database to the
standby system, and apply the redo log files to the
standby database (pipe it). Remember the database is
recovering and will prompt you for the next log file to
apply.
Oracle 8i onwards provide an "Automated Standby
Database" feature, which will send archived, log files
to the remote site via NET8, and apply then to the
standby database.
When one needs to activate the standby database, stop
the recovery process and activate it:
ALTER DATABASE ACTIVATE STANDBY DATABASE;
How does one give developers access to trace files
(required as input to tkprof)? (for DBA)
The "alter session set sql_trace=true" command generates
trace files in USER_DUMP_DEST that can be used by
developers as input to tkprof. On Unix the default file
mask for these files are "rwx r-- ---".
There is an undocumented INIT.ORA parameter that will
allow everyone to read (rwx r-r--) these trace files:
_trace_files_public = true
Include this in your INIT.ORA file and bounce your
database for it to take effect.
What are the responsibilities of a Database
Administrator ?
Installing and upgrading the Oracle Server and
application tools. Allocating system storage and
planning future storage requirements for the database
system. Managing primary database structures (tablespaces)
Managing primary objects (table,views,indexes) Enrolling
users and maintaining system security. Ensuring
compliance with Oralce license agreement Controlling and
monitoring user access to the database. Monitoring and
optimizing the performance of the database. Planning for
backup and recovery of database information. Maintain
archived data on tape Backing up and restoring the
database. Contacting Oracle Corporation for technical
support.
What is a trace file and how is it created ?
Each server and background process can write an
associated trace file. When an internal error is
detected by a process or user process, it dumps
information about the error to its trace. This can be
used for tuning the database.
What are the roles and user accounts created
automatically with the database?
DBA - role Contains all database system privileges.
SYS user account - The DBA role will be assigned to this
account. All of the base tables and views for the
database's dictionary are store in this schema and are
manipulated only by ORACLE. SYSTEM user account - It has
all the system privileges for the database and
additional tables and views that display administrative
information and internal tables and views used by oracle
tools are created using this username.
What are the minimum parameters should exist in the
parameter file (init.ora) ?
DB NAME - Must set to a text string of no more than 8
characters and it will be stored inside the datafiles,
redo log files and control files and control file while
database creation.
DB_DOMAIN - It is string that specifies the network
domain where the database is created. The global
database name is identified by setting these parameters
(DB_NAME & DB_DOMAIN) CONTORL FILES - List of control
filenames of the database. If name is not mentioned then
default name will be used.
DB_BLOCK_BUFFERS - To determine the no of buffers in the
buffer cache in SGA.
PROCESSES - To determine number of operating system
processes that can be connected to ORACLE concurrently.
The value should be 5 (background process) and
additional 1 for each user.
ROLLBACK_SEGMENTS - List of rollback segments an ORACLE
instance acquires at database startup. Also optionally
LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and
LICENSE_MAX_USERS.
Why and when should I backup my database? (for DBA)
Backup and recovery is one of the most important aspects
of a DBAs job. If you lose your company's data, you
could very well lose your job. Hardware and software can
always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly
and monthly backups, however consult with your users
before deciding on a backup schedule. Backup frequency
normally depends on the following factors:
. Rate of data change/ transaction rate
. Database availability/ Can you shutdown for cold
backups?
. Criticality of the data/ Value of the data to the
company
. Read-only tablespace needs backing up just once right
after you make it read-only
. If you are running in archivelog mode you can backup
parts of a database over an extended cycle of days
. If archive logging is enabled one needs to backup
archived log files timeously to prevent database freezes
. Etc.
Carefully plan backup retention periods. Ensure enough
backup media (tapes) are available and that old backups
are expired in-time to make media available for new
backups. Off-site vaulting is also highly recommended.
Frequently test your ability to recover and document all
possible scenarios. Remember, it's the little things
that will get you. Most failed recoveries are a result
of organizational errors and miscommunications.
What strategies are available for backing-up an Oracle
database? (for DBA)
The following methods are valid for backing-up an Oracle
database:
Export/Import - Exports are "logical" database backups
in that they extract logical definitions and data from
the database to a file.
Cold or Off-line Backups - Shut the database down and
backup up ALL data, log, and control files.
Hot or On-line Backups - If the databases are available
and in ARCHIVELOG mode, set the tablespaces into backup
mode and backup their files. Also remember to backup the
control files and archived redo log files.
RMAN Backups - While the database is off-line or
on-line, use the "rman" utility to backup the database.
It is advisable to use more than one of these methods to
backup your database. For example, if you choose to do
on-line database backups, also cover yourself by doing
database exports. Also test ALL backup and recovery
scenarios carefully. It is better to be save than sorry.
Regardless of your strategy, also remember to backup all
required software libraries, parameter files, password
files, etc. If your database is in ARCGIVELOG mode, you
also need to backup archived log files.
What is the difference between online and offline
backups? (for DBA)
A hot backup is a backup performed while the database is
online and available for read/write. Except for Oracle
exports, one can only do on-line backups when running in
ARCHIVELOG mode.
A cold backup is a backup performed while the database
is off-line and unavailable to its users.
What is the difference between restoring and recovering?
(for DBA)
Restoring involves copying backup files from secondary
storage (backup media) to disk. This can be done to
replace damaged files or to copy/move a database to a
new location.
Recovery is the process of applying redo logs to the
database to roll it forward. One can roll-forward until
a specific point-in-time (before the disaster occurred),
or roll-forward until the last transaction recorded in
the log files. Sql> connect SYS as SYSDBA
Sql> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00'
USING BACKUP CONTROLFILE;
How does one backup a database using the export utility?
(for DBA)
Oracle exports are "logical" database backups (not
physical) as they extract data and logical definitions
from the database into a file. Other backup strategies
normally back-up the physical data files.
One of the advantages of exports is that one can
selectively re-import tables, however one cannot
roll-forward from an restored export file. To completely
restore a database from an export file one practically
needs to recreate the entire database.
Always do full system level exports (FULL=YES). Full
exports include more information about the database in
the export file than user level exports.
What are the built_ins used the display the LOV?
Show_lov
List_values
How do you call other Oracle Products from Oracle Forms?
Run_product is a built-in, Used to invoke one of the
supported oracle tools products and specifies the name
of the document or module to be run. If the called
product is unavailable at the time of the call, Oracle
Forms returns a message to the operator.
What is the main diff. bet. Reports 2.0 & Reports 2.5?
Report 2.5 is object oriented.
What are the Built-ins to display the user-named editor?
A user named editor can be displayed programmatically
with the built in procedure SHOW-EDITOR, EDIT_TETITEM
independent of any particular text item.
How many number of columns a record group can have?
A record group can have an unlimited number of columns
of type CHAR, LONG, NUMBER, or DATE provided that the
total number of column does not exceed 64K.
What is a Query Record Group?
A query record group is a record group that has an
associated SELECT statement. The columns in a query record group derive their
default names, data types, had lengths from the database columns referenced in
the SELECT statement. The records in query record group are the rows retrieved
by the query associated with that record group.
What does the term panel refer to with regard to pages?
A panel is the no. of physical pages needed to print one
logical page.
What is a master detail relationship?
A master detail relationship is an association between
two base table blocks- a master block and a detail
block. The relationship between the blocks reflects a
primary key to foreign key relationship between the
tables on which the blocks are based.
What is a library?
A library is a collection of subprograms including user
named procedures, functions and packages.
What is an anchoring object & what is its use? What are
the various sub events a mouse double click event
involves?
An anchoring object is a print condition object
which used to explicitly or implicitly anchor other
objects to itself.
Use the add_group_column function to add a column to
record group that was created at a design time?
False
What are the various sub events a mouse double click
event involves? What are the various sub events a mouse
double click event involves?
Double clicking the mouse consists of the mouse down,
mouse up, mouse click, mouse down & mouse up events.
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 35
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
