Oracle DBA Interview Questions by Expert 5

1. Explain by one sentences, role, references, privilege on a table and profile?

2. What can trigger a snapshot to old error?

3. What are the DB files, how oracle discover them?

4. Where do you look for the trace file?

5. 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? Explain how the extents for rollback segments are allocated?

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

10. How do you chose your db_block_size ?

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

12. How can you move the tablespace ROLLBACK from one disk to an other, given this information?
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 location is /dev/rdsk/c1t1d0s0

13. Estimate the size of the table? The answer is not a number but a list of steps you will use to derive this number.
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.

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

15. How to shutdown a database in Oracle version 8i and 9i?
PRO*C

1. When you can NOT use Pro*C ? Where in your application you can use Embedded SQL?

2. Explain:
ORACA
SQLCA
SQLDA
ora-1403 OR ora-100

3. What will happen in this example?
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 ;
...

4. How can you know a value fetch is null ?
OCI

1. Explain the following?
lda
hda

2. 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)

1. Three users at a time giving the following command:
alter database backup controlfile to trace;
How can you differentiate the file using the filename itself?

2. When you execute:
alter tablespace begin backup;
How can Oracle engine can identify that the corresponding datafiles are in backupmode?

3. When exporting using filesize parameter I have generated three files. How the oracle name this three files?

4. One of my rollback segments got corrupted? How can I rectified?

5. When yesterday developer gives the following command:
select count(*) from it gives the output within minutes Today the same command takes hours? What will u check?

6. When the developer trying to compile the package. But the database gets hanged?
What will u check?

7. What is the use of query in Tkprof?

8. What is the maximum number of value allowed in parse column?

9. How can we force the query not to use the index? (Something else then a using a hint)

10. Till yesterday the query is running smoothly. But today the develper is complaining?
How will u start your tuning?

11. What is the difference between count(*) and count(1)?

12. As a Oracle DBA what are all the UNIX file you should be familar with?