Oracle sql Level-3 questions.
QUES-1: What are the steps involved in database replay.
a.) play,capture
b.) replay,play
c.) capture, replay
d.) capture, play
ans. is c) capture,replay(sure)
QUES-2: What are the directories used in database replay
ans. is capture directory, replay directory (this should in be the sequence) ye bhi shi kiya tha
QUES-3: Which statement is true.
a.)Database Replay applies to the capture and replay of all (subject to some filtering) activities in a
database,
SQL Performance Analyzer allows you to capture specific SQL statements and replay them.
b.)Database Replay applies to the capture and replay of all (subject to some filtering) activities in a
database,
SQL Performance Analyzer allows you to capture all SQL statements and replay them. (Ye wala
kiya tha maine but ye galt tha)
c.)Database Replay applies to the capture and replay of all (subject to some filtering) activities in a
database,
SQL Performance Analyzer allows you to capture any SQL statements and replay them.
ans. is a)Database Replay applies to the capture and replay of all (subject to some filtering) activities
in a database,
SQL Performance Analyzer allows you to capture specific SQL statements and replay them.
QUEST-4: Pivot and UNPivot is extension of which statement.
a.) SELECT
b.) Groub by
c.) Having( I did this)
d.) Where
ans. is a.) SELECT
QUES:-5 Which option is set for DDL wait option.
a.) DDL_wait(I did this)
b.) DDL_wait_lock
c.) DDL_lock_timeout
d.) dont remember
ans. is c.) DDL_lock_timeout
QUES:6 About invisible index in oracle 11g?
a.)Ignored by user
b.)Ignored by optimizer( I did this)
c.)Ignored by both user and optimizer
d.)not ignored by anyone.
ans. is also b.)Ignored by optimizer( I did this)
QUEST:-7 How do you set the optimizer to make use of invisible indexes?
a.) Alter table set index_name invisible
b.) Optimizer= set index index_name invisible( I did this)
c.) Alter index index_name invisible;
d.) Alter optimizer set index index name invisible
ans. is c.) Alter index index_name invisible;
QUES:-8 In Oracle 11g, which syntax makes an index 1X_1 invisible.
ans. is OPTIMIZER_USE_INVISIBLE_INDEXES
QUES:-9 What are the 3 components of SQL Plan management.
a.)SQL plan baseline capture, SQL plan baseline selection, SQL plan baseline evolution
b.)SQL plan baseline capture, SQL plan baseline selection, SQL plan baseline evalution(ye option
mere mai bold hoke aaya but galt tha aur maine yhi option tick kardiya)
c.)yaad ni option
d.) option yad ni
ans. is a.)SQL plan baseline capture, SQL plan baseline selection, SQL plan baseline evolution
QUES:-10 Native compilation is faster in oracle 11g than oracle 10g.
a.) True( I did this)
b.) False
ans. is a.) True( I did this)
QUES:- 11 How do you create invisible index?
a.) create(ans.)
b.) alter
c.)
d.)
QUES:12- SQL Statement is valid in DDL wait option in 11g?
a.)ALTER SESSION SET ddl_lock_timeout=10
b.)ALTER Database SET ddl_lock_timeout=10
c.) kuch kuch tha
d.) kuch kuch tha
ans. a.)ALTER SESSION SET ddl_lock_timeout=10
QUES:-13- Which statement is new in oracle 11g.
a.) Continue( I did this)
b.) next
c.) Do nothing
d.) None of the above
ans. is a.) Continue( I did this)
QUES:-14 Which is new datatype in plsql in oracle 11g
a.) Pls_integer( I did this)
b.) simple_integer
c.) kuch kuch
d.) kuch kuch
ans. is b.) simple_integer
QUES:-15 In 12c, which of the following are used for active and inactive options.
a.)0,1
b.)1,0
c.)
d.)
ans. is 0 means active and non-zero denotes as inactive
Ques->16 Check whether Table Trans is read only or not?
a.) Select read_only from user_tables where table_name= 'trans'
b.) Select read_only from user_objects where table_name= 'trans'
c.) Select table_status from user_tables where table_name= 'trans'
d.) None of the above
ans. is a.) Select read_only from user_tables where table_name= 'trans'
Ques->17 Is 11g introduced Read only tables?
a.) Yes
b.) No
ans. is a.) Yes
Ques->18 Do 11g provides DDL wait option?
a.) Yes
b.)No
ans. a.) Yes
Ques->19 Which is true about invisible index in 11g?
a.) Invisible to user only
b.) invisible to optimizer only
c.) invisible to user& optimizer
d.) None
ans. is b.) invisible to optimizer only
Ques->20 What does happend when below statement is issued.
Alter session set DDL_LOCK_TIMEOUT=10;
a.) DDL WAIT time is set to 10 seconds
b.) DDL WAIT time is set to 10 milliseconds
c.) Locked for 10 seconds
d.) Locked for 10 millseconds
ans. is a.) DDL WAIT time is set to 10 seconds
Ques->21 In Oracle 11g where do you use PLSQL simple_integer datatype?
a.) When NO need of storing null values in Plsql variable. for reference:
(nullness)
b.) No possibility of accidentally going over max value or going under min value. for
reference:(overflow)
c.) Both
d.) None
ans. is a.) When NO need of storing null values in Plsql variable.
Ques->22 Do 11g Create index on virtual columns.
a.) Yes
b.) No
c.) Only when virtual column does not have a default value
d.) Only when virtual column does have a default value
ans. is a.) Yes
Ques->23 In 11g can you create virtual column on table that convey business sense without adding
any complexity or per impact.
Himani did not wrote the options for this quest.( But yes, we can create virtual column on table)
but answer should be from this below para.
============
SQL> create table sales
2 (
3 sales_id number,
4 cust_id number,
5 sales_amt number,
6 sale_category varchar2(6)
7 generated always as
8 (
9 case
10 when sales_amt <= 10000 then 'LOW'
11 when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
12 when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
13 else 'ULTRA'
14 end
15 ) virtual
16 );
Note lines 6-7; the column is specified as "generated always as",
meaning the column values are generated at runtime, not stored as part of the table.
That clause is followed by how the value is calculated in the elaborate CASE statement.
Finally, in line 15,"virtual" is specified to reinforce the fact that this is a virtual column. Now, if you
insert some records:
===============
Ques->24 How PLSQL simple integer different PLS_int in oracle 11g.
a.) Simple_Int does not allow null
b.) Simple does allow overflow
c.) ALL
d.) None
ans is. c.) ALL
Note for this ques in documention:-It does not allow nulls, but does allow overflow
Ques->25 New archiving feature of 12c.
a.) IN database archiving
b.) IN table archiving
c.) IN Row archiving
d.) IN Column archiving
ans. is c.) IN Row archiving
Ques->26 Is it possible to insert predefined value to virtual column?
a.) Yes
b.) No
ans. is a.) Yes
Ques->27 What is true for virtual column?
a.) Virtual column can be created on tables
b.) Virtual column can be created on Views
c.) Both
d.) Virtual column implemented using compound triggers
ans. is a.) Virtual column can be created on tables(not sure but 90% I am :-P)
Ques-> 29 What is simple_integer of PLs integer?
a.) superset
b.)subtype
c.) kuch kuch :-P
d.) kuch kuch :-P
ans is a.) is subtype
Ques-> 30 Version of Oracle DB has compound triggers?
a.)Oracle 10g R2 and above
b.) Oracle 11g R1
c.) Oracle 10g R1 AND ABOVE
d.) All
ans. is b.) Oracle 11g R1
Ques-> 31 SQL> Alter table Trans read read only;
SQL> delete trans
SQL> what happens when the above queries execute.
a.) Both statement executed successfully.
b.) 1st statement execute successfully and second throw error
c.) 1st statement throw error and second execute successfully
d.) Both error throw
ans is d.) Both error throw(according to me) correct ans is b)
(Note:When you want to make the table update-able, you will need to make it read/write as shown
below:
alter table trans read write;)
Ques-> 32 Consider a table called TRANS, created as:
create table trans
(
trans_id number(10),
trans_amt number(12,2),
store_id number(2),
trans_type varchar2(1)
)
create or replace view vw_trans
as
select trans_id, trans_amt
from
What will be the status of the view VW_TRANS.
a.)invalid
b.)valid
ans. is b.)valid
Ques-> 33 What is the significance of continue statement in ORACEL 11g?
ans. is The CONTINUE statement exits the current iteration of a loop, either conditionally or
unconditionally,
and transfers control to the next iteration of either the current loop or an enclosing labeled loop.
(mujhe options yaad ni to yhi pdhlo isi mai se tha)
******************************************************
===========================
Lateral Views
===========================
• Objective:
select acc_desc, interest
from accounts a,
(select * from account_types t where
t.acctype = a.acctype);
• Will fail with
• ORA-00904: "A"."ACCTYPE": invalid
identifier
select acc_desc, interest
from accounts a,
lateral (select * from account_types t
where a.acctype = t.acctype)
/
********************************************************
