Expected answer: SQLCODE returns the value of the error number for the last error encountered. The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements.
The SQLERRM returns the actual error message for the last error encountered. 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. 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. This indicates you need to tune the sort area parameters in the initialization files. What parameters control copy latches When you get excessive contention for the copy latches as shown by the "redo copy" latch hit ratio. 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. 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.
If they include the SQL routines provided by Oracle, great, but not really what was asked. Describe the use of PL/SQL tables Expected answer: PL/SQL tables are scalar arrays that can be referenced by a binary integer. However if you also have chained rows this can hurt performance. How do you set up tablespaces during an Oracle installation?
They can be used to hold values for use in later queries or calculations. The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. 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. You see multiple fragments in the SYSTEM tablespace, what should you check first? t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by checking the DBA_USERS view. What are some indications that you need to increase the SHARED_POOL_SIZE parameter?
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.
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. command, but this only works against a single instance installation. 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.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.In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD. It must come first in a PL/SQL stand alone file if it is used. 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? Expected answer: OPEN then FETCH then LOOP followed by the exit when. Poor data dictionary or library cache hit ratios, getting error ORA-04031.
If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers? Another indication is steadily decreasing performance with all other tuning parameters the same. What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans? The two should have a product equal to 64 or a multiple of 64. What is the fastest query method for a table Fetch by rowid 7. What initialization parameter should be turned on to get full TKPROF output?
They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. Another possible method is to just use the SHOW ERROR command, but this only shows errors. 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. The major sort are parameter is the SORT_AREA_SIZe parameter. You can increase copy latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs on your system. Where can you get a list of all initialization parameters for your instance? This is bad since it makes tracking which table the constraint belongs to or what the constraint does harder. 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. 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. 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. 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.