Oracle Interview Questions – Part 2
Q.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.
Q.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 comman (Ver 7.0)
Q.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.
Q.What does a Control file Contain ?
A Control file records the physical structure of the database. It contains the following information.
Names and locations of a database’s files and redolog files.
Time stamp of database creation.
Q.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.
Q.What is Index Cluster ?
A Cluster with an index on the Cluster Key
Q.When does a Transaction end ?
When it is committed or Rollbacked.
Q.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.
Q.What is Rollback Segment ?
A Database contains one or more Rollback Segments to temporarily store “undo” information.
Q.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.
Q.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).
Q.What are the different approaches used by Optimizer in choosing an execution plan ?
Rule-based and Cost-based.
Q.What does ROLLBACK do ?
ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.
Q.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/';
Q.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.
Q.What are 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
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)
CKPT (Check Point)
User Process with associated PGS
Q.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:
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);
ANALYZE TABLE owner.table ESTIMATE STATISTICS;
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.
Q.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.
Q.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.
Q.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.
Q.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
Q.What is a Tablespace?
A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together
Q.Define Transaction ?
A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.
Q.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.
Q.What is Read-Only Transaction ?
A Read-Only transaction ensures that the results of each query executed in the transaction are consistant with respect to the same point in time.
Q.What is a deadlock ? Explain .
Two processes wating to update the rows of a table which are locked by the other process then deadlock arises. In a database environment this will often happen because of not issuing 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.
Q.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.
Q.What is Parallel Server ?
Multiple instances accessing the same database (Only In Multi-CPU environments)
Q.What is a Schema ?
The set of objects owned by user account is called the schema.
Q.What is clusters ?
Group of tables physically stored together because they share common columns and are often used together is called Cluster.