|
ORACLE Interview Questions and Answers
Part:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
(Continued from previous part...)
GENERAL INTERVIEW QUESTIONS (5)
61.What is Auditing ?
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
62.While designing in client/server what are the 2 imp. things to be considered ?
Network Overhead (traffic), Speed and Load of client server
64.When to create indexes ?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
65.How can you avoid indexes ?
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.
66.What is the result of the following SQL :
Select 1 from dual
UNION
Select 'A' from dual;
Error
67.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.
Yes, database trigger would fire.
68.Can you alter synonym of view or view ?
No
69.Can you create index on view
No
70.What is the difference between a view and a synonym ?
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.
71.What is the difference between alias and synonym ?
Alias is temporary and used with one query. Synonym is permanent and not used as alias.
72.What is the effect of synonym and table name used in same Select statement ?
Valid
73.What's the length of SQL integer ?
32 bit length
74.What is the difference between foreign key and reference key ?
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.
75.Can dual table be deleted, dropped or altered or updated or inserted ?
Yes
76.If content of dual is updated to some value computation takes place or not ?
Yes
77.If any other table same as dual is created would it act similar to dual?
Yes
78.For which relational operators in where clause, index is not used ?
<> , like '% ...' is NOT functions, field +constant, field || ''
79.Assume that there are multiple databases running on one machine. How can you switch from one to another ?
Changing the ORACLE_SID
80.What are the advantages of Oracle ?
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.
81.What is a forward declaration ? What is its use ?
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.
82.What are actual and formal parameters ?
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;
(Continued on next part...)
Part:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
| 