Username: Password: Forgot Password?   Register Now !
Home Material/FAQ's Oracle DBA Interview Questions
Oracle DBA Interview Questions

How does one create a new database?

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.

What database block size should I use?

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).

How does one coalesce free space ?

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?

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.

Where can one find the high water mark for a table?

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.

How are extents allocated to a segment?

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)?

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

How do I find used/free space in a TEMPORARY tablespace?

Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:SELECT tablespace_name, SUM (bytes used), SUM (bytes free)

FROM V$temp_space_header

GROUP BY tablespace_name;

How does one do off-line database backups?

Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database. Do the following queries to get a list of all files that needs to be backed up:

select name from sys.v_$datafile;

select member from sys.v_$logfile;

select name from sys.v_$controlfile;

Sometimes Oracle takes forever to shutdown with the “immediate” option. As workaround to this problem, shutdown using these commands:

alter system checkpoint;

shutdown abort

startup restrict

shutdown immediate

Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.

How does one do on-line database backups?

Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary

storage (tapes). Look at this simple example.

ALTER TABLESPACE xyz BEGIN BACKUP;

! cp xyfFile1 /backupDir/

ALTER TABLESPACE xyz END BACKUP;

It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:

ALTER SYSTEM SWITCH LOGFILE; — Force log switch to update control file headers

ALTER DATABASE BACKUP CONTROLFILE TO  /backupDir/control.dbf ;

NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.

How does one put a database into ARCHIVELOG mode?

The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups. To enable ARCHIVELOG mode, simply change your database startup command script, and bounce the database:

SQLPLUS> connect sys as sysdba

SQLPLUS> startup mount exclusive;

SQLPLUS> alter database archivelog;

SQLPLUS> archive log start;

SQLPLUS> alter database open;

NOTE1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.

NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.

NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE,

log_archive_dest=… and log_archive_format=…

NOTE4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO  directory ; statement.

This statement is often used to switch archiving between a set of directories.

NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.

How does one backup archived log files?

One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will

hang! Look at this simple RMAN backup script:

RMAN> run {

2> allocate channel dev1 type disk;

3> backup

4> format  /app/oracle/arch_backup/log_t%t_s%s_p%p

5> (archivelog all delete input);

6> release channel dev1;

7> }

Does Oracle write to data files in begin/hot backup mode?

Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode. In backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas (changes) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks).

Because of this, one should notice increased log activity and archiving during on-line backups.

What is an administrative (privileged) user?

Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. An administrative account is a user that is granted SYSOPER or SYSDBA privileges. SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and

special operating system groups. This password file is created with the orapwd utility.

How does one connect to an administrative user?

If an administrative user belongs to the “dba” group on Unix, or the “ORA_DBA” (ORA_sid_DBA) group on NT, he/she can connect like this:

connect / as sysdba

No password is required. This is equivalent to the desupported “connect internal” method.

A password is required for “non-secure” administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example:

connect sys/password as sysdba

How does one create a password file?

The Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect.

Follow this procedure to create a new password file:

. Log in as the Oracle software owner

. Runcommand: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

. Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)

. Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set.

. Startup the database (SQLPLUS> STARTUP)

NOTE: The orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table of many systems. Administrators needs to be aware of this!

How does one add users to a password file?

One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility. GRANT SYSDBA TO scott;

Why are OPS$ accounts a security risk in a client/server environment?

If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be sure who they really are. With terminals, you can rely on operating system passwords, with Windows, you cannot. If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user. If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED

EXTERNALLY will only be in effect from the local host. Also, if you are using “OPS$” as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.

What third party tools can be used with Oracle EBU/ RMAN?

The following Media Management Software Vendors have integrated their media management software packages with Oracle Recovery Manager and Oracle7 Enterprise Backup Utility. The Media Management Vendors will provide first line technical support for the

integrated backup/recover solutions.

Veritas NetBackup

EMC Data Manager (EDM)

HP OMNIBack II

IBM s Tivoli Storage Manager - formerly ADSM

Legato Networker

ManageIT Backup and Recovery

Sterling Software s SAMS:Alexandria - formerly from Spectralogic

Sun Solstice Backup

Why and when should one tune?

One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. One should do performance tuning for the following reasons:

The speed of computing might be wasting valuable human time (users waiting for response); Enable your system to keep-up with the speed business is conducted; and Optimize hardware usage to save money (companies are spending millions on hardware). Although this FAQ is not overly concerned with hardware issues, one needs to remember than you cannot tune a Buick into a Ferrari.

What database aspects should be monitored?

One should implement a monitoring system to constantly monitor the following aspects of a database. Writing custom scripts,  implementing Oracle s Enterprise Manager, or buying a third-party monitoring product can achieve this. If an alarm is triggered, the system should automatically notify the DBA (e-mail, page, etc.) to take appropriate action.

Infrastructure availability:

. Is the database up and responding to requests

. Are the listeners up and responding to requests

. Are the Oracle Names and LDAP Servers up and responding to requests

. Are the Web Listeners up and responding to requests

Things that can cause service outages:

. Is the archive log destination filling up?

. Objects getting close to their max extents

. User and process limits reached

Things that can cause bad performance:

See question “What tuning indicators can one use?”.

Where should the tuning effort be directed?

Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.

 

Database Design (if it s not too late):

Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the “data access path” in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

 

Application Tuning: Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

Memory Tuning: Properly size your database buffers (shared pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.

 

Disk I/O Tuning:

Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc

 

Eliminate Database Contention:

Study database locks, latches and wait events carefully and eliminate where possible.

Tune the  Operating System: Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.

Does one need to drop/ truncate objects before importing?

Before one import rows into already populated tables, one needs to truncate or drop these tables to get rid of the old data. If not, the new data will be appended to the existing tables. One must always DROP existing Sequences before re-importing. If the sequences are not dropped, they will generate numbers inconsistent with the rest of the database.

Note: It is also advisable to drop indexes before importing to speed up the import process. Indexes can easily be recreated after the data was successfully imported.

Can one import/export between different versions of Oracle?

Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next.Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow

the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

How can one improve Import/ Export performance?

EXPORT:

Set the BUFFER parameter to a high value (e.g. 2M)

Set the RECORDLENGTH parameter to a high value (e.g. 64K)

Stop unnecessary applications to free-up resources for your job.

If you run multiple export sessions, ensure they write to different physical disks.

DO NOT export to an NFS mounted filesystem. It will take forever.

 

IMPORT:

Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.

Place the file to be imported on a separate physical disk from the oracle data files

Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file

Set the LOG_BUFFER to a big value and restart oracle.

Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)

Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)

Use COMMIT=N in the import parameter file if you can afford it

Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements

Remember to run the indexfile previously created

What are the common Import/ Export problems?

ORA-00001: Unique constraint (…) violated - You are importing duplicate rows. Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).

ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO

ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y while importing

IMP-00015: Statement failed … object already exists… - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

My database is down and I cannot restore. What now?

Recovery without any backup is normally not supported, however, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the

data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!!!

I ve lost my REDOLOG files, how can I get my DB back?

The following INIT.ORA parameter may be required if your current redo logs are corrupted or blown away. Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.

_allow_resetlogs_corruption = true

I ve lost some Rollback Segments, how can I get my DB back?

Re-start your database with the following INIT.ORA parameter if one of your rollback segments is corrupted. You can then drop the corrupted rollback segments and create it from scratch.

Caution is advised when enabling this parameter, as uncommitted transactions will be marked as committed. One can very well end up with lost or inconsistent data!!! Please contact Oracle Support before using it. _Corrupted_rollback_segments =(rbs01, rbs01, rbs03, rbs04)

What are the differences between EBU and RMAN?

Enterprise Backup Utility (EBU) is a functionally rich, high performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility. The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7 s EBU utility. However, there is no direct upgrade path from EBU to RMAN.

How does one create a RMAN recovery catalog?

Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role.

Look at this example:

sqlplus sys

SQL>create user rman identified by rman;

SQL> alter user rman default tablespace tools temporary tablespace temp;

SQL> alter user rman quota unlimited on tools;

SQL> grant connect, resource, recovery_catalog_owner to rman;

SQL> exit;

Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script. rman

 

catalog rman/rman

RMAN>create catalog tablespace tools;

RMAN> exit;

You can now continue by registering your databases in the catalog. Look at this example:

rman catalog rman/rman target backdba/backdba

RMAN> register database;

What is the difference between locks, latches, enqueues and semaphores?

A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET is used to implement latches. Latches are more restrictive than locks in that they are always

exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out. Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.

Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix

parameters: semmni, semmns and semmsl. Typical settings are:

semmns = sum of the “processes” parameter for each instance

(see init.ora for each instance)

semmni = number of instances running simultaneously;

semmsl = semmns

What is a database EVENT and how does one set it?

Oracle trace events are useful for debugging the Oracle database server. The following two examples are simply to demonstrate

syntax. Refer to later notes on this page for an explanation of what these particular events do.

Either adding them to the INIT.ORA parameter file can activate events. E.g.

event= 1401 trace name errorstack, level 12′

… or, by issuing an ALTER SESSION SET EVENTS command: E.g.

alter session set events  10046 trace name context forever, level 4′;

The alter session method only affects the user s current session, whereas changes to the INIT.ORA file will affect all sessions once the database has been restarted.

What database events can be set?

The following events are frequently used by DBAs and Oracle Support to diagnose problems:

” 10046 trace name context forever, level 4 Trace SQL statements and show bind variables in trace output.

” 10046 trace name context forever, level 8 This shows wait events in the SQL trace files

” 10046 trace name context forever, level 12 This shows both bind variable names and wait events in the SQL trace files

” 1401 trace name errorstack, level 12 1401 trace name errorstack, level 4 1401 trace name processstate Dumps out trace

information if an ORA-1401 “inserted value too large for column” error occurs. The 1401 can be replaced by any other Oracle

Server error code that you want to trace.

” 60 trace name errorstack level 10 Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose the problem.

The following lists of events are examples only. They might be version specific, so please call Oracle before using them:

” 10210 trace name context forever, level 10 10211 trace name context forever, level 10 10231 trace name context forever, level 10 These events prevent database block corruptions

” 10049 trace name context forever, level 2 Memory protect cursor

” 10210 trace name context forever, level 2 Data block check

” 10211 trace name context forever, level 2 Index block check

” 10235 trace name context forever, level 1 Memory heap check

” 10262 trace name context forever, level 300 Allow 300 bytes memory leak for connections

Note: You can use the Unix oerr command to get the description of an event. On Unix, you can type “oerr ora 10053″ from the

command prompt to get event details.

How can one dump internal database structures?

The following (mostly undocumented) commands can be used to obtain information about internal database structures.

Dump control file contents

alter session set events  immediate trace name CONTROLF level 10′

/

Dump file headers

alter session set events  immediate trace name FILE_HDRS level 10′

/

Dump redo log headers

alter session set events  immediate trace name REDOHDR level 10′

/

Dump the system state

NOTE: Take 3 successive SYSTEMSTATE dumps, with 10-minute intervals alter session set events  immediate trace name

SYSTEMSTATE level 10′

/

Dump the process state

alter session set events  immediate trace name PROCESSSTATE level 10′

/

Dump Library Cache details

alter session set events  immediate trace name library cache level 10′

/

Dump optimizer statistics whenever a SQL statement is parsed (hint: change statement or flush pool) alter session set events  10053 trace name context forever, level 1′

/

Dump a database block (File/ Block must be converted to DBA address) Convert file and block number to a DBA (database block address).

Eg: variable x varchar2;

exec :x := dbms_utility.make_data_block_address(1,12);

print x

alter session set events  immediate trace name blockdump level 50360894′

/

When is cost based optimization triggered?

It s important to have statistics on all tables for the CBO (Cost Based Optimizer) to work correctly. If one table involved  in a statement does not have statistics, Oracle has to revert to rule-based optimization for that statement. So you really  want for all tables to have statistics right away; it won t help much to just have the larger tables analyzed.

Generally, the CBO can change the execution plan when you:

1. Change statistics of objects by doing an ANALYZE;

2. Change some initialization parameters (for example: hash_join_enabled, sort_area_size, db_file_multiblock_read_count).

How can one optimize %XYZ% queries?

It is possible to improve %XYZ% queries by forcing the optimizer to scan all the entries from the index instead of the table. This can be done by specifying hints. If the index is physically smaller than the table (which is usually the case) it will take less time to scan the entire index than to scan the entire table.

Where can one find I/O statistics per table?

The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O. The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and table to gather the required information. After executing the procedure, one can do a simple SELECT * FROM io_per_object; to extract the required information. For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.

My query was fine last week and now it is slow. Why?

The likely cause of this is because the execution plan has changed. Generate a current explain plan of the offending query and compare it to a previous one that was taken when the query was performing well. Usually the previous plan is not available.

Some factors that can cause a plan to change are:

.hich tables are currently analyzed? Were they previously analyzed? (ie. Was the query using RBO and now CBO?)

Has OPTIMIZER_MODE been changed in INIT.ORA?

. Has the DEGREE of parallelism been defined/changed on any table?

. Have the tables been re-analyzed? Were the tables analyzed using estimate or compute? If estimate, what percentage was used?

. Have the statistics changed?

. Has the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?

. Has the INIT.ORA parameter SORT_AREA_SIZE been changed?

. Have any other INIT.ORA parameters been changed?

. What do you think the plan should be? Run the query with hints to see if this produces the required performance.

What is a view?

Why is Oracle not using the damn index?

This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is  because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index.

Fundamental things that can be checked are:

. USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.

. USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby is making the index less desirable.

. USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

. Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.

. Remember that you MUST supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).

. There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or

with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

When should one rebuild an index?

You can run the  ANALYZE INDEX VALIDATE STRUCTURE  command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The  badness  of the index can then be judged by the ratio of  DEL_LF_ROWS  to  LF_ROWS .

What is Oracle Financials?

Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:

Oracle Assets: Ensures that an organization s property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.

Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial  reporting needs.

Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.

Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.

Oracle Payables: Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.

Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.

Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.

Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research.

Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.

Oracle Revenue Accounting Gives an organization timely and accurate revenue and flexible commissions reporting.

Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What is the most important module in Oracle Financials?

The General Ledger (GL) module is the basis for all other Oracle Financial modules. All other modules provide information to it. If you implement Oracle Financials, you should switch your current GL system first.GL is relatively easy to implement. You should go live with it first to give your implementation team a chance to be familiar with Oracle Financials.

What is the MultiOrg and what is it used for?

MultiOrg or Multiple Organizations Architecture allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications. This keeps each operating unit s transaction data separate and secure.

Use the following query to determine if MuliOrg is intalled:

select multi_org_flag from fnd_product_groups;

What is the difference between Fields and FlexFields?

A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.

A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values.

Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key

flexfields and descriptive flexfields.

What is Fine Grained Auditing?

Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL. Look at this

example:

o Add policy on table with autiting condition…

execute dbms_fga.add_policy( HR ,  EMP ,  policy1′,  deptno > 10′);

o Must ANALYZE, this feature works with CBO (Cost Based Optimizer)

analyze table EMP compute statistics;

select * from EMP where c1 = 11; — Will trigger auditing

select * from EMP where c1 = 09; — No auditing

o Now we can see the statments that triggered the auditing condition…

select sqltext from sys.fga_log$;

delete from sys.fga_log$;

What is a Virtual Private Database?

Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies  data within the same schema, without them knowing about it. VPD configuration is done

via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.

What is Oracle Label Security?

Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle8i to implement row level security. Access to rows are restricted according to a user s security sensitivity tag or label.

Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

What is OEM (Oracle Enterprise Manager)?

OEM is a set of systems management tools provided by Oracle Corporation for managing the Oracle environment. It provides tools to monitor the Oracle environment and automate tasks (both one-time and repetitive in nature) to take database

administration a step closer to “Lights Out” management.

What are the components of OEM?

Oracle Enterprise Manager (OEM) has the following components:

. Management Server (OMS): Middle tier server that handles communication with the intelligent agents. The OEM Console connects to the management server to monitor and configure the Oracle enterprise.

. Console: This is a graphical interface from where one can schedule jobs, events, and monitor the database. The console can be opened from a Windows workstation, Unix XTerm (oemapp command) or Web browser session (oem_webstage).

. Intelligent Agent (OIA): The OIA runs on the target database and takes care of the execution of jobs and events scheduled through the Console.

How does one stop and start the OMS?

Use the following command sequence to stop and start the OMS (Oracle Management Server):

oemctl start oms

oemctl status oms sysman/oem_temp

oemctl stop oms sysman/oem_temp

Windows NT/2000 users can just stop and start the required services. The default OEM administrator is “sysman” with a password of “oem_temp”.

NOTE: Use command oemctrl instead of oemctl for Oracle 8i and below.

How does one create a repository?

For OEM v2 and above, start the Oracle Enterprise Manager Configuration Assistant (emca on Unix) to create and configure the management server and repository. Remember to setup a backup for the repository database after creating it.

If a View on a single base table is manipulated will the changes be reflected on the base table ?

If changes are made to the tables which are base tables of a view will the changes be reference on the view.

The following describes means to create a OEM V1.x (very old!!!) repository on WindowsNT:

. Create a tablespace that would hold the repository data. A size between 200- 250 MB would be ideal. Let us call it

Dummy_Space.

. Create an Oracle user who would own this repository. Assign DBA, SNMPAgent, Exp_Full_database, Imp_Full_database roles to this user. Lets call this user Dummy_user. Assign Dummy_Space as the default tablespace.

. Create an operating system user with the same name as the Oracle username. I.e. Dummy_User. Add  Log on as a batch job

under advanced rights in User manager.

. Fire up Enterprise manager and log in as Dummy_User and enter the password. This would trigger the creation of the

repository. From now on, Enterprise manager is ready to accept jobs.

How does one list one s databases in the OEM Console?

Follow these steps to discover databases and o