Select Category 
 

Oracle Interview Questions Answers

Oracle Interview Question - 1 : -

Is the After report trigger fired if the report execution fails?

Oracle Interview Answer - 1 : -

Yes.
 

Oracle Interview Question - 2 : -

What is Oracle table?

Oracle Interview Answer - 2 : -

A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.
 

Oracle Interview Question - 3 : -

How does one create a password file?

Oracle Interview Answer - 3 : -

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!
 

Oracle Interview Question - 4 : -

From which designation is it preferred to send the output to the printed?

Oracle Interview Answer - 4 : -

Previewer
 

Oracle Interview Question - 5 : -

What are the difference between lov & list item?

Oracle Interview Answer - 5 : -

Lov is a property where as list item is an item. A list item can have only one column, lov can have one or more columns.
 

Oracle Interview Question - 6 : -

What are synonyms used for?

Oracle Interview Answer - 6 : -

- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.
 

Oracle Interview Question - 7 : -

What is strip sources generate options?

Oracle Interview Answer - 7 : -

Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final deployment, but can not be subsequently edited in the designer.ex. f45gen module=old_lib.pll userid=scott/tiger strip_source YES output_file
 

Oracle Interview Question - 8 : -

What is an Index Segment ?

Oracle Interview Answer - 8 : -

Each Index has an Index segment that stores all of its data.
 

Oracle Interview Question - 9 : -

What is mean by Program Global Area (PGA)?

Oracle Interview Answer - 9 : -

It is area in memory that is used by a single Oracle user process.
 

Oracle Interview Question - 10 : -

What is a Schema ?

Oracle Interview Answer - 10 : -

The set of objects owned by user account is called the schema.
 

Oracle Interview Question - 11 : -

What is system.coordination_operation?

Oracle Interview Answer - 11 : -

It represents the coordination causing event that occur on the master block in master-detail relation.
 

Oracle Interview Question - 12 : -

What are the options available to refresh snapshots ?

Oracle Interview Answer - 12 : -

COMPLETE - Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.
FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.
FORCE - Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.
 

Oracle Interview Question - 13 : -

What is a shared pool?

Oracle Interview Answer - 13 : -

The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.
 

Oracle Interview Question - 14 : -

How are extents allocated to a segment? (for DBA)

Oracle Interview Answer - 14 : -

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.
 

Oracle Interview Question - 15 : -

How does one coalesce free space ? (for DBA)

Oracle Interview Answer - 15 : -

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.