Part-3

When a query is sent to the database and an index is not being used, what type of execution is taking place? -
Full Table Scan(FTS)

How do you measure table fragmentation? -
To find the table level fragmentation:The first thing we have to do is, we have to analyze the table by using ANALYZE TABLE COMPUTE STATISTICS; After that we have to check thetable name called dba_tables column called chain_cnt if the value of chain_cnt>0 the table is fragmented.keep in mind that we came to know this only after analyzing the particular table by using ANALYZE.. cmd. before go to the analyzing we check when the corresponding table has analyzed by the table dba_tables column is last_analyzed.

Difference between lock and latches? -
locks are used to protect the data or resourses from the simulteneous use of them by multiple sessions which might set them in inconsistant state... Locks are external mechanism, means user can also set locks on objects by using various oracle statementsWhile latches are for the same purpose but works at internal level. Latches are used to Protect and control access to internal data structres like various SGA buffers.They are handled and maintained by oracle and we can't access or set it.. this is the main difference

why do we switch from pfile to spfile and when do we use spfile -
We switch from pfile to spfile when we want to change some parameters in init.ora dynamically (ie. when the database is up and running ) .In case of pfile if we change the value of some parameters while the db is running , it will take effect on next time the db starts .

what is checkpoint? -

At checkpoint the background process CKPT does the following1)signalling the DBWR(Background process) at checkpoints2)Updating the datafile headers with checkpoint information3)Updating controlfiles with checkpoint information.

Can you use a commit statement within a database trigger? -

Yes we can, using pragma autonomous_transaction
CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT ON t1 FOR EACH ROW
DECLAREPRAGMA AUTONOMOUS_TRANSACTION;i PLS_INTEGER;
BEGIN
SELECT COUNT(*)INTO iFROM t1;
INSERT INTO t2VALUES(i);
COMMIT;END;

What is the difference between TRUNCATE and DELETE? -

The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

What is written in Redo Log Files? -

redolog gets written by the background process lgwr in the following circumfstances1. at commits2. redo log bufer becomes 1/3 full3. when there is more thana 1Mb of changed records in the redo log bufer4. when a timeout occurs (every 3 seconds)5. before the DBWn writes modified blocks in the databse buffer cache to the data files

How many maximum Redo Logfiles one can have in a Database? -

Maximum number of log files a database can accomodate depends on the parameter "MAXLOGMEMBERS" specificed during database creation.

Is it possible to disable the parameter while running reports? -Yes
Where would you look for errors from the database engine? -In the alert log.
How do you switch from an init.ora file to a spfile? -

Issue the create spfile from pfile command

What is a request Sets? How to create a request Set? -

Request set is a group of requests.It is made to perform the request in a certain sequence.Request se can be created from system admin responsibility.

How to register a table or view or synonym from backend? -
Use AD_DD.Register_Table procedure to register the table.

Where does the SCN resides (system change number) ?
It resides in control files, datafile headers, and redo records,alert log file.

What is mutating trigger? How do you avoid mutating trigger? -
The basic reason for this error is the way Oracle manages a read consistent view of data. The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating.