Oracle Interview Questions – Part 33
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
Q.What command would you use to encrypt a PL/SQL application?
Q.What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
Q.Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
Q.What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
Q.Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
Q.When you start an Oracle DB which file is accessed first?
Oracle first opens and reads the initialization parameter file (init.ora)
[[email protected] ~]$ ls -la $ORACLE_HOME/dbs/initDB1_SID.ora
-rw-r–r– 1 oracle oinstall 1023 May 10 19:27 /u01/app/oracle/product/11.2.0/dbs/initDB1_SID.ora
Q.What is the job of SMON and PMON processes?
SMON – System Monitor Process – Performs recovery after instance failure, monitors temporary segments and extents; cleans temp segments, coalesces free space (mandatory process for DB and starts by default)
PMON – Process Monitor – Recovers failed process resources. In Shared Server architecture, monitors and retarts any failed dispatcher or server proceses (mandatory process for DB and starts by default)
[[email protected] ~]$ ps -ef |grep -e pmon -e smon |grep -v grep
oracle 6755 1 0 12:59 ? 00:00:05 ora_pmon_DB1_SID
oracle 6779 1 0 12:59 ? 00:00:06 ora_smon_DB1_SID
Q.What is the purpose of Redo Log files?
Before Oracle changes data in a datafile it writes these changes to the redo log.
If something happens to one of the datafiles, a backed up datafile can be restored and the redo, that was written since, replied, which brings the datafile to the state it had before it became unavailable.
Q.Which default Database roles are created when you create a Database?
CONNECT , RESOURCE and DBA are three default roles. The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role.
The following query lists all the roles in the database:
SELECT * FROM DBA_ROLES;
Q.What is a Shared Pool?
The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE.
The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms.
Increasing the value of this parameter increases the amount of memory reserved for the shared pool.
Q.What is kept in the Database Buffer Cache?
The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles.
All user processes concurrently connected to the instance share access to the database buffer cache
Q.How often you should collect statistics for a table?
Analyse if it’s necessary!
– Refresh STALE statistics before the batch processes run but only for tables involved in batch run,
– Don’t do it if you don’t have to.
– Oracle databse has default, scheduled job “gather_stats_job” that analyses stats on a daily basis during the maintenance window time.
Q.How do you collect statistics for a table, schema and Database?
Using DBMS_STATS package to gather Oracle dictionary statistics.
Q.A table is having few rows, should you create indexes on this table
Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large.
You can create an index on any column; however, if the column is not used in any of these situations, creating an index on the column does not increase performance and the index takes up resources unnecessarily.
Q.How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual.
Q.Name three advisory statistics you can collect.
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics
Q.What view would you use to look at the size of a data file?
Q.What view would you use to determine free space in a tablespace?
Q.How can you gather statistics on a table?
The ANALYZE command.
Q.How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
Q.What is Instance Recovery?
While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started.
Instance recovery occurs in two steps:
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.
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.
Q.What is being written into the Redo Log Files?
Redo log records all changes made in datafiles.
In the Oracle database, redo logs comprise files in a proprietary format which log a history of all changes made to the database. Each redo log file consists of redo records. A redo record, also called a redo entry, holds a group of change-vectors, each of which describes or represents a change made to a single block in the database.
Let’s get into this topic a little bit dipper:
Log writer (LGWR) writes redo log buffer contents Into Redo Log FIles. LGWR does this every three seconds, when the redo log buffer is 1/3 full and immediately before the Database Writer (DBWn) writes its changed buffers into the datafile. The redo log of a database consists of two or more redo log files. The database requires a minimum of two files to guarantee that one is always available for writing while the
other is being archived (if the DB is in ARCHIVELOG mode). LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again.
Filled redo log files are available to LGWR for reuse depending on whether archiving is enabled.
If archiving is disabled (the database is in NOARCHIVELOG mode), a filled redo log file is available after the changes recorded in it have been written to the datafiles.
If archiving is enabled (the database is in ARCHIVELOG mode), a filled redo log file is available to LGWR after the changes recorded in it have been written to the datafiles and the file has been archived.
Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file. Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.
If the database is in ARCHIVELOG mode it cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents.
If archiving is disabled (DB is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.
A log switch is the point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.
Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it.
When the database archives redo log files, the archived log retains its log sequence number.
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 where and then issue the recover command with the using backup control file clause.
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 difference between Multithreaded/Shared Server and Dedicated Server?
Oracle Database creates server processes to handle the requests of user processes connected to an instance.
A server process can be either of the following:
-A dedicated server process, which services only one user process
-A shared server process, which can service multiple user processes
Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters.
Q.What is Automatic Management of Segment Space setting?
Oracle9i New Feature Series: Automatic Segment Space Management
Automatic Segment Space Management (ASSM) introduced in Oracle9i is an easier way of managing space in a segment using bitmaps.
It eliminates the DBA from setting the parameters pctused, freelists, and freelist groups.
ASSM can be specified only with the locally managed tablespaces (LMT).
Oracle uses bitmaps to manage the free space. Bitmaps allow Oracle to manage free space more automatically.
Here is an example:
CREATE TABLESPACE example
DATAFILE ‘/oradata/ORA_SID/example01.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
SEGMENT SPACE MANAGEMENT AUTO;
The storage parameters PCTUSED, FREELISTS and FREELIST GROUPS specified while creating a table are ignored by Oracle on a LMT ASSM tablespace. Oracle does not produce an error.
One huge benefit of having ASSM is to reduce the “Buffer Busy Waits” you see on segments.
Using ASSM can hinder database DML performance, and most Oracle experts will use manual freelists and freelist groups.
Q.What is the difference between DELETE and TRUNCATE statements?
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows.
If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
DELETE will cause all DELETE triggers on the table to fire.
TRUNCATE removes all rows from a table. A WHERE clause is not permited. The operation cannot be rolled back and no triggers will be fired.
As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.
Q.When should you rebuilt indexes?
In 90% cases – NEVER.
When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based.
Also index blevel is one of the key indicators of performance of sql queries doing Index range scans.
Q.Can you built indexes online?
YES. You can create and rebuild indexes online.
This enables you to update base tables at the same time you are building or rebuilding indexes on that table.
You can perform DML operations while the index build is taking place, but DDL operations are not allowed.
Parallel execution is not supported when creating or rebuilding an index online.
The following statements illustrate online index build operations:
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
Q.Where in the Oracle directory tree structure are audit traces placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
Q.Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
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>;