ORACLE DBA SQL*Plus Questions

1. What are the advantages and disadvantages, compared to the standard SQL and SQL*Plus?

2. Read the exercise. This block has not any compilation errors, sometimes it can have run time errors. How can you trap the errors and how can I force to execute the everytimes the line B := 10 ; ? Declare
A number ;
B number ;
begin
--
select Col
into A
from tab
where col2 = 'Constant' ;
--
B := 10 ;
--
...
end ;

3. How to display the loop counter at the screen in a PL/SQL loop?

4. How can I display a LONG column? What are the maximum value, how to bypass then?

5. What is the first information displayed when you connect in sqlplus between sqlplus scott/tiger@db
and SQL>

6. If you execute the following commands, why in the same session have I different answer?
select 'count=' || to_char ( count ( 1 ) ) from tab where Col = 1 ;
count=100
select 'count=' || to_char ( count ( 1 ) ) from tab where Col = 2 ;
count=110
select 'count=' || to_char ( count ( 1 ) ) from tab ;
count=99

7. How can I have consistency and what are the locks ?

8. Explain the following term by a sentence
Initrans
pctincrease
ORACLE_SID

9. What are the differences betwen tkprof and explain plan?

10. What is an instance and what is a data base ?

11. When start and end a transaction ?

12. What are the database control command language? Give examples.

13. What is a view what are the problems and the benefits of it?

14. What happens in this example?
Create table a ( a number ) ;
Create view V_A as select * from a ;
Alter table a add ( b number ) ;
Alter table a modify ( a not null ) ;
select * from from V_A ;

15. What is a database link what are the problems and the benefits of it ?
select T1.col1 , T2.col2
from TAB1 T1 , TAB2@link2 T2
where T1.col1 = T2.col1
order by T2.col1 ;

16. Look at the queries, in rules base mode does Oracle use the index in the following case?
select .... from tab1 where colonne * 13.85 > 100000 /* column NUMBER */ ;
select .... from tab1 where colonne not > 10000 /* column NUMBER */ ;
select .... from tab1 where colonne = 123 /* column CHAR */ ;
select .... from tab1 where trunc ( colonne ) = trunc ( sysdate ) /* column date */ ;
select .... from tab1 where rowid = '000001D3.0000.0001' ;
select .... from tab1 where substr ( colonne , 1 , 5 ) = 'MAIN:' /* column CHAR */ ;
select .... from tab1 where colonne not in ( select colonne from tab1 where col2 = 123 ) ; /* col2
number */ ;