|
Oracle Interview Questions and Answers
What are snap shots and views
Snapshots are mirror or replicas of tables. Views are
built using the columns from one or more tables. The
Single Table View can be updated but the view with multi
table cannot be updated
What are the OOPS concepts in Oracle.
Oracle does implement the OOPS concepts. The best
example is the Property Classes. We can categorize the
properties by setting the visual attributes and then
attach the property classes for the objects. OOPS
supports the concepts of objects and classes and we can
consider the property classes as classes and the items
as objects
What is the difference between candidate key, unique key
and primary key
Candidate keys are the columns in the table that could
be the primary keys and the primary key is the key that
has been selected to identify the rows. Unique key is
also useful for identifying the distinct rows in the
table.)
What is concurrency
Concurrency is allowing simultaneous access of same data
by different users. Locks useful for accesing the
database are
a) Exclusive
The exclusive lock is useful for locking the row when an
insert,update or delete is being done.This lock should
not be applied when we do only select from the row.
b) Share lock
We can do the table as Share_Lock as many share_locks
can be put on the same resource.
Previleges and Grants
Previleges are the right to execute a particulare type
of SQL statements. e.g :: Right to Connect, Right to
create, Right to resource Grants are given to the
objects so that the object might be accessed
accordingly.The grant has to be given by the owner of
the object
Table Space,Data Files,Parameter File, Control Files
Table Space :: The table space is useful for storing the
data in the database.When a database is created two
table spaces are created.
a) System Table space :: This data file stores all the
tables related to the system and dba tables
b) User Table space :: This data file stores all the
user related tables
We should have seperate table spaces for storing the
tables and indexes so that the access is fast.
Data Files :: Every Oracle Data Base has one or more
physical data files.They store the data for the
database.Every datafile is associated with only one
database.Once the Data file is created the size cannot
change.To increase the size of the database to store
more data we have to add data file.
Parameter Files :: Parameter file is needed to start an
instance.A parameter file contains the list of instance
configuration parameters e.g.::
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files :: Control files record the physical
structure of the data files and redo log files
They contain the Db name, name and location of dbs, data
files ,redo log files and time stamp.
Physical Storage of the Data
The finest level of granularity of the data base are the
data blocks.
Data Block :: One Data Block correspond to specific
number of physical database space
Extent :: Extent is the number of specific number of
contigious data blocks.
Segments :: Set of Extents allocated for Extents. There
are three types of Segments
a) Data Segment :: Non Clustered Table has data segment
data of every table is stored in cluster data segment
b) Index Segment :: Each Index has index segment that
stores data
c) Roll Back Segment :: Temporarily store 'undo'
information
What are the Pct Free and Pct Used
Pct Free is used to denote the percentage of the free
space that is to be left when creating a table.
Similarly Pct Used is used to denote the percentage of
the used space that is to be used when creating a table
eg.:: Pctfree 20, Pctused 40
What is Row Chaining
The data of a row in a table may not be able to fit the
same data block.Data for row is stored in a chain of
data blocks .
What is a 2 Phase Commit
Two Phase commit is used in distributed data base
systems. This is useful to maintain the integrity of the
database so that all the users see the same values. It
contains DML statements or Remote Procedural calls that
reference a remote object. There are basically 2 phases
in a 2 phase commit.
a) Prepare Phase :: Global coordinator asks participants
to prepare
b) Commit Phase :: Commit all participants to
coordinator to Prepared, Read only or abort Reply
What is the difference between deleting and truncating
of tables
Deleting a table will not remove the rows from the table
but entry is there in the database dictionary and it can
be retrieved But truncating a table deletes it
completely and it cannot be retrieved.
What are mutating tables
When a table is in state of transition it is said to be
mutating. eg :: If a row has been deleted then the table
is said to be mutating and no operations can be done on
the table except select.
What are Codd Rules
Codd Rules describe the ideal nature of a RDBMS. No
RDBMS satisfies all the 12 codd rules and Oracle
Satisfies 11 of the 12 rules and is the only Rdbms to
satisfy the maximum number of rules.
What is Normalisation
Normalisation is the process of organising the tables to
remove the redundancy.There are mainly 5 Normalisation
rules.
a) 1 Normal Form :: A table is said to be in 1st Normal
Form when the attributes are atomic
b) 2 Normal Form :: A table is said to be in 2nd Normal
Form when all the candidate keys are dependant on the
primary key
c) 3rd Normal Form :: A table is said to be third Normal
form when it is not dependant transitively
What is the Difference between a post query and a pre
query
A post query will fire for every row that is fetched but
the pre query will fire only once.
Deleting the Duplicate rows in the table
We can delete the duplicate rows in the table by using
the Rowid
Can U disable database trigger? How?
Yes. With respect to table
ALTER TABLE TABLE
[[ DISABLE all_trigger ]]
What is pseudo columns ? Name them?
A pseudocolumn behaves like a table column, but is not
actually stored in the table. You can select from
pseudocolumns, but you cannot insert, update, or delete
their values. This section describes these pseudocolumns:
* CURRVAL
* NEXTVAL
* LEVEL
* ROWID
* ROWNUM
How many columns can table have?
The number of columns in a table can range from 1 to
254.
Is space acquired in blocks or extents ?
In extents .
What is clustered index?
In an indexed cluster, rows are stored together based on
their cluster key values . Can not applied for HASH.
What are the datatypes supported By oracle (INTERNAL)?
Varchar2, Number,Char , MLSLABEL.
What are attributes of cursor?
%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
Can you use select in FROM clause of SQL select ?
Yes.
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
|
