Oracle Interview Questions – Part 37
Q.What is Row Migration and Row Chaining?
Row Migration refers to rows that were moved to another blocks due to an update making them too large to fit into their original blocks.
Oracle will leave a forwarding pointer in the original block so indexes will still be able to “find” the row. Note that Oracle does not discriminate between chained and migrated rows, even though they have different causes. A chained row is a row that is too large to fit into a single database data block.
For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces.
Some conditions that will cause row chaining are:
•Tables whose row size exceeds the blocksize
•Tables with long and long raw columns are prone to having chained rows
•Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.
Detecting row chaining:
This query will show how many chained (and migrated) rows each table has:
SQL>SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;
To see which rows are chained:
SQL>ANALYZE TABLE tablename LIST CHAINED ROWS;
This will put the rows into the INVALID_ROWS table which is created by the utlvalid.sql script (located in $ORACLE_HOME/rdbms/admin).
Q.What is ORA-01555 – Snapshot Too Old error and how do you avoid it?
The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 11:30 AM, Oracle ensures that all rows are as they appeared at 11:30 AM, even if the query runs until noon!
Oracles does this by reading the “before image” of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear. ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:
ORA-01555: snapshot too old: rollback segment number string with name “string” too small
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.
You can get an ORA-01555 error with a too-small undo_retention, even with a large undo tables.
However, you can set a super-high value for undo_retention and still get an ORA-01555 error.
The ORA-01555 snapshot too old error can be addressed by several remedies:
•Re-schedule long-running queries when the system has less DML load
•Increasing the size of your rollback segment (undo) size
•The ORA-01555 snapshot too old also relates to your setting for automatic undo retention
•Don’t fetch between commits
Q.What type of Indexes are available in Oracle?
There are many index types within Oracle:
B*Tree Indexes – common indexes in Oracle. They are similar construct to a binary tree, they provide fast access by key, to an individual row or range of rows, normally requiring very few reads to find the correct row.
The B*Tree index has several subtypes:
•Index Organised Tables – A table stored in a B*Tree structure
•B*Tree Cluster Indexes – They are used to index the cluster keys
•Reverse Key Indexes – The bytes in the key are reversed. This is used to stop sequential keys being on the same block like 999001, 999002, 999003 would be reversed to 100999, 200999, 300999 thus these would be located on different blocks.
•Descending Indexes – They allow data to be sorted from big to small (descending) instead of small to big (ascending).
Bitmap Indexes – With a bitmap index , a single index entry uses a bitmap to point to many rows simultaneously, they are used with low data that is mostly read-only. Schould be avoided in OLTP systems.
Function Based Indexes – These are B*Tree or bitmap indexes that store the computed result of a function on a row(s) (for example sorted results)- not the column data itself.
Application Domain Indexes – These are indexes you build and store yuorself, either in Oracle or outside of Oracle
interMedia Text Indexes – This is a specialised index built into Oracle to allow for keyword searching of large bodies of text.
Q.Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will produce rollback data and thus take longer to complete.
Q.Give the reasoning behind using an index.
Faster access to data blocks in a table.
Q.Give some examples of the types of database contraints you may find in Oracle and indicate their purpose.
•A Primary or Unique Key can be used to enforce uniqueness on one or more columns.
•A Referential Integrity Contraint can be used to enforce a Foreign Key relationship between two tables.
•A Not Null constraint – to ensure a value is entered in a column
•A Value Constraint – to check a column value against a specific set of values.
Q.What command would you use to create a backup control file?
Alter database backup control file to trace.
Q.What background process refreshes materialized views?
Job Queue Process (CJQ)
Q.How would you determine what sessions are connected and what resources they are waiting for?
Q.Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
Q.Describe the difference between a procedure, function and anonymous pl/sql block. Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn’t have to.
Q.In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?
OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.
Q.What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
Q.Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
Q.Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed
You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.
Q.What happens if a tablespace clause is left off of a primary key constraint clause
This results in the index that is automatically generated being placed in then users default tablespace. Since this will usually be the same tablespace as the table is being created in, this can cause serious performance problems.
Q.What is the proper method for disabling and re-enabling a primary key constraint
You use the ALTER TABLE command for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause for primary keys.
Q.You want to include a carriage return/linefeed in your output from a SQL script, how can you do this
The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function “||”. Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.
Q.How can you call a PL/SQL procedure from SQL
By use of the EXECUTE (short form EXEC) command.
Q.What is a CO-RELATED SUBQUERY
A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier of a search condition in the WHERE clause of the subquery. eg
SELECT field1 from table1 X
WHERE field2>(select avg(field2) from table1 Y
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)
Q.What are various joins used while writing SUBQUERIES
Self join-Its a join foreign key of a table references the same table.
Outer Join–Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.
Equi-join–Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.
Q.There is a string ‘120000 12 0 .125′ ,how you will find the position of the decimal place
INSTR(‘120000 12 0 .125′,’.’,1) output 13
Q.There is a ‘%’ sign in one field of a column. What will be the query to find it.
” Should be used before ‘%’.
Q.What is a pseudo column. Give some examples
It is a column that is not an actual column in the table.
eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.
Suppose customer table is there having different columns like customer no, payments.What will be the query to select top three max payments.
Q.What is the purpose of a cluster.
Oracle does not allow a user to specifically locate tables, since that is a part of the function of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer to create a CLUSTER. A CLUSTER provides a means for storing data from different tables together for faster retrieval than if the table placement were left to the RDBMS.
Q.How you open and close a cursor variable.Why it is required.
OPEN cursor variable FOR SELECT…Statement CLOSE cursor variable In order to associate a cursor variable with a particular SELECT statement OPEN syntax is used.In order to free the resources used for the query CLOSE statement is used.
Q.How you were passing cursor variables in PL/SQL 2.2.
In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2,the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter.
Q.What is syntax for dropping a procedure and a function .Are these operations possible.
Drop Procedure procedure_name
Drop Function function_name
Q.What are ORACLE PRECOMPILERS.
Using ORACLE PRECOMPILERS ,SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as Pro*C,Pro*Cobol,… This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to creater an executable.
Q.When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it
Buffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the “count” column tells where the problem is, the “class” column tells you with what. UNDO is rollback segments, DATA is data base buffers.
Q.Give the two types of tables involved in producing a star schema and
the type of data they hold.
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Q.What type of index should you use on a fact table?
A Bitmap index.
Q.When a user process fails, what background process cleans up after it?
Q.How would you force a log switch?
alter system switch logfile;
Q.How would you determine who has added a row to a table?
By implementing an INSERT trigger for logging details during each INSERT operation on the table
Q.How can you enable a trace for a session?
alter session set sql_trace=’TRUE';
Q.Describe the use of %ROWTYPE and %TYPE in PL/SQL
%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.
Q.What packages (if any) has Oracle provided for use by developers?
Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE. If they can mention a few of these and describe how they used them, even better. If they include the SQL routines provided by Oracle, great, but not really what was asked.
Q.What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.
Q.What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?
Oracle almost always reads in 64k chunks. The two should have a product equal to 64 or a multiple of 64.
Q.You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not
You can’t use hot backup without being in archivelog mode. So no, you couldn?t recover.
Q.What causes the “snapshot too old” error? How can this be prevented or mitigated
This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.
Q.What are different Oracle database objects
TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES etc
Q.What is difference between Rename and Alias
Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.
Q.When you use WHERE clause and when you use HAVING clause
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.
Q.How you will avoid your query from using indexes
SELECT * FROM emp Where emp_no+’ ‘=12345;
i.e you have to concatenate the column name with space within codes in the where condition.
SELECT /*+ FULL(a) */ ename, emp_no from emp where emp_no=1234;
i.e using HINTS
Q.Can cursor variables be stored in PL/SQL tables.If yes how.If not why.
No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.
Q.Difference between procedure and function.
Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.
Q.If a tablespace shows excessive fragmentation what are some methods to defragment the tablespace? (7.1,7.2 and 7.3 only)
In Oracle 7.0 to 7.2 The use of the ‘alter session set events ‘immediate trace name coalesce level ts#';? command is the easiest way to defragment contiguous free space fragmentation. The ts# parameter corresponds to the ts# value found in the ts$ SYS table. In version 7.3 the ?alter tablespace coalesce;? is best. If the free space isn?t contiguous then export, drop and import of the tablespace contents may be the only way to reclaim non-contiguous free space.
Q.How can you tell if a tablespace has excessive fragmentation
If a select against the dba_free_space table shows that the count of a tablespaces extents is greater than the count of its data files, then it is fragmented.
Q.How many control files should you have? Where should they be located
At least 2 on separate disk spindles. Be sure they say on separate disks, not just file systems.
Q.How many redo logs should you have and how should they be configured for maximum recoverability
You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.
Q.How can you determine if the SQLNET process is running for SQLNET V1? How about V2
For SQLNET V1 check for the existence of the orasrv process. You can use the command “tcpctl status” to get a full status of the V1 TCPIP server, other protocols have similar command formats. For SQLNET V2 check for the presence of the LISTENER process(s) or you can issue the command “lsnrctl status”.
Q.What file will give you Oracle instance status information? Where is it located
The alert.ora log. It is located in the directory specified by the background_dump_dest parameter in the v$parameter table.
Q.You have installed Oracle and you are now setting up the actual instance. You have been waiting an hour for the initialization script to finish, what should you check first to determine if there is a problem
Check to make sure that the archiver isn?t stuck. If archive logging is turned on during install a large number of logs will be created. This can fill up your archive log destination causing Oracle to stop to wait for more space.
Q.When configuring SQLNET on the server what files must be set up
INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file
Q.Give the stages of instance startup to a usable state where normal users may access it.
STARTUP NOMOUNT – Instance startup
STARTUP MOUNT – The database is mounted
STARTUP OPEN – The database is opened
Q.What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the information came from.
Q.When creating a user, what permissions must you grant to allow them to connect to the database?
Grant create session to username;
Q.What view would you use to look at the size of a data file?