Oracle Interview Questions – Part 38
Q.How would you go about generating an EXPLAIN plan?
Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = ‘tst1’ into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql
Q.How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
Q.What view would you use to determine free space in a tablespace?
Q.What is the difference between the SQL*Loader and IMPORT utilities?
SQL*LOADER loads external data which is in OS files to oracle database tables while IMPORT utility imports data only which is exported by EXPORT utility of oracle database.
Q.You have a rollback segment in a version 7.2 database that has expanded beyond optimal, how can it be restored to optimal
Use the ALTER TABLESPACE ….. SHRINK command.
Q.If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why
The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement (only data dictionary objects and the system rollback segment should be in SYSTEM).
Q.If you have 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 pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows.
Q.How can you find out how many users are currently logged into the database? How can you find their operating system id
There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a “ps -ef|grep oracle|wc -l? command, but this only works against a single instance installation.
Q.How do you prevent Oracle from giving you informational messages during and after a SQL statement execution
The SET options FEEDBACK and VERIFY can be set to OFF.
Q.How do you generate file output from SQL
By use of the SPOOL comm
Q.How will you delete duplicating rows from a base table
DELETE FROM table_name A WHERE rowid>(SELECT min(rowid) from table_name B where B.table_no=A.table_no);
CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;
DROP old_table RENAME new_table TO old_table DELETE FROM table_name A WHERE rowid NOT IN (SELECT MAX(ROWID) FROM table_name GROUP BY column_name)
Q.What is difference between SUBSTR and INSTR
SUBSTR returns a specified portion of a string eg SUBSTR(‘BCDEF’,4) output BCDE INSTR provides character position in which a pattern is found in a string.
eg INSTR(‘ABC-DC-F’,’-‘,2) output 7 (2nd occurence of ‘-‘)
Q.What a SELECT FOR UPDATE cursor represent.
SELECT……FROM……FOR……UPDATE[OF column-reference][NOWAIT] The processing done in a fetch loop modifies the rows that have been retrieved by the cursor. A convenient way of modifying the rows is done by a method with two parts: the FOR UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an UPDATE or declaration statement.
Q.What ‘WHERE CURRENT OF ‘ clause does in a cursor.
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
WHERE CURRENT OF X;
Q.If you see the value for reloads is high in the estat library cache report is this a matter for concern
Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.
Q.You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem
A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.
Q.What is an ERD
An ERD is an Entity-Relationship-Diagram. It is used to show the entities and relationships for a database logical model.
Q.Why are recursive relationships bad? How do you resolve them
A recursive relationship (one where a table relates to itself) is bad when it is a hard relationship (i.e. neither side is a “may” both are “must”) as this can result in it not being possible to put in a top or perhaps a bottom of the table (for example in the EMPLOYEE table you couldn?t put in the PRESIDENT of the company because he has no boss, or the junior janitor because he has no subordinates). These type of relationships are usually resolved by adding a small intersection entity.
Q.How would you determine the time zone under which a database was operating?
SELECT dbtimezone FROM DUAL;
Q.Explain the use of setting GLOBAL_NAMES equal to TRUE.
It ensure the use of consistent naming conventions for databases and links in a networked environment.
Q.How do you set up tablespaces during an Oracle installation?
You should always attempt to use the Oracle Flexible Architecture standard or another partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK, REDO LOG, DATA, TEMPORARY and INDEX segments.
Q.You see multiple fragments in the SYSTEM tablespace, what should you check first?
Ensure that users don?t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view.
Q.What SQLPlus command is used to format output from a select
This is best done with the COLUMN command.
Q.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), item_no The only column that can be grouped on is the “item_no” column, the rest have aggregate functions associated with them.
Q.What is a view
A view is stored procedure based on one or more tables, its a virtual table.
Q.What are various privileges that a user can grant to another user
SELECT CONNECT RESOURCE
Q.Difference between an implicit & an explicit cursor.
PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.
Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR…IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.
Q.What are cursor attributes
%ROWCOUNT %NOTFOUND %FOUND %ISOPEN
Q.If you see contention for library caches how can you fix it
Increase the size of the shared pool.
Q.If you see statistics that deal with “undo” what are they really talking about
Rollback segments and associated structures.
Q.What command would you use to encrypt a PL/SQL application? WRAP
Q.Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
They are all named PL/SQL blocks.
Function must return a value. Can be called inside a query.
Procedure may or may not return value.
Package is the collection of functions, procedures, variables which can be logically grouped together.
Q.A tablespace has a table with 30 extents in it. Is this bad? Why or why not.
Multiple extents in and of themselves aren?t bad. However if you also have chained rows this can hurt performance.
Q.What is the fastest query method for a table
Fetch by rowid
Q.A user selects from a sequence and gets back two values, his select is: SELECT pk_seq.nextval FROM dual;What is the problem
Somehow two values have been inserted into the dual table. This table is a single row, single column table that should only have one value in it.
Q.How do you execute a host operating system command from within SQL
By use of the exclamation ball “!” (in UNIX and some other OS) or the HOST (HO) command.
Q.What is difference between UNIQUE and PRIMARY KEY constraints
A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.
Q.Can a primary key contain more than one columns
Q.Difference between NO DATA FOUND and %NOTFOUND
NO DATA FOUND is an exception raised only for the SELECT….INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.
Q.What are different modes of parameters used in functions and procedures.
IN OUT INOUT
Q.Can you use a commit statement within a database trigger.
Q.What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
Q.If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)
The SMON process won?t automatically coalesce its free space fragments.
Q.You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem
A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.
Q.What is an artificial (derived) primary key? When should an artificial (or derived) primary key be used
A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.
Q.When should you consider denormalization
Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
Q.What does coalescing a tablespace do?
Coalesce simply takes contigous free extents and makes them into a single bigger free extent.
Q.What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
TEMP tablespace gets cleared once the transaction is done where as PERMANENT tablespace retails the data.
Q.What are some of the Oracle provided packages that DBAs should be aware of
Oracle provides a number of packages in the form of the DBMS_ packages owned by the SYS user. The packages used by DBAs may include: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT. They may also try to answer with the UTL*.SQL or CAT*.SQL series of SQL procedures. These can be viewed as extra credit but aren?t part of the answer.
Q.What happens if the constraint name is left out of a constraint clause
The Oracle system will use the default name of SYS_Cxxxx where xxxx is a system generated number. This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder.
Q.Can a function take OUT parameters.If not why.
Yes. A function return a value, but can also have one or more OUT parameters. it is best practice, however to use a procedure rather than a function if you have multiple values to return.
Q.What is OCI. What are its uses.
Oracle Call Interface is a method of accesing database from a 3GL program. Uses–No precompiler is required,PL/SQL blocks are executed like other DML statements.
The OCI library provides
-functions to parse SQL statemets
-bind input variables
-bind output variables
-fetch the results
Q.In a system with an average of 40 concurrent users you get the following from a query on rollback extents:
ROLLBACK CUR EXTENTS
R01 11 R02 8 R03 12 R04 9 SYSTEM 4
OFA stands for Optimal Flexible Architecture. It is a method of placing directories and files in an Oracle system so that you get the maximum flexibility for future tuning and file placement.
Q.Users from the PC clients are getting the following error stack: ERROR: ORA-01034: ORACLE not available ORA-07318: smsget: open error when opening sgadef.dbf file. HP-UX Error: 2: No such file or directory
What is the probable cause The Oracle instance is shutdown that they are trying to access, restart the instance.
Q.You look at your fragmentation report and see that smon hasn?t coalesced any of you tablespaces, even though you know several have large chunks of contiguous free extents. What is the problem
Check the dba_tablespaces view for the value of pct_increase for the tablespaces. If pct_increase is zero, smon will not coalesce their free space.
Q.What OS user should be used for the first part of an Oracle installation (on UNIX)
You must use root first.
Q.When should the default values for Oracle initialization parameters be used as is