Forms 4.5 , Reports 2.5, SQL*Plus, PL/SQL Questions asked during mock interview
Forms 4.5 :
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.
Reports 2.5 :
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*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.
DBA RESPONSIBALITIES :
1)Install & upgrade the ORACLE7 server and application tools
2)Create primary database storage structures and primary objects
3)Allocate system storage and plan future storage requirements for the database system.
4)Modify the database structure
5)Enrol Users.
6)Control and monitor user access to the database
7)Backup and recover the database
8)Maintain system security.
9)Monitor and optimize database performance.
ORACLE ARCHITECTURE :
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.
MULTI-THREADED SERVER ARCHITECTUE.
Uses of Multithreaded server
1. Reduce the number of processes running against instance.
2. Increase the number of possible users.
3. Achieve load balancing(Server start and terminate automatically)
4. Reduce the number of idle server processes
5. Reduce memory usage and system overhead.
Connecting in MULTI-THREADED SERVER ARCHITECTUE.
The listener processes waits for connection requests from user processes, then determines if each user processes can use a shared server process.
When the user can use shared server, the listener gives the user process the address of dispatcher process.
When the user process requests for dedicated server, the listener creates dedicated server process ad connects user process to it.
PROCESSING A REQUEST
A user send a request to its dispatcher.
The dispatcher places the request into the request queue in the SGA.
A shared server picks up the request from the request queue and processes the request.
The shared server places the response on the calling dispatchers response queue
The dispatcher returns the response to the user.
Steps to configure Multi-threaded Server.
1. Configure and start the listener process.
2. Specify the dispatcher service name
3. Specify the initial number of dispatchers
4. Specify the maximum number of dispatchers
5. Specify the initial number of shared servers
6. specify the maximum number of shared servers
In the parameter file following parameter are to be set.
MTS_LISTENER_ADDRESS :Address of listener process
“(address=(protocol=tcp)(host=erseq5) (PORT=7000))”
MTS_SERVICE ;Name of the service
MTS_DISPATCHERS :Initial number of dispatcher process
MTS_MAX_DISPATCHERS :Maximum number of dispatcher process
MTS_SERVERS :Initial number of shared servers
MTS_MAX_SERVERS :Maximum number of shared servers.
Steps to start listener process .
1)Login as ORACLE7 with the password larry
2)Go to $ORACLE_HOME/rdbms/admin dir
3)$lsnrctl start (this command will start the listener process, it is advised to start the listener process before the starting database).
On the client end connect string has to be specified in the file tnsnames.ora
Colin = (Description = (Address=(Protocol=TCP) (Host=erseq5) (PORT=7000) (CONNECT_DATA=(SID=prod)))
$sqlplus scott/tiger@colin
DATABASE STORAGE
TERM DEFINITION
BLOCK :Multiple Physical file blocks allocated from an existing data file
EXTENT :A set of contiguous database blocks
SEGMENT :A set of one or more extents that contains all the data for a specific structure within a tablespace
TABLESPACE :A logical repository of physically grouped data
FILE :A physical data file belonging to a single tablespace
DATABASE :A logical collection of shared data stored in tablespaces.
An ORACLE datablock is the smallest unit of I/O used by the database also called logical blocks and ORACLE blocks which corresponds to one or more physical blocks on disk. The size is determined upon database creation by initialization parameter DB_BLOCK_SIZE which is constant throughout all datafiles. Size also determines the size of each database buffer in SGA. The block size cannot be changed after database creation except by recreating the database. The block size should be equal to the size of O.S block size or more that that, typically 2K or 4K bytes.
PARTS OF DATABASE BLOCK
HEADER :Contain general block information, such as the block address, and the segment type. On the average, the fixed and variable portions of the block total 85 to 100 bytes.
TABLE DIRECTORY :Stores information about the tables in the cluster and is used with clustered segments.
ROW DIRECTORY :Contains row information about the actual rows in the block. Allow two bytes of overhead per row.
FREE SPACE :Consists of set of bytes in the block that is still available for insert, or update space requirements.
ROW DATA :Stores table or index data.
Block free space utilization parameters.
PCTFREE
PCTUSED
INITTRANS
MAXTRANS
PCTFREE :Set the percentage of block to be reserved (Kept free) for possible updates to rows that are already contained in that block with the PCTFREE parameter.
PCTFREE sets the percentage of usable block space to be reserved during row insertion for possible updates to rows contained in that block.
After PCTFREE is met, the block is considered full, and is not available for inserts of new rows.
Space remaining in block after PCTFREE quota is reached is reserved for update commands on rows within the block.
The PCTFREE parameter can also be specified when creating or altering indexes.
Increasing PCTFREE reduces the occurrence of rowchaining and row migration. A PCTFREE set too low for a given table may result in row migration.
ROW CHAINING & ROW MIGRATION.
Chaining occurs with large rows that contain long columns when all the data for a row in a table can not fit in the same block. The data for the row is stored in a chain of data blocks.
Migration occurs if a row in a data block is updated so that the overall row length increases and the block’s free space has been completely filled. The data for the row is migrated to a new datablock, assuming the entire row can fit in a new block.
Performance is affected due to rowchaining and row migration, because during I/O ORACLE must scan more than one data block to retrieve the information for the row.
TO RESOLVE ROW CHAINING AND ROW MIGRATION.
1)Examine the extent of chaining or migrating with the ANALYZE command.
2)Alter PCTFREE for the object.
3)export, drop and import the object.
LOW PCTFREE :Allows inserts to fill blocks more frequently, can cause row migration and row chaining, increases processing costs if ORACLE must frequently reorganize the blocks.
HIGHER PCTFREE :Reserves more room for future updates, may require more block to store the data, low processing costs, reduces the need to row chaining and row migration.
PCTUSED :Set PCTUSED to allow a block to be reconsidered for the insertion of new rows. New rows are inserted as long as the block is on the free list. A block is removed from the free list upon reaching the PCTFREE threshold.
PCTUSED is the threshold for determining when the block is available for row insertion.
When the percentage of block being used falls below PCTUSED either through row deletion or updates reducing column storage, the block is again available for insertion of new rows.
The system default for PCTUSED is 40
High delete activity may result in block fragmentation.
BLOCK FRAGMENTATION :Free space is not contiguously available due to high delete activity, low PCTUSED setting.
RESOLVE BLOCK FRAGMENTATION :ORACLE will perform a memory operation to condense the data in the block together under special cirucmstances.
Otherwise, export, drop, and import the object.
Modify PCTUSED to a more appropriate setting.
LOWER PCTUSED :Reduces processing costs because blocks are not often free, increases unused space.
HIGHER PCTUSED :Increases processing costs because blocks may often become free, improves space usage.
INITTRANS : is the initial number of transaction entries, for concurrent transactions, that are allocated in each block header when block is allocated (default is 1, minimum 1, maximum 255). Each transaction entry is 23 bytes in length.
MAXTRANS :is the maximum number of concurrent transaction that a block will support (default 255, minimum 1, maximum 255)
EXTENTS :
An extent is a set of contiguous blocks allocated to a segment, when a database object grows, space is allocated to it.
Each segment in a database created with at least one extent to hold its data, Rollback segment, however always have at least two extents.
The first extent is called the segments INITAL extent.
Subsequent extents are called the segments NEXT extent.
An object will only allocate a new extent if all of its currently allocated extents are already used.
Frequently de-allocation of extents can lead to fragmented physical data files.
Specified storage parameters:
INITIAL :Size in bytes of the first extent allocated to a segment. Default is the equivalent of 5 data blocks.
NEXT :Size in bytes of the next incremental extent allocated to a segment. Default is the equivalent of 5 data blocks.
MAXEXTENTS :Total number of extents that can ever be allocated for the segments. The maximum depends upon the ORACLE block size, default is 121.
MINEXTENTS :Total number of extents to be allocated when the segment is created. Default is one extent.
PCTINCREASE :Percent by which each incremental extent grows over the last incremental extent allocated. Default is 50 percent.
OPTIMAL :Specifies the optimal size in bytes for a rollback segment. Default is null.
FREELISTS :Number of lists of free blocks kept for inserts into table.
Set the storage parameter for the following objects :
TABLE, CLUSTER, INDEX, ROLLBACK SEGMENT, TABLESPACE
Rules of precedence :
Any storage parameter specified at the object level overrides the corresponding option set at the tablespace level.
When storage parameter are not explicitly set at the object level, they default to those at the tablespace level.
When storage parameters are not explicitly set at the tablespace level, ORACLE system defaults apply.
If storage parameter are altered, the new option apply only to the extents not yet allocated.
Optimal is only specified for rollback segments.
Uncontrolled extent allocation can cause performance degradation due to potentially excessive physical I/O operation and operating system file fragmentation. Fragmentation can be reduced by fitting a segment into the INITIAL extent, and setting PCTINCREASE appropriately to enlarge incremental extents.
SEGMENTS :
A Segments is a set of one or more extents that contain all the data for a specific type of logical storage structure within a tablespace.
A segment is a logical structure that can be created, will occupy storage, and can grow. Segments cannot span tablespaces. An extent is a set of contiguous database blocks. The storage parameters for temporary segments always use the default storage parameters set for the associated tablespace , they can never be set explicitly.
DATA SEGMENT :A collection of extents that holds all the data for a table or a cluster. When a table is created in table space data segment gets created.
Ex. Create Table tablename ( colname datatype)
PCTFREE 5 PCTUSED 65
STORAGE(
INITIAL 5M
NEXT 5M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 121)
TABLESPACE tablespacename;
INDEX SEGMENT :A collection of extents that holds all of the index data for search optimization on large tables and clusters. If an index is created on a table, an index segment is created for each index.
Ex. Create Index indexname on tablename (colname)
STORAGE (INITIAL 500K NEXT 500K PCTINCREASE 0)
TABLESPACE tablespacename;
TEMPORARY SEGMENT :A collection of extents that holds data belonging to temporary tables created during a sort operation. Temporary segments provide workspace for the RDBMS to sort and join tables while performing complex searches, reclaimed at the end of the transaction (SMON process), space allocation determined by default storage of its tablespace, not protected by redolog.
ROLLBACK SEGMENT :A collection of extents that hold rollback data for rollback, read-consistency or recovery. Rollback segment entries are written in circular fashion for rollback, read consistency and recovery.
Consists of several rollback entries from multiple transactions.
Ex. SQL >CREATE PUBLIC ROLLBACK SEGMENT rbs01
TABLESPACE rbs
STORAGE(INITIAL 10K NEXT 10K OPTIMAL 20K MAXEXTENTS 121);
After creating set the rollback segment online.
SQL >ALTER ROLLBACK SEGMENT rbs01 ONLINE;
BOOTSTRAP SEGMENT ;An extent that contains dictionary definitions for dictionary tables to be loaded when the database is opened. Requires no attention on the part of the Database storage Administrator
The bootstrap segment contains data dictionary tables to be loaded when the database is opened. It cannot be read, modified, or dropped. Exists in system tablespace and is owned by the user sys. Usually needs less than 50 ORACLE BLOCKS.
TABLESPACES & DATAFILES
Data in an ORACLE database is logically stored in tablespaces and physically stored in database files.
The ORACLE database can be logically divided into separate tablespaces. The system tablespace must exist in order for the database to run. Tablespaces are designated to contain sever database segments.
Each tablespaces contains one or more operating system files.
Tablespace can be brought online while the database is running.
Tablespace can be taken offline except the system tablespace, leaving database running.
Objects created in a tablespace can never be allocated space outside of their original tablespace.
TABLESPACE USAGES;
Control space allocation and assign space quotas to users.
Control availability of data by taking individual tablespaces online or offline.
Distribute data storage across devices to improve I/O performance and reduce I/O contention against a single disk.
Perform partial backup and partial recovery operations.
IMPORTANT DATA DICTIONARY VIEWS.
USER/DBA_EXTENTS
USER/DBA_FREE_SPACE
USER/DBA_SEGMENTS
DBA_TABLESPACES
DBA_DATA_FILES
1)To view tablespaces and status
SQL>SELECT TABLE_SPACE_NAME, STATUS FROM DBA_TABLESPACES;
2)To view datafiles
SQL>SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES FROM DBA_DATA_FILES;
3)To view extents of free space in each tablespace
SQL>SELECT * FROM DBA_FREE_SPACE ORDER BY FILE_ID, BLOCK_ID;
4)To view general information about all segments in the database.
SQL>SELECT OWNER, SEGMENT_NAME, EXTENTS, MAX_EXTENTS FROM DBA_SEGMENTS ORDER BY 1, 2;
1. How to find out the Duplicate Rows in an Existing Table
(Using SQL)?
Ans=> select * from emp a
where a.rowid = (select max(b.rowid) from emp b
where b.empno = a.empno)
This will display rows which are having duplicates.
2. Modification in a Package Body, will it affect Package Spec?
Ans=> No, It will not affect the Package Specifications as long as the
parameters and names are not changed.
3. A view is created through multiple table. If you change the data in
the table of view, Will it affect the view.
Ans=> No, It will not affect the View. If the change is in the Structure of
of any table or renaming or dropping of tables will change the view
status.
4. What are type of the DB Triggers.
Ans=> There are 12 Database Triggers. These can be mainly classified into two
types such as Statement wise, and Each Row wise. and Each one will have
one Before and one After Trigger.
5. Difference between On-Error and On-Message Triggers?
Ans=> An On-Error trigger fires whenever Oracle Forms would normally cause
an error message to display.
An On-Message trigger Fires whenever Oracle Forms would normally cause
a message to display.
So the On- Error will fire only when the Error Message to be displayed
where as the On-Message will fire for all the message commands including
the error messages.
Generally an On-Message trigger is used for the following purposes:
· to trap and respond to an informative message
· to replace a standard informative message with a custom message
· to exclude an inappropriate message
where as an On-Error trigger is used for
· to trap and recover from an error
· to replace a standard error message with a custom message
6. Difference between Oracle 6.0 and Oracle 7.0
Ans=> There are lot of differences between 6 and 7
I am listing whatever on hand i have.
0) There are some new datatypes in Ver 7.0 such as
Variable Length VARCHAR2,
i) There are no Constraints such as referential Integrity, Primary Key.
ii)There are no Stored Procedures
iii)There are no Database Triggers
iv) There is no Shared pool of parsed SQL in 6.0
v) There is no Packages funda in v 6.0
vi) There are no Hinting to Optimize such as in Sql Execution
vii) In ver 6.0 only dedicated server is available whereas in ver 7.0
there are several types of servers are available such as
- Dedicated Server
- Multi Threaded Server
viii) There is no concept of Distributed Databases in ver 6.0 whereas
this is supported in ver 7.0 in the form of two phase commit.
ix) There were only Rule based optimisation in Ver 6.0 whereas in ver 7.0
both Rule Based and Cost Based Optimization is supported. In ver 7.0
By default Cost based optimization is adopted.
x) There is an Archieve Log facility in Ver 7.0
xi) Snapshots are not available in ver 6.0.
xii) There are new commands such as ANALYSE and TRUNCATE
7. How do you check 1-1 relationship existing between two tables?
Ans=> Raghu This Question is little vague, If it is to find out the Relation
ship between two Blocks in a Form, Then using the follwing command you
will know.
If you still insist on tables, You can select from the user_constraints
view to find out the Foriegn Key constraint.
8. When you create Master-Detail Blocks, What are all triggers created?
Ans=> There are three triggers will be created by default such as
On-Check-Delete-Master (Block Level)
On-Clear-Details (Form Level)
On-Populate-Details (Block Level)
and it also creates following procedures to coordinate the m/d
Check_package_failure
Clear_all_master_details
Query_master_details
9. How do you join two tables ?
Ans=> This Question is also either vague or silly?
But any way i will try to answer it.
By using the Where Clause of the SELECT Statement we can Join the Tables
Following are the types of Joins such as Equi Join, Self Join and Outer Join
10.How do you avoid using indexes?
Ans=> There are two ways by which we can avoid using indexes by Oracle.
- By Hinting the SQL Statement we can instruct Oracle to use Full Table
Scan
- By using some function or calculation in the Select Statement Oracle
will avoid using Indexes. For e.g.,
Select * from emp where 1 * empno = empno;
TELEPHONIC INTERVIEW DETAILS
Venue : ODSI
Date : 25-Sep-96
Interviewers : Molly (from OCS)
Interviewee : Shridhar Kamat
Type: Skill matrix non-technical reseller interview.
(After initial introduction…)
1) You have any problem in working anywhere in states?
A) No. I don’t mind.
2) Are you on the project or are you available immediately?
A) I am available immediately?
3) Since how long you are working on Oracle?
A) Since November 92
4) OK! That means for about 4.0 years. And in Forms 4.0?
A) About 7 months.
5) Forms 3.0?
A) About 10 months
6) Roughly how many forms you developed in 3.0 and 4.0
A) I don’t remember from top of my head right now but somewhere around 25.
7) In both?
A) No only in 4.0. Developed around another 15-20 in 3.0.
8) So roughly about 40. How about reports. You worked on both 2.0 and 1.1 right?
A) Right.
9) How many reports you developed in each of them?
A) Around 40 in reports 2.0 and 20 in 1.1.
10) How many of them were complex?
A) About 5 in 2.0 and in 1.1 most of them were simple.
11) OK so I will write about 10 in both of them. On the level of 1..5, 1 is trained, 2 is modest, 3 is average, 4 is proficient and 5 is expert., how will you rate yourself in forms 4.0?
A) I will rate myself 4.
12) In forms 3.0?
A) Forms 3.0 also 4.
13) In reports 2.0 and 1.1
A) In reports 2.0 4 and 1.1 I rate myself 3.
14) How long you are working in SQL*Plus?
A) About 4 years
15) And in PL/SQL?
A) About three and half years.
16) How you rate your skills in these Oracle Tools?
A) In SQL*Plus, PL/SQL and SQL*Loader I rate myself 4.
17) Have you worked on Pro*C?
A) Not on any live projects but I have worked on C
18) How long you are working in C?
A) About 4-5 months.
19) How long you are working in Oracle Financials?
A) For about one and half years.
20) Which all modules you worked on Oracle Financials?
A) Mainly Accounts Payable.
21) How long? For one and half years?
A) It is difficult to say but for about one year because I worked on other modules also interactively?
22) Which other modules you have worked and approximately how long?
A) In Purchasing for 2 months and General Ledger for about 2-3 months.
23) Have you worked on Accounts Receivables? Or Manufacturing like Oracle MRP or BOM?
A) No.
24) Have you worked as a DBA?
A) No.
25) Are you familiar with Analysis or Design?
A) Yes, as a matter of fact I did the table design in one of my projects in Sun Microsystems.
26) Have you worked in any interface projects?
A) Yes. In one of my projects at Starkey Laboratories I worked on interface mapping to change the data from Legacy system into oracle Financials tables with help of mapping the GL code combinations. However, all the specifications were given. I have not worked in functional part of it, I was working in the technical side, working on forms and reports and PL/SQL
27) OK! So you rate yourself as a technical and not a functional person. Have you worked on AOL?
A) Yes. I worked on AOL mainly to implement reports in Oracle Financials and creating or revoking a user or its user responsibilities.
28) How would you rate yourself in AOL?
A) About 3.
29) OK! I will get back to you or Manoj soon. Thanks!
A) Thanks a lot
Interview Transcript
Interviewer : John (Quantum Corp.)
Interviewee : R.Poominathan
Date : 09/19/96
Duration : 30 mins.
J : What is your experience on ORACLE ?
P : I have approximately 5 years experience.
J : What is the procedure that you followed in tuning ?
P : First I will find the cause of poor performance. Depends on the cause I will tune SQL,
Memory and I/O.
J : What is the first step that you do when there is a performance problem with SQL ?
P : I will generate trace file using the latest statistics to monitor the SQL execution path.
J : How will you say whether a SQL statement is well tuned ?
P : By seeing the execution path of SQL one can conclude whether a SQL is tuned.
J : How to generate output using the trace files ?
P : I use TKPROF utility to generate the formatted output from trace files.
J : What is the content of TKPROF output ?
P : It will show the execution path it followed like index scan or full scan was used for retrieving
data from a table.
J : How many years have you worked as a DBA ?
P : About 2.5 years I have worked as a DBA.
J : Give me an example where you tuned the badly performing SQL statement ?
P : I gave an example of SQL statement using internal sort/merge and how it was tuned to avoid
internal sort.
J : How do you use cost based optimizer?
P : Statistics must be generated for the objects using cost based optimizer. If it is available,
Oracle by default use the cost based optimization for finding the best execution path.
J : How do you go about tuning init.ora parameters ?
P : Monitoring the contention level of objects like database buffers, library cache, redo log buffer
over a period of time, we can alter the init.ora parameters for better performance.
J : How to identify badly written reports without using trace ?
P : Using v$sqlarea, one can monitor the performance of sql statements used in the reports.
J : Okay. Thank you. bye.
P : Okay. Thanks bye bye.
Mock Interview Transcript
Date: : 24th Sep 1996
Venue : European Operations
Interviewee : T.Geresh and Rajkumar
Interviewer : Oracle Group
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.
DBA - Interview Transcripts (telephonic)
Interviewer : Hiten (Visualtech consulting inc.)
Interviewee : R.Poominathan
Date : Sep.16,1996.
Duration : 15 mins.
H : How many years experience do you have in DBA ?
P : It is about 2.5 years.
H : What was your job as DBA ?
P : Installation of Oracle, creating logical structures, database design, formulating backup and
recovery procedure, maintaining database security by defining roles and privileges and performance tuning.
H : What is the purpose of using SQL*Net ?
P : SQL*Net is required to connect client and server in the C/S architecture. It is also used to configure multi-threaded architecture.
H : What is the use of having multi-threaded architecture ?
P : Multi-threaded architecture basically reduces the number of servers accessing ORACLE database. As one server can handle the requests of many users, number of processes accessing the database will be greatly reduced. In this way, it contributes to the over all system performance.
H : What is SGA ?
P : System Global area is a memory structure used to act as an interface for accessing the Oracle database. It contains shared pool (Library cache & Data dictionary cache), Database buffer cache, redo log buffer. It also caches rollback segment. Private SQL area is also a part of shared pool if it is a multi-threaded architecture.
H : What is the tool that used for monitoring hardware I/O ?
P : I didn’t use any such tool.
H : Have you implemented Distributed data processing system?
P : I didn’t get a chance to work on Distributed data processing system, I know how to configure Distributed databases and network access methodologies.
H : What is striped table ?
P : Striped table is one whose data is spread among more than one disks to improve access performance.
H : What are the disadvantages with striped table ?
P : If one of the disk holding table data crashes, the table cannot be accessed.
H : Have you used striped tables in your application ? Tell me other disadvantages.
P : There was no requirement for using striped tables in my application. I have no idea about other disadvantages.
H : What is the functionality of LGWR ?
P : This background process is used to copy the data from log buffer to redo log file. If CKPT is not present, it does the work of CKPT process, ie., it updates the headers of datafiles, controlfiles with the latest SCN (system change number).
H : Ok. Bye.
P : Bye bye.
TELEPHONIC INTERVIEW DETAILS
Venue : ODSI
Date : 16-Sep-96
Interviewers : Sushil Kumar (from CST)
Interviewee : Shridhar Kamat
(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
DBA - Interview transcripts (telephonic)
Staff name : Poominathan. R
Interviewer : Anil Gupte (MCI Communications Inc.)
Date : Sep.11,1996.
Duration : 25 mins.
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.
PERSONAL INTERVIEW DETAILS
Venue : Santa Clara, CA
Date : 05-Sep-96
Interviewers : Sharie Lee and Ming from Rational Software
Interviewee : Shridhar Kamat
First, I was introduced to Sharie who is the project manager.
1) Are you going to work here on a full time basis?
A) (Thinking that she meant full time or part time) Yes, but what is meant by full time basis.
2) I mean to say that are you coming here for a permanent position?
A) No, I came here as a contractor through my company ODSi.
3) Actually, this requirement is for a permanent position, but if we cannot find anybody for permanent position then I will have to convince my boss that we may have to go for contractor as we could not get anybody for permanent position and since we are in crises. Do you know about our company?
A) A very little.
4) Ok let me explain about our company in brief.
(Talked for about half an hour about the Rational Software. While concluding…)
5) We have already implemented Accounts Payable, General Ledger, Purchasing. Currently we are working on Order Entry and Accounts Receivable. Also, we will be implementing MRP and BOM in near future. However, currently the requirement is for Order Entry and Accounts Receivable. Have you worked on in any of these two?
A) No. But I have worked on Accounts Payable and General Ledger and Purchasing.. So it should not be a problem for me as earlier I was not worked on these modules either.
6) How long you are in Oracle Financials?
A) I am in Oracle Financials for about last one and half years. First I was in Honeywell working on custom purchasing reports which were developed in reports 2.0 then for Starkey labs on forms 4.0 and reports 2.0 and again for Honeywell on year end reports on report 2.0. All of which were on Oracle Financials.
7) Tell me something about your last project, about year end reports you worked at Honeywell.
A) The reports which I developed for Honeywell were all year end reports and developed in reports 2.0 like Invoices on Hold. I was the part of the IS team and we used to discus with the user about the reports. The user used to give us the layout and should they require the report urgently they used to give me the specifications also as to which table or column to pick. Otherwise, I used to find it out for myself . Then I used to design the report and implement it in the Oracle Financials with the help of AOL.
8) Here there is nobody to give you specifications. So you may have to use manuals or contact Oracle. Tell me about this report invoices on hold that you have developed.
A) Invoices on the hold report they were using to find out the total invoices which they have on hold along with the invoice amount and vendor details. It uses the tables AP_INVOICES, AP_INVOICE_DISTRIBUTIONS, PO_VENDORS, PO_VENDOR_SITES. I used to discus with the users about the requirements about their reports and used to design and then implement in AOL.
9) What all things you did in AOL?
A) Mainly I implemented the reports using AOL. I also created or disabled users and their responsibilities using the AOL.
10) Ok so mainly implementation of custom reports, menus and forms.
A) Yes, though I could not get a chance to implement menus and forms it should not be a problem as the most difficult part is the implementation of reports as it require the report parameters also.
11) Have you defined any Flexfields?
A) Yes. As one of the requirement of the last project was to print the check reconciliation report, which they used to do it on the weekly basis. One of the user used to run a SQL script by specifying the dates every week. Rather than doing this, we defined one of the attribute of AP_CHECKS as R for reconciled and V for Void. Then we run a SQL script for one time just to change the existing attribute to R or V and after that now it is running every week marking R and V automatically as it is running through automatic job schedular called Autosys.
12) Have you implemented key code combinations? Have you been in before the implementation stage any time?
A) No. I was mostly in the post implementation stage only. Though I have not implemented any key code combinations but my project at Starkey was of similar type. They had the old Legacy system which they want to convert into Oracle Financials and eventually they wanted to discontinue with the Legacy system. Initially it was decided to convert the data with SQL*Loader, since there were some systems which were still running in the Legacy system because of which one person may be involved permanently for this job, rather than we decided to develop a package with which they could change the data in the flat file format into temporary oracle tables and then used to updated in the appropriate tables after verifying the data and mapped with the key code combinations which they have already entered and validated at time of entering. However, this was not implemented in Oracle Financials because it is going to be used till they transfer all the data from Legacy system to Oracle tables.
13) Here we are also in the post implementation stage. We require someone for doing reports and assisting our users. He should have good knowledge of the Oracle Tools and oracle modules particularly in Order Entry and Accounts Receivable.
A) Yes, I am quite good in Oracle Tools such as Forms and reports 2.0 and also in SQL*Plus and PL/SQL. I am also familiar with Forms 4.5 and Reports 2.5.
14) That’s great! Ming is our technical person who is a senior DBA here. He will ask you some technical questions. You have any questions?
A) You said you have lot of projects here to be done. In case if I joined what would be your highest priority?
15) Well our priority is for Order Entry and Accounts Receivable. There are lots of reports to be finished first.
A) Which versions of forms and reports are you using right now?
16) As of now we do not want to modify Oracle Forms or Tables. But we are using forms 2.3 and SQL*Plus as reports and for some complex reports we are using Reports 2.0. But we may go for Forms 4.5 and reports 2.5 if we migrate from Oracle financials 10.5 to 10.6 in near future.
A) I am quite familiar with Forms 4.5 and Reports 2.0 though I haven’t worked on live projects.
17) That’s great! Any other questions?
A) No. Not at the moment.
18) Great I will call Ming now, he may ask you some technical questions. Thanks!
A) Thanks Sharie.
Then Sharie introduced me to Ming and she left.
1) How long you are in Financials?
A) About one and half years.
2) Have you worked on Accounts Receivable or Order Entry?
A) No. But I worked on Accounts Payable, General Ledger and Purchasing.
3) Allright. But our main requirement is for Order Entry and Receivable. Tell me about the project you have done.
A) (Explained about the Year end report project for Honeywell and Interface mapping project for Starkey)
4) Oh! We have Legacy system here and we have a similar requirement for transferring data from Legacy to Oracle tables.
A) What hardware platform you are working on?
5) We have a Sun Sparc stations here.
A) Oh! I worked for Sun Microsystems on the same platform.
6) That’s great. Did you find any difference in HP and Sun sparc.
A) I was using pentium PCs which were connected to the HP unix boxes. But I liked Sun Sparcs better.
7) Yeah! We also have some emulators which connects to the Unix system. I think I am done. Do you have any questions?
A) What would be your highest priority?
8) We have lot of things to be done in Order Entry and Accounts Receivables.
A) When do you think you can get back to me about this project?
9) Well, I have no authority. Sharie is my project manager and she will get back to you. Do you have any other questions?
A) No.
10) Ok! Thanks!
A) Thanks a lot
Interview Transcript (Telephonic)
Staff Name : Poominathan R.
Interviewer : Charles Schwab Financial Systems.
Date : August 29, 1996.
Duration : 30 mins.
C: What is your experience on handling large databases ?
P: I have handled a database of size 2GB.
C: Can you tell me the memory structure of your database ?
P : The SGA size was of 20MB. I mentioned the size of database buffer, shared pool and redo log buffer caches.
C: How do you arrive at the SGA size ?
P: It depends on various parameters such as number of concurrent transactions at the peak hours, transaction size, number of applications using the database, number of other processes using the system resources and system configuration (dedicated/multi-threaded). We have to also ensure that no paging and swapping occurs for SGA.
C: How do you find whether the allotted memory resources are sufficient ?
P: We have to look for possible contention for memeory structures. for example, if the hit ratio for database buffer cache is low, we can allot more database buffers. Likewise, we can monitor the contention for library cache, redolog buffers, redo log latches and rollback segments.
C: Can you tell me the procedure to monitor rollback contention ?
P: Well, Using V$waitstat and V$sysstat we can see the how many times a server process waited for a free extent in rollback segment. If the percentage of waiting is more than one, we can conclude that the number of rollback segments are insufficient.
C: Why is ‘OPTIMAL’ parameter used in rollback segment ?
P: OPTIMAL parameter is used to bring back the rollback segment size if it were to grow due to long transactions. To arrive at the OPTIMAL size, We can monitor using ‘MONITOR ROLLBACK’ from SQL*DBA. Using this, we can see how much space ever allocated for each rollback segment in bytes, Cumulative number of times a transaction writing from one extent in a rollback segment to another existing extent and average number of bytes in active extents in the rollback segment, measured over a period of time.
C: What was your backup strategy ?
P: Everyday I used to take hot backup which includes taking of relevent datafiles and then control file. Maintenance of archived redologs, taking of cold backup and maintaining ASCII version of a few tables data were the other backup strategies formulated.
C: Have you done recovery ?
P: Yes, I have done recovery a lot many times.
C: How do you recover if a datafile is lost/damaged ?
P: First step is to see whether the database is in archivemode. Secondly, Whether the disk containing the datafile is accessible. If not accessible, rename the datafile to another usable disk. If archived, restore the affected datafile and archived redologs from backups; if the damaged file was part of the system tablespace, keep the database mounted and closed; use alter database option to do recovery, if it belongs to a non-system tablespace, use alter tablespace command to recover it after taking the tablespace offline.
C: How to recover if the database was in noarchivemode ?
P: Only way to recover the database is to restore the most recent complete backup.
C: Have you done tuning ?
P: Yes, I have tuned both applications and database.
C: How to tune an application ?
P: First, we have to ensure that the application uses well tuned SQL statements. We can use SQL trace facility and TKPROF to see the execution plan for SQL statement. If we are not satisfied with the current execution plan, use EXPLAIN PLAN command to see the execution plan for different versions of the SQL statement. We can choose the SQL statement which gives faster response time as well as consumes less system resources.
C: OK. All right. You will get a call tomorrow. Do have any question to ask ?
P: Yes, I want to know what will be my role in your project.
M: The system is under development. You will be involved at this stage itself. It is expected to go to production in Jan’97.
No comments:
Post a Comment