|
Oracle Interview Questions and Answers
How does one stop and start the OMS? (for DBA)
Use the following command sequence to stop and start the
OMS (Oracle Management Server):
oemctl start oms
oemctl status oms sysman/oem_temp
oemctl stop oms sysman/oem_temp
Windows NT/2000 users can just stop and start the
required services. The default OEM administrator is "sysman"
with a password of "oem_temp".
NOTE: Use command oemctrl instead of oemctl for Oracle
8i and below.
What is an Integrity Constraint ?
Integrity constraint is a rule that restricts values to
a column in a table.
How does one create a repository? (for DBA)
For OEM v2 and above, start the Oracle Enterprise
Manager Configuration Assistant (emca on Unix) to create
and configure the management server and repository.
Remember to setup a backup for the repository database
after creating it.
If a View on a single base table is manipulated will the
changes be reflected on the base table ?
If changes are made to the tables which are base tables
of a view will the changes be reference on the view.
The following describes means to create a OEM V1.x (very
old!!!) repository on WindowsNT:
. Create a tablespace that would hold the repository
data. A size between 200- 250 MB would be ideal. Let us
call it Dummy_Space.
. Create an Oracle user who would own this repository.
Assign DBA, SNMPAgent, Exp_Full_database,
Imp_Full_database roles to this user. Lets call this
user Dummy_user. Assign Dummy_Space as the default
tablespace.
. Create an operating system user with the same name as
the Oracle username. I.e. Dummy_User. Add 'Log on as a
batch job' under advanced rights in User manager.
. Fire up Enterprise manager and log in as Dummy_User
and enter the password. This would trigger the creation
of the repository. From now on, Enterprise manager is
ready to accept jobs.
What is a database link ?
Database Link is a named path through which a remote
database can be accessed.
How does one list one's databases in the OEM Console?
(for DBA)
Follow these steps to discover databases and other
services from the OEM Console:
1. Ensure the GLOBAL_DBNAME parameter is set for all
databases in your LISTENER.ORA file (optional). These
names will be listed in the OEM Console. Please note
that names entered are case sensitive. A portion of a
listener.ora file:
(SID_DESC =
(GLOBAL_DBNAME = DB_name_for_OEM)
(SID_NAME = ...
2. Start the Oracle Intelligent Agent on the machine you
want to discover. See section "How does one start the
Oracle Intelligent Agent?".
3. Start the OEM Console, navigate to menu "Navigator/
Discover Nodes". The OEM Discovery Wizard will guide you
through the process of discovering your databases and
other services.
What is CYCLE/NO CYCLE in a Sequence ?
CYCLE specifies that the sequence continues to generate
values after reaching either maximum or minimum value.
After pan ascending sequence reaches its maximum value,
it generates its minimum value. After a descending
sequence reaches its minimum, it generates its
maximum.NO CYCLE specifies that the sequence cannot
generate more values after reaching its maximum or
minimum value.
What is correlated sub-query ?
Correlated sub query is a sub query which has reference
to the main query.
What are the data types allowed in a table ?
CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.
What is difference between CHAR and VARCHAR2 ? What is
the maximum SIZE allowed for each type ?
CHAR pads blank spaces to the maximum length. VARCHAR2
does not pad blank spaces. For CHAR it is 255 and 2000
for VARCHAR2.
Can a view be updated/inserted/deleted? If Yes under
what conditions ?
A View can be updated/deleted/inserted if it has only
one base table if the view is based on columns from one
or more tables then insert, update and delete is not
possible.
What are the different types of Coordinations of the
Master with the Detail block?
POPULATE_GROUP(function)
POPULATE_GROUP_WITH_QUERY(function)
SET_GROUP_CHAR_CELL(procedure)
SET_GROUPCELL(procedure)
SET_GROUP_NUMBER_CELL(procedure)
Use the ADD_GROUP_COLUMN function to add a column to a
record group that was created at design time?
I) TRUE II) FALSE
II) FALSE
Use the ADD_GROUP_ROW procedure to add a row to a static
record group?
I) TRUE II) FALSE
I) FALSE
maxvalue.sql Select the Nth Highest value from a table?
select level, max('col_name') from my_table where level
= '&n' connect by prior ('col_name') > 'col_name')
group by level;
Example:
Given a table called emp with the following columns:
-- id number
-- name varchar2(20)
-- sal number
--
-- For the second highest salary:
-- select level, max(sal) from emp
-- where level=2
-- connect by prior sal > sal
-- group by level
Find out nth highest salary from emp table?
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT
COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For E.g.:-
Enter value for n: 2
SAL
---------
3700
Suppose a customer table is having different columns
like customer no, payments.What will be the query to
select top three max payments?
SELECT customer_no, payments from customer C1
WHERE 3<=(SELECT COUNT(*) from customer C2
WHERE C1.payment <= C2.payment)
How you will avoid your query from using indexes?
SELECT * FROM emp
Where emp_no+' '=12345;
i.e you have to concatenate the column name with space
within codes in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp
where emp_no=1234;
i.e using HINTS
What utility is used to create a physical backup?
Either rman or alter tablespace begin backup will do..
What are the Back ground processes in Oracle and what
are they.
This is one of the most frequently asked question.There
are basically 9 Processes but in a general system we
need to mention the first five background processes.They
do the house keeping activities for the Oracle and are
common in any system.
The various background processes in oracle are
a) Data Base Writer(DBWR) :: Data Base Writer Writes
Modified blocks from Database buffer cache to Data
Files.This is required since the data is not written
whenever a transaction is committed.
b)LogWriter(LGWR) :: LogWriter writes the redo log
entries to disk. Redo Log data is generated in redo log
buffer of SGA. As transaction commits and log buffer
fills, LGWR writes log entries into a online redo log
file.
c) System Monitor(SMON) :: The System Monitor performs
instance recovery at instance startup. This is useful for
recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor performs
process recovery when user Process fails. Pmon Clears
and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified
database buffers in SGA are written to data files by
DBWR at Checkpoints and Updating all data files and
control files of database to indicate the most recent
checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log
files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve
the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in
Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter
instance locking in parallel sql.
How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are
a) Data Definition Language(DDL) :: The DDL statements
define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statements
manipulate database data.
c) Transaction Control Statements :: Manage change by
DML
d) Session Control :: Used to control the properties of
current session enabling and disabling roles and
changing .e.g. :: Alter Statements, Set Role
e) System Control Statements :: Change Properties of
Oracle Instance .e.g.:: Alter System
f) Embedded Sql :: Incorporate DDL, DML and T.C.S in
Programming Language.e.g:: Using the Sql Statements in
languages such as 'C', Open, Fetch, execute and close
What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises
one or more SQL Statements executed by a single User.
According to ANSI, a transaction begins with first
executable statement and ends when it is explicitly
committed or rolled back.
Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Committing :: A transaction is said to be committed
when the transaction makes permanent changes resulting
from the SQL statements.
b) Rollback :: A transaction that retracts any of the
changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many
SQL statements, intermediate markers or savepoints are
declared. Savepoints can be used to divide a transaction
into smaller points.
d) Rolling Forward :: Process of applying redo log
during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer)
for the memory associated with a specific stamen. A
cursor is basically an area allocated by Oracle for
executing the Sql Statement. Oracle uses an implicit
cursor statement for Single row query and Uses Explicit
cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory
region allocated by the Oracle that contains Data and
control information for one Oracle Instance. It consists
of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory
buffer that contains data and control information for
server process.
g) Database Buffer Cache :: Database Buffer of SGA
stores the most recently used blocks of database data.
The set of database buffers in an instance is
called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all
the redo log entries.
i) Redo Log Files :: Redo log files are set of files
that protect altered database data in memory that has
not been written to Data Files. They are basically used
for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or
mechanism in Operating System that executes series of
steps.
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
|
