Interview Minutes - for DBA - ORACLE

A: Can you tell me something about you ?
P: I have 2.5 yrs. experience as Oracle DBA and approximately 5 yrs, on Oracle RDBMS. I have worked on Oracle versions from 5.0 to 7.2. My overall experience in the S/W field is over 8 yrs.
I have worked on various platforms as PC, Mini computers and mainframe computers. Besides
administration of Oracle RDBMS, I have worked on other large databases in various capacities
from developer to project leader.

A: What was your role in BAYBIS ?
P: My role was DBA. I briefly told him about the nature of the application and the work I had done.

A: Were you involved in database modeling ?
P: Yes, I was involved in modeling and responsible for database schema creation for BAYBIS.

A: Have you done tuning in BAYBIS ?
P: Yes. At the design time itself, the database was designed with regard to performance aspects.
In production stage, the performance of the system was continuously monitored and corrective steps were taken to give the best performance.

A: What are the init parameters that you tuned ?
P: I mentioned a few parameters such as database buffer cache, shared pool & log buffer.

A: What is the use of shared pool ?.
P: It has two parts Library cache and data dictionary. Library cache stores all SQL statements & stored procedures issued by users.  Data dictionary stores all oracle dictionary tables.  Basically this pool is shared by all users connected to the system. In multi-threaded architecture shared pool also stores private SQL area containing session information.

A: Have you tuned database buffer cache ?
P: Yes,  based on the usage of database buffer cache at peak hours, I tuned the size.

A: How did you arrive at the buffer cache size ?
P: We can make out from the hit ratio on DBS buffer cache.

A: How hit ratio is used to determine ?
P: If the hit ratio is near 100%, one can conclude that the  buffer cache size is sufficient. If the hit ratio is low, contention for buffer cache can be reduced by increasing the buffer cache size.

A: How do you arrive at the optimum size of buffer cache ?
P: Before increasing the size of buffer cache, one can monitor the effect of  increased size by using two dynamic tables, named, X$KB… By comparing the hit ratio manipulated for various buffer cache sizes, one can arrive at optimum size which will contain blocks no more than required.

A:  Have you worked on Distributed data processing ?
P: I have worked on multi-threaded system with SQL*Net 2.0. I configured parameters for network connection. For Distributed, we need to create database link to connect to the target database.

A: How will you handle if one site has problem in transaction ? How will you resolve it ?.
P: There is a concept called two-phase commit. In the first phase, the server makes sure that all sites are available for transaction. If all are available, then the commit/rollback phase occurs. If any transaction is held up due to unavailability of resources in the target site, the transaction is named in-doubt transaction and data is stored in the data dictionary of the target site. This will be later committed/rolled back by ‘RECO’ background process. DBA can also see the status of in-doubt transaction and based on the comment given along with commit/rollback, he can take commit/rollback manually.

A: what was your backup strategy ?
P: Everyday I used to take hot backup along with archived redo logs. Besides, I used to take ascii text backup for a few important tables using exp command.

A: Have you done database recovery ?
P: Yes. 5 times I have recovered database, mostly data files using archived redo logs and old data files.

A: Have you done trigger based applications ?
P: Yes, I have done many applications using DBS triggers. If you are specific, I can tell you more.

A: Have you handled DML statements using triggers ?
P: Yes. I mentioned about the use of DBS triggers in one of my application.

A: How would you handle duplicate rows in a table ? I want to find out the duplicate rows. How to go about it using primary key?
P: When one uses alter table command with exceptions clause, the duplicate row information will go into a pre-defined table structure.

A: It’s okay with you. Do you have any questions ?
P: Yes, I want to know more about the client and nature of application.

A: He told about the client’s business and the type of applications using the database.
P: Okay. Thanks. No more questions.

A: Okay. We will call you back. Thanks. Bye.
P: Thanks. Bye.

Telephonic Interview - Oracle Finance

(After initial introduction…)

1)     Since how long you have been in Oracle Financials?
A)   Since January 95 I am in Oracle Financials

2)     Tell me about the projects you have done?
A)   (Explained to him about the Custom Purchasing report for Honeywell, Interface mapping system for Starkey. Interrupting…)

3)     So you have worked on Purchasing, Have you worked on GL?
A)   I have worked on GL, Purchasing and AP modules?

4)     Have you worked on FA (Fixed Assets)?
A)   No.

5)    That is fine. What all work you did in your projects?
A)     Mainly I was involved in writing custom reports. I have also written some PL/SQL blocks. I was handling the technical side. After creating reports I also implemented it using AOL in ….. (Interrupting)

6)     OK! My client Hemal may give you call today. Give me your residence number
A)     (Gave him the residence number)

7)     He has requirements in Denver and Omaha. He is also an Indian. He is staying here for almost 15-20 years. You have an excellent communication skills. However, when you talk to him, talk in American accent so that he will be thoroughly impressed. He may give a call as soon as he receives your resume. Then I will give you call sometime in the evening. Are you available?
A)   Yes. Certainly. I will be in the Office till 5.00 pm after that you can reach at my residence.

8)     OK! Bye now.
A)   Bye

Oracle Interview Questions

1)    What are the multithreaded server  and how it is different from dedicated server .
2)    What are the new features incorporated in ORACLE 7.2 version.
3)    What are the various difference between ORACLE 6.0 ORCALE7.0.
4)    How many types of triggers U have used in your application.
5)    What are the difference between WHEN-VALIDATE-ITEM and POST-CHANGE trigger.
6)    What is Record group and what are the advantages of having it.
7)    What is the differences between Tlist,ComboBox and PopList properties of list items.
8)    What are the differences between copying and referencing an object.
9)    What is MDI Window.
10)    What is the procedure to call a Microsoft Application from forms 4.5.
11)    What is VBX controls and how it helps to build an application in Forms 4.5.
12)    How Parameters will be passed to other Oracle tools.
13)    What kind of quality management Sonata is having.
14)    What is meant by ISO9000 an dwhat are the advantage to have it.
15)    What  do you mean by debugger in Forms 4.5
16)    While debugging we can change the script or not.
17)    Can We have an OLE server for Oracle application.
18)     are the various variables in Proc and how we are declaring it.
19)    What is meant by Indicator variable in Pro*C
20)    What is difference between SQLCA and ORACA.
21)    How You are trapping errors in Pro*C program
22)    What is meant by UserExits and how we You will call it from your forms 4.5 application.
23)    What is difference between UserExits and Pro*C program
24)    What are the various triggers that Reports 2.5 supports.
25)    What is icon.
26)    How many(minimum) groups are required to prepare a Matrix Report.
27)    What do you mean by bind parameter and lexical parameter.
28)    What are the various difference in UNIX and WINDOWS95 Operating systems.
29)    What are the various differences in Forms 3.0 and Forms 4.5
30)    Can I have a HTML document from Forms 45 application.
31)    What do you mean by activation style property of an OLE object.
32)    What is difference in Object embedding and object Linking.
33)    What are the situations in which we can go for embedding and in which we can go for Linking.
34)    How You are connecting to Oracle database and what is meant by connect string.
35)    What are the difference between Post-fetch and Post-Query trigger and when they will fire.
36)    What is meant by partition view.
37)    What are various triggers that You have extensively used in your application.
38)    What is difference between following built-ins
39)    Create_group_with_query and Populate_group_with_query.
40)    What is Dynamic SQL.

41)    How you can change menu type property
42)    what are the backup procedures for oracle.
43)    What is meant by 2 phase commit.
44)    Which case designer you have used and what was the version of it.
45)    what is SQL loader.
46)    What are the various security provided by oracle.
47)    What is difference between content and stacked canvas
48)    Can you tell something about TOOLBAR.
49)    What are the sequence of commit processing triggers
50)    can you create dynamic LOV.
51)    How you will control transaction in MDI.
52)    what do you mean by session in MDI.
53)    Database triggers call database procedures and vice versa.
54)     do you use global variables and parameters.
55)    you will define and raise  your own exceptions.
56)    many error codes you  can use to have your own error messages.
57)    Can you change the retrieved value of reports.
58)    What is an anchor in Reports 2.5.
59)    What is the trigger that fires while navigating between the form.
60)    Can you tell something about window based triggers.

ORACLE - How TO

Consists of  System Global Area, Background processes, Database files, Redolog files, control files & Parameter files

I.     SYSTEM GLOBAL AREA consists of Shared Pool (Shared SQL Area), Database buffer Cache, Redolog buffer.

Shared Pool consists of Library cache & Data Dictionary cache, Parsed form of the SQL or PL/SQL statements, Execution Plan for SQL and PL/SQL statements, Data dictionary cache containing rows of datadictionary information.

Three Phases of processing SQL statements.
I.     PARSE    :Checks syntax, Queries the datadictionary for object resolution, security privileges and the most effective search path, Determines the parse tree or Execution Plan.
II.     EXECUTE    :Applies parse tree to data buffers, performs physical reads or logical read writes.
III.     FETCH    :Retrieves row of data for a select statement.

Database buffer Cache : The database buffer cache hold copies of data blocks read from disk, shared by all ORACLE user processes concurrently connected to the instance, the size is determined by the parameter DB_BLOCK_SIZE.  The number of block cached in memory is determined by the parameter DB_BLOCK_BUFFERS.

REDOLOG Buffer    :The redo log buffer is a circular buffer containing information about changes made to the database. Stores all changes made to the database in the redo entries of redo log buffer, used to reconstruct or rollback changes made to the database when recovery is necessary.  The size of redo log buffer is determined by the parameter
LOG_BUFFER.



II.     BACKGROUND PROCESSES :

1)DBWR    :Modified data blocks are written back to disk by DBWR process.

2)User        :A user process is created when a user runs an application program

3)LGWR    :Oracle records all changes made to the database in the redolog buffer.  LGWR process writes the information in the redo log buffer to disk when a commit occurs, the redo log buffer pool reaches one third full threshold, the DBWR needs to clean the buffer blocks for a checkpoint or a time out(3 sec) occurs.

4)ARCH    :The Archiver process copies online redo log files to a designated storage device once they become full.   

5)PMON    :The Process Monitor cleans up abnormally terminated connections, Roll back uncommitted transactions, Releases locks held by terminated process.  Free SGA resources allocated to the failed processes, Detects deadlocks and automatically resolves by rolling back the transaction.

6)SMON    :Performs automatic instance recovery, Reclaims sort table space.

7)RECO    :The Recoverer process resolves failures involving a distributed transaction.

8)LCkn    :The Lock process performs inter instance locking in a parallel server system.

9)Dnnn    :The Dispatcher process gets the request from user processes and puts in request que in SGA, & also gets the response from response que in SGA and passes back to user process.

10)LISTENER    :The listener process identifies the path and protocol in MTS environment.


11)Server    :A server process communicates with user process and SGA. & datafiles. Parses and executes SQL statements, Reads datablock from disk into the shared database buffers of the SGA, returns the results of SQL statements to the userprocesses.

12)CKPT    :process updates headers of data and control files after check point has been completed, more frequent checkpoint will reduce the time necessary for recovering from instance failure, at the expense of performance, the check point process is enabled through the parameter CHECKPOINT_PROCESS.

13)SNPn      Snapshot refresher


SGA    :Every time ORACLE is started, the SGA is allocated and the ORACLE background processes are started.  The combination of memory buffers and background processes is called an ORACLE instance
ORACLE database  is composed of Control files, and the database and redo log files named in control files.

III     Data files    : Contain all the database data, logical structures such as tables and indexes, are physically stored in the datafiles.

Redolog files    :Transaction logs record all changes made to the database  and are used for data recovery, if the redo log files are mirrored, the same redolog  information is written to multiple online redo log files.  The redo log files are written to in a circular fashion, there must be at least two redo log groups. 
Log Switches    :A log switch occurs when ORACLE switches from one relog to another, when LGWR has filled one log file group, A log switch can be forced by a DBA when current redo log needs to be archived ( ALTER SYSTEM SWITCH LOGFILE), At a log switch the current redo log is assigned a log sequence number that identifies the information stored in that redo log and is also used for synchronization, A checkpoint automatically occurs at logswitch.
CHECKPOINT    :During a check point DBWR writes all dirty buffer in the database buffer cache to disk, guaranteeing that all data blocks are modified since the Previous checkpoint are actually written to disk.
A Check point occurs at every log switch, a specified number of seconds after the last database checkpoint, when a predetermined number of redo log blocks have been written to disk since the last check point, at instance shutdown, when forced by DBA (ALTER SYSTEM CHECKPOINT) , when a table space is taken offline.
During a check point and after the log switch LGWR will update the headers of database and control files, unless check point process has been started, The parameter LOG_CHECKPOINT_TIMEOUT determines the interval of time after which another check point occurs, The parameter LOG_CHECKPOINT_INTERVAL determines the number of newly filled redo log file blocks needed to initiate a checkpoint.


CONTROL FILES    :The control file is a small binary file that describes the structure of the database, All necessary database files and log files are identified by the control files, the name of the database is stored in the control file, the control file is required to open and access the database, synchronization information needed for recovery is stored inside the control file.  It is advised to have minimum of two control files on different disks.  The parameter CONTROL_FILES identifies the Control files. Parameter file points to control file,  Control file points to redo log files and database files.


STARTUP STEPS    :


1)The Init.ora is read, the SGA is created, the background processes are started and the instance is started.

2)The control file is read and the database is mounted.

3)Rolling forward occurs to recover the data that has not been recovered in the database files, yet has been recorded in the online redo log, including the contents of rollback segments.

4)Transaction that have been explicitly rolled back or have not been committed are rolled back, as indicated by rollback segments regenerated in prior step.

5)Any resources held by pending transactions at the time of failure are released.

6)Any pending distributed transactions are resolved undergoing a two phase commit at the time of instance failure.

7)Once the SMON processes has synchronzied the database and all the outstanding information in the redo log  files have been applied to all the data files, the database is open for users to log into the database.

Interview Questions - SQL*Plus, PL/SQL, ORACLE Concepts

1.     What are the minimum priveleges required to be given to a newly created user?
2.     What is a synonym? Diff between a view and a synonym
3.     If Db is mounted but not open, which view can U access
4.     Contents of  a control file
5.     How to mirror a control file?
6.     What is a instance?
7.     Where does oracle log its errors?
8.     Which process does automatic recovery if an instance fails before writing into datafile but after issuing a commit statement?
9.     How can u maintain consistency across forms?
10.     How do constraints differ from DB triggers?
11.     If rollback statement has been dropped or corrupted( eg. By deleting the file in O.S ) what will hapen during startup of the instance? How can the situation be handled?
12.     What are the issues to be attended to when a tablespace has to be designed and created?
13.     When will you experience the following error - “Snapshot too old” How can it be attended?
14.     What is a mutating and a constraining table?
15.     How do you monitor locks in Oracle?
16.     If a user has locked a resource for a long time and is not using it, How will you handle the situation and release the resource?
17.     What are the differences bet a trigger and a stored procedure?
18.     What is a pl/sql table?
19.     What is a record datatype?
20.     What will happen when a table begin accessed by a stored procedure is dropped?
21.     What is table stripping?
22.     Which view will give the details of datafiles?
23.     Which default tablespace is created by ORACLE?
24.     How do you decide the size of the DB block ?
25.     How do you increase the DB block size?
26.     What are priveleges?
27.     What are tuning steps?
28.     What are the diagnostic tools available for Tuning?
29.     What are the advantages of stored procedures?
30.     If a user does have access rights on a table but has executable rights on a procedure accessing that table; what will happen?
31.     what will happen when importing a table with duplicates in it?
32.     What is a Instance?
33.     Can you have multiple instances of the same DB/
34.     Can you share bet two instances?
35.     What features are provided by Oracle for DB Audit Trail?
36.     How do you avoid using indexes?
37.     What is cluster? How is it stored?
38.     What is ananymous block?
39.     If a SQL statements are given one by one from the SQL prompt and if they are written in a file and executed then what will be the difference?
40.     Is it possible to use a index in SQL?
41.     What is the criteria for creating a index?
42.     Will droping a index and recreating it increase performance? If yes, Why?
43.     What is analyzing a table?
44.     Diff bet “Union” and “Union All”?
45.     Result of count(*) and count(field name) , DIFF?
46.     What are DCL statements?
47.     What is a exetent, segment?
48.     How many types of segments exists in Oracle?
49.     Can you create rollback segments and data segments explicitly?
50.     How are execptions raised in pl/sql?
51.     Implicit and explicit cursors, diff?
52.     Can you use DDL statements in PL/SQl?
53.     How is a SQL statement parsed?
54.     How can you delete duplicates in a table?
55.     what is the diff bet rowid and rownum?
56.     What are hierarchical queries? ( connect by prior)
57.     What is a transaction?
58.     What is a read lock?
59.     What are SQL statements you can use in case of read transcation?
60.     In case of a trigger firing another trigger and that in turn firing another and so on ; upto what level is this kind of nesting allowed ? ( ans. 32 )
61.     What is referential integrity?
62.     Normalisation and De-normalisation?
63.     Diff bet 2nd and 3rd normal form?
64.     Optimiser 1. Cost based 2. Rule based . Diff? Which is used by Oracle?
65.     When is index recreated  or remapped?
66.     Diff bet truncate and delete?
67.     Will a “After-delete” trigger fire when the table is truncated?
68.     What are the things you will keep in mind when writing SQL statement?
69.     In case of a join statement which should be the driving table? ( ans. With max number of records.)
70.     Upto what level can you nest blocks in PL/SQL ? ( ans. 200 depending on the stack status ).
71.     What are the multithreaded server  and how it is different from dedicated server .
72.     What are the new features incorporated in ORACLE 7.2 version.
73.     What are the various difference between ORACLE 6.0 ORCALE7.0.
74.     How many types of triggers U have used in your application.
75.     How are the various variables in Proc and how we are declaring it.
76.     What is meant by Indicator variable in Pro*C
77.     What is difference between SQLCA and ORACA.
78.     How You are trapping errors in Pro*C program
79.     What is difference between UserExits and Pro*C program
80.     What are the various difference in UNIX and WINDOWS95 Operating systems.
81.     How You are connecting to Oracle database and what is meant by connect string.
82.     What is meant by partition view.
83.     What are various triggers that You have extensively used in your application.
84.     What is Dynamic SQL.
85.     What are the backup procedures for oracle.
86.     What is meant by 2 phase commit.
87.     Which case designer you have used and what was the version of it.
88.     what is SQL loader.
89.     What are the various security provided by oracle.
90.     What is difference between content and stacked canvas
91.     Database triggers call database procedures and vice versa.
92.     You will define and raise  your own exceptions.
93.     many error codes you  can use to have your own error messages.

SQL Reports - Interview Questions

1.     How will you implement conditional highliting in Reports 2.5?
2.     How many triggers are available and when are they used?
3.     What are the diff types of layouts available?
4.     What are the steps to create your own layouts?
5.     What are the steps to create report containg two diff layouts?
6.     How do you refer field values and parameter values in PL/SQL code in reports?
7.     How do you print the value of a parameter?
8.     How do you restrict that only ten records are printed on one page?
9.     How do you print the name in Vertical Orientation in a Matrix report?
10.     Diff bet report writer 1.1 and 2.5?
11.     Can you write pl/sql in reports 1.1?
12.     How do you give a page level break in 2.5?
13.     How many groups and queries should there be in a matrix report in 1.1 and 2.5?
14.     What is an anchor in Reports 2.5.
15.     Can you change the retrieved value of reports.

SQL Interview Questions

SQL Interview Questions
1.     Which trigger will fire first ? Block level or item level .
2.     List mouse triggers in forms 4.5
3.     Steps in displaying multiple records in control block
4.     How to integrate reports with graphics?
5.     What are the types of relationships in Forms 4.5?
6.     Which triggers are generated by forms during the creation of  Master-Detail relationship?
7.     How will you delete the total transaction in a Master-Detail situation without writing code?
8.     When does “ON-POPULATE-DETAILS” tigger fire?
9.     How do you generate sequential numbers for key0fields ( eg. Purchase Order number )
10.     What trigger fires when the value in Radio button is changed?
11.     Can a null value be inserted using radio button?
12.     How will you set the value of radio button when the value is begin queried from the db?
13.     If a null value or other values is queried, how is the radio button set?
14.     What are the diff types of list Items available?
15.     How will you populate a list item dynamically?
16.     How will you implement duplicate check using “Record Groups” ?
17.     How will you stop the user dynamically to Insert record in tables?
18.     What triggers will not fire in Enter Query mode by default?
19.     What is the maximum limit of Timer expiry/
20.     What will be the Form_Status after the DB Commit is fired?
21.     What are Global variables? What are the advantages?
22.     How do you create reusable componentes in forms?
23.     What is the sequence of triggers fired in Navigation to other item When validation is 1.Item level and 2. Block level?
24.     what are form parameters?
25.     What is referencing?
26.     What are the locking mechanism used in F45?
27.     how will you inforce security in forms?
28.     What are the difference between WHEN-VALIDATE-ITEM and POST-CHANGE trigger.
29.     What is Record group and what are the advantages of having it.
30.     What is the differences between Tlist,ComboBox and PopList properties of list items.
31.     What are the differences between copying and referencing an object.
32.     What is MDI Window.
33.     What is the procedure to call a Microsoft Application from forms 4.5.
34.     What is VBX controls and how it helps to build an application in Forms 4.5.
35.     How Parameters will be passed to other Oracle tools.
36.     What  do you mean by debugger in Forms 4.5
37.     While debugging can we change the script or not.
38.     Can We have an OLE server for Oracle application.
39.     What is meant by UserExits and how we You will call it from your forms 4.5 application.
40.     What are the various triggers that Reports 2.5 supports.
41.     What is icon.
42.     How many(minimum) groups are required to prepare a Matrix Report.
43.     What do you mean by bind parameter and lexical parameter.
44.     What are the various differences in Forms 3.0 and Forms 4.5
45.     Can I have a HTML document from Forms 45 application.
46.     What do you mean by activation style property of an OLE object.
47.     What is difference in Object embedding and object Linking.
48.     What are the situations in which we can go for embedding and in which we can go for Linking.
49.     What are the difference between Post-fetch and Post-Query trigger and when they will fire.
50.     What is difference between following built-ins
51.     Create_group_with_query and Populate_group_with_query.
52.     How you can change menu type property
53.     Can you tell something about TOOLBAR.
54.     What are the sequence of commit processing triggers
55.     can you create dynamic LOV.
56.     How you will control transaction in MDI.
57.     what do you mean by session in MDI.
58.      Do you use global variables and parameters.
59.     Can you tell something about window based triggers.