Oracle Interview Questions – Part 35

Q.What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

Q.How do you add a data file to a tablespace?
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name> SIZE <size>

Q.How do you resize a data file?
ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

Q.What is difference between PFile and SPFile?
A PFILE is a static, text file located in $ORACLE_HOME/dbs – UNIX
An SPFILE (Server Parameter File) is a persistent server-side binary file that can only be modified with the “ALTER SYSTEM SET” command.

Q.What is PCT Increase setting?

PCTINCREASE refers to the percentage by which each next extent (beginning with the third extend) will grow.
The size of each subsequent extent is equal to the size of the previous extent plus this percentage increase.
Preventing tablespace fragmentation
Try to 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. Eg. 100K, 100K, 200K, 400K, etc.
Locally Managed tablespaces (available from Oracle 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 is a Global Index and Local Index?
Local  Index – each partition of a local index is associated with exactly one partition of the table.
Global Index – global index is associated with multiple partitions of the table.
Oracle offers two types of global partitioned index:
-Global Range Partitioned Indexes
-Global Hash Partitioned Indexes
Global Nonpartitioned Indexes – behave just like a nonpartitioned index.

Q.What is the difference between Range Partitioning and Hash Partitioning?
Range Partitioning maps data to partitions based on a range of column values (e.g. a date column)
Hash Partitioning maps data to partitions based on a hashing algorithm, evenly distributing data between the partitions.
This is typically used where ranges aren’t appropriate, i.e. customer number, product ID

Q.What is B-Tree Index?
A B-Tree index is a data structure in the form of a tree, but it is a tree of database blocks, not rows.
Note: “B” is not for binary; it’s balanced.

Q.What is STATSPACK tool?
STATSPACK is a performance diagnosis tool provided by Oracle starting from Oracle 8i and above.
STATSPACK is a diagnosis tool for instance-wide performance problems; it also supports application tuning activities by providing data which identifies high-load SQL statements.
Although AWR and ADDM (introduced in Oracle 10g) provide better statistics than STATSPACK,
users that are not licensed to use the Enterprise Manager Diagnostic Pack should continue to use statspack.
More information about STATSPACK, can be found in file $ORACLE_HOME/rdbms/admin/spdoc.txt.

Q.Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

Q.You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files were and then issue the recover command with the using backup control file clause.

Q.A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

Q.Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

Q.You have just compiled a PL/SQL package but got errors, how would you view the errors?
show errors

Q.How can you gather statistics on a table?
exec dbms_stats.gather_table_stats
Also, remember to analyze all associated indexes on that table using dbms_stats.gather_index_stats

Q.When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?

If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZe parameter.

Q.When should you increase copy latches? What parameters control copy latches.

When you get excessive contention for the copy latches as shown by the “redo copy” latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system

Q.Can you make collection of Statistics for tables automatically?
YES. Oracle databse has default, scheduled job “gather_stats_job” that analyses stats on a daily basis during the maintenance window time.
There are two scheduled activities related to the collection of Oracle “statistics”:
•AWR statistics:  Oracle has an automatic method to collect AWR “snapshots” of data that is used to create elapsed-time performance reports.
•Optimizer statistics:  Oracle has an automatic job to collect statistics to help the optimizer make intelligent decisions about the best access method to fetch the desired rows.
This job can be disabled with this command: exec dbms_scheduler.disable(’SYS.GATHER_STATS_JOB’);
Oracle collects optimizer statistics for SQL via the default of autostats_target = auto.

Q.On which columns you should create Indexes?
In general, you should create an index on a column in any of the following situations:
•The column is queried frequently
•A referential integrity constraint exists on the column
•A UNIQUE key integrity constraint exists on the column
The following list gives guidelines in choosing columns to index:
•You should create indexes on columns that are used frequently in WHERE clauses
•Are used frequently to join tables
•Are used frequently in ORDER BY clauses
•On columns that have few of the same values or unique values in the table

Q.What is the difference between Direct Path and Conventional Path 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.

Q.How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.

Q.Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

Q.How do you add a data file to a tablespace?

alter tablespace USERS add datafile ‘/ora01/oradata/users02.dbf’ size 50M;

Q.How do you re-size a data file?

alter database datafile ‘/ora01/oradata/users02.dbf’ resize 100M;

Q.How can you generate debugging output from PL/SQL?

Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.

Q.What are the types of triggers? 

There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT etc.

Q.Give one method for transferring a table from one schema to another:

There are several possible methods, export-import, CREATE TABLE… AS SELECT, or COPY.

Q.What is the purpose of the IMPORT option IGNORE? What is it?s default setting

The IMPORT IGNORE option tells import to ignore “already exists” errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the tables data will be inserted. The default value is N.

Q.What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause

The index is created in the user?s default tablespace and all sizing information is lost. Oracle doesn?t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.

Q.(On UNIX) When should more than one DB writer process be used? How many should be used

If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter.

Q.What is explain plan and how is it used

The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

Q.How do you set the number of lines on a page of output?  

The SET command in SQLPLUS is used to control the number of lines generated per page and the width of those lines, for example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.