oracle interview questions part 6

51 When to create indexes ?

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

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

53 What is the result of the following SQL :
Select 1 from dual UNION Select 'A' from dual;

Answer: Error

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

55  Can you alter synonym of view or view ?

Answer: No

56   Can you create index on view

Answer: No.

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

58  What's the length of SQL integer ?

Answer: 32 bit length

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

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

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

Answer: Yes

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

Answer: Yes

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

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

64 .Assume that there are multiple databases running on one machine.How can you switch from one to another ?

Answer: Changing the ORACLE_SID

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

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

67 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 followingprocedure declares two formal parameters named emp_id and increase:
Eg.PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;

68  What are the types of Notation ?

Answer: Position, Named, Mixed and Restrictions.

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

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

71  What are various types of joins ?

Answer: Types of joins are:

• Equijoins
• Non-equijoins
• self join
• outer join

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

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

74 What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

What are the components of logical database structure of Oracle database?
There are tablespaces and database's schema objects.

75 What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

 76 What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

77 Explain the relationship among database, tablespace and data file ?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

78 What is schema?
A schema is collection of database objects of a user.

79 What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

80  Can objects of the same schema reside in different tablespaces?
Yes.

81 Can a tablespace hold objects from different schemes?
Yes.

82 What is Oracle table?
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.

83 What is an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

84 What is Partial Backup ?
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

85 What is Mirrored on-line Redo Log ?

A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

86 What is Full Backup ?
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.

87 Can a View based on another View ?
Yes.

88 Can a Tablespace hold objects from different Schemes ?
Yes.

89 Can objects of the same Schema reside in different tablespace ?
Yes.

90 What is the use of Control File ?
When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

91 Do View contain Data ?
Views do not contain or store data.

92 What are the Referential actions supported by FOREIGN KEY integrity constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

93 What are the type of Synonyms?
There are two types of Synonyms Private and Public.

 94 What is a Redo Log ?
The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

 95 What is an Index Segment ?
Each Index has an Index segment that stores all of its data.

96 Explain the relationship among Database, Tablespace and Data file?
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

97 What are the different type of Segments ?
Data Segment, Index Segment, Rollback Segment and Temporary Segment.

98 What are Clusters ?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

99 What is an Integrity Constrains ?
An integrity constraint is a declarative way to define a business rule for a column of a table.

100 What is an Index ?
An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

101What is an Extent ?
An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

102 What is a View ?
A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

103 What is Table ?
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.

104 Can a view based on another view?
Yes.

105 What are the advantages of views?
- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.

106 What is an Oracle sequence?
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

107 What is a synonym?
A synonym is an alias for a table, view, sequence or program unit.

108 What are the types of synonyms?
There are two types of synonyms private and public.

109 What is a private synonym?
Only its owner can access a private synonym.

110 What is a public synonym?
Any database user can access a public synonym.

111 What are synonyms used for?
- 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.

112 What is an Oracle index?
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

 113 How are the index updates?
Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

114 What is a Tablespace?
A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together

115 What is Rollback Segment ?
A Database contains one or more Rollback Segments to temporarily store "undo" information.

116 What are the Characteristics of Data Files ?
A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.