Re: Interview Question

From: Your Name <Your_Name_at_pt.lu>
Date: 1997/06/17
Message-ID: <01bc7b59$8553d1e0$96d79ac2@LangeFrancois>

Hi,

        Sorry for the following bloody English, you will read, I haven't got an Oxford English.

        Here you can find question ask to many contractors at a Bank in Luxembourg.

	Some question like:
		what is displayed when you connect to sqlplus ?
	can appear a little bit stupid but if you read the book you will read it

one time, if you used it you will see it many times.

        The question about the count and read consistancy, come from the fact I met a EXPERT DBA on windows 95, where there isn't any concurrent access !

        Read it and mail to the group any comments and a GOOD ENGLISH TRANSLATION.

Thanks.

PL-SQL: Advantages and disadvantages, compared to the sqlplus.

Read the exercise:

Declare

	A number ;
	B number ;
begin


	--

	select Col
	into A
	from tab
	where col2 = 'Constant' ;


	--

	B := 10 ;


	--


	...

end ;

This block has not any compilation errors, sometimes it can have run time errors. How can I trap the errors and how can I force to execute the every times the line B := 10 ;

How to display the loop counter at the screen in a pl-sql loop?

SQL-Plus :

How can I display a long ? What are the maximum value, how to bypass then ?

What is the first information displayed when you connect in sqlplus between

sqlplus scott/tiger_at_db

and

SQL> ?

The following things happens:

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

Why in the same session have I different answer ?

How can I have consistency and what are the locks ?

Explain the following term by a sentence

	Initrans
	pctincrease
	ORACLE_SID
	P:
	tkprof and explain plan what are the differences ?
	what is an instance and what is a data base ?

When start and end a transaction ?

        What are the control command ?

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

	what happens ?
		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 ;

What is a database link what are the problems and the benefits of it ?

select T1.col1 , T2.col2
from TAB1 T1 , TAB2_at_link2 T2
where T1.col1 = T2.col1
order by T2.col1 ;

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 */ ;

Difference between version 5,6,7. ( Candidate using Version 5 and/or Version 6 ).

DBA
explain by one sentences,

	role
	references privilege on a table.
	profile
	What is can produce a snapshot to old.
	what are the DB files, how oracle discover it.
	where do you look for the trace file.

The following question about implementation, the answer is not a number but the way you do it.

You have got a raw partition

        The size is 800M
dd if=/dev/rdsk/c.t.d.s. of=/dev/null bs=1024

        819200 block.
You create one file table space what is it's maximum size available for the rollback and what is the maximum size for rollback.

                Speak about extents for rollback segments.         

How to backup a database and made a recovery of a table ?

How do you chose your db_block_size ?

A database has been created with the default parameters how do you increase the DB_BLOCK_SIZE and the DB_FILE ?

How can I change the table space ROLLBACK from a disk to an other:

        select segment_name , tablespace_name , status , owner from dba_rollback_segs ;

	SYSTEM SYSTEM ONLINE SYS
	RBS1 ROLLBACK ONLINE PUBLIC
	RBS2 ROLLBACK ONLINE PUBLIC

	select tablespace_name , file_name from dba_data_files where

tablespace_name = 'ROLLBACK' ;

        ROLLBACK /dev/rdsk/c0t0d0s0

        New name file /dev/rdsk/c1t1d0s0

Estimate the size of the table

	create table emp ( emp_id number not null ,
			name varchar2(50) not null ,
			surname varchar2(50) not null,
			social_number varchar2(10) ) ;

	emp will contains 1000 static rows.

	The answer is not a number but a list of what you take care about.

Same question but the table already exists and has one extents of 1Gbytes ?

How to shutdown a database in version 5,6,7 ( if candidate used 5 and/or 6)

PRO*C When can you NOT do Pro*c ? Embedded Sql or where in your program.

Explain:

	ORACA
	SQLCA
	SQLDA
	ora-1403 OR ora-100

what will happen:

        EXEC SQL AT DB1 DECLARE CURSOR C1 AS SELECT COL1 FROM TABLE2 WHERE COL = :VAR1 ;
        EXEC SQL AT DB1 OPEN C1 ;

	EXEC SQL AT DB1 FETCH C1 INTO :VAR2 ;
	while ( .... )
	{
		EXEC SQL AT DB1 UPADTE TABLE1 SET COL1 = COL1 WHERE COL2 = :VAR ;
		if ( ......)
		{
		}

		EXEC SQL AT DB2 UPDATE TABLE1 SET COL1 = COL1 + 1 WHERE COL2 = :VAR ;
		if ( ....)
		{
		}

		EXEC SQL AT DB1 COMMIT ;
		EXEC SQL AT DB2 COMMIT ;
		EXEC SQL AT DB1 FETCH C1 INTO :VAR2 ;
	}

	EXEC SQL AT DB1 CLOSE C1 ;
	...

How can you know a value fetch is null ?

OCI explain:

	lda
	hda

Modelling.

Translate the following logical model to a physical one ( tables,indexes,constraints ... ). Explain how do you read the relation between these two entities.

 A (a1 a2 a3 a4 ... an) 1,1 <-> 0,N B (b1 b2 b3 ... bm)                                 

 A (a1 a2 a3 a4 ... an) 1,1 <-> 0,1 B (b1 b2 b3 ... bm)

 A (a1 a2 a3 a4 ... an) 1,1 <-> 1,N B (b1 b2 b3 ... bm)                                 

 A (a1 a2 a3 a4 ... an) 1,1 <-> 1,1 B (b1 b2 b3 ... bm)

-- 
F. Lange Certified DBA.

Received on Tue Jun 17 1997 - 00:00:00 CDT