1. What is an Oracle Instance?
The
collection of allocated memory and the running processes on the server is
called an "instance".
2.
What
information is stored in Control File?
The control files
include information about the physical structure of the database, such as
database name, names and locations of
datafiles and redo log files , archived log information, backup set and backup
piece information, the current log sequence number and checkpoint information.
Therefore, if you make a change to the physical structure of your database
(using ALTER DATABASE statements), then you should immediately make a backup of
your control file.
3.
When you
start an Oracle DB which file is accessed first?
Initialization
Parameter file i.e spfile_SID.ora or init_SID.ora.
4.
What is
the Job of SMON, PMON processes?
System monitor (SMON): monitors
the system activity and performs the instance recovery when a system failure
occurs or when a shutdown immediate or abort statement is issued it will
disconnect the users and performs necessary commit and rollback operations
respectively.
Process monitor (PMON):
Monitors the users activity and the performs the session recovery when a
session fails for ex: when I session failed or lost connection the PMON
performs the cleanup process of session data or transactions.
5.
What is
Instance Recovery?
When an Oracle instance fails, Oracle performs an instance recovery when
the associated database is re-started.
Instance recovery occurs in
two steps:
Cache recovery: Changes being made to a database are recorded in
the database buffer cache. These changes are also
recorded in online redo log
files simultaneously. When there are enough data in the database buffer cache,
they are written to data files. If an Oracle instance fails before the data in
the database buffer cache are written to data files, Oracle uses the data
recorded in the online redo log files to recover the lost data when the
associated database is re-started. This process is called
cache recovery.
Transaction recovery: When a transaction modifies data in a
database, the before image of the modified data is stored in an undo segment.
The data stored in the undo
segment is used to restore the original values
in
case a transaction is rolled back. At the time of an instance failure, the
database may have uncommitted
transactions. It is possible that changes made by
these uncommitted transactions have gotten saved in data files. To maintain
read consistency, Oracle rolls back all uncommitted transactions when the
associated database is re-started. Oracle uses the undo
data stored in undo
segments to accomplish this. This process is called transaction recovery.
6.
What is
written in Redo Log Files?
The
most crucial structure for recovery operations is the redo log, which consists
of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle
Database has an associated redo log to protect the database in case of an
instance failure.
7.
How do
you control number of Datafiles one can have in an Oracle database?
By using DB_FILES parameter update the init
file before starting the DB.
8.
How many
Maximum Datafiles can there be in an Oracle Database?
Depends on the OS support.
9
. What is
a Tablespace?
A logical object which
is mapped to the physical data files on the OS/storage.
A logical container
which holds the DB objects such as tables, indexes, MV’s.
10.
What is
the purpose of Redo Log files?
Redo log files provide a means and a mechanism for allowing
Oracle to redo transactions in the event of a database failure. Every
transaction that occurs that alters data in any way on the database (other than
transactions such as direct loads, and loads that are done with NOLOGGING option) is written synchronously to the
redo log files to provide a recovery mechanism in the case of media or other
system failure. This includes even transactions that have not been committed,
undo segment information, and any schema and object management statements for
objects in the database.
11.
Which
default Database roles are created when you create a Database?
Resource,
Connect and DBA are the three default roles created when you create a database.
12.
What is
a Checkpoint?
A checkpoint performs the following three
operations:
1. Every dirty block in the buffer cache is
written to the data files. That is, it synchronizes the data blocks in the buffer cache with
the datafiles on disk.
It's the DBWR that writes all modified database blocks back to the
datafiles.
The update of the datafile headers and the
control files is done by the LGWR.
Events that trigger a checkpoint
The following events
trigger a checkpoint.
13. Which
Process reads data from Datafiles?
Server process.
14.
Which
Process writes data in Datafiles?
DBWR db writer process.
15.
Can you
make a Data file auto extendible. If yes, how?
Alter
database datafile '/u01/oradata/DBSID/EXAMPLE01.DBF' autoextend on MAXSIZE 1G.
16.
What is
a Shared Pool?
The
shared pool is like a buffer for SQL statements. It is to store the SQL
statements so that the identical SQL statements do not have to be parsed each
time they're executed.
17.
What is
kept in the Database Buffer Cache?
Database Buffer cache is one of the most
important components of System Global Area (SGA). Database Buffer Cache is the
place where data blocks are copied from datafiles to perform SQL operations.
Buffer Cache is shared memory structure and it is concurrently accessed by all
server processes.
18.
How
many maximum Redo Logfiles one can have in a Database?
In a
database we can create 255 maximum redo log files.
19.
What is
difference between PFile and SPFile?
We can
change parameter in spfile dynamically but we cant change pfile dynamically
...to change parameter in pfile we need to bounce the instance.
20.
What is PGA_AGGREGRATE_TARGET parameter? size exceeds
PGA_AGGREGATE_TARGET is
an Oracle server parameter that specifies the target aggregate
PGA memory available to all server processes attached to the instance.
Stack Space: memory to hold session variable,
arrays and other session related info.
Non-shared area: an area where process writes their trace information and
copied to the trace files on the server.
21.
Large
Pool is used for what?
Large Pool is an optional memory structure used
for the following purposes:-
(1) Session information for shared server
(2) I/O server processes
(3) Parallel queries
(4) Backup and
recovery if using through RMAN.
22.
What is
PCT Increase setting?
PCTINCREASE refers to the percentage by which each
next extent (beginning with the third extend) will grow.
23.
What is
PCTFREE and PCTUSED Setting?
The setting for PCTFREE
sets the value for the percent of a block to reserve for updates. If PCTFREE is
to small, adequate space may not be reserved in the block for update of
variable sized rows in the block, or, may not have enough space for a complete
row insert. In this case a block chaining will occur where the data is migrated
to a new block and a pointer will be established from the old block to a new
block.
24.
What is Row Migration and Row Chaining?
A row
will be migrated when an update to that row would cause it to not fit on the
block anymore (with all of the other data that exists there currently). A
migration means that the entire row will move and we just leave behind the «forwarding
address». So, the original block just has the rowid of the new block and the
entire row is moved.
A row
is too large to fit into a single database block. For example, if you use a 4KB
blocksize for your database, and you need to insert a row of 8KB into it,
Oracle will use 3 blocks and store the row in pieces. Some conditions that will
cause row chaining are: Tables whose row size exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained
rows. Tables with more than 255 columns will have chained rows as Oracle break
wide tables up into pieces. So, instead of just having a forwarding address on
one block and the data on another we have data on two or more blocks.
25.
What is
01555 - Snapshot Too Old error and how do you avoid it?
The ORA-01555 is caused by Oracle read consistency
mechanism. If you have a long running SQL that starts at 10:30 AM, Oracle
ensures that all rows are as they appeared at 10:30 AM, even if the query runs
until noon!
Oracles does this by reading the "before image" of
changed rows from the online undo segments. If you have lots of updates,
long running SQL and too small UNDO, the ORA-01555 error will appear.
26.
What is
a Locally Managed Tablespace?
DICTIONARY: Specifies that the table space is managed
using dictionary tables
LOCAL: Specifies that tablespace is locally managed with a bitmap
27.
Can you
audit SELECT statements?
Auditing SQL select statements is used in systems governed by GLB,
SOX and HIPAA. But beware; you will need a storage mechanism to hold your
SQL select audits, a high data volume that can exceed the size of
your whole database, every day.
Auditing select statement is extremely complex because of the huge
amount of data that is generated every day. In large hospital systems, the select audit
trails can exceed terabytes every month with millions of dollars in extra
storage costs.
28.
What
does DBMS_FGA package do?
The central mechanism for the FGA is implemented in the package dbms_fga,
where all the APIs are defined.
Typically, a user other than SYS is given the responsibility of maintaining these policies.
29.
What is
Cost Based Optimization?
The Cost Based Optimizer (CBO) is a SQL Query optimizer that uses data statistics
to identify the query plan with lowest cost before execution. The cost is based
on the number of rows in a table, index efficiency, etc.
30.
How
often you should collect statistics for a table?
When
you collect new stats you are almost guaranteed to change some execution plans
in your system. Do you want that? No, you want the same execution plans as long
as they have the response time your users want. They haven't asked you for a
better (or worse) plan.
31.
How do
you collect statistics for a table, schema and database?
There are 3 different
methods of gathering stats
o ANALYZE statements
o DBMS_UTILITY package
o DBMS_STATS package
32.
Can you
make collection of statistics for table automatic?
Oracle
gather statistics on all database objects automatically and maintains those
statistics in a regularly-scheduled maintenance job. Automated statistics
collection significantly reduces the chances of getting poor execution plans
because of missing or stale statistics.
33.
On
which columns you should create indexes?
The following list gives guidelines in choosing columns to index:
·
You should create
indexes on columns that are used frequently in WHERE clauses.
·
You should create
indexes on columns that are used frequently to join tables.
·
You should create
indexes on columns that are used frequently in ORDER BY clauses.
·
You should create
indexes on columns that have few of the same values or unique values in the
table.
·
You should not create
indexes on small tables (tables that use only a few blocks) because a full
table scan may be faster than an indexed query.
·
If possible, choose a
primary key that orders the rows in the most appropriate order.
·
If only one column of
the concatenated index is used frequently in WHERE clauses, place that column
first in the CREATE INDEX statement.
·
If more than one column
in a concatenated index is used frequently in WHERE clauses, place the most
selective column first in the CREATE INDEX statement.
34.
What
type of indexes aree available in oracle?
Bitmap and B-tree.
35.
What is
B-tree index?
36. A table
is having few rows should you create indexes on this table?
No.
37. A column
is having many repeated values which type of index you should create on this
column?
Bitmap
for low cardinality columns.
38. When should you rebuilt indexes?
When
you have large data updates /loads or deletes.
39. can you built indexes online?
Yes.
Alter index index_name rebuilt online. It always good to drop an recreate the
indexes then rebuilding.
40. can you see execution plan of a statement?
Yes.
Through oracle’s Plan_table or using sql*plus trace command.
41. A table is created with the following
setting
storage
(initial 200k next 200k minextents 2
maxextents 100 pctincrease 40)
what
will be size of 4th extent?
200k
42. what is DB buffer cache
advisor?
The Buffer Cache Advisor provides advice on how
to size the Database Buffer Cache to obtain optimal cache hit ratios.
43. Can you change shared pool
size online?
Yes. It can be changed online as it’s a dynamic
parameter.
45. you want users to change the
passwords every two months. How do you enforce it?
Using
profile parameters.
46. how do you delete duplicate
rows?
Using
rowID as each row in the table has unique rowID.
DELETE FROM EMP t
WHERE
t.rowid > ( SELECT MIN(X.rowid)
FROM emp X
WHERE X.empid= t.empid );
47.
What is difference between DELETE and
TRUNCATE statements? size exceeds
TRUNCATE is a DDL command whereas DELETE
is a DML command.
TRUNCATE is much faster than DELETE.
You can't rollback in TRUNCATE but in
DELETE you can rollback.TRUNCATE removes the record permanently.
In case of TRUNCATE, Trigger doesn't get
fired. But in DML commands like DELETE .Trigger get fired.
You can't use conditions (WHERE clause)
in TRUNCATE. But in DELETE you can write conditions using WHERE clause.
48.
What is difference between direct path
loading and conventional loading?
A
conventional path load executes SQL INSERT statements to populate tables in an
Oracle database. A direct path load eliminates much of the Oracle database
overhead by formatting Oracle data blocks and writing the data blocks directly
to the database files. A direct load does not compete with other users for
database resources, so it can usually load data at near disk speed.
49. can
you disable and enable primary key?
Yes,
Alter table
disable constraint constraint_name.
Alter table
enable constraint constraint_name.
50. What
is the difference between range partition and hash partition?
Selects a
partition by determining if the partitioning key is inside a certain range.
The value
of a hash function determines membership
in a partition. Assuming there are four partitions, the hash function could
return a value from 0 to 3.
