Explore topic-wise InterviewSolutions in .

This happens with triggers. It occurs because the TRIGGER is TRYING to update a ROW it is currently using. The usual fix involves EITHER use of views or temporary tables so the database is selecting from one while UPDATING the other.

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

ORACLE_BASE is the ROOT directory for oracle. ORACLE_HOME LOCATED beneath ORACLE_BASE is where the oracle PRODUCTS reside.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

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.

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.

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 point in TIME

NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point 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.

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 point in time

NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point 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.

TNSNAMES.ORA and SQLNET.ORA

TNSNAMES.ORA and SQLNET.ORA

Partitioning is a method of TAKING LARGE tables and indexes and splitting them into smaller, more manageable pieces.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

GRANT the CONNECT to the USER.

Grant the CONNECT to the user.

The SYSTEM TABLESPACE.

The SYSTEM tablespace.

Coalescing is only valid for dictionary-managed table SPACES and de-fragments space by combining neighboring FREE extents into LARGE single extents.

Coalescing is only valid for dictionary-managed table spaces and de-fragments space by combining neighboring free extents into large single extents.

You COULD USE LOG MINER or STREAMS.

You could use Log miner or Streams.

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

MATERIALIZED views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are TYPICALLY USED in data warehouse or DECISION SUPPORT systems.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

Buffer CACHE ADVICE, Segment Level STATISTICS, & Timed Statistics.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics.

Table functions are DESIGNED to return a set of rows through PL/SQL LOGIC but are intended to be used as a normal table or view in a SQL statement. They are also used to PIPELINE INFORMATION in an ETL process.

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

A function and PROCEDURE are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to RETURN any VALUES to the calling application, a function will return a single VALUE. A package on the other hand is a collection of functions and procedures that are grouped together based on their COMMONALITY to a business function or application.

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Setting GLOBAL_NAMES dictates how you might CONNECT to a database. This variable is either TRUE or FALSE and if it is SET to TRUE it enforces database LINKS to have the same name as the remote database to which they are linking.

Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

PRIMARY KEY and a FOREIGN key.

primary key and a foreign key.

A BITMAP INDEX.

A Bitmap index.

Fact tables and DIMENSION tables. A fact table CONTAINS measurements while dimension tables will CONTAIN DATA that will help describe the fact tables.

Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

FASTER ACCESS to DATA BLOCKS in a table.

Faster access to data blocks in 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.

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.

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.

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.

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 point 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.

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 point 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.

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:

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:

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.

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.

USE the %ISOPEN CURSOR STATUS VARIABLE.

Use the %ISOPEN cursor status variable.

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.

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.

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.

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.

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.

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.

%ROWTYPE allows you to ASSOCIATE a VARIABLE with an ENTIRE table row. The %TYPE associates a variable with a single column type.

%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.

FLOOR.

FLOOR.

IS NULL OPERATOR.

IS NULL operator.

REVOKE.

REVOKE.

RUN.

RUN.

When this clause is used with the DROP COMMAND, a parent table can be dropped EVEN when a child table exists.

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

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.

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.

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.

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.

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.

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.

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.

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.