Oracle DBA Job Interview Questions

 

  1. Give Methods of transferring a table from one schema to another.

Export/import, CREATE TABLE … AS SELECT, COPY, etc.

 

  1. What is the purpose of the IMPORT option IGNORE? What is the default setting?

The IMPORT IGNORE option tells import to ignore already exists errors. If it is not specified the already existing tables will be skipped. The default is N.

 

  1. You have a rollback segment in Oracle8 database that has expanded beyond optimal. How can it be restored to optimal.

Use the rollback segment … shrink command.

 

  1. If the DEFAULT and TEMPORARY tablespace clauses are left out of the create user statement in Oracle8i what happens? Is this good or bad? Why?

The user is assigned to the SYSTEM tablespace as a default and temporary tablespace. This is bad because no user object should be in SYSTEM.

 

  1. What are the some of the Oracle provided packages that DBA should be aware of?

Owned by the SYS user: dbms_shared_pool, dbms_utility, dbms_sql, dbms_ddl, dbms_session, dbms_output, dbms_snapshot. Also CAT*.SQL and UTL*.SQL.

 

  1. What happens if constraint name is left out of the constraint clause?

The Oracle will use the default name of SYS_Cxxxx where xxx is a system generated number. Hard to track.

 

  1. What happens if a tablespace clause is left off a primary key constraint clause?

This results in the index that is automatically generated being placed in the user’s default tablespace. Since this will be in the same tablespace as the table, this will cause serious performance problems.

 

8. What is the proper method for disabling and re-enabling a primary key constraint?

You can use ALTER TABLE for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause.

 

9. What happens if a primary key constraint is disabled and then re-enabled without specifying the index clause?

The index is created in the user’s default tablespace and all sizing information is lost.

 

10. When should more than one DN writer be used? How many?

If the UNIX system is capable of asynchronous I/O, then only one is required. If the UNIX system is incapable of asynchronous I/O, then up to twice the number of disks used by Oracle or twice the number of CPUs.

 

11. You are using a hot backup without being in ARCHIVELOG mode. Can you recover in the event of failure? Why not?

You can’t use a hot backup without being in ARCHICVELOG mode.

 

12. What causes the “Snapshot too old error”? How can this be prevented or mitigated?

This is caused by large or long running transactions that are either wrapped onto their own rollback space or had another transaction write on part of their rollback space. This can be mitigated by breaking the transactions into smaller pieces and running them separately or increasing the size of the rollback segments and their extents.

 

13. How can you tell if a database object is invalid?

By checking the status column in the DBA_OBJECTS or ALL_ and USER_ views.

 

14. If a user gets the ORA-00942 error, yet you know you granted them the privilege, what else should you check?

Check if the user specified the full name of the object (schema), or has synonym pointing to that object.

 

15. A developer is trying to create a view but the database will not let him. He has the DEVELOPER role which has the CREATE VIEW system privilege and the selects on the table he is using. What is the problem?

You need to verify that the developer has direct grants on the tables on which the view is based. You can not create a stored object based with grants given through views.

 

16. If you are using an example table what is the best way to get sizing data for the production table implementation?

The best way is to analyze the table and then use the data provided in the dba_tables view to get the average row length and other data for the calculation. The quick and the dirty way is to look at the number of blocks the table is actually using and ratio the number of rows to its number of blocks against expected number of rows.

 

17. How can you tell how many users are currently logged into the database? How can you find their system ID?

Query V$SESSION and V$PROCESS. The other is to check the current_logins parameter in the V$SYSSTAT. Another way on UNIX is to do a “ps –ef|grep oracle| wc –l” command, but this works only against a single instance installation.

 

18. The user selects from a sequence and gets back 2 values, his select is SELECT pk_seq.nextval FROM dual; What is the problem?

Somehow two rows have been inserted into DUAL.

 

19. How do you determine if an index has to be dropped or rebuilt?

Run the analyze index to validate structure and then calculate the ratio of LF_ROWS_LEN / LF_ROWS_LEN + DEL_LF_ROWS_LEN and if it is not at least 70% the index should be rebuilt. Or id the ratio of DEL_LF_ROWS_LEN / LF_ROWS_LEN + DEL_LF_ROWS_LEN is nearing 30%.

 

 

 

SQL * PLUS and SQL Job Questions

 

  1. How can you pass variables into a SQL routine?

By the use of & or && symbol. For passing in variable numbers can be used &1, &2. To be prompted for a specific variable, place ampersand variable into the code itself: SELECT * FROM dba_tables WHERE owner = &owner_name; The use of double ampersand tells Oracle to reuse the variable for subsequent times, unless ACCEPT is used to get value from the user.

 

  1. You want to include carriage return / linefeed into your output from a SQL script. How can you do this?

The best way is to use the CHR() function (CHR(10) as a return / linefeed and the concatenation function. Another method is to use the return / linefeed as a part of a quoted string.

 

  1. How do you call a PL/SQL procedure in SQL?

By using EXECUTE or wrap the call in a BEGIN END block and treat it as an anonymous block.

 

  1. How do you execute a host OS command from within SQL?

By using “!” or”HOST” command.

 

  1. You want to use SQL to generate SQL. What is it called and give an example.

This is called dynamic SQL. An example:

“Set lines 90

Pages 0

Termout off

Feedback off

Verify off

Spool drop_all.sql

SELECT ‘drop user ‘||username||’ cascade;’ from dba_users

Where username not in (“SYS”, “SYSTEM”);

Spool off;

 

  1. What SQL * PLUS command is used to format output from a select?

This is done with the COLUMN.

 

  1. You want to group the following set of select returns, what can you group on?

MAX(sum_of_cost), MIN(sum_of_cost), COUNT(item_no)?

The only column you can group by is the COUNT(item_no), the rest are aggregate functions.

 

  1. What special Oracle feature allows you to specify how the CBO treats SQL functions?

You can use hints – FIRST ROWS, ALL_ROWS, RULE, USING INDEX, STAR.

 

  1. You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?

If you use MIN / MAX function against your ROWID, then select against the proposed primary key you can squeeze out the ROWID of duplicate rows quickly.

SELECT ROWID FROM emp e

WHERE e.ROWID > (SELECT MIN(x.ROWID)

FROM emp x

WHERE x.emp_n0 = e.emp_no);

In a situation if multiple columns make up the proposed key, they all must be used in the WHERE clause.

 

  1. What is a Cartesian product?

A Cartesian product is a result of an unrestricted of 2 or more tables.

 

  1. You are joining a local and remote table and the network manager complains about network traffic involved. Hodo you reduce the amount of traffic?

Push the processing of the remote data to the remote server by using a view to preselect information for the join. This will result in only data needed for the join being sent across the network.

 

  1. What is the default ordering in the ORDER BY statement?

Ascending

 

  1. What is TKPROF and how is it used?

The TKPROF is a tuning tool used to determine the execution time for SQL statements. Use it first by setting TIMED_STATISTICS parameter to TRUE and then setting the entire instance SQL_TRACE to on or just for the session with an ALTER SESSION command. Once that is done you run TKPROF and generate a readable report with an explain plan.

 

  1. What is EXPLAIN plan and how is it used?

The EXPLAIN plan is used to tune SQL statements. You have to have the EXPLAIN_TABLE generated for the user you are generating the explain plan for. This is done with the utlxplan.sql. Once the EXPLAIN_TABLE exists, you run the explain command with the statement to be explained. The explain table then is queried to see the execution plan.

 

  1. How do you set the number of lines per page? The width?

The SET command in SQL * PLUS is used to control the number of lines generated per pager and the width of those lines. For example SET PAGESIZE 60 LINESIZE 80 will generate reports that are 60 lines long with a line width of 80 characters.

 

  1. How do you prevent output from coming to the screen?

The SET option of TERMOUT controls output to the screen. Setting TERMOUT OFF turns off the screen output.

 

  1. How do you prevent Oracle from giving you informational messages during and after a SQL statement execution?

The SET option FEEDBACK and VERIFY can be set to OFF.

 

  1. How do you generate file output from SQL?

By use of the SPOOL command.

 

 

 

Oracle Performance Tuning Job Interview Questions

 

  1. A tablespace has a table with 300 extents in it. Is this bad? Why or why not?

Multiple extents are not bad. However, if you also have chained rows, this can hurt performance.

 

  1. How do you set up tablespaces during an Oracle installation?

You should always use OFA. For the best results SYSTEM, ROLLBACK, UNDO, TEMPORARY, INDEX and DATA segments should be separated.

 

  1. You use multiple fragments in the SYSTEM tablespace. What should you check first?

Ensure that users don’t have SYSTEM tablespace as their default and or temporary tablespace by checking DBA_USERS.

 

  1. What are the indications that you need to increase or decrease the shared_pool size parameter?

Poor data dictionary or library cache hit ratios or getting ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters are the same.

 

  1. What are the general guidelines for sizing DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT for an application that does many table scans?

OS almost always reads in 64K chunks. The two should have a product of = 64K, a multiple of 64K, or the value for read size from your OS.

 

  1. What is the fastest query method of a table in the RULE based optimizer?

Fetch by ROWID.

 

  1. Explain the use of TKPROF. What OS parameter should be set to get full TKPROFF output?

The TKPROF is a tuning tool used to determine the execution time for SQL statements. Use it first by setting TIMED_STATISTICS parameter to TRUE and then setting the entire instance SQL_TRACE to on or just for the session with an ALTER SESSION command. Once that is done you run TKPROF and generate a readable report with an explain plan.

 

  1. When looking at V$SYSSTAT you see that sorts (disk) is high. Is that bad or good? If bad – how do you correct it?

If you get excessive disk sorts this is bad. This means you need to tune the sorts area parameters in the init file – SORT_AREA_SIZe is the major one.

 

  1. When should you increase copy latches? What parameter controls copy latches?

When you get excessive contention of the copy latches as shown by the redo copy latch hit ratio you can increase copy latches via the init parameter log simultenious_copies to twice the number of CPUs on your system.

 

  1. Where can you get a list of all initialization parameters for your system? How about if they are the default settings or have been changed?

You can look in the init.ora file or the V$PARAMETER view.

 

  1. Describe hit ratios as pertains to the database buffers. What is the difference between an instantaneous and cumulative hit ratio? Which one should you use for tuning?

A hit ratio is measurement of how many times the database was able to read the value from the buffers, instead of disk. A value of 80%-90% is good. If you simply take the ratio of existing parameters, they will be applicable to since the instance started. If you do a comparison of readings based on some 2 arbitrary time spans, this is the instantaneous ratio for that time span (more valuable)

 

  1. Discuss row chaining. How does it happen? How do you correct it?

The row chaining happens when a variable length value is updated and the new value is longer than the old value and will not fit into remaining block space. This results in row chaining to another block. You can correct this by setting appropriate values for the table storage clause (PCTFREE). This can be corrected be exporting and importing the table.

 

  1. When looking at the estat events report you see that you are getting buffer busy waits. Is this bad/ How can you find what is causing it?

Buffer busy wait can indicate contention in redo rollback or data blocks. You need to check the V$WAITSTAT to see what areas are causing the problem. The value of count tells you where the problem is, class tells you with what.

 

  1. If you see contention for library caches how can you fix it?

Increase the size of the shared pool.

 

  1. If you see statistics that deal with UNDO, what are they really talking about?

Rollback segments associated with structures.

 

  1. If a tablespace has a default PCTINCREASE of 0, what will it cause (in relation to SMON).

The SMON will not automatically coalesce its free space fragments.

 

  1. If a tablespace shows excessive fragmentation, what are the methods to de-fragment the tablespace?

In Oracle 7.0 and 7.2 use the ‘ALTER SESSION SET EVENTS IMMEDIATE TRACE NAME COALESCE LEVEL ts#’ command is the easiest way to defragment the space. The ts# is in the ts$ table owned by SYS. In version 7.3 alter tablespace <> coalesce;  If the free space is not contiguous, export, drop and import the tablespace.

 

  1. How can you tell if the tablespace has excessive fragmentation?

If a select against DBA_FREE_SPACE shows that the count of tablespace’s extents is greater than the count of its datafiles, then it is fragmented.

 

  1. You see the following on your status report: redo log space requests – 23, redo log space wait time – 0. Is this something to worry about? What is the redo log wait time is high? How can you fix this?

Since the wait time is zero – no. If the wait time is high it might indicate a need for more or larger redo logs.

 

  1. What can cause a high value for recursive calls? How can this be fixed?

The high value of recursive calls is caused by improper usage of cursors, extensive dynamic space management actions, and excessive statements re-parses. You need to determine cause and correct it by either relinking applications to hold cursors or use proper space management techniques (proper storage and sizing) to ensure repeated queries are placed in the packages for proper use.

 

  1. If you see a pin hit ratio of less than 0.8 in the estat library report – is this a problem? If so, how do you resolve it?

This indicates that shared pool size is too small. Increase the size of shared pool.

 

  1. If you see a high value for reloads in estat library cache report, is this a matter for concern?

You should strive for zero reloads if possible. If you see excessive reloads – increase the size of shared pool.

 

  1. You look at the DBA_ROLLBACK_SEGS and see that there is a large number of shrinks and they are relatively small in size. Is this a problem? How can this be fixed?

The large number of small shrinks indicates the need to increase the size of extents of rollback segments. Ideally, you should have no shrinks or small number of large shrinks. To alleviate this just increase the size of extents and adjust optimal accordingly.

 

  1. You look at the DBA_ROLLBACK_SEGS and see a large number of wraps. Is this a problem?

A large number of wraps indicates that rollback segment extent size is too small. Increase the size of your extents. You can look at an average transaction size in the same view.

 

  1. You have a room to grow extents by 20%. Is there a problem? Should you take any action?

No, it is not a problem. You have 40 extents showing and an average of 40 users. Since there is plenty of room to grow there is no problem.

 

  1. You see multiple extents in the temporary tablespace. Is this good or bad?

As long as they are all the same size it is not a problem.

 

 

 

Oracle Installation and Configuration Job Interview Questions

 

 

  1. Define OFA.

OFA is Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system for future tuning and file placement.

 

  1. How do you set up tablespaces on installation?

At least 7 disks arrays: SYSTEM on one, two mirrored redo logs on different disks, TEMPORARY tablespace on one, ROLLBACK tablespace on another, and still have 2 disks for data and indexes.

 

  1. What should be done prior to installation of Oracle?

Adjust kernel parameters and disk space.

 

  1. You have installed Oracle and now setting up the actual instance. You have been waiting for an hour for the initialization script to finish. What should you check first?

Check to make sure that the archiver is not stuck. If archive logging is turned on during install, this will generate an enormous amount of archived log space. If the space is full, Oracle will stop and wait until you free more space.

 

  1. When configuring SQL * NET on the server what files should be set up?

Initialization files SQLNET.ORA, TNSNAMES.ORA and LISTENER.ORA.

 

  1. When configuring SQLNET on the client what files need to be set up?

SQLNET.ORA, TNSNAMES.ORA.

 

  1. What must be installed with ODBC on the client in order to work with Oracle?

SQLNET and protocol – TCP/IP, etc.

 

  1. You have started a new instance with a large SGA on a busy existing server. The performance is terrible and the users are complaining. What should you check for first?

Check if the large SGA is not being swapped out.

 

  1. What OS user should be first set up on UNIX as an Oracle user?

You must use root first, then create DBA and OINSTALL roles.

 

  1. When should default Oracle parameter be used?

Never.

 

  1. How many control files should you have? Where should they be located?

At least 2 on separate disks, not just 2 file systems.

 

  1. How many redo logs should you have and how should they be configured for maximum recoverability?

At least 2 are required. The OFA specifies at least 3 groups with at least 2 members in each. The logs files should be on 2 different disks mirrored by Oracle. The files should not be on raw devices on UNIX.

 

  1. You have a simple application with no “hot” tables (uniform I/O and access requirements). How many disk arrays should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces?

At least 7 disks. See above.

 

 

 

 

Oracle Data Modeling Job Interview Questions

 

  1. Describe the third normal form

All attributes in an entity relate to the primary key and only primary key.

 

  1. Is this statement TRUE or FALSE – all databases must be in third normal form?

FALSE. While the 3d normal form is good for logical design, most databases will not perform well under 3NF. Usually, some entity will be denormalized in the logical to physical transfer process.

 

  1. What is ERD?

ERD stands for Entity relational Diagram.

 

  1. Why are recursive relationships are bad? How do you resolve them?

Recursive relationships (when tables are related to themselves) is bad when it is a hard relationship (neither side is “may” both sides are a “must” as this results in it not being possible to put on either bottom or top of the table. You resolve them by using an intersection entity.

 

  1. What does a hard one-to-one relationship mean (when it has to be “must” on both ends)?

This means that the two entities should probably be made one. 

 

  1. How is the many-to-many relationship be handled?

By adding an intersection entity table.

 

  1. What is an artificial (derived) primary key? When should it be used?

A derived key comes from a sequence. Usually, it is used when a concatenated index becomes too cumbersome to use as a foreign key.

 

  1. When should you consider denormalization?

When performance analysis indicates it will be beneficial without compromising data integrity?

 

 

 

 

Oracle Troubleshooting Job Interview Questions

 

 

  1. How can you determine if an Oracle system is up from an OS level?

The following processes will be running – smon, pmon, dbwr, lgwr. Use the “ps –ef| grep dbwr” will show what instances are up.

 

  1. Users from the PC client are getting messages: “ORA-06114: (Cnct errcan’t get err txt. See Servr Msg and Codes Manual). What could be the problem?

The instance name is probably incorrect in their connection string.

 

  1. Users from the PC clients are getting the following stack: “ERROR: ORA-01034: ORACLE not available”. What is the problem?

The Oracle instance is shut down. Restart the instance.

 

  1. How can you determine if the SQL * NET process is running for SQL * NET V1? How about V2 for NET8?

For SQL * NET V1 check for the orapsv process. You can use TCPCTL STATUS to get the full status of SQL * NET server. For other protocols check the existence of LISTENER process or you can use lsnrctl status

 

  1. What file will give you Oracle instance status information? Where is it located?

The alert<SID>.log file located in the BACKGROUND_DUMP_DEST from V$PARAMETER table.

 

  1. Users are not being allowed in the system – “ORA-00257 archiver is stuck. Connect internal only until freed” What is the problem?

The archiver destination is probably full. Backup the archive logs and remove them and archiver will restart.

 

  1. Where should you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs?

You must check the alert<>.log for this info.

 

  1. You attempt to add a datafile and get: “ORA-01118: cannot add more datafiles: limit of 40 exceeded”?

When the database was created the DB_FILES parameter was set to 40. You can specify a higher value up to the MAX_DATAFILES in the control files. You will have to rebuild the control file to set the value higher than MAX_DATAFILES.

 

  1. You look at the fragmentation report and see that SMON has not coalesced any of your tablespaces. What is the problem?

Check the DBA_TABLESPACES for the value of PCT_INCREASE for the tablespaces. If the PCT_INCREASE is 0 SMON will not coalesce their free extents.

 

  1. Your users get the following error: “ORA-00055 maximum number of DML locks exceeded.” What is the problem and how do you fix it?

This is set with the parameter DML_LOCKS. If the value is set too low (default) you will get this error. If you think it is a temporary problem, you can wait till it clears.

 

  1. You get a call from your backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE. What do you do?

As long as the datafiles are OK and he was successful with the backup controlfile command, you can use the following:

CONNECT INTERNAL

STARTUP MOUNT

(Take read only tablespaces offline before next step ALTER DATABASE DATAFILE <> OFFLINE;)

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE RESETLOGS;

(Bring read only tablespaces online)

Shutdown, backup the syste, then restart.

If no controlfile backup is avialbale, then the following is required:

CONNECT INTERNAL

STARTUP MOUNT

CREATE CONTROLFILE …;

However, they will need to know all datafiles, logfiles and settings for the MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database.

 

 

 

 

1