Hello Friends, Today I'am going to share some Topic- wise Inteview Questions I found on Internet.
Source FreeJavaGuide.com
Oracle Concepts and Architecture
Database Structures
1. What are
the components of physical database structure of Oracle database?
Oracle database is comprised of three types of
files. One or more datafiles, two are more redo log files, and one or more
control files.
2.
What are the components of logical database structure of Oracle database?
There are tablespaces and database’s
schema objects.
3.
What is a tablespace?
A database is divided into Logical
Storage Unit called tablespaces. A tablespace is used to grouped related
logical structures together.
4.
What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM,
which is automatically created when the database is created. The SYSTEM
tablespace always contains the data dictionary tables for the entire database.
5.
Explain the relationship among database, tablespace and data file.
Each databases logically divided
into one or more tablespaces one or more data files are explicitly created for
each tablespace.
6.
What is schema?
A schema is collection of database
objects of a user.
7.
What are Schema Objects?
Schema objects are the logical
structures that directly refer to the database’s data. Schema objects include
tables, views, sequences, synonyms, indexes, clusters, database triggers,
procedures, functions packages and database links.
8. Can
objects of the same schema reside in different table spaces?
Yes.
9. Can
a tablespace hold objects from different schemes?
Yes.
10.
What is Oracle table?
A table is the basic unit of data
storage in an Oracle database. The tables of a database hold all
of the user accessible data. Table data is stored in rows and columns.
11.
What is an Oracle view?
A view is a virtual table. Every
view has a query attached to it. (The query is a SELECT statement that
identifies
the columns and rows of the table(s) the view uses.)
12. Do
a view contain data?
13.
Can a view based on another view?
Yes.
14.
What are the advantages of views?
- Provide an additional level of
table security, by restricting access to a predetermined set of rows and
columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different
perspective from that of the base table.
- Store complex queries.
15.
What is an Oracle sequence?
A sequence generates a serial list
of unique numbers for numerical columns of a database’s tables.
16.
What is a synonym?
A synonym is an alias for a table,
view, sequence or program unit.
17.
What are the types of synonyms?
There are two types of synonyms
private and public.
18.
What is a private synonym?
Only its owner can access a private
synonym.
19.
What is a public synonym?
Any database user can access a
public synonym.
20.
What are synonyms used for?
- Mask the real name and owner
of an object.
- Provide public access to an object
- Provide location transparency for
tables, views or program units of a remote database.
- Simplify the SQL statements for
database users.
21.
What is an Oracle index?
An index is an optional structure
associated with a table to have direct access to rows, which can be created to
increase the performance of data retrieval. Index can be created on one or more
columns of a table.
22.
How are the index updates?
Indexes are automatically maintained
and used by Oracle. Changes to table data are automatically incorporated into
all relevant indexes.
23.
What are clusters?
Clusters are groups of one or more
tables physically stores together to share common columns and are often used
together.
24. What is cluster key?
The related columns of the tables in
a cluster are called the cluster key.
25.
What is index cluster?
A cluster with an index on the
cluster key.
26.
What is hash cluster?
A row is stored in a hash cluster
based on the result of applying a hash function to the row’s cluster key value.
All rows with the same hash key value are stores together on disk.
27.
When can hash cluster used?
Hash clusters are better choice when
a table is often queried with equality queries. For such queries the specified
cluster key value is hashed. The resulting hash key value points directly to
the area on disk that stores the specified rows.
28.
What is database link?
A database link is a named object
that describes a “path” from one database to another.
29.
What are the types of database links?
Private database link, public
database link & network database link.
30.
What is private database link?
Private database link is created on
behalf of a specific user. A private database link can be used only when the
owner of the link specifies a global object name in a SQL statement or in the
definition of the owner’s views or procedures.
31.
What is public database link?
Public database link is created for
the special user group PUBLIC. A public database link can be used when any
user
in the associated database specifies a global object name in a SQL statement or
object definition.
32.
What is network database link?
Network database link is created and
managed by a network domain service. A network database link can be used when any user of any
database in the network specifies a global object name in a SQL statement or
object definition.
33.
What is data block?
Oracle database’s data is stored in
data blocks. One data block corresponds to a specific number of bytes of physical
database space on disk.
34.
How to define data block size?
A data block size is specified for
each Oracle database when the database is created. A database users and
allocated free database space in Oracle data blocks. Block size is specified in
init.ora file and cannot be changed latter.
35.
What is row chaining?
In circumstances, all of the data
for a row in a table may not be able to fit in the same data block. When this
occurs, the data for the row is stored in a chain of data block (one or more)
reserved for that segment.
36.
What is an extent?
An extent is a specific number of
contiguous data blocks, obtained in a single allocation and used to store a
specific type of information.
37.
What is a segment?
A segment is a set of extents
allocated for a certain logical structure.
38.
What are the different types of segments?
Data segment, index segment,
rollback segment and temporary segment.
39.
What is a data segment?
Each non-clustered table has a data
segment. All of the table’s data is stored in the extents of its data segment.
Each cluster has a data segment. The data of every table in the cluster is
stored in the cluster’s data segment.
40.
What is an index segment?
Each index has an index segment that
stores all of its data.
41.
What is rollback segment?
A database contains one or more
rollback segments to temporarily store “undo” information.
42.
What are the uses of rollback segment?
To generate read-consistent database
information during database recovery and to rollback uncommitted
transactions by the users.
43.
What is a temporary segment?
Temporary segments are created by
Oracle when a SQL statement needs a temporary work area to complete execution.
When the statement finishes execution, the temporary segment extents are
released to the system for future use.
44.
What is a datafile?
Every Oracle database has one or
more physical data files. A database’s data files contain all the database
data. The data of logical database structures such as tables and indexes is
physically stored in the data files allocated for a database.
45.
What are the characteristics of data files?
A data file can be associated with
only one database. Once created a data file can’t change size. One or more data
files form a logical unit of database storage called a tablespace.
46.
What is a redo log?
The set of redo log files for a
database is collectively known as the database redo log.
47.
What is the function of redo log?
The primary function of the redo log
is to record all changes made to data.
48.
What is the use of redo log information?
The information in a redo log file
is used only to recover the database from a system or media failure prevents
database data from being written to a database’s data files.
49.
What does a control file contains?
- Database name
- Names and locations of a
database’s files and redolog files.
- Time stamp of database creation.
50.
What is the use of control file?
When an instance of an Oracle
database is started, its control file is used to identify the database and redo
log files that must be opened for database operation to proceed. It is also
used in database recovery.
Data
Base Administration
51. What is
a database instance? Explain.
A database instance (Server) is a
set of memory structure and background processes that access a set of database
files. The processes can be shared by all of the users.
The memory structure that is used to
store the most queried data from database. This helps up to improve database
performance by decreasing the amount of I/O performed against data file.
52. What is
Parallel Server?
Multiple instances accessing the
same database (only in multi-CPU environments)
53. What is
a schema?
The set of objects owned by user
account is called the schema.
54.
What is an index? How it is implemented in Oracle database?
An index is a database structure
used by the server to have direct access of a row in a table. An index is
automatically created when a unique of primary key constraint clause is
specified in create table command
55. What are
clusters?
Group of tables physically stored
together because they share common columns and are often used together is
called cluster.
56. What is
a cluster key?
The related columns of the tables
are called the cluster key. The cluster key is indexed using a cluster
index and its value is stored only once for multiple tables in the cluster.
57. What is
the basic element of base configuration of an Oracle database?
It consists of
one or more data files.
one or more control files.
two or more redo log files.
The Database contains
multiple users/schemas
one or more rollback segments
one or more tablespaces
Data dictionary tables
User objects (table,indexes,views
etc.,)
The server that access the database
consists of
SGA (Database buffer, Dictionary
Cache Buffers, Redo log buffers, Shared SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGS
58. What is
a deadlock? Explain.
Two processes waiting to update the
rows of a table, which are locked by other processes then deadlock arises.
In a database environment this will
often happen because of not issuing the proper row lock commands. Poor design
of front-end application may cause this situation and the performance of server
will reduce drastically.
These locks will be released
automatically when a commit/rollback operation performed or any one of this
processes being killed externally.
Memory Management
59. What is
SGA?
The System Global Area in an Oracle
database is the area in memory to facilitate the transfer of information
between users. It holds the most recently requested structural information
between users. It holds the most recently requested structural information
about the database. The structure is database buffers, dictionary cache, redo
log buffer and shared pool area.
60. What is
a shared pool?
The data dictionary cache is stored
in an area in SGA called the shared pool. This will allow sharing of parsed SQL
statements among concurrent users.
61.
What is mean by Program Global Area (PGA)?
It is area in memory that is used by
a single Oracle user process.
62.
What is a data segment?
Data segment are the physical areas
within a database block in which the data associated with tables and clusters
are stored.
63.
What are the factors causing the reparsing of SQL statements in SGA?
Due to insufficient shared pool
size.
Monitor the ratio of the reloads
takes place while executing SQL statements. If the ratio is greater than 1 then
increase the SHARED_POOL_SIZE.
Database
Logical & Physical Architecture
64.
What is Database Buffers?
Database buffers are cache in the
SGA used to hold the data blocks that are read from the data segments in the
database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in
INIT.ORA decides the size.
65. What is
dictionary cache?
Dictionary cache is information
about the database objects stored in a data dictionary table.
66. What is
meant by recursive hints?
Number of times processes repeatedly
query the dictionary table is called recursive hints. It is due to the data
dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we
can optimize the size of data dictionary cache.
67. What is
redo log buffer?
Changes made to the records are
written to the on-line redo log files. So that they can be used in roll forward
operations during database recoveries. Before writing them into the redo log
files, they will first brought to redo log buffers in SGA and LGWR will write
into files frequently. LOG_BUFFER parameter will decide the size.
68. How will
you swap objects into a different table space for an existing database?
- Export the user
- Perform import using the command
imp system/manager file=export.dmp indexfile=newrite.sql. This will create all
definitions into newfile.sql.
- Drop necessary objects.
- Run the script newfile.sql after
altering the tablespaces.
- Import from the backup for the
necessary objects.
69. List the
Optional Flexible Architecture (OFA) of Oracle database? How can we
organize the tablespaces in Oracle database to have maximum performance?
SYSTEM – Data dictionary tables.
DATA – Standard operational
tables.
DATA2- Static tables used for
standard operations
INDEXES – Indexes for Standard
operational tables.
INDEXES1 – Indexes of static tables
used for standard operations.
TOOLS – Tools table.
TOOLS1 – Indexes for tools table.
RBS – Standard Operations Rollback
Segments,
RBS1,RBS2 – Additional/Special
Rollback segments.
TEMP – Temporary purpose tablespace
TEMP_USER – Temporary tablespace for
users.
USERS – User tablespace.
70. How will
you force database to use particular rollback segment?
SET TRANSACTION USE ROLLBACK SEGMENT
rbs_name.
71.
What is meant by free extent?
A free extent is a collection of
continuous free blocks in tablespace. When a segment is dropped its extents are
reallocated and are marked as free.
72.Which
parameter in Storage clause will reduce number of rows per block?
PCTFREE parameter
Row size also reduces no of rows per
block.
73.
What is the significance of having storage clause?
We can plan the storage for a table
as how much initial extents are required, how much can be extended next, how
much % should leave free for managing row updating, etc.,
74.
How does Space allocation table place within a block?
Each block contains entries as
follows
Fixed block header
Variable block header
Row Header, row date (multiple rows
may exists)
PCTEREE (% of free space for row
updating in future).
75.
What is the role of PCTFREE parameter is storage clause?
This is used to reserve certain amount
of space in a block for expansion of rows.
76.
What is the OPTIMAL parameter?
It is used to set the optimal length
of a rollback segment.
77.
What is the functionality of SYSTEM table space?
To manage the database level
transactions such as modifications of the data dictionary table that record
information about the free space usage.
78.
How will you create multiple rollback segments in a database?
- Create a database, which
implicitly creates a SYSTEM rollback segment in a SYSTEM tablespace.
- Create a second rollback segment
name R0 in the SYSTEM tablespace.
- Make new rollback segment
available (after shutdown, modify init.ora file and start database)
- Create other tablespaces (RBS) for
rollback segments.
- Deactivate rollback segment R0 and
activate the newly created rollback segments.
79. How the
space utilization takes place within rollback segments?
It will try to fit the transaction
in a cyclic fashion to all existing extents. Once it found an extent is in use
then it forced to acquire a new extent (number of extents is based on the
optimal size)
80. Why
query fails sometimes?
Rollback segment dynamically extent
to handle larger transactions entry loads.
A single transaction may wipeout
all available free space in the rollback segment tablespace. This prevents
other user using rollback segments.
81. How will
you monitor the space allocation?
By querying DBA_SEGMENT table/view
82. How will
you monitor rollback segment status?
Querying the DBA_ROLLBACK_SEGS view
IN
USE
– Rollback Segment is on-line.
AVAILABLE
– Rollback Segment available but not on-line.
OFF-LINE
– Rollback Segment off-line
INVALID
– Rollback Segment Dropped.
NEEDS RECOVERY
– Contains data but need recovery or corrupted.
PARTLY AVAILABLE – Contains
data from an unresolved transaction involving a distributed
database.
83. List the
sequence of events when a large transaction that exceeds beyond its optimal
value when an entry wraps and causes the rollback segment to expand into
another extend.
Transaction Begins.
An entry is made in the RES header
for new transactions entry
Transaction acquires blocks in an
extent of RBS
The entry attempts to wrap into
second extent. None is available, so that the RBS must extent.
The RBS checks to see if it is part
of its OPTIMAL size.
RBS chooses its oldest inactive
segment.
Oldest inactive segment is
eliminated.
RBS extents
The data dictionary tables for space
management are updated.
Transaction Completes.
84. How can
we plan storage for very large tables?
Limit the number of extents in the
table
Separate table from its indexes.
Allocate sufficient temporary
storage.
85. How will
you estimate the space required by a non-clustered table?
Calculate the total header size
Calculate the available data space
per data block
Calculate the combined column
lengths of the average row
Calculate the total average row
size.
Calculate the average number rows
that can fit in a block
Calculate the number of blocks and
bytes required for the table.
After arriving the calculation, add
10 % additional space to calculate the initial extent size for a working table.
86. It is
possible to use raw devices as data files and what are the advantages over file
system files?
Yes.
The advantages over file system
files are that I/O will be improved because Oracle is bye-passing the kernel
which writing into disk. Disk corruption will be very less.
87. What is
a Control file?
Database’s overall physical
architecture is maintained in a file called control file. It will be used to
maintain internal consistency and guide recovery operations. Multiple copies of
control files are advisable.
88. How to
implement the multiple control files for an existing database?
Shutdown the database
Copy one of the existing control
file to new location
Edit Config ora file by adding new
control filename
Restart the database.
89. What is
redo log file mirroring? How can be achieved?
Process of having a copy of redo log
files is called mirroring.
This can be achieved by creating
group of log files together, so that LGWR will automatically writes them to all
the members of the current on-line redo log group. If any one group fails then
database automatically switch over to next group. It degrades performance.
90. What is
advantage of having disk shadowing / mirroring?
Shadow set of disks save as a backup
in the event of disk failure. In most operating systems if any disk failure
occurs it automatically switchover to place of failed disk.
Improved performance because most OS
support volume shadowing can direct file I/O request to use the shadow set of
files instead of the main set of files. This reduces I/O load on the main set
of disks.
91. What is
use of rollback segments in Oracle database?
They allow the database to maintain
read consistency between multiple transactions.
92. What is
a rollback segment entry?
It is the set of before image data
blocks that contain rows that are modified by a transaction.
Each rollback segment entry must be
completed within one rollback segment.
A single rollback segment can have
multiple rollback segment entries.
93. What is
hit ratio?
It is a measure of well the data
cache buffer is handling requests for data.
Hit Ratio = (Logical Reads –
Physical Reads – Hits Misses)/ Logical Reads.
94. When
will be a segment released?
When Segment is dropped.
When Shrink (RBS only)
When truncated (TRUNCATE used with
drop storage option)
95. What are
disadvantages of having raw devices?
We should depend on export/import
utility for backup/recovery (fully reliable)
The tar command cannot be used for
physical file backup, instead we can use dd command, which is less flexible and
has limited recoveries.
96. List the
factors that can affect the accuracy of the estimations?
- The space used transaction entries
and deleted records, does not become free immediately after completion due to
delayed cleanout.
- Trailing nulls and length bytes
are not stored.
- Inserts of, updates to and deletes
of rows as well as columns larger than a single data block, can cause
fragmentation a chained row pieces.
Database
Security & Administration
97. What is
user Account in Oracle database?
A user account is not a physical
structure in database but it is having important relationship to the objects in
the database and will be having certain privileges.
98. How will
you enforce security using stored procedures?
Don’t grant user access directly to
tables within the application.
Instead grant the ability to access
the procedures that access the tables.
When procedure executed it will
execute the privilege of procedures owner. Users cannot access tables except
via the procedure.
99. What are
the dictionary tables used to monitor a database space?
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.
SQL*Plus
Statements
100. What
are the types of SQL statement?
Data Definition Language: CREATE,
ALTER, DROP, TRUNCATE, REVOKE, NO AUDIT & COMMIT.
Data Manipulation Language: INSERT,
UPDATE, DELETE, LOCK TABLE, EXPLAIN PLAN & SELECT.
Transactional Control: COMMIT &
ROLLBACK
Session Control: ALTERSESSION &
SET ROLE
System Control: ALTER SYSTEM.
101. What is
a transaction?
Transaction is logical unit between
two commits and commit and rollback.
102. What is
difference between TRUNCATE & DELETE?
TRUNCATE commits after deleting
entire table i.e., cannot be rolled back.
Database triggers do not fire on
TRUNCATE
DELETE allows the filtered deletion.
Deleted records can be rolled back or committed.
Database triggers fire on DELETE.
103.
What is a join? Explain the different types of joins?
Join is a query, which retrieves
related columns or rows from multiple tables.
Self Join – Joining the table with
itself.
Equi Join – Joining two tables by
equating two common columns.
Non-Equi Join – Joining two tables
by equating two common columns.
Outer Join – Joining two tables in
such a way that query can also retrieve rows that do not have corresponding
join value in the other table.
104.
What is the sub-query?
Sub-query is a query whose return
values are used in filtering conditions of the main query.
105.
What is correlated sub-query?
Correlated sub-query is a sub-query,
which has reference to the main query.
106.
Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order
eg.
select empno, ename from emp where.
107.
Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the
m-th occurrence of the string 2 in string1. The search begins from nth position
of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of
size m in string1, starting from n-th position of string1.
108.
Explain UNION, MINUS, UNION ALL and INTERSECT?
INTERSECT – returns all
distinct rows selected by both queries.
MINUS
– returns all distinct rows selected by the first query but not by the second.
UNION
– returns all distinct rows selected by either query
UNION ALL – returns
all rows selected by either query, including all duplicates.
109.
What is ROWID?
ROWID is a pseudo column attached to
each row of a table. It is 18 characters long, blockno, rownumber are the
components of ROWID.
110. What is
the fastest way of accessing a row in a table?
Using ROWID.CONSTRAINTS
111.
What is an integrity constraint?
Integrity constraint is a rule that
restricts values to a column in a table.
112.
What is referential integrity constraint?
Maintaining data integrity through a set of rules that
restrict the values of one or more columns of the tables based on the values of
primary key or unique key of the referenced table.
113.
What is the usage of SAVEPOINTS?
SAVEPOINTS are used to subdivide a
transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.
114.
What is ON DELETE CASCADE?
When ON DELETE CASCADE is specified
Oracle maintains referential integrity by automatically removing dependent
foreign key values if a referenced primary or unique key value is removed.
115.
What are the data types allowed in a table?
CHAR, VARCHAR2, NUMBER, DATE, RAW,
LONG and LONG RAW.
116.
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 the maximum length is 255
and 2000 for VARCHAR2.
117.
How many LONG columns are allowed in a table? Is it possible to use LONG
columns in WHERE clause or ORDER BY?
Only one LONG column is allowed. It
is not possible to use LONG column in WHERE or ORDER BY clause.
118.
What are the pre-requisites to modify datatype of a column and to add a column
with NOT NULL constraint?
- To modify the datatype of a column
the column must be empty.
- To add a column with NOT NULL
constrain, the table must be empty.
119.
Where the integrity constraints are stored in data dictionary?
The integrity constraints are stored
in USER_CONSTRAINTS.
120.
How will you activate/deactivate integrity constraints?
The integrity constraints can be
enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.
121.
If unique key constraint on DATE column is created, will it validate the rows
that are inserted with SYSDATE?
It won’t, Because SYSDATE format
contains time attached with it.
122.
What is a database link?
Database link is a named path
through which a remote database can be accessed.
123.
How to access the current value and next value from a sequence? Is it possible
to access the current value in a session before accessing next value?
Sequence name CURRVAL, sequence name
NEXTVAL. It is not possible. Only if you access next value in the session,
current value can be accessed.
124.
What is CYCLE/NO CYCLE in a Sequence?
CYCLE specifies that the sequence
continue 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.
125.
What are the advantages of VIEW?
- To protect some of the columns of
a table from other users.
- To hide complexity of a query.
- To hide complexity of
calculations.
126.
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.
127.
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
and these tables are the base tables of a view, then the changes will be
reference on the view.
See Also : OS Interview for DBA
No comments:
Post a Comment