Oracle updating through view
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?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.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.
Or if the ratio BR_BLK_LEN/ LF_BLK_LEN BR_BLK_LEN is nearing 0.3. How can variables be passed to a SQL routine By use of the & symbol.
A value greater than 80-90% is good, less could indicate problems. It can be reduced by setting the storage parameters on the table to appropriate values. If it is specified, the error is ignored and the tables data will be inserted. The packages used by DBAs may include: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT. 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. You are using hot backup without being in archivelog mode, can you recover in the event of a failure? One is to look at the v$session or v$process views.
If you simply take the ratio of existing parameters this will be a cumulative value since the database started. 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? It can be corrected by export and import of the effected table. Give one method for transferring a table from one schema to another: There are several possible methods, export-import, CREATE TABLE... Another way is to check the current_logins parameter in the v$sysstat view.
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.