|
Oracle Interview Questions and Answers
Can a view based on another view?
Yes.
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.
What is an Oracle sequence?
A sequence generates a serial list of unique numbers for
numerical columns of a database's tables.
What is a synonym?
A synonym is an alias for a table, view, sequence or
program unit.
What are the types of synonyms?
There are two types of synonyms private and public.
What is a private synonym?
Only its owner can access a private synonym.
What is a public synonym?
Any database user can access a public synonym.
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.
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.
How are the index updates?
Indexes are automatically maintained and used by Oracle.
Changes to table data are automatically incorporated
into all relevant indexes.
What is a Tablespace?
A database is divided into Logical Storage Unit called
tablespace. A tablespace is used to grouped related
logical structures together
What is Rollback Segment ?
A Database contains one or more Rollback Segments to
temporarily store "undo" information.
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.
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 can’t be
changed latter.
What does a Control file Contain ?
A Control file records the physical structure of the
database. It contains the following information.
Database Name
Names and locations of a database's files and redolog
files.
Time stamp of database creation.
What is difference between UNIQUE constraint and PRIMARY
KEY constraint ?
A column defined as UNIQUE can contain Nulls while a
column defined as PRIMARY KEY can't contain Nulls.
What is Index Cluster ?
A Cluster with an index on the Cluster Key
When does a Transaction end ?
When it is committed or Rollbacked.
What is the effect of setting the value "ALL_ROWS" for
OPTIMIZER_GOAL parameter of the ALTER SESSION command ?
What are the factors that affect OPTIMIZER in choosing
an Optimization approach ?
Answer The OPTIMIZER_MODE initialization parameter
Statistics in the Data Dictionary the OPTIMIZER_GOAL
parameter of the ALTER SESSION command hints in the
statement.
What is the effect of setting the value "CHOOSE" for
OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
The Optimizer chooses Cost_based approach and optimizes
with the goal of best throughput if statistics for
atleast one of the tables accessed by the SQL statement
exist in the data dictionary. Otherwise the OPTIMIZER
chooses RULE_based approach.
How does one create a new database? (for DBA)
One can create and modify Oracle databases using the
Oracle "dbca" (Database Configuration Assistant)
utility. The dbca utility is located in the $ORACLE_HOME/bin
directory. The Oracle Universal Installer (oui) normally
starts it after installing the database server software.
One can also create databases manually using scripts.
This option, however, is falling out of fashion, as it
is quite involved and error prone. Look at this example
for creating and Oracle 9i database:
CONNECT SYS AS SYSDBA
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';
CREATE DATABASE;
What database block size should I use? (for DBA)
Oracle recommends that your database block size match,
or be multiples of your operating system block size. One
can use smaller block sizes, but the performance cost is
significant. Your choice should depend on the type of
application you are running. If you have many small
transactions as with OLTP, use a smaller block size.
With fewer but larger transactions, as with a DSS
application, use a larger block size. If you are using a
volume manager, consider your "operating system block
size" to be 8K. This is because volume manager products
use 8K blocks (and this is not configurable).
What are the different approaches used by Optimizer in
choosing an execution plan ?
Rule-based and Cost-based.
What does ROLLBACK do ?
ROLLBACK retracts any of the changes resulting from the
SQL statements in the transaction.
How does one coalesce free space ? (for DBA)
SMON coalesces free space (extents) into larger,
contiguous extents every 2 hours and even then, only for
a short period of time.
SMON will not coalesce free space if a tablespace's
default storage parameter "pctincrease" is set to 0.
With Oracle 7.3 one can manually coalesce a tablespace
using the ALTER TABLESPACE ... COALESCE; command, until
then use:
SQL> alter session set events 'immediate trace name
coalesce level n';
Where 'n' is the tablespace number you get from SELECT
TS#, NAME FROM SYS.TS$;
You can get status information about this process by
selecting from the SYS.DBA_FREE_SPACE_COALESCED
dictionary view.
How does one prevent tablespace fragmentation? (for DBA)
Always set PCTINCREASE to 0 or 100.
Bizarre values for PCTINCREASE will contribute to
fragmentation. For example if you set PCTINCREASE to 1
you will see that your extents are going to have weird
and wacky sizes: 100K, 100K, 101K, 102K, etc. Such
extents of bizarre size are rarely re-used in their
entirety. PCTINCREASE of 0 or 100 gives you nice round
extent sizes that can easily be reused. E.g.. 100K, 100K,
200K, 400K, etc.
Use the same extent size for all the segments in a given
tablespace. Locally Managed tablespaces (available from
8i onwards) with uniform extent sizes virtually
eliminates any tablespace fragmentation. Note that the
number of extents per segment does not cause any
performance issue anymore, unless they run into
thousands and thousands where additional I/O may be
required to fetch the additional blocks where extent
maps of the segment are stored.
Where can one find the high water mark for a table? (for
DBA)
There is no single system table, which contains the high
water mark (HWM) for a table. A table's HWM can be
calculated using the results from the following SQL
statements:
SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
ANALYZE TABLE owner.table ESTIMATE STATISTICS;
SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
Thus, the tables' HWM = (query result 1) - (query result
2) - 1
NOTE: You can also use the DBMS_SPACE package and
calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.
What is COST-based approach to optimization ?
Considering available access paths and determining the
most efficient execution plan based on statistics in the
data dictionary for the tables accessed by the statement
and their associated clusters and indexes.
What does COMMIT do ?
COMMIT makes permanent the changes resulting from all
SQL statements in the transaction. The changes made by
the SQL statements of a transaction become visible to
other user sessions transactions that start only after
transaction is committed.
How are extents allocated to a segment? (for DBA)
Oracle8 and above rounds off extents to a multiple of 5
blocks when more than 5 blocks are requested. If one
requests 16K or 2 blocks (assuming a 8K block size),
Oracle doesn't round it up to 5 blocks, but it allocates
2 blocks or 16K as requested. If one asks for 8 blocks,
Oracle will round it up to 10 blocks.
Space allocation also depends upon the size of
contiguous free space available. If one asks for 8
blocks and Oracle finds a contiguous free space that is
exactly 8 blocks, it would give it you. If it were 9
blocks, Oracle would also give it to you. Clearly Oracle
doesn't always round extents to a multiple of 5 blocks.
The exception to this rule is locally managed
tablespaces. If a tablespace is created with local
extent management and the extent size is 64K, then
Oracle allocates 64K or 8 blocks assuming 8K-block size.
Oracle doesn't round it up to the multiple of 5 when a
tablespace is locally managed.
Can one rename a database user (schema)? (for DBA)
No, this is listed as Enhancement Request 158508.
Workaround:
Do a user-level export of user A
create new user B
Import system/manager fromuser=A touser=B
Drop user A
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
|
