Question and Answer
http://www.dwhworld.com/2010/07/sql-interview-questions-part-1/
http://www.dwhworld.com/2010/10/sql-interview-questions-part-2/
http://www.dwhworld.com/2010/10/plsql-interview-questions-part-1/
http://www.dwhworld.com/2010/10/plsql-interview-questions-part-2/
What are the various types of queries ?
The types of queries are:
A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
What is implicit cursor and how is it used by Oracle ?
An implicit cursor is a cursor which is internally created by Oracle. It is created by Oracle for each individual SQL.
Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Public synonyms
What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'
Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available. The syntax is: wrap iname=myscript.sql
Can one use DYNAMIC SQL within PL/SQL? OR Can you use a DDL in a procedure? How?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
What are the various types of Exceptions ?
User defined and Predefined Exceptions.
Can we define exceptions twice in same block?
No.
What is the difference between a procedure and a function?
Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
Can you have two functions with the same name in a PL/SQL block?
Yes. This is called overloading/overriding
Can you have two stored functions with the same name?
Yes. This is called overloading/overriding
Can you call a stored function in the constraint of a table?
No.
What are the various types of parameter modes in a procedure?
IN, OUT AND INOUT.
What is Over Loading and what are its restrictions?
Over Loading means an object performing different functions depending upon the no of parameters or the data type of the parameters passed to it.
Can functions be overloaded?
Yes.
Can 2 functions have same name & input parameters but differ only by return datatype?
No.
What are the constructs of a procedure, function or a package?
The constructs of a procedure, function or a package are :
So that Grants are not dropped.
Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.
What are the parts of a database trigger ?
The parts of a trigger are:
There are 12 types of triggers, they are combination of :
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
What is the maximum no.of statements that can be specified in a trigger statement ?
One.
Can views be specified in a trigger statement ?
No
What are the values of :new and :old in Insert/Delete/Update Triggers ?
These are referencing clauses
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.
What are mutating triggers ?
A trigger giving a SELECT on the table ON WHICH the trigger is written.
Resolution : Use a View or conver to statement level trigger
What are constraining triggers ?
A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.
Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.
Can you increase the size of datafiles ? How ?
No (for Oracle 7.0)
Yes (for Oracle 7.3 and above by using the Resize clause )
What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.
What is the use of Data Dictionary ?
It Used by Oracle to store information about various physical and logical Oracle structures e.g.Tables, Tablespaces, datafiles, etc
What are the advantages of clusters ?
Access time reduced for joins.
What are the disadvantages of clusters ?
The time for Insert increases.
Can Long/Long RAW be clustered ?
No.
Can null keys be entered in cluster index, normal index ?
Yes.
Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
What are the min.extents allocated to a rollback extent ?
Two
What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :
Unique key can be null; Primary key cannot be null.
An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
No.
Can you define multiple savepoints ?
Yes.
Can you Rollback to any savepoint ?
Yes.
What is the maximum no.of columns a table can have ?
254 -> 1000(now)
What is the significance of the & and && operators in PL/SQL ?
The &; operator means that the PL/SQL block requires user input for a variable.
The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable
Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
What are the various types of RollBack Segments ?
The types of Rollback segments are as follows :
Yes
Is the query below allowed ?
Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15))
Yes
Is the assignment given below allowed ?
ABC = PQR (Where ABC and PQR are records)
Yes
Is this for loop allowed ?
For x in & Start.. & End Loop
Yes
How many rows will the following SQL return ?
Select * from emp Where rownum < 10;
9 rows
How many rows will the following SQL return ?
Select * from emp Where rownum = 10;
No rows
Which symbol precedes the path to the table in the remote database ?
@
Are views automatically updated when base tables are updated ?
Yes
Can a trigger written for a view ?
No not directly.
Yes indirectly using Instead of Trigger
If all the values from a cursor have been fetched and another fetch is issued, the output will be ?error, last record or first record ?
Last Record
A table has the following data : [[5, Null, 10]]. What will the average function return ?
7.5
Is Sysdate a system variable or a system function?
System Function
Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?
3
Definition of relational Database by Dr. Codd (IBM)?
A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.
Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes
What is Multi Threaded Server (MTA) ?
In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user.But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.
Which are initial RDBMS, Hierarchical & N/w database ?
Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R
What is Auditing ?
The database has the ability to audit all actions that take place within it.
a) Login attempts,
b) Object Access,
c) Database Action Result of Greatest (1, NULL) or Least (1, NULL) NULL
While designing in client/server what are the 2 imp.things to be considered ?
Network Overhead (Traffic), Speed and Load of client server.
What are the disadvantages of SQL ?
Disadvantages of SQL are :
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
How can you avoid indexes ?
Error
Yes, database trigger would fire.
Can you alter synonym of view or view ?
No
Can you create index on view
Not on regular views but yes on materialized views.
What is the difference between a view and a synonym ?
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.
What's the length of SQL integer ?
32 bit length
What is the difference between foreign key and reference key ?
Foreign key is the key i.e.attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.
Can dual table be deleted, dropped or altered or updated or inserted ?
Yes
If content of dual is updated to some value computation takes place or not ?
Yes
If any other table same as dual is created would it act similar to dual?
Yes
For which relational operators in where clause, index is not used ?
<>, like '%...' is NOT functions, field + constant, field
Assume that there are multiple databases running on one machine. How can you switch from one to another ?
Select 1 from dual UNION Select 'A' from dual;
Changing the ORACLE_SID
What are the advantages of Oracle ?
Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols.
Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue.This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available.
Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerance to disk failure.You can also do point-in-time recovery.
Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases.Oracle can manage > 100GB databases.
Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.
What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.
What are actual and formal parameters ?
Actual Parameters : Subprograms pass information using parameters.The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.For example, the following procedure call lists two actual parameters named emp_num and amount:
Eg. raise_salary(emp_num, amount);
Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.For example, the following procedure declares two formal parameters named emp_id and increase:
Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;
What are the types of Notation ?
Position, Named, Mixed and Restrictions.
What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?
In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}. The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.
If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?
Yes
What are various types of joins ?
Types of joins are:
A package cursor is a cursor which you declare in the package specification without an SQL statement. The SQL statement for the cursor is attached dynamically at runtime from calling procedures.
If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted ?
Yes. Because Create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.
Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.
Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
Give two examples of how you might determine the structure of the table DEPT.
Use the describe command or use the dbms_metadata.get_ddl package.
Where would you look for errors from the database engine?
In the alert log.
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 a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
Give the reasoning behind using an index.
Faster access to data blocks in a table.
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.
What type of index should you use on a fact table?
A Bitmap index.
Give two examples of referential integrity constraints.
A primary key and a foreign key.
A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any ball in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any ball in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
What command would you use to create a backup control file?
Alter database backup control file to trace.
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
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.
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
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.
Explain an ORA-01555
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
1. How would you determine the time zone under which a database was operating?
2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
3. What command would you use to encrypt a PL/SQL application?
4. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
5. Explain the use of table functions.
6. Name three advisory statistics you can collect.
7. Where in the Oracle directory tree structure are audit traces placed?
8. Explain materialized views and how they are used.
9. When a user process fails, what background process cleans up after it?
PMON
10. What background process refreshes materialized views?
11. How would you determine what sessions are connected and what resources they are waiting for?
join v$locked_objects, v$user_objects, v$session
12. Describe what redo logs are.
13. How would you force a log switch?
14. Give two methods you could use to determine what DDL changes have been made.
15. What does coalescing a tablespace do?
16. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
17. Name a tablespace automatically created when you create a database.
USERS
18. When creating a user, what permissions must you grant to allow them to connect to the database?
create session, connect
19. How do you add a data file to a tablespace?
20. How do you resize a data file?
By adding more files
21. What view would you use to look at the size of a data file?
22. What view would you use to determine free space in a tablespace?
23. How would you determine who has added a row to a table?
24. How can you rebuild an index?
Drop and Recreate
25. Explain what partitioning is and what its benefit is.
26. You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS
27. How can you gather statistics on a table?
ANALYZE TABLE
Note :
Statistics Gathering Procedures in the DBMS_STATS Package
http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm
28. How can you enable a trace for a session?
29. What is the difference between the SQL*Loader and IMPORT utilities?
30. Name two files used for network connection to a database.
1. Describe the difference between a procedure, function and anonymous pl/sql block.
Expected answer: Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.
2. What is a mutating table error and how can you get around it? This happens with triggers.
Expected answer: It occurs because the trigger is trying to update a row it is currently using (selecting). The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Expected answer:
%ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.
4. What packages (if any) has Oracle provided for use by developers?
Expected answer:
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.
5. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
6. When is a declare statement needed ?
Expected answer: The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
7. 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?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order it will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.
8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: 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.
9. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.
10. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
11. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT etc.
DBA and Performance Tuning Questions I
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. What is the fastest query method for a table
Fetch by rowid
7. 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.
8. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?
If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZE parameter.
9. When should you increase copy latches? What parameters control copy latches
When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.
10. 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.
11. Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning
The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.
12. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won't fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
DBA and Performance Tuning Questions II
1. Give one method for transferring a table from one schema to another:
There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY.
2. What is the purpose of the IMPORT option IGNORE? What is it?s default setting
The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the tables data will be inserted. The default value is N.
3. 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.
4. 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).
5. 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.
6. 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.
7. 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.
8. 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.
9. What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause
The index is created in the user?s default tablespace and all sizing information is lost. Oracle doesn?t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
10. (On UNIX) When should more than one DB writer process be used? How many should be used
If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter.
11. 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.
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 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.
13. How can you tell if a database object is invalid By checking the status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.
13. A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check
You need to check that the user has specified the full name of the object (select empid from scott.emp; instead of select empid from emp;) or has a synonym that balls to the object (create synonym emp for scott.emp;)
14. A developer is trying to create a view and the database won?t let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT grants on the tables he is using, what is the problem
You need to verify the developer has direct grants on all tables used in the view. You can?t create a stored object with grants given through views.
15. 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.
16. 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.
17. 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.
18. How can you determine if an index needs to be dropped and rebuilt
Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.
DBA and Performance Tuning Questions II
1. How can variables be passed to a SQL routine
By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself: "select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.
2. 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.
3. How can you call a PL/SQL procedure from SQL
By use of the EXECUTE (short form EXEC) command.
4. 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.
5. You want to use SQL to build SQL, what is this called and give an example
This is called dynamic SQL. An example would be: 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 Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?
? the values selected from the database.
6. What SQLPlus command is used to format output from a select
This is best done with the COLUMN command.
7. 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.
8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement
The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
9. 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
Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example: select rowid from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no); In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
10. What is a Cartesian product
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
11. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
12. What is the default ordering of an ORDER BY clause in a SELECT statement
Ascending
13. What is tkprof and how is it used
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.
14. What is explain plan and how is it used
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
15. How do you set the number of lines on a page of output? The width
The SET command in SQLPLUS is used to control the number of lines generated per page 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. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.
16. How do you prevent output from coming to the screen
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
17. 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.
18. How do you generate file output from SQL
By use of the SPOOL comm
Oracle general questions
1. 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
where
field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)
2. 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.
3. What are various constraints used in SQL
NULL NOT NULL CHECK DEFAULT
4. What are different Oracle database objects
TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES etc
5. 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.
6. What is a view
A view is stored procedure based on one or more tables, its a virtual table.
7. What are various privileges that a user can grant to another user
SELECT CONNECT RESOURCE
8. 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.
9. Can a primary key contain more than one columns
Yes
10. How you will avoid duplicating records in a query
By using DISTINCT
11. What is difference between SQL and SQL*PLUS
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
12. Which datatype is used for storing graphics and images
LONG RAW data type is used for storing BLOB's (binary large objects).
13. 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)
14. 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 '-')
15. 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
16. There is a '%' sign in one field of a column. What will be the query to find it.
'\' Should be used before '%'.
17. 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.
18. Which is more faster - IN or EXISTS
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
19. What is a OUTER JOIN
Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they dont satisfy the join condition.
20. 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
1. 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.
SELECT customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) from customer C2 WHERE C1.payment >= C2.payment)
2. 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.
3. What is a cursor.
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.
4. 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.
5. What are cursor attributes
%ROWCOUNT %NOTFOUND %FOUND %ISOPEN
6. What is a cursor for loop.
Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.
7. 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.
8. 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.
9. What 'WHERE CURRENT OF ' clause does in a cursor.
LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;
END LOOP
COMMIT;
END;
10. What is use of a cursor variable? How it is defined.
A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.
11. What should be the return type for a cursor variable.Can we use a scalar data type as return type.
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
12. 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.
13. 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.
14. 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.
15. 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.
16. What are different modes of parameters used in functions and procedures.
IN OUT INOUT
17. What is difference between a formal and an actual parameter
The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters
18. Can the default values be assigned to actual parameters.
Yes
19. Can a function take OUT parameters.If not why.
No.A function has to return a value,an OUT parameter cannot return a value.
20. What is syntax for dropping a procedure and a function .Are these operations possible.
Drop Procedure procedure_name
Drop Function function_name
21. 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.
22. 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
-execute statements
-fetch the results
23. Difference between database triggers and form triggers.
a) Data base trigger(DBT) fires when a DML operation is performed on a data base table.Form trigger(FT) Fires when user presses a key or navigates between fields on the screen b) Can be row level or statement level No distinction between row level and statement level. c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms. d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger. e) Can cause other database triggers to fire.Can cause other database triggers to fire,but not other form triggers.
24. What is an UTL_FILE.What are different procedures and functions associated
with it. UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
25. Can you use a commit statement within a database trigger.
No
26. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
1,000,000
1. 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.
2. If you see contention for library caches how can you fix it
Increase the size of the shared pool.
3. If you see statistics that deal with "undo" what are they really talking about
Rollback segments and associated structures.
4. 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.
5. 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.
6. 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.
7. You see the following on a status report: redo log space requests 23 redo log space wait time 0 Is this something to worry about? What if redo log space wait time is high? How can you fix this Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.
8. What can cause a high value for recursive calls? How can this be fixed
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
9. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it
This indicate that the shared pool may be too small. Increase the shared pool size.
10. 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.
11. 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.
12. 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.
1. You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for
The first thing to check with a large SGA is that it isn?t being swapped out.
2. What OS user should be used for the first part of an Oracle installation (on UNIX)
You must use root first.
3. When should the default values for Oracle initialization parameters be used as is
Never
4. 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.
5. 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.
6. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces
At least 7, see disk configuration answer above.
7. Describe third normal form
Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
8. Is the following statement true or false:
"All relational databases must be in third normal form" False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.
9. 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.
10. 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.
11. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")
Expected answer: This means the two entities should probably be made into one entity.
12. How should a many-to-many relationship be handled
By adding an intersection entity table
13. 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.
1. When should you consider denormalization
Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
2. How can you determine if an Oracle instance is up from the operating system level
There are several base Oracle processes that will be running on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any answer that has them using their operating system process showing feature to check for these is acceptable. For example, on UNIX a ps -ef
grep dbwr will show what instances are up.
3. Users from the PC clients are getting messages indicating : ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)
What could the problem be The instance name is probably incorrect in their connection string.
4. 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.
5. 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".
6. 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.
7. Users aren?t being allowed on the system. The following message is received: ORA-00257 archiver is stuck. Connect internal only, until freed What is the problem The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.
8. Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs
There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert.log file for this information.
9. You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded What is the problem and how can you fix it When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
10. 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.
11. Your users get the following error: ORA-00055 maximum number of DML locks exceeded What is the problem and how do you fix it The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.
12. You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do
As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following: CONNECT INTERNAL STARTUP MOUNT (Take any read-only tablespaces offline before next step ALTER DATABASE DATAFILE .... OFFLINE;) RECOVER DATABASE USING BACKUP CONTROLFILE ALTER DATABASE OPEN RESETLOGS; (bring read-only tablespaces back online) Shutdown and backup the system, then restart If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well. If no backup of the control file is available then the following will be required: CONNECT INTERNAL STARTUP NOMOUNT CREATE CONTROL FILE .....; However, they will need to know all of the datafiles, logfiles, and settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.
1. How would you determine the time zone under which a database was operating?
2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
3. What command would you use to encrypt a PL/SQL application?
4. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
5. Explain the use of table functions.
6. Name three advisory statistics you can collect.
7. Where in the Oracle directory tree structure are audit traces placed?
8. Explain materialized views and how they are used.
9. When a user process fails, what background process cleans up after it?
10. What background process refreshes materialized views?
11. How would you determine what sessions are connected and what resources they are waiting for?
12. Describe what redo logs are.
13. How would you force a log switch?
14. Give two methods you could use to determine what DDL changes have been made.
15. What does coalescing a tablespace do?
16. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
17. Name a tablespace automatically created when you create a database.
18. When creating a user, what permissions must you grant to allow them to connect to the database?
19. How do you add a data file to a tablespace?
20. How do you resize a data file?
21. What view would you use to look at the size of a data file?
22. What view would you use to determine free space in a tablespace?
23. How would you determine who has added a row to a table?
24. How can you rebuild an index?
25. Explain what partitioning is and what its benefit is.
26. You have just compiled a PL/SQL package but got errors, how would you view the errors?
27. How can you gather statistics on a table?
28. How can you enable a trace for a session?
29. What is the difference between the SQL*Loader and IMPORT utilities?
30. Name two files used for network connection to a database.
1. 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
2. You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action
No there is not a problem. You have 40 extents showing and an average of 40 concurrent users. Since there is plenty of room to grow no action is needed.
3. You see multiple extents in the temporary tablespace. Is this a problem
As long as they are all the same size this isn?t a problem. In fact, it can even improve performance since Oracle won?t have to create a new extent when a user needs one.
4. Define OFA.
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.
5. How do you set up your tablespace on installation
The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation os SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on another, ROLLBACK tablespace on another and still have two for DATA and INDEXES. They should indicate how they will handle archive logs and exports as well. As long as they have a logical plan for combining or further separation more or less disks can be specified.
6. What should be done prior to installing Oracle (for the OS and the disks)
Adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.
7. 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.
INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file
SQLNET.ORA, TNSNAMES.ORA
10. What must be installed with ODBC on the client in order for it to work with Oracle
SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.
11. Give two examples of referential integrity constraints.
A primary key and a foreign key.
16. 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.
9. 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
Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
11. What is the default ordering of an ORDER BY clause in a SELECT statement
Ascending
13. What is explain plan and how is it used
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
6. What is the fastest query method for a table
Fetch by rowid
12. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won?t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
41. Describe third normal form
Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
7. What are various constraints used in SQL
NULL
NOT NULL
CHECK
DEFAULT
8. What are different Oracle database objects
TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc
10. What is a view
A view is stored procedure based on one or more tables, its a virtual table.
17. 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)
18. What is difference between SUBSTR and INSTR
SUBSTR returns a specified portion of a string eg SUBSTR('ABCDE',4) output ABCD INSTR provides character position in which a pattern is found in a string.
eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
19. 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
20. There is a '%' sign in one field of a column. What will be the query to find it.
'\' Should be used before '%'.
21. 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.
22. Which is more faster - IN or EXISTS
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
23. What is a OUTER JOIN
Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they dont satisfy the join condition.
24. 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
25. 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.
SELECT customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) from customer C2 WHERE C1.payment <= C2.payment)
26. 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.
27. What is a cursor.
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.
28. 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.
29. What are cursor attributes
%ROWCOUNT
%NOTFOUND
%FOUND
%ISOPEN
30. What is a cursor for loop.
Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.
31. 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.
32. 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.
33. What 'WHERE CURRENT OF ' clause does in a cursor.
LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;
END LOOP
COMMIT;
END;
34. What is use of a cursor variable? How it is defined.
A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C).
Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.
35. What should be the return type for a cursor variable.Can we use a scalar data type as return type.
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
36. 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.
37. 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.
38. 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.
39. 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.
40. What are different modes of parameters used in functions and procedures.
IN
OUT
INOUT
41. What is difference between a formal and an actual parameter
The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters
42. Can the default values be assigned to actual parameters.
Yes
43. Can a function take OUT parameters.If not why.
No.A function has to return a value,an OUT parameter cannot return a value.
44. What is syntax for dropping a procedure and a function .Are these operations possible.
45. Drop Procedure procedure_name
46. Drop Function function_name
47. 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.
48. 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
-execute statements
-fetch the results
64. Difference between database triggers and form triggers.
a) Data base trigger(DBT) fires when a DML operation is performed on a data base table.Form trigger(FT) Fires when user presses a key or navigates between fields on the screen
b) Can be row level or statement level No distinction between row level and statement level.
c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.
d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.
e) Can cause other database triggers to fire.Can cause other database triggers to fire,but not other form triggers.
65. What is an UTL_FILE.What are different procedures and functions associated
with it. UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
66. Can you use a commit statement within a database trigger.
No
67. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function? Oracle Interview Questions
1,000,000
1. How would you determine the time zone under which a database was operating?
2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
3. What command would you use to encrypt a PL/SQL application?
4. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
5. Explain the use of table functions.
6. Name three advisory statistics you can collect.
7. Where in the Oracle directory tree structure are audit traces placed?
8. Explain materialized views and how they are used.
9. When a user process fails, what background process cleans up after it?
10. What background process refreshes materialized views?
11. How would you determine what sessions are connected and what resources they are waiting for?
12. Describe what redo logs are.
13. How would you force a log switch?
14. Give two methods you could use to determine what DDL changes have been made.
15. What does coalescing a tablespace do?
16. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
17. Name a tablespace automatically created when you create a database.
18. When creating a user, what permissions must you grant to allow them to connect to the database?
19. How do you add a data file to a tablespace?
20. How do you resize a data file?
21. What view would you use to look at the size of a data file?
22. What view would you use to determine free space in a tablespace?
23. How would you determine who has added a row to a table?
24. How can you rebuild an index?
25. Explain what partitioning is and what its benefit is.
26. You have just compiled a PL/SQL package but got errors, how would you view the errors?
27. How can you gather statistics on a table?
28. How can you enable a trace for a session?
29. What is the difference between the SQL*Loader and IMPORT utilities?
30. Name two files used for network connection to a database.
1. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationships can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
It will be a good idea to read up a database designing fundamentals text book.
2. What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates anonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but nique key allows one NULL only.
3. Define candidate key, alternate key, composite key
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
4. What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.
5. What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
6. Explain different isolation levels
An isolation level determines the degree of isolation of data between concurrent transactions. The default isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. Read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
7. CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
8. What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the redo log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
9. Explain the storage models of OLAP
Check out MOLAP, ROLAP and HOLAP for more info
10. What are constraints? Explain different types of constraints
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
11. What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
Indexes help retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
12. What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.
13. What are the steps you will take to improve performance of a poor performing query?
This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers , poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
14. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
You can select * from v$locked_object to find the locked object and kill one session.
15. What is blocking and how would you troubleshoot it?
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
16. Explain CREATE DATABASE syntax
Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why being a DBA you should be familiar with the CREATE DATABASE syntax.
17. What are statistics, under what circumstances they go out of date, how do you update them?
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.
Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3) Database is upgraded from a previous version
18. What is database replication? What are the different types of replication you can set up in Oracle?
Replication is the process of copying/moving data between databases on the same or different servers.
o Snapshot replication using MVs
o Oracle Streams
o Partition Exchange
o EXP/IMP
19. What is a join and explain different types of joins
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
20. What is a self join? Explain it with an example
Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid
Basic XMLquestion (in ETL we load XML files)
Describe the role that XSL can play when dynamically generating HTML pages from a relational database. XML. Querying a database and then formatting the result set so that it can be validated as an XML document allows developers to translate the data into an HTML table using XSLT rules. Consequently, the format of the resulting HTML table can be modified without changing the database query or application code since the document rendering logic is isolated to the XSLT rules.
Even if candidates have never participated in a project involving this type of architecture, they should recognize it as one of the common uses of
Give a few examples of types of applications that can benefit from using XML.
There are literally thousands of applications that can benefit from XML technologies. The ball of this question is not to have the candidate rattle off a laundry list of projects that they have worked on, but, rather, to allow the candidate to explain the rationale for choosing XML by citing a few real world examples. For instance, one appropriate answer is that XML allows content management systems to store documents independently of their format, which thereby reduces data redundancy. Another answer relates to B2B exchanges or supply chain management systems. In these instances, XML provides a mechanism for multiple companies to exchange data according to an agreed upon set of rules. A third common response involves wireless applications that require WML to render data on hand held devices.
What is DOM and how does it relate to XML?
The Document Object Model (DOM) is an interface specification maintained by the W3C DOM Workgroup that defines an application independent mechanism to access, parse, or update XML data. In simple terms it is a hierarchical model that allows developers to manipulate XML documents easily Any developer that has worked extensively with XML should be able to discuss the concept and use of DOM objects freely. Additionally, it is not unreasonable to expect advanced candidates to thoroughly understand its internal workings and be able to explain how DOM differs from an event-based interface like SAX.
What is SOAP and how does it relate to XML?
The Simple Object Access Protocol (SOAP) uses XML to define a protocol for the exchange of information in distributed computing environments. SOAP consists of three components: an envelope, a set of encoding rules, and a convention for representing remote procedure calls. Unless experience with SOAP is a direct requirement for the open position, knowing the specifics of the protocol, or how it can be used in conjunction with HTTP, is not as important as identifying it as a natural application of XML.
Can you walk us through the steps necessary to parse XML documents?
Superficially, this is a fairly basic question. However, the ball is not to determine whether candidates understand the concept of a parser but rather have them walk through the process of parsing XML documents step-by-step. Determining whether a non-validating or validating parser is needed, choosing the appropriate parser, and handling errors are all important aspects to this process that should be included in the candidate's response.
Give some examples of XML DTDs or schemas that you have worked with.
Although XML does not require data to be validated against a DTD, many of the benefits of using the technology are derived from being able to validate XML documents against business or technical architecture rules. Polling for the list of DTDs that developers have worked with provides insight to their general exposure to the technology. The ideal candidate will have knowledge of several of the commonly used DTDs such as FpML, DocBook, HRML, and RDF, as well as experience designing a custom DTD for a particular project where no standard existed.
Using XSLT, how would you extract a specific attribute from an element in an XML document? XML data. For those who view XML primarily as a way to denote structure for text files, a common answer is to build a full-text search and handle the data similarly to the way Internet portals handle HTML pages. Others consider XML as a standard way of transferring structured data between disparate systems. These candidates often describe some scheme of importing XML into a relational or object database and relying on the database's engine for searching. Lastly, candidates that have worked with vendors specializing in this area often say that the best way the handle this situation is to use a third party software package optimized for XML data.
Successful candidates should recognize this as one of the most basic applications of XSLT. If they are not able to construct a reply similar to the example below, they should at least be able to identify the components necessary for this operation: xsl:template to match the appropriate XML element, xsl:value-of to select the attribute value, and the optional xsl:apply-templates to continue processing the document.
Extract Attributes from XML Data
Example 1.
Attribute Value:
When constructing an XML DTD, how do you create an external entity reference in an attribute value?
Every interview session should have at least one trick question. Although possible when using SGML, XML DTDs don't support defining external entity references in attribute values. It's more important for the candidate to respond to this question in a logical way than than the candidate know the somewhat obscure answer.
How would you build a search engine for large volumes of XML data?
The way candidates answer this question may provide insight into their view of
To see current user name
Sql> show user;
Change SQL prompt name
SQL>set sqlprompt “Manimara > “
Manimara >
Manimara >
Switch to DOS prompt
SQL> host
How do I eliminate the duplicate rows ?
delete ename from emp a where rowid > ( select min(rowid) from emp b where a.ename = b.ename);
How do I display row number with records?
SQL> select rownum, ename from emp;
Display the records between two range say between 7 & 10
select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=10 minus select rowid from emp where rownum < 7);
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
SQL
. select nvl(to_char(comm.),'NA') from emp;
-----------------------
NA
300
500
Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
Explicit Cursor attributes There are four cursor attributes used in Oracle
cursor_name%FOUND, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements. .
: 2. All are Boolean attributes
To view installed Oracle version information
select banner from v$version;
Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;
the output like,
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
Display Odd/ Even number of records 1
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
Which date function returns number value?
months_between
Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’ \
What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.
What is the maximum number of triggers, can apply to a single table?
12 triggers.
What is a synonym ?
A synonym is an alternative name for tables,views,procedures and other database objects
generally when we have more than one schema and we wish to access an object of a different schema then we create synonyms for that object in the schema where we wish to access the object.
What is a schema ?
A schema is a logical collection of database objects like tables, views, pkgs, procedures, triggers, etc. It usually has an associated database user.
The NVL function
NVL(expression, value_if_null)
If expression is null, returns value_if_null; if expression is not null, returns expression.
Assume that there are multiple databases running on one machine.How can you switch from one to another ?
Answer: Changing the ORACLE_SID
A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?
Which three of the following are valid database datatypes in Oracle? (Choose three.)
CHAR
VARCHAR2
BOOLEAN X
NUMBER
How do you create sequence.
CREATE SEQUENCE seq_key_1 INCREMENT 2 START 10
You query the database with this command
SELECT name
FROM employee
WHERE name LIKE ‘_a%’;
Which names are displayed?
Names starting with “a”
What cursor type do you use to retrieve multiple recordsets?
EXPLICIT CURSOR
What is the difference between a "where" clause and a "having" clause?- "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.
What structure can you implement for the database to speed up table reads? - Follow the rules of DB tuning we have to:
Properly use indexes
Properly locate different DB objects across different tablespaces, files and so on.
Create a special tablespace to locate some of the data with special datatype ( for example CLOB)
What are the tradeoffs with having indexes? –
1. Faster selects, BUT slower updates.
2. Extra storage space to store indexes.
What is a "join"? - ‘join’ used to connect two or more tables logically with or without common field.
What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
What is a "constraint"? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE , FOREIGN KEY -, CHECK - checks that every value stored in a column is in some specified list. NOT NULL
What is a "functional dependency"?
Functional dependency relates to how one object depends upon the other in the database.
for xample, procedure sp2 may be called by procedure sp1. Then we say that sp1 has functional
dependency on sp2.
What is a "trigger"? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations.
TRIGGERS ARE USEFUL:
1. A database column cannot carry PSEUDO columns as criteria where a trigger can.
2. A database constraint cannot refer old and new values for a row where a trigger can.
Why can a "group by" or "order by" clause be expensive to process? - Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query.
What is "index covering" of a query? - Index covering means that "Data can be found only using indexes, without touching the tables"
What is a SQL view? - View is a precomplied SQL query which is used to select data from one or more tables. It is moreover a virtual table.
How do you return the top-N results of a query in Oracle?
SELECT * FROM (SELECT * FROM table_name ORDER BY column_name DESC) WHERE rownum <=N
Why is a UNION ALL faster than a UNION?UNION ALL faster than a UNION because for union operation server needs to remove the duplicate values but for union all its not
NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.
Date Functions
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE
What are Character Functions
Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.
JOIN
JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.
Self Join joins a table to itself as though it were two separate tables.
Union
Union is the product of two or more tables.
Intersect
Intersect is the product of two tables listing only the matching rows.
Minus
Minus is the product of two tables listing only the non-matching rows.
Correlated Subquery
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.
Sequences
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
Synonyms
Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.
Indexes
Indexes are optional structures associated with tables used to speed query execution. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes.
Concatenated index max. is 16 columns.
Order of SQL statement executionWhere clause, Group By clause, Having clause, Order By clause & Select.
Transaction
Transaction is defined as all changes made to the database between successive commits.
Rollback
Rollback causes work in the current transaction to be undone.
Savepoint
Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
Integrity
Assures database data and structures reflects all changes made to them in the correct sequence. Locks ensure data integrity and maximum concurrent access to data. Commit statement releases all locks.
Types of locks are given below.
Data Locks protects data i.e. Table or Row lock.
Exclusive Lock allows queries on locked table but no other activity is allowed.
Share Lock allows concurrent queries but prohibits updates to the locked tables.
Deadlock
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource.To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.
Mutating Table
Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Pseudo Columns behaves like a column in a table but are not actually stored in the table.
E.g. Currval, Nextval, Rowid, Rownum, Level etc.
What is Log Switch? - The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
What is On-line Redo Log? - The On-line Redo Log is a set of two or more on-line redo files that record all committed changes made to the database.
What are the steps involved in Database Startup? - Start an instance,Mount the Database and Open the Database.
What are the steps involved in Instance Recovery?.
If either a single instance database crashes or all instances of an OPS (Oracle Parallel Server) configuration crash, Oracle performs instance recovery at the next startup. If one or more instances of an OPS configuration crash, a surviving instance performs recovery.
Instance and crash recovery occur in two phases.
In phase one, Oracle applies all committed and uncommitted changes in the redo log files to the affected datablocks.
In phase two, Oracle applies information in the rollback segments to undo changes made by uncommitted transactions to the data blocks.
68. Can Full Backup be performed when the database is open?- No.
69. What are the different modes of mounting a Database with the Parallel Server? –
Exclusive Mode: If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database.
Parallel Mode: If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
70. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? - Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.
71. What are the steps involved in Database Shutdown? - Close the Database, Dismount the Database and Shutdown the Instance.
72. What is Archived Redo Log? - Archived Redo Log consists of Redo Log files that have archived before being reused.
73. What is Restricted Mode of Instance Startup? - An instance can be started in restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
74. What is Partial Backup? - A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
75. What is Mirrored on-line Redo Log? - A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
76. What is Full Backup? - A full backup is an operating system backup of all data files, on- line redo log files and control files etc.
Can a View based on another View? - Yes.
78. Can a Tablespace hold objects from different Schemes? - Yes.
79. Can objects of the same Schema reside in different tablespaces? - Yes.
80. What is the use of Control File? - When an instance of an ORACLE database is created the control file is created and when the DB gets started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
81. Do View contain Data? – No
82. What are the Referential actions supported by FOREIGN KEY integrity constraint? - UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
83. What are the type of Synonyms? - There are two types of Synonyms Private and Public
84. What is a Redo Log? - Redo logs are transaction journals. Each transaction is recorded in the redo logs..
85. What is an Index Segment? - Each Index has an Index segment that stores all of its data.
86. Explain the relationship among Database, Tablespace and Data file.? - Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace
87. What are the different type of Segments? - Data Segment, Index Segment, Rollback Segment and Temporary Segment.
88. What are Clusters? - Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
89. What is an Integrity Constrains? - An integrity constraint is a declarative way to define a business rule for a column of a table.
90. What is an Index? - An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
91. What is a Synonym? - A synonym is an alias for a table, view, sequence or program unit.
92. What is a Sequence? - A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.
93. What is a Segment? - A segment is a set of extents allocated for a certain logical structure.
94. What is schema? - A schema is collection of database objects of a User.
95. Describe Referential Integrity? - Forigen key relation
96. What is a Private Synonyms? - A Private Synonyms can be accessed only by the owner.
97. What is a Tablespace? - A database is divided into Logical Storage Unit called tablespaces.
98. What is Rollback Segment? - A Database contains one or more Rollback Segments to temporarily store “undo” information.
99. What is difference between UNIQUE constraint and PRIMARY KEY constraint? - A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls.
100. What is the function of Optimizer? - The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
101. What is SAVE POINT? - For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.
02. What is a Schema? - The set of objects owned by user account is called the schema.
103. What is Parallel Server? - Multiple instances accessing the same
104. What is clusters? - Group of tables physically stored together because they share common columns and are often used together is called Cluster.
105. How can you enable automatic archiving? - Shut the database, Backup the database Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file. Start up the database.
106. What are the responsibilities of a Database Administrator?
Installing and upgrading the Oracle Server and application tools.
Allocating system storage and planning future storage requirements for the database system.
Managing primary database structures (tablespaces)
Managing primary objects (table, views, indexes)
Enrolling users and maintaining system security.
Ensuring compliance with Oracle license agreement
Controlling and monitoring user access to the database.
Monitoring and optimizing the performance of the database.
Planning for backup and recovery of database information.
Maintain archived data on tape
Backing up and restoring the database.
Contacting Oracle Corporation for technical support.
107. How will you enforce security using stored procedures? - Don’t grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
108. How can we specify the Archived log file name format and destination? - By setting LOG_ARCHIVE_FORMAT in init.ora file.
109. What dynamic data replication? - Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.
110. What is Two-Phase Commit? - Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase.
111. What is a SNAPSHOT? - Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.
112. What is snapshot log? - It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.
113. What is Distributed database? - A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
How can we reduce the network traffic? - Replication of data in distributed environment. - Using snapshots to replicate data. - Using remote procedure calls.
What is hot backup and how it can be taken? - Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.
How will you force database to use particular rollback segment? –
SET TRANSACTION USE ROLLBACKS
http://pl-sql-interviewquestions.blogspot.com/
http://techforum4u.com/showthread.php/1963-Oracle-Interview-Questions
http://kyuoracleblog.wordpress.com/2012/02/29/my-interview-with-kamran-agavevs-oracle-blog/
http://www.orafaq.com/wiki/Interview_Questions
https://blogs.oracle.com/campusrecruitment/entry/how_to_prepare_for_a_telephone
http://www.careerride.com/Oracle-Interview-Questions.aspx
http://it.toolbox.com/blogs/database-solutions/oracle-technical-interview-questions-answered-part2-6387
http://kamranagayev.com/
http://www.techpdf.in/blog/oracle-interview-questions-and-answers/
http://churmura.com/general/oracle-faqs-3/8558/
http://www.zimbio.com/Oracle+database/articles/bonSKTPx4p0/Oracle+Performance+Tuning+Interview+Questions
http://kyuoracleblog.wordpress.com/
http://www.dwhworld.com/2010/12/combine-multiple-rows-to-a-column-oracle/
Tools
SPLIT
log4plsql
hotsos
convert comma separated string to columns
th t as (select '1,2,3,4,5,6,aa,c,d,w,1,2,S,d' col1 from dual)
SELECT REGEXP_SUBSTR(col1,'[0-9]+
[a-z]+
[A-Z]+',1,lvl) FROM
(SELECT col1,level lvl FROM
t CONNECT BY level <= LENGTH(col1) - LENGTH(REPLACE(col1,',')) + 1)
/
to_number
is_number
select * from all_errors
select * from session_priviledges
http://www.dwhworld.com/2010/07/sql-interview-questions-part-1/
http://www.dwhworld.com/2010/10/sql-interview-questions-part-2/
http://www.dwhworld.com/2010/10/plsql-interview-questions-part-1/
http://www.dwhworld.com/2010/10/plsql-interview-questions-part-2/
What are the various types of queries ?
The types of queries are:
- Normal Queries
- Sub Queries
- Co-related queries
- Nested queries
- Compound queries
A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.
What is implicit cursor and how is it used by Oracle ?
An implicit cursor is a cursor which is internally created by Oracle. It is created by Oracle for each individual SQL.
Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
Public synonyms
What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'
Can one read/write files from PL/SQL?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=...parameter).Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available. The syntax is: wrap iname=myscript.sql
Can one use DYNAMIC SQL within PL/SQL? OR Can you use a DDL in a procedure? How?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
What are the various types of Exceptions ?
User defined and Predefined Exceptions.
Can we define exceptions twice in same block?
No.
What is the difference between a procedure and a function?
Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
Can you have two functions with the same name in a PL/SQL block?
Yes. This is called overloading/overriding
Can you have two stored functions with the same name?
Yes. This is called overloading/overriding
Can you call a stored function in the constraint of a table?
No.
What are the various types of parameter modes in a procedure?
IN, OUT AND INOUT.
What is Over Loading and what are its restrictions?
Over Loading means an object performing different functions depending upon the no of parameters or the data type of the parameters passed to it.
Can functions be overloaded?
Yes.
Can 2 functions have same name & input parameters but differ only by return datatype?
No.
What are the constructs of a procedure, function or a package?
The constructs of a procedure, function or a package are :
- variables and constants
- cursors
- exceptions
So that Grants are not dropped.
Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.
What are the parts of a database trigger ?
The parts of a trigger are:
- A triggering event or statement
- A trigger restriction
- A trigger action
There are 12 types of triggers, they are combination of :
- Insert, Delete and Update Triggers.
- Before and After Triggers.
- Row and Statement Triggers.
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
What is the maximum no.of statements that can be specified in a trigger statement ?
One.
Can views be specified in a trigger statement ?
No
What are the values of :new and :old in Insert/Delete/Update Triggers ?
These are referencing clauses
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.
What are mutating triggers ?
A trigger giving a SELECT on the table ON WHICH the trigger is written.
Resolution : Use a View or conver to statement level trigger
What are constraining triggers ?
A trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.
Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.
Can you increase the size of datafiles ? How ?
No (for Oracle 7.0)
Yes (for Oracle 7.3 and above by using the Resize clause )
What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.
What is the use of Data Dictionary ?
It Used by Oracle to store information about various physical and logical Oracle structures e.g.Tables, Tablespaces, datafiles, etc
What are the advantages of clusters ?
Access time reduced for joins.
What are the disadvantages of clusters ?
The time for Insert increases.
Can Long/Long RAW be clustered ?
No.
Can null keys be entered in cluster index, normal index ?
Yes.
Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
What are the min.extents allocated to a rollback extent ?
Two
What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :
- ONLINE
- OFFLINE
- PARTLY AVAILABLE
- NEEDS RECOVERY
- INVALID.
Unique key can be null; Primary key cannot be null.
An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
No.
Can you define multiple savepoints ?
Yes.
Can you Rollback to any savepoint ?
Yes.
What is the maximum no.of columns a table can have ?
254 -> 1000(now)
What is the significance of the & and && operators in PL/SQL ?
The &; operator means that the PL/SQL block requires user input for a variable.
The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable
Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
What are the various types of RollBack Segments ?
The types of Rollback segments are as follows :
- Public Available to all instances
- Private Available to specific instance
Yes
Is the query below allowed ?
Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15))
Yes
Is the assignment given below allowed ?
ABC = PQR (Where ABC and PQR are records)
Yes
Is this for loop allowed ?
For x in & Start.. & End Loop
Yes
How many rows will the following SQL return ?
Select * from emp Where rownum < 10;
9 rows
How many rows will the following SQL return ?
Select * from emp Where rownum = 10;
No rows
Which symbol precedes the path to the table in the remote database ?
@
Are views automatically updated when base tables are updated ?
Yes
Can a trigger written for a view ?
No not directly.
Yes indirectly using Instead of Trigger
If all the values from a cursor have been fetched and another fetch is issued, the output will be ?error, last record or first record ?
Last Record
A table has the following data : [[5, Null, 10]]. What will the average function return ?
7.5
Is Sysdate a system variable or a system function?
System Function
Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?
3
Definition of relational Database by Dr. Codd (IBM)?
A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.
Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes
What is Multi Threaded Server (MTA) ?
In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user.But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.
Which are initial RDBMS, Hierarchical & N/w database ?
- RDBMS - R system
- Hierarchical - IMS
- N/W - DBTG
Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R
What is Auditing ?
The database has the ability to audit all actions that take place within it.
a) Login attempts,
b) Object Access,
c) Database Action Result of Greatest (1, NULL) or Least (1, NULL) NULL
While designing in client/server what are the 2 imp.things to be considered ?
Network Overhead (Traffic), Speed and Load of client server.
What are the disadvantages of SQL ?
Disadvantages of SQL are :
- Cannot drop a field
- Cannot rename a field
- Cannot manage memory
- Procedural Language option not provided
- Index on view or index on index not provided
- View updation problem
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
How can you avoid indexes ?
- To make index access path unavailable
- Use FULL hint to optimizer for full table scan
- Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another.
- Use an expression in the Where Clause of the SQL.
Error
Yes, database trigger would fire.
Can you alter synonym of view or view ?
No
Can you create index on view
Not on regular views but yes on materialized views.
What is the difference between a view and a synonym ?
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.
What's the length of SQL integer ?
32 bit length
What is the difference between foreign key and reference key ?
Foreign key is the key i.e.attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.
Can dual table be deleted, dropped or altered or updated or inserted ?
Yes
If content of dual is updated to some value computation takes place or not ?
Yes
If any other table same as dual is created would it act similar to dual?
Yes
For which relational operators in where clause, index is not used ?
<>, like '%...' is NOT functions, field + constant, field
Assume that there are multiple databases running on one machine. How can you switch from one to another ?
Select 1 from dual UNION Select 'A' from dual;
Changing the ORACLE_SID
What are the advantages of Oracle ?
Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols.
Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue.This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available.
Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerance to disk failure.You can also do point-in-time recovery.
Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases.Oracle can manage > 100GB databases.
Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.
What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.
What are actual and formal parameters ?
Actual Parameters : Subprograms pass information using parameters.The variables or expressions referenced in the parameter list of a subprogram call are actual parameters.For example, the following procedure call lists two actual parameters named emp_num and amount:
Eg. raise_salary(emp_num, amount);
Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.For example, the following procedure declares two formal parameters named emp_id and increase:
Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;
What are the types of Notation ?
Position, Named, Mixed and Restrictions.
What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?
In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}. The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.
If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?
Yes
What are various types of joins ?
Types of joins are:
- Equijoins
- Non-equijoins
- Self join
- Outer join
A package cursor is a cursor which you declare in the package specification without an SQL statement. The SQL statement for the cursor is attached dynamically at runtime from calling procedures.
If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted ?
Yes. Because Create table is a DDL which commits automatically as soon as it is executed. The DDL commits the transaction even if the create statement fails internally (eg table already exists error) and not syntactically.
Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any ball in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.
Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
Give two examples of how you might determine the structure of the table DEPT.
Use the describe command or use the dbms_metadata.get_ddl package.
Where would you look for errors from the database engine?
In the alert log.
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 a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
Give the reasoning behind using an index.
Faster access to data blocks in a table.
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.
What type of index should you use on a fact table?
A Bitmap index.
Give two examples of referential integrity constraints.
A primary key and a foreign key.
A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any ball in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any ball in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
What command would you use to create a backup control file?
Alter database backup control file to trace.
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
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.
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
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.
Explain an ORA-01555
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
1. How would you determine the time zone under which a database was operating?
2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
3. What command would you use to encrypt a PL/SQL application?
4. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
5. Explain the use of table functions.
6. Name three advisory statistics you can collect.
7. Where in the Oracle directory tree structure are audit traces placed?
8. Explain materialized views and how they are used.
9. When a user process fails, what background process cleans up after it?
PMON
10. What background process refreshes materialized views?
11. How would you determine what sessions are connected and what resources they are waiting for?
join v$locked_objects, v$user_objects, v$session
12. Describe what redo logs are.
13. How would you force a log switch?
14. Give two methods you could use to determine what DDL changes have been made.
15. What does coalescing a tablespace do?
16. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
17. Name a tablespace automatically created when you create a database.
USERS
18. When creating a user, what permissions must you grant to allow them to connect to the database?
create session, connect
19. How do you add a data file to a tablespace?
20. How do you resize a data file?
By adding more files
21. What view would you use to look at the size of a data file?
22. What view would you use to determine free space in a tablespace?
23. How would you determine who has added a row to a table?
24. How can you rebuild an index?
Drop and Recreate
25. Explain what partitioning is and what its benefit is.
26. You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS
27. How can you gather statistics on a table?
ANALYZE TABLE
Note :
COMPUTE STATISTICS always gathers exact statistics. |
Statistics Gathering Procedures in the DBMS_STATS Package
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'interval', interval => 720, stattab => 'mystats', statid => 'OLTP'); END; /
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''mystats'',''OLTP'');'
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/
Oracle can gather some statistics automatically while creating or rebuilding a B-tree or bitmap index. The
COMPUTE STATISTICS option of CREATE INDEX or ALTER INDEX ... REBUILD enables this gathering of statistics.
The statistics that Oracle gathers for the
COMPUTE STATISTICS option depend on whether the index is partitioned or nonpartitioned.- For a nonpartitioned index, Oracle gathers index, table, and column statistics while creating or rebuilding the index. In a concatenated-key index, the column statistics refer only to the leading column of the key.
- For a partitioned index, Oracle does not gather any table or column statistics while creating the index or rebuilding its partitions.
- While creating a partitioned index, Oracle gathers index statistics for each partition and for the entire index. If the index uses composite partitioning, then Oracle also gathers statistics for each subpartition.
Table 3-4 Default Index Values When Statistics are Missing
While rebuilding a partition or subpartition of an index, Oracle gathers index statistics only for that partition or subpartition.Index Statistic Default Value Used by Optimizer
Levels
1Leaf blocks 25 Leaf blocks/key 1 Data blocks/key 1 Distinct keys 100 Clustering factor 800 (8 * number of blocks)
To ensure correctness of the statistics, Oracle always uses base tables when creating an index with the
COMPUTE STATISTICS option, even if another index is available that could be used to create the index.
If you do not use the
COMPUTE STATISTICS clause, or if you have made significant changes to the data, then use the DBMS_STATS.GATHER_INDEX_STATS procedure to collect index statistics.
Objects are considered stale when 10% of the total rows have been changed. When you issue
GATHER_TABLE_STATS with GATHER STALE, the procedure checks the USER_TAB_MODIFICATIONS view. If a monitored table has been modified more than 10%, then statistics are gathered again.
The statistics gathered help you determine how the data is distributed across the tables. The optimizer assumes that the data is uniformly distributed. You can analyze the actual data distribution in the tables by viewing the appropriate dictionary table:
DBA_TABLES for tables and DBA_TAB_COL_STATISTICS for column statistics.DECLARE num_rows NUMBER; num_blocks NUMBER; avg_row_len NUMBER; BEGIN -- retrieve the values of table statistics on OE.ORDERS -- statistics table name: OE.SAVESTATS statistics ID: TEST1 DBMS_STATS.GET_TABLE_STATS('OE','ORDERS',null, 'SAVESTATS','TEST1', num_rows,num_blocks,avg_row_len); -- print the values DBMS_OUTPUT.PUT_LINE('num_rows='||num_rows||',num_blocks='||num_blocks|| ',avg_row_len='||avg_row_len); END;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT =>
'FOR ALL COLUMNS SIZE SKEWONLY
FOR COLUMNS (CUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY');
select dbms_stats.create_extended_stats(null,'customers','(lower(cust_state_province))') from dual;
http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm
28. How can you enable a trace for a session?
29. What is the difference between the SQL*Loader and IMPORT utilities?
30. Name two files used for network connection to a database.
1. Describe the difference between a procedure, function and anonymous pl/sql block.
Expected answer: Candidate should mention use of DECLARE statement, a function must return a value while a procedure doesn't have to.
2. What is a mutating table error and how can you get around it? This happens with triggers.
Expected answer: It occurs because the trigger is trying to update a row it is currently using (selecting). The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
3. Describe the use of %ROWTYPE and %TYPE in PL/SQL
Expected answer:
%ROWTYPE allows you to associate a variable with an entire table row.
The %TYPE associates a variable with a single column type.
4. What packages (if any) has Oracle provided for use by developers?
Expected answer:
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.
5. Describe the use of PL/SQL tables
Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
6. When is a declare statement needed ?
Expected answer: The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
7. 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?
Expected answer: OPEN then FETCH then LOOP followed by the exit when. If not specified in this order it will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.
8. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
Expected answer: 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.
9. How can you find within a PL/SQL block, if a cursor is open?
Expected answer: Use the %ISOPEN cursor status variable.
10. How can you generate debugging output from PL/SQL?
Expected answer: Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
11. What are the types of triggers?
Expected Answer: There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words: BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT etc.
DBA and Performance Tuning Questions I
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. What is the fastest query method for a table
Fetch by rowid
7. 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.
8. When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?
If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort are parameter is the SORT_AREA_SIZE parameter.
9. When should you increase copy latches? What parameters control copy latches
When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system.
10. 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.
11. Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning
The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.
12. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won't fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
DBA and Performance Tuning Questions II
1. Give one method for transferring a table from one schema to another:
There are several possible methods, export-import, CREATE TABLE... AS SELECT, or COPY.
2. What is the purpose of the IMPORT option IGNORE? What is it?s default setting
The IMPORT IGNORE option tells import to ignore "already exists" errors. If it is not specified the tables that already exist will be skipped. If it is specified, the error is ignored and the tables data will be inserted. The default value is N.
3. 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.
4. 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).
5. 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.
6. 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.
7. 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.
8. 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.
9. What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause
The index is created in the user?s default tablespace and all sizing information is lost. Oracle doesn?t store this information as a part of the constraint definition, but only as part of the index definition, when the constraint was disabled the index was dropped and the information is gone.
10. (On UNIX) When should more than one DB writer process be used? How many should be used
If the UNIX system being used is capable of asynchronous IO then only one is required, if the system is not capable of asynchronous IO then up to twice the number of disks used by Oracle number of DB writers should be specified by use of the db_writers initialization parameter.
11. 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.
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 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.
13. How can you tell if a database object is invalid By checking the status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.
13. A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check
You need to check that the user has specified the full name of the object (select empid from scott.emp; instead of select empid from emp;) or has a synonym that balls to the object (create synonym emp for scott.emp;)
14. A developer is trying to create a view and the database won?t let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT grants on the tables he is using, what is the problem
You need to verify the developer has direct grants on all tables used in the view. You can?t create a stored object with grants given through views.
15. 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.
16. 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.
17. 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.
18. How can you determine if an index needs to be dropped and rebuilt
Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.
DBA and Performance Tuning Questions II
1. How can variables be passed to a SQL routine
By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself: "select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.
2. 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.
3. How can you call a PL/SQL procedure from SQL
By use of the EXECUTE (short form EXEC) command.
4. 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.
5. You want to use SQL to build SQL, what is this called and give an example
This is called dynamic SQL. An example would be: 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 Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?
? the values selected from the database.
6. What SQLPlus command is used to format output from a select
This is best done with the COLUMN command.
7. 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.
8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement
The COST based system allows the use of HINTs to control the optimizer path selection. If they can give some example hints such as FIRST ROWS, ALL ROWS, USING INDEX, STAR, even better.
9. 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
Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example: select rowid from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no); In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
10. What is a Cartesian product
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
11. You are joining a local and a remote table, the network manager complains about the traffic involved, how can you reduce the network traffic Push the processing of the remote data to the remote instance by using a view to pre-select the information for the join. This will result in only the data required for the join being sent across.
12. What is the default ordering of an ORDER BY clause in a SELECT statement
Ascending
13. What is tkprof and how is it used
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.
14. What is explain plan and how is it used
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
15. How do you set the number of lines on a page of output? The width
The SET command in SQLPLUS is used to control the number of lines generated per page 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. The PAGESIZE and LINESIZE options can be shortened to PAGES and LINES.
16. How do you prevent output from coming to the screen
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
17. 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.
18. How do you generate file output from SQL
By use of the SPOOL comm
Oracle general questions
1. 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
where
field1=X.field1);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)
2. 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.
3. What are various constraints used in SQL
NULL NOT NULL CHECK DEFAULT
4. What are different Oracle database objects
TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES etc
5. 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.
6. What is a view
A view is stored procedure based on one or more tables, its a virtual table.
7. What are various privileges that a user can grant to another user
SELECT CONNECT RESOURCE
8. 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.
9. Can a primary key contain more than one columns
Yes
10. How you will avoid duplicating records in a query
By using DISTINCT
11. What is difference between SQL and SQL*PLUS
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
12. Which datatype is used for storing graphics and images
LONG RAW data type is used for storing BLOB's (binary large objects).
13. 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)
14. 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 '-')
15. 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
16. There is a '%' sign in one field of a column. What will be the query to find it.
'\' Should be used before '%'.
17. 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.
18. Which is more faster - IN or EXISTS
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
19. What is a OUTER JOIN
Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they dont satisfy the join condition.
20. 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
1. 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.
SELECT customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) from customer C2 WHERE C1.payment >= C2.payment)
2. 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.
3. What is a cursor.
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.
4. 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.
5. What are cursor attributes
%ROWCOUNT %NOTFOUND %FOUND %ISOPEN
6. What is a cursor for loop.
Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.
7. 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.
8. 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.
9. What 'WHERE CURRENT OF ' clause does in a cursor.
LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;
END LOOP
COMMIT;
END;
10. What is use of a cursor variable? How it is defined.
A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.
11. What should be the return type for a cursor variable.Can we use a scalar data type as return type.
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
12. 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.
13. 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.
14. 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.
15. 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.
16. What are different modes of parameters used in functions and procedures.
IN OUT INOUT
17. What is difference between a formal and an actual parameter
The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters
18. Can the default values be assigned to actual parameters.
Yes
19. Can a function take OUT parameters.If not why.
No.A function has to return a value,an OUT parameter cannot return a value.
20. What is syntax for dropping a procedure and a function .Are these operations possible.
Drop Procedure procedure_name
Drop Function function_name
21. 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.
22. 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
-execute statements
-fetch the results
23. Difference between database triggers and form triggers.
a) Data base trigger(DBT) fires when a DML operation is performed on a data base table.Form trigger(FT) Fires when user presses a key or navigates between fields on the screen b) Can be row level or statement level No distinction between row level and statement level. c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms. d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger. e) Can cause other database triggers to fire.Can cause other database triggers to fire,but not other form triggers.
24. What is an UTL_FILE.What are different procedures and functions associated
with it. UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
25. Can you use a commit statement within a database trigger.
No
26. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
1,000,000
1. 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.
2. If you see contention for library caches how can you fix it
Increase the size of the shared pool.
3. If you see statistics that deal with "undo" what are they really talking about
Rollback segments and associated structures.
4. 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.
5. 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.
6. 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.
7. You see the following on a status report: redo log space requests 23 redo log space wait time 0 Is this something to worry about? What if redo log space wait time is high? How can you fix this Since the wait time is zero, no. If the wait time was high it might indicate a need for more or larger redo logs.
8. What can cause a high value for recursive calls? How can this be fixed
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
9. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it
This indicate that the shared pool may be too small. Increase the shared pool size.
10. 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.
11. 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.
12. 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.
1. You have just started a new instance with a large SGA on a busy existing server. Performance is terrible, what should you check for
The first thing to check with a large SGA is that it isn?t being swapped out.
2. What OS user should be used for the first part of an Oracle installation (on UNIX)
You must use root first.
3. When should the default values for Oracle initialization parameters be used as is
Never
4. 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.
5. 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.
6. You have a simple application with no "hot" tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces
At least 7, see disk configuration answer above.
7. Describe third normal form
Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
8. Is the following statement true or false:
"All relational databases must be in third normal form" False. While 3NF is good for logical design most databases, if they have more than just a few tables, will not perform well using full 3NF. Usually some entities will be denormalized in the logical to physical transfer process.
9. 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.
10. 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.
11. What does a hard one-to-one relationship mean (one where the relationship on both ends is "must")
Expected answer: This means the two entities should probably be made into one entity.
12. How should a many-to-many relationship be handled
By adding an intersection entity table
13. 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.
1. When should you consider denormalization
Whenever performance analysis indicates it would be beneficial to do so without compromising data integrity.
2. How can you determine if an Oracle instance is up from the operating system level
There are several base Oracle processes that will be running on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any answer that has them using their operating system process showing feature to check for these is acceptable. For example, on UNIX a ps -ef
grep dbwr will show what instances are up.
3. Users from the PC clients are getting messages indicating : ORA-06114: (Cnct err, can't get err txt. See Servr Msgs & Codes Manual)
What could the problem be The instance name is probably incorrect in their connection string.
4. 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.
5. 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".
6. 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.
7. Users aren?t being allowed on the system. The following message is received: ORA-00257 archiver is stuck. Connect internal only, until freed What is the problem The archive destination is probably full, backup the archive logs and remove them and the archiver will re-start.
8. Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs
There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert.log file for this information.
9. You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded What is the problem and how can you fix it When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
10. 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.
11. Your users get the following error: ORA-00055 maximum number of DML locks exceeded What is the problem and how do you fix it The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear.
12. You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do
As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following: CONNECT INTERNAL STARTUP MOUNT (Take any read-only tablespaces offline before next step ALTER DATABASE DATAFILE .... OFFLINE;) RECOVER DATABASE USING BACKUP CONTROLFILE ALTER DATABASE OPEN RESETLOGS; (bring read-only tablespaces back online) Shutdown and backup the system, then restart If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well. If no backup of the control file is available then the following will be required: CONNECT INTERNAL STARTUP NOMOUNT CREATE CONTROL FILE .....; However, they will need to know all of the datafiles, logfiles, and settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.
1. How would you determine the time zone under which a database was operating?
2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
3. What command would you use to encrypt a PL/SQL application?
4. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
5. Explain the use of table functions.
6. Name three advisory statistics you can collect.
7. Where in the Oracle directory tree structure are audit traces placed?
8. Explain materialized views and how they are used.
9. When a user process fails, what background process cleans up after it?
10. What background process refreshes materialized views?
11. How would you determine what sessions are connected and what resources they are waiting for?
12. Describe what redo logs are.
13. How would you force a log switch?
14. Give two methods you could use to determine what DDL changes have been made.
15. What does coalescing a tablespace do?
16. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
17. Name a tablespace automatically created when you create a database.
18. When creating a user, what permissions must you grant to allow them to connect to the database?
19. How do you add a data file to a tablespace?
20. How do you resize a data file?
21. What view would you use to look at the size of a data file?
22. What view would you use to determine free space in a tablespace?
23. How would you determine who has added a row to a table?
24. How can you rebuild an index?
25. Explain what partitioning is and what its benefit is.
26. You have just compiled a PL/SQL package but got errors, how would you view the errors?
27. How can you gather statistics on a table?
28. How can you enable a trace for a session?
29. What is the difference between the SQL*Loader and IMPORT utilities?
30. Name two files used for network connection to a database.
1. 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
2. You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action
No there is not a problem. You have 40 extents showing and an average of 40 concurrent users. Since there is plenty of room to grow no action is needed.
3. You see multiple extents in the temporary tablespace. Is this a problem
As long as they are all the same size this isn?t a problem. In fact, it can even improve performance since Oracle won?t have to create a new extent when a user needs one.
4. Define OFA.
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.
5. How do you set up your tablespace on installation
The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation os SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on another, ROLLBACK tablespace on another and still have two for DATA and INDEXES. They should indicate how they will handle archive logs and exports as well. As long as they have a logical plan for combining or further separation more or less disks can be specified.
6. What should be done prior to installing Oracle (for the OS and the disks)
Adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.
7. 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.
INITIALIZATION file, TNSNAMES.ORA file, SQLNET.ORA file
SQLNET.ORA, TNSNAMES.ORA
10. What must be installed with ODBC on the client in order for it to work with Oracle
SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.
11. Give two examples of referential integrity constraints.
A primary key and a foreign key.
16. 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.
9. 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
Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example:
select rowid from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no);
In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
11. What is the default ordering of an ORDER BY clause in a SELECT statement
Ascending
13. What is explain plan and how is it used
The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.
6. What is the fastest query method for a table
Fetch by rowid
12. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won?t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
41. Describe third normal form
Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
7. What are various constraints used in SQL
NULL
NOT NULL
CHECK
DEFAULT
8. What are different Oracle database objects
TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc
10. What is a view
A view is stored procedure based on one or more tables, its a virtual table.
17. 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)
18. What is difference between SUBSTR and INSTR
SUBSTR returns a specified portion of a string eg SUBSTR('ABCDE',4) output ABCD INSTR provides character position in which a pattern is found in a string.
eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')
19. 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
20. There is a '%' sign in one field of a column. What will be the query to find it.
'\' Should be used before '%'.
21. 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.
22. Which is more faster - IN or EXISTS
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
23. What is a OUTER JOIN
Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they dont satisfy the join condition.
24. 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
25. 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.
SELECT customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) from customer C2 WHERE C1.payment <= C2.payment)
26. 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.
27. What is a cursor.
Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.
28. 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.
29. What are cursor attributes
%ROWCOUNT
%NOTFOUND
%FOUND
%ISOPEN
30. What is a cursor for loop.
Cursor For Loop is a loop where oracle implicitly declares a loop variable, the loop index that of the same record type as the cursor's record.
31. 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.
32. 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.
33. What 'WHERE CURRENT OF ' clause does in a cursor.
LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;
END LOOP
COMMIT;
END;
34. What is use of a cursor variable? How it is defined.
A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C).
Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.
35. What should be the return type for a cursor variable.Can we use a scalar data type as return type.
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
36. 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.
37. 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.
38. 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.
39. 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.
40. What are different modes of parameters used in functions and procedures.
IN
OUT
INOUT
41. What is difference between a formal and an actual parameter
The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters
42. Can the default values be assigned to actual parameters.
Yes
43. Can a function take OUT parameters.If not why.
No.A function has to return a value,an OUT parameter cannot return a value.
44. What is syntax for dropping a procedure and a function .Are these operations possible.
45. Drop Procedure procedure_name
46. Drop Function function_name
47. 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.
48. 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
-execute statements
-fetch the results
64. Difference between database triggers and form triggers.
a) Data base trigger(DBT) fires when a DML operation is performed on a data base table.Form trigger(FT) Fires when user presses a key or navigates between fields on the screen
b) Can be row level or statement level No distinction between row level and statement level.
c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.
d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.
e) Can cause other database triggers to fire.Can cause other database triggers to fire,but not other form triggers.
65. What is an UTL_FILE.What are different procedures and functions associated
with it. UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
66. Can you use a commit statement within a database trigger.
No
67. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function? Oracle Interview Questions
1,000,000
1. How would you determine the time zone under which a database was operating?
2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
3. What command would you use to encrypt a PL/SQL application?
4. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
5. Explain the use of table functions.
6. Name three advisory statistics you can collect.
7. Where in the Oracle directory tree structure are audit traces placed?
8. Explain materialized views and how they are used.
9. When a user process fails, what background process cleans up after it?
10. What background process refreshes materialized views?
11. How would you determine what sessions are connected and what resources they are waiting for?
12. Describe what redo logs are.
13. How would you force a log switch?
14. Give two methods you could use to determine what DDL changes have been made.
15. What does coalescing a tablespace do?
16. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
17. Name a tablespace automatically created when you create a database.
18. When creating a user, what permissions must you grant to allow them to connect to the database?
19. How do you add a data file to a tablespace?
20. How do you resize a data file?
21. What view would you use to look at the size of a data file?
22. What view would you use to determine free space in a tablespace?
23. How would you determine who has added a row to a table?
24. How can you rebuild an index?
25. Explain what partitioning is and what its benefit is.
26. You have just compiled a PL/SQL package but got errors, how would you view the errors?
27. How can you gather statistics on a table?
28. How can you enable a trace for a session?
29. What is the difference between the SQL*Loader and IMPORT utilities?
30. Name two files used for network connection to a database.
1. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationships can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
It will be a good idea to read up a database designing fundamentals text book.
2. What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates anonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but nique key allows one NULL only.
3. Define candidate key, alternate key, composite key
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
4. What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.
5. What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
6. Explain different isolation levels
An isolation level determines the degree of isolation of data between concurrent transactions. The default isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. Read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
7. CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
8. What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the redo log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
9. Explain the storage models of OLAP
Check out MOLAP, ROLAP and HOLAP for more info
10. What are constraints? Explain different types of constraints
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
11. What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?
Indexes help retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes. When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
12. What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance.
13. What are the steps you will take to improve performance of a poor performing query?
This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers , poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
14. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
You can select * from v$locked_object to find the locked object and kill one session.
15. What is blocking and how would you troubleshoot it?
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
16. Explain CREATE DATABASE syntax
Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why being a DBA you should be familiar with the CREATE DATABASE syntax.
17. What are statistics, under what circumstances they go out of date, how do you update them?
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.
Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3) Database is upgraded from a previous version
18. What is database replication? What are the different types of replication you can set up in Oracle?
Replication is the process of copying/moving data between databases on the same or different servers.
o Snapshot replication using MVs
o Oracle Streams
o Partition Exchange
o EXP/IMP
19. What is a join and explain different types of joins
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
20. What is a self join? Explain it with an example
Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid
Basic XMLquestion (in ETL we load XML files)
Describe the role that XSL can play when dynamically generating HTML pages from a relational database. XML. Querying a database and then formatting the result set so that it can be validated as an XML document allows developers to translate the data into an HTML table using XSLT rules. Consequently, the format of the resulting HTML table can be modified without changing the database query or application code since the document rendering logic is isolated to the XSLT rules.
Even if candidates have never participated in a project involving this type of architecture, they should recognize it as one of the common uses of
Give a few examples of types of applications that can benefit from using XML.
There are literally thousands of applications that can benefit from XML technologies. The ball of this question is not to have the candidate rattle off a laundry list of projects that they have worked on, but, rather, to allow the candidate to explain the rationale for choosing XML by citing a few real world examples. For instance, one appropriate answer is that XML allows content management systems to store documents independently of their format, which thereby reduces data redundancy. Another answer relates to B2B exchanges or supply chain management systems. In these instances, XML provides a mechanism for multiple companies to exchange data according to an agreed upon set of rules. A third common response involves wireless applications that require WML to render data on hand held devices.
What is DOM and how does it relate to XML?
The Document Object Model (DOM) is an interface specification maintained by the W3C DOM Workgroup that defines an application independent mechanism to access, parse, or update XML data. In simple terms it is a hierarchical model that allows developers to manipulate XML documents easily Any developer that has worked extensively with XML should be able to discuss the concept and use of DOM objects freely. Additionally, it is not unreasonable to expect advanced candidates to thoroughly understand its internal workings and be able to explain how DOM differs from an event-based interface like SAX.
What is SOAP and how does it relate to XML?
The Simple Object Access Protocol (SOAP) uses XML to define a protocol for the exchange of information in distributed computing environments. SOAP consists of three components: an envelope, a set of encoding rules, and a convention for representing remote procedure calls. Unless experience with SOAP is a direct requirement for the open position, knowing the specifics of the protocol, or how it can be used in conjunction with HTTP, is not as important as identifying it as a natural application of XML.
Can you walk us through the steps necessary to parse XML documents?
Superficially, this is a fairly basic question. However, the ball is not to determine whether candidates understand the concept of a parser but rather have them walk through the process of parsing XML documents step-by-step. Determining whether a non-validating or validating parser is needed, choosing the appropriate parser, and handling errors are all important aspects to this process that should be included in the candidate's response.
Give some examples of XML DTDs or schemas that you have worked with.
Although XML does not require data to be validated against a DTD, many of the benefits of using the technology are derived from being able to validate XML documents against business or technical architecture rules. Polling for the list of DTDs that developers have worked with provides insight to their general exposure to the technology. The ideal candidate will have knowledge of several of the commonly used DTDs such as FpML, DocBook, HRML, and RDF, as well as experience designing a custom DTD for a particular project where no standard existed.
Using XSLT, how would you extract a specific attribute from an element in an XML document? XML data. For those who view XML primarily as a way to denote structure for text files, a common answer is to build a full-text search and handle the data similarly to the way Internet portals handle HTML pages. Others consider XML as a standard way of transferring structured data between disparate systems. These candidates often describe some scheme of importing XML into a relational or object database and relying on the database's engine for searching. Lastly, candidates that have worked with vendors specializing in this area often say that the best way the handle this situation is to use a third party software package optimized for XML data.
Successful candidates should recognize this as one of the most basic applications of XSLT. If they are not able to construct a reply similar to the example below, they should at least be able to identify the components necessary for this operation: xsl:template to match the appropriate XML element, xsl:value-of to select the attribute value, and the optional xsl:apply-templates to continue processing the document.
Extract Attributes from XML Data
Example 1.
Attribute Value:
When constructing an XML DTD, how do you create an external entity reference in an attribute value?
Every interview session should have at least one trick question. Although possible when using SGML, XML DTDs don't support defining external entity references in attribute values. It's more important for the candidate to respond to this question in a logical way than than the candidate know the somewhat obscure answer.
How would you build a search engine for large volumes of XML data?
The way candidates answer this question may provide insight into their view of
To see current user name
Sql> show user;
Change SQL prompt name
SQL>set sqlprompt “Manimara > “
Manimara >
Manimara >
Switch to DOS prompt
SQL> host
How do I eliminate the duplicate rows ?
delete ename from emp a where rowid > ( select min(rowid) from emp b where a.ename = b.ename);
How do I display row number with records?
SQL> select rownum, ename from emp;
Display the records between two range say between 7 & 10
select rownum, empno, ename from emp where rowid in (select rowid from emp where rownum <=10 minus select rowid from emp where rownum < 7);
ROWNUM EMPNO ENAME
--------- --------- ----------
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?
SQL
. select nvl(to_char(comm.),'NA') from emp;
-----------------------
NA
300
500
Oracle cursor : Implicit & Explicit cursors
Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.
Explicit Cursor attributes There are four cursor attributes used in Oracle
cursor_name%FOUND, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
Implicit Cursor attributes
Same as explicit cursor but prefixed by the word SQL
SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after executing SQL statements. .
: 2. All are Boolean attributes
To view installed Oracle version information
select banner from v$version;
Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;
the output like,
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
Display Odd/ Even number of records 1
Odd number of records:
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)
2
4
6
Which date function returns number value?
months_between
Any three PL/SQL Exceptions?
Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others
What are PL/SQL Cursor Exceptions?
Cursor_Already_Open, Invalid_Cursor
Other way to replace query result null value with a text
SQL> Set NULL ‘N/A’
to reset SQL> Set NULL ‘’ \
What are the more common pseudo-columns?
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM
What is the output of SIGN function?
1 for positive value,
0 for Zero,
-1 for Negative value.
What is the maximum number of triggers, can apply to a single table?
12 triggers.
What is a synonym ?
A synonym is an alternative name for tables,views,procedures and other database objects
generally when we have more than one schema and we wish to access an object of a different schema then we create synonyms for that object in the schema where we wish to access the object.
What is a schema ?
A schema is a logical collection of database objects like tables, views, pkgs, procedures, triggers, etc. It usually has an associated database user.
The NVL function
NVL(expression, value_if_null)
If expression is null, returns value_if_null; if expression is not null, returns expression.
Assume that there are multiple databases running on one machine.How can you switch from one to another ?
Answer: Changing the ORACLE_SID
A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user?
Which three of the following are valid database datatypes in Oracle? (Choose three.)
CHAR
VARCHAR2
BOOLEAN X
NUMBER
How do you create sequence.
CREATE SEQUENCE seq_key_1 INCREMENT 2 START 10
You query the database with this command
SELECT name
FROM employee
WHERE name LIKE ‘_a%’;
Which names are displayed?
Names starting with “a”
What cursor type do you use to retrieve multiple recordsets?
EXPLICIT CURSOR
What is the difference between a "where" clause and a "having" clause?- "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.
What structure can you implement for the database to speed up table reads? - Follow the rules of DB tuning we have to:
Properly use indexes
Properly locate different DB objects across different tablespaces, files and so on.
Create a special tablespace to locate some of the data with special datatype ( for example CLOB)
What are the tradeoffs with having indexes? –
1. Faster selects, BUT slower updates.
2. Extra storage space to store indexes.
What is a "join"? - ‘join’ used to connect two or more tables logically with or without common field.
What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
What is a "constraint"? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE , FOREIGN KEY -, CHECK - checks that every value stored in a column is in some specified list. NOT NULL
What is a "functional dependency"?
Functional dependency relates to how one object depends upon the other in the database.
for xample, procedure sp2 may be called by procedure sp1. Then we say that sp1 has functional
dependency on sp2.
What is a "trigger"? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations.
TRIGGERS ARE USEFUL:
1. A database column cannot carry PSEUDO columns as criteria where a trigger can.
2. A database constraint cannot refer old and new values for a row where a trigger can.
Why can a "group by" or "order by" clause be expensive to process? - Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query.
What is "index covering" of a query? - Index covering means that "Data can be found only using indexes, without touching the tables"
What is a SQL view? - View is a precomplied SQL query which is used to select data from one or more tables. It is moreover a virtual table.
How do you return the top-N results of a query in Oracle?
SELECT * FROM (SELECT * FROM table_name ORDER BY column_name DESC) WHERE rownum <=N
Why is a UNION ALL faster than a UNION?UNION ALL faster than a UNION because for union operation server needs to remove the duplicate values but for union all its not
NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.
Date Functions
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE
What are Character Functions
Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.
JOIN
JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.
Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.
Self Join joins a table to itself as though it were two separate tables.
Union
Union is the product of two or more tables.
Intersect
Intersect is the product of two tables listing only the matching rows.
Minus
Minus is the product of two tables listing only the non-matching rows.
Correlated Subquery
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.
Sequences
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
Synonyms
Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.
Indexes
Indexes are optional structures associated with tables used to speed query execution. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes.
Concatenated index max. is 16 columns.
Order of SQL statement executionWhere clause, Group By clause, Having clause, Order By clause & Select.
Transaction
Transaction is defined as all changes made to the database between successive commits.
Rollback
Rollback causes work in the current transaction to be undone.
Savepoint
Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
Integrity
Assures database data and structures reflects all changes made to them in the correct sequence. Locks ensure data integrity and maximum concurrent access to data. Commit statement releases all locks.
Types of locks are given below.
Data Locks protects data i.e. Table or Row lock.
Exclusive Lock allows queries on locked table but no other activity is allowed.
Share Lock allows concurrent queries but prohibits updates to the locked tables.
Deadlock
Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource.To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.
Mutating Table
Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Pseudo Columns behaves like a column in a table but are not actually stored in the table.
E.g. Currval, Nextval, Rowid, Rownum, Level etc.
What is Log Switch? - The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
What is On-line Redo Log? - The On-line Redo Log is a set of two or more on-line redo files that record all committed changes made to the database.
What are the steps involved in Database Startup? - Start an instance,Mount the Database and Open the Database.
What are the steps involved in Instance Recovery?.
If either a single instance database crashes or all instances of an OPS (Oracle Parallel Server) configuration crash, Oracle performs instance recovery at the next startup. If one or more instances of an OPS configuration crash, a surviving instance performs recovery.
Instance and crash recovery occur in two phases.
In phase one, Oracle applies all committed and uncommitted changes in the redo log files to the affected datablocks.
In phase two, Oracle applies information in the rollback segments to undo changes made by uncommitted transactions to the data blocks.
68. Can Full Backup be performed when the database is open?- No.
69. What are the different modes of mounting a Database with the Parallel Server? –
Exclusive Mode: If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database.
Parallel Mode: If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
70. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? - Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.
71. What are the steps involved in Database Shutdown? - Close the Database, Dismount the Database and Shutdown the Instance.
72. What is Archived Redo Log? - Archived Redo Log consists of Redo Log files that have archived before being reused.
73. What is Restricted Mode of Instance Startup? - An instance can be started in restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.
74. What is Partial Backup? - A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
75. What is Mirrored on-line Redo Log? - A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
76. What is Full Backup? - A full backup is an operating system backup of all data files, on- line redo log files and control files etc.
Can a View based on another View? - Yes.
78. Can a Tablespace hold objects from different Schemes? - Yes.
79. Can objects of the same Schema reside in different tablespaces? - Yes.
80. What is the use of Control File? - When an instance of an ORACLE database is created the control file is created and when the DB gets started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.
81. Do View contain Data? – No
82. What are the Referential actions supported by FOREIGN KEY integrity constraint? - UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
83. What are the type of Synonyms? - There are two types of Synonyms Private and Public
84. What is a Redo Log? - Redo logs are transaction journals. Each transaction is recorded in the redo logs..
85. What is an Index Segment? - Each Index has an Index segment that stores all of its data.
86. Explain the relationship among Database, Tablespace and Data file.? - Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace
87. What are the different type of Segments? - Data Segment, Index Segment, Rollback Segment and Temporary Segment.
88. What are Clusters? - Clusters are groups of one or more tables physically stores together to share common columns and are often used together.
89. What is an Integrity Constrains? - An integrity constraint is a declarative way to define a business rule for a column of a table.
90. What is an Index? - An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.
91. What is a Synonym? - A synonym is an alias for a table, view, sequence or program unit.
92. What is a Sequence? - A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.
93. What is a Segment? - A segment is a set of extents allocated for a certain logical structure.
94. What is schema? - A schema is collection of database objects of a User.
95. Describe Referential Integrity? - Forigen key relation
96. What is a Private Synonyms? - A Private Synonyms can be accessed only by the owner.
97. What is a Tablespace? - A database is divided into Logical Storage Unit called tablespaces.
98. What is Rollback Segment? - A Database contains one or more Rollback Segments to temporarily store “undo” information.
99. What is difference between UNIQUE constraint and PRIMARY KEY constraint? - A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls.
100. What is the function of Optimizer? - The goal of the optimizer is to choose the most efficient way to execute a SQL statement.
101. What is SAVE POINT? - For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.
02. What is a Schema? - The set of objects owned by user account is called the schema.
103. What is Parallel Server? - Multiple instances accessing the same
104. What is clusters? - Group of tables physically stored together because they share common columns and are often used together is called Cluster.
105. How can you enable automatic archiving? - Shut the database, Backup the database Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file. Start up the database.
106. What are the responsibilities of a Database Administrator?
Installing and upgrading the Oracle Server and application tools.
Allocating system storage and planning future storage requirements for the database system.
Managing primary database structures (tablespaces)
Managing primary objects (table, views, indexes)
Enrolling users and maintaining system security.
Ensuring compliance with Oracle license agreement
Controlling and monitoring user access to the database.
Monitoring and optimizing the performance of the database.
Planning for backup and recovery of database information.
Maintain archived data on tape
Backing up and restoring the database.
Contacting Oracle Corporation for technical support.
107. How will you enforce security using stored procedures? - Don’t grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.
108. How can we specify the Archived log file name format and destination? - By setting LOG_ARCHIVE_FORMAT in init.ora file.
109. What dynamic data replication? - Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.
110. What is Two-Phase Commit? - Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase.
111. What is a SNAPSHOT? - Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.
112. What is snapshot log? - It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.
113. What is Distributed database? - A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
How can we reduce the network traffic? - Replication of data in distributed environment. - Using snapshots to replicate data. - Using remote procedure calls.
What is hot backup and how it can be taken? - Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.
How will you force database to use particular rollback segment? –
SET TRANSACTION USE ROLLBACKS
http://pl-sql-interviewquestions.blogspot.com/
http://techforum4u.com/showthread.php/1963-Oracle-Interview-Questions
http://kyuoracleblog.wordpress.com/2012/02/29/my-interview-with-kamran-agavevs-oracle-blog/
http://www.orafaq.com/wiki/Interview_Questions
https://blogs.oracle.com/campusrecruitment/entry/how_to_prepare_for_a_telephone
http://www.careerride.com/Oracle-Interview-Questions.aspx
http://it.toolbox.com/blogs/database-solutions/oracle-technical-interview-questions-answered-part2-6387
http://kamranagayev.com/
http://www.techpdf.in/blog/oracle-interview-questions-and-answers/
http://churmura.com/general/oracle-faqs-3/8558/
http://www.zimbio.com/Oracle+database/articles/bonSKTPx4p0/Oracle+Performance+Tuning+Interview+Questions
http://kyuoracleblog.wordpress.com/
http://www.dwhworld.com/2010/12/combine-multiple-rows-to-a-column-oracle/
Tools
SPLIT
log4plsql
hotsos
convert comma separated string to columns
th t as (select '1,2,3,4,5,6,aa,c,d,w,1,2,S,d' col1 from dual)
SELECT REGEXP_SUBSTR(col1,'[0-9]+
[a-z]+
[A-Z]+',1,lvl) FROM
(SELECT col1,level lvl FROM
t CONNECT BY level <= LENGTH(col1) - LENGTH(REPLACE(col1,',')) + 1)
/
to_number
is_number
select * from all_errors
select * from session_priviledges
No comments:
Post a Comment