Oracle Interview Questions 17

• What is Functional Dependency

Answer: Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if

each X-value has associated with it precisely one -Y value in R

• What is Auditing ?

Answer: The database has the ability to audit all actions that take place within it. a) Login attempts, b)

Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL

• While designing in client/server what are the 2 imp.things to be considered ?

Answer: Network Overhead (traffic), Speed and Load of client server

• What are the disadvantages of SQL ?

Answer: Disadvantages of SQL are :

 Cannot drop a field

Cannot rename a field

Cannot manage memory

Procedural Language option not provided

Index on view or index on index not provided

 View updation problem

• When to create indexes ?

Answer: To be created when table is queried for less than 2% or 4% to 25% of the table rows.

• How can you avoid indexes ?

Answer: To make index access path unavailable

 Use FULL hint to optimizer for full table scan

Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another.

 Use an expression in the Where Clause of the SQL.

• What is the result of the following SQL :

Select 1 from dual UNION Select 'A' from dual;

Answer: Error

• Can database trigger written on synonym of a table and if it can be then what would be

the effect if original table is accessed.

Answer: Yes, database trigger would fire.

• Can you alter synonym of view or view ?

Answer: No

• Can you create index on view

Answer: No.

• What is the difference between a view and a synonym ?

Answer: Synonym is just a second name of table used for multiple link of database.View can be created

with many tables, and with virtual columns and with conditions.But synonym can be on view.

• What's the length of SQL integer ?

Answer: 32 bit length

• What is the difference between foreign key and reference key ?

Answer: Foreign key is the key i.e.attribute which refers to another table primary key. Reference key is

the primary key of table referred by another table.

• Can dual table be deleted, dropped or altered or updated or inserted ?

Answer: Yes

• If content of dual is updated to some value computation takes place or not ?

Answer: Yes

• If any other table same as dual is created would it act similar to dual?

Answer: Yes

• For which relational operators in where clause, index is not used ?

Answer: <> , like '%...' is NOT functions, field +constant, field||''

• .Assume that there are multiple databases running on one machine.How can you switch

from one to another ?

Answer: Changing the ORACLE_SID

• What are the advantages of Oracle ?

Answer: Portability : Oracle is ported to more platforms than any of its competitors, running on more

than 100 hardware platforms and 20 networking protocols. Market Presence : Oracle is by far the

largest RDBMS vendor and spends more on R & D than most of its competitors earn in total

revenue.This market clout means that you are unlikely to be left in the lurch by Oracle and there are

always lots of third party interfaces available. Backup and Recovery : Oracle provides industrial

strength support for on-line backup and recovery and good software fault tolerence to disk failure.You

can also do point-in-time recovery. Performance : Speed of a 'tuned' Oracle Database and application is

quite good, even with large databases.Oracle can manage > 100GB databases. Multiple database

support : Oracle has a superior ability to manage multiple databases within the same transaction using a

two-phase commit protocol.

• What is a forward declaration ? What is its use ?

Answer: PL/SQL requires that you declare an identifier before using it.Therefore, you must declare a

subprogram before calling it.This declaration at the start of a subprogram is called forward

declaration.A forward declaration consists of a subprogram specification terminated by a semicolon.

• What are actual and formal parameters ?

Answer: Actual Parameters : Subprograms pass information using parameters.The variables or

expressions referenced in the parameter list of a subprogram call are actual parameters.For example,

the following procedure call lists two actual parameters named emp_num and amount:

Eg.raise_salary(emp_num, amount);

Formal Parameters : The variables declared in a subprogram specification and referenced in the

subprogram body are formal parameters.For example, the following procedure declares two formal

parameters named emp_id and increase:

Eg.PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;

• What are the types of Notation ?

Answer: Position, Named, Mixed and Restrictions.

• What all important parameters of the init.ora are supposed to be increased if you want to

increase the SGA size ?

Answer: In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500)

shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was

changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to

4096 (4K) {at the time of database creation}. The initial SGA was around 4MB when the server RAM

was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.

• .If I have an execute privilege on a procedure in another users schema, can I execute his

procedure even though I do not have privileges on the tables within the procedure ?

Answer: Yes

• What are various types of joins ?

Answer: Types of joins are:

 Equijoins

Non-equijoins

self join

 outer join

• What is a package cursor ?

Answer: A package cursor is a cursor which you declare in the package specification without an SQL

statement.The SQL statement for the cursor is attached dynamically at runtime from calling

procedures.

• If you insert a row in a table, then create another table and then say Rollback.In this case

will the row be inserted ?

Answer: Yes.Because Create table is a DDL which commits automatically as soon as it is executed.The

DDL commits the transaction even if the create statement fails internally (eg table already exists error)

and not syntactically.