Oracle Interview Questions and Answers
76. WHAT IS
A VIEW? HOW IT IS DIFFERENT FROM A TABLE?
Ans: View
is database object, which exists logically but contains no
physical
data and manipulates the base table.
View is
saved as a select statement in the database and contains no
physical
data whereas Table exists physically.
77. WHAT IS
DIFFERENCE BETWEEN SIMPLE AND COMPLEX VIEWS?
Ans: Simple
views can be modified whereas Complex views (created based
on more
than one table) cannot be modified.
78. WHAT IS
AN INLINE VIEW?
Ans: Inline
view is basically a subquery with an alias that u can use
like a view
inside a SQL statement. It is not a schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX VIEW?
Ans:
Using 'INSTEAD OF' TRIGGERS Complex
views can be Updated.
80. WHAT FOR "WITH CHECK OPTION" FOR
A VIEW?
Ans:
"WITH CHECK OPTION" clause specifies that inserts and updates r
performed through the view r not allowed to create rows which the view cannot select and therefore
allows integrity constraints and data validation checks to be enforced on data
being inserted or updated.
81. WHAT IS
AN INDEX? ADVANTAGE OF AN INDEX?
Ans: An Index
is a database object used n Oracle to provide quick
access to
rows in a table. An Index increases the performance of the database.
82. WHAT IS
A SEQUENCE? PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans:
Sequence is a Database Object used to generate unique integers to use as primary keys. Nextval, Currval are
the Pseudo Columns associated with the sequence.
83. WHAT IS
A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER
WHEN
CLUSTERED TABLE EXISTS?
Ans:
Cluster and Indexes are transparent to the user. Clustering is a
method of
storing tables that are intimately related and are often joined together into
the same area on the disk.
When
cluster table exists then to drop cluster we have to drop the table first then
only cluster is to be dropped.
84. WHAT IS
A SNAPSHOT OR MATERIALIZED VIEW?
Ans:
Materialized views can be used to replicate data. Earlier the data
was
replicated through CREATE SNAPSHOT command. Now CREATE MATERIALIZED VIEW can be
used as synonym for CREATE SNAPSHOT. Query performance is improved using the
materialized view as these views pre calculate expensive joins and aggregate
operations on the table.
85. WHAT IS
A SYNONYM?
Ans: A Synonym is a database object that allows
you to create alternate names for Oracle tables and views. It is an alias for a
table, view, snapshot, sequence, procedure, function or
package.
86. WHAT IS
DIFFERENCE BETWEEN PRIVATE AND PUBLIC SYNONYM?
Ans: Only
the user or table owner can reference Private synonym whereas
any user
can reference the Public synonym.
87. WHAT IS
DIFFERENCE BETWEEN "SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL commands are stored in the buffer whereas
SQL*PLUS are not.
88. NAME
SOME SQL*PLUS COMMANDS?
Ans: DESC [CRIBE], START, GET, SAVE, / are
SQL*PLUS COMMANDS.
89. WHAT
ARE "SQL*PLUS REPORTING" COMMANDS?
Ans: SPOOL
file-name, SPOOL OFF, TTITLE, BTITLE, BREAK ON, COMPUTE <any
aggregate
function> OF <column name> [break] ON <column name> etc are
SQL*PLUS REPORTING COMMANDS.
90. WHAT
ARE SYSTEM AND OBJECT PRIVILEGES?
Ans:
Connect and Resource etc are System Privileges.
Create
<object>, Select, Insert, Alter etc are Object Privileges.
91. WHAT
FOR DCL COMMANDS ARE?
Ans:
Commit, Rollback are DCL commands.
92. WHAT
FOR GRANT COMMAND WITH "WITH GRANT OPTION"?
Ans: “With
Grant Option” with Grant Command gives privileges to the
user to
grant privileges to other user(s)
among the
privileges he/she has.
93. HOW TO
CHANGE PASSWORD OF A USER?
Ans: Using
Password command or
Using ALTER
USER <user name> IDENTIFIED BY <new password> COMAND.
94. WHAT IS
A SCHEMA AND SCHEMA OBJECTS?
Ans: A
schema is a collection of logical structures of data, or schema objects.
A
schema is owned by the database user and has the same name as that of user.
Each user owns a single schema. Schema objects
include following
type of objects Clusters, Database
Links, Functions, Indexes, Packages,Procedures, Sequences, Synonyms, Tables, Database
Triggers, Views.
95. HOW TO
STARTUP AND SHUTDOWN ORACLE DATABASE?
Ans:
Startup and Shutdown Oracle database can be done by only the
administator.
Startup is done by using STARTUP command and Shutdown is done by SHUTDOWN
command
96. WHAT IS
A SESSION?
Ans: The
period between Login and Logoff on schema.
97. WHAT IS
A CLIENT PROCESS? WHAT IS A SERVER PROCESS?
Ans: ref:
172 Q & A.
98. HOW TO
MAKE EVERY DML OPERATION AS AUTO COMMIT?
Ans: By
using SET AUTOCOMMIT ON
99. HOW TO
DISPLAY DATA PAGE WISE IN SQL?
Ans: By
using SET PAUSE ON command.
100. HOW TO
CHANGE LINE SIZE, PAGE SIZE AND SQL PROMPT?
Ans: By using
SET LINESIZE <value>,
SET
PAGESIZE <value>,
SET
SQLPROMPT <new prompt>.
.jpg)
0 comments:
Post a Comment