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.

Part-2

Can anyone tell me the main difference between the Checkpoint and SCN. How does that work in reality -

Checkpoint

Checkpoint in database is used to reduce the amount of the time for recovery. It is a background process in oracle(CKPT), which cause DBWR to write all the data blocks that have been modified since the last checkpoint to the datafile.Checkpoint is responsible for->Signaling DBWn->Updating datafile headers with checkpoint info. (i.e. SCN)->Updating Control file with checkpoint info. (i.e. scn)

SCN

SCN is the ever increasing number to determine the age of database.it's System Change Number / System Commint Number...It's used to determine the consistancy of database..It's stored at various places like control file, data files, redo log files.If it doesn't match, that means inconsistancy..

why do we add online redo group and when do we do it in real time . -

we add redolog group to give the checkpoint(CKPT) enough time to write the scn on the control and the data file header. this happens wen the redolog group is full and a log switch is occurs. to avoid the logwriter(LGWR) to overwrite the redolog groups before the scn's and the LSN's(log sequence number) are writen on the header of the controlfile and the data file by the CKPT.

Compare and contrast TRUNCATE and DELETE for a table. -

TRUNCATE:will remove the rows permanently from table without deleting the table structure. And will free up the space occupied by the data. Data can't be ROLLBACKDELETEwill remove the data from table but will not release the space occupied by rows. Can be retrieved by ROLLBACK command. Both are used to remove data from the table.But the difference between their work is:->Delete is DML statement, While Truncate is DDL statement.-> The changes done by Delete commmand are stored in redo log files, while those by Truncate, are not logged.-> So, the changes made by Delete command can be rolled back, but the Changes made by Truncate are Permanent...-> Moreover, Because of this nature, Truncate works faster than Delete command

why oracle returns the error snapshot too old? -

with the help of a example we can understand snapshot to old error .suppose scott run's a script where lot's of updation is performed. mean's lot's of undo is generated. and if your undo tablespace is small then it flush out the old values within it.And suppose there is another user try to see the value that is updated by the scott.in this case because that value is flushed by the scott so if other user is try to see the value oracle server prompt's a message "SNAPSHOT TO OLD"the solution is:-1. increase the size of undo tablespace2. check your undo retention policy.

what is the use of maxlogfiles parameter? -

MAXLOGFILES-->This parameter specifis the maximum number of redo log file groups that can ever be created for the database. Oracle Database uses this value to determine how much space to allocate in the control file for the names of redo log files. The default, minimum, and maximum values depend on your operating system.This value must be at least 2.

What is the difference between Hot and Cold backup? -

Hot backup is done when database is online while cold backup is done when database is offline.

how do we create data dictionary -

catalog.sql or catproc.sql script create all data dictionary views.

the use of control file -

It maintains the consistency and integrity of database physical files. It keeps track of the present state of database structure. It is opened in Mount state.

Part-1

How we can switch Database Read only to Read/write with out shutdown the database -
alter database startup readonly (for read only mode)
alter databse startup read/write (without shutting it down)
how can we know that which file we r using in a database SPFILE R PFILE? -
If your init.ora is in format initSID.ora that means you are using pfile.
if ARCn process is not working properly and our database is in archievelog mode, then what will happen to our database? it will run r not? Give reason ?
-if arc n fails for any reason after transation activity filled all the rodologs ,oracle server hangs.it is a legal hang becuase when we set the database in arh mode oracle server must not overwrite redo logs unless they are archived.
If any of our back-ground process(mandatory) is not working properly then what will happen to our database? Can we use our database?
-With ORACLE 10g,all six mandatory background process has to run in order to start the database and run it.The instance even will not start,forget about database.This u can check out in alert_SID.log file at OS level(without starting the database).All mandatory background process have to be up and running in order to have a Up n running DB.
Can we use Non-standard block size in temporary tales? -no
what is high water mark in oracle -
The high water mark is divides a segment into used blocks free blocks. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan. Oracle keeps track of the high water mark for a segment in the segment header. It acts as the boundary between used and unused space. As the amount of data grows due to row inserts and updates, the segment's high water mark grows accordingly. But as row deletes and updates shrink the amount of data in the object, the high water mark is not altered to reflect the segment's new characteristics.
when do we use list partitioning on tables. can we use indexes in temp tables -
by making the table as a partitions internally headers will be created for the table partitions.so that data will be retrieved faster than we use the indexes.ex:if we want 2 retrieve 1,00,000 records of the same table without table partioninng it will take 30 minutes.for this only one header will be used.now divide the table into 4 partitions,than 4 headers will be created and 4,00,000 records will be retrieved in the same 30 mins time.this is the added advantage of table partioning.indexes will be slower than this table partitioning.indexes are helpful if the table contains less number of records only.
Why do we need Virtual IP in Oracle 10G RAC? -
For the internal comunication of all the nodes of RAC.Each nodes have his own vertual IP.
How recyclebin works in Oracle 10G? how long an database objects remain in recyclebin? -
The object in the recycle bin will remain until the space used by it is not reused.Once Oracle wants the space for newer objects or newer rows, it will be reused and we cannot get back the object from recycle bin.
What is the simplest command to create a database -
create database

Apps Questions also

Reference:http://dba.fyicenter.com/interview/Oracle_DBA_Interview_questions_and_Answers.html

This site has about 16 pages of interview questions

Differentiate 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 the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
1000000

Can you use a commit statement within a database trigger?
Yes, if you are using autonomous transactions in the Database triggers.

What is an UTL_FILE? What are different procedures and functions associated with it?
The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).Subprogram -DescriptionFOPEN function-Opens a file for input or output with the default line size.IS_OPEN function -Determines if a file handle refers to an open file.FCLOSE procedure -Closes a file.FCLOSE_ALL procedure -Closes all open file handles.GET_LINE procedure -Reads a line of text from an open file.PUT procedure-Writes a line to a file. This does not append a line terminator.NEW_LINE procedure-Writes one or more OS-specific line terminators to a file.PUT_LINE procedure -Writes a line to a file. This appends an OS-specific line terminator.PUTF procedure -A PUT procedure with formatting.FFLUSH procedure-Physically writes all pending output to a file.FOPEN function -Opens a file with the maximum line size specified.

Difference between database triggers and form triggers?
Database triggers are fired whenever any database action like INSERT, UPATE, DELETE, LOGON LOGOFF etc occurs. Form triggers on the other hand are fired in response to any event that takes place while working with the forms, say like navigating from one field to another or one block to another and so on.

What is OCI. What are its uses?
OCI is Oracle Call Interface. When applications developers demand the most powerful interface to the Oracle Database Server, they call upon the Oracle Call Interface (OCI). OCI provides the most comprehensive access to all of the Oracle Database functionality. The newest performance, scalability, and security features appear first in the OCI API. If you write applications for the Oracle Database, you likely already depend on OCI. Some types of applications that depend upon OCI are: · PL/SQL applications executing SQL· C++ applications using OCCI· Java applications using the OCI-based JDBC driver· C applications using the ODBC driver· VB applications using the OLEDB driver· Pro*C applications· Distributed SQL

What are ORACLE PRECOMPILERS?
A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Examples are the Pro*C Precompiler for C, Pro*Cobol for Cobol, SQLJ for Java etc.

What is syntax for dropping a procedure and a function? Are these operations possible?
Drop Procedure/Function ; yes, if they are standalone procedures or functions. If they are a part of a package then one have to remove it from the package definition and body and recompile the package.

Reference:http://www.iheavy.com/node/87
1. What is the difference between RMAN and a traditional hotbackup?
RMAN is faster, can do incremental (changes only) backups, and does not place tablespaces into hotbackup mode.

2. What are bind variables and why are they important?
With bind variables in SQL, Oracle can cache related queries a single timein the SQL cache (area). This avoids a hard parse each time, which saves onvarious locking and latching resources we use to check objects existence andso on. BONUS: For rarely run queries, especially BATCH queries, we explicitely DO NOT want to use bind variables, as they hide information from the Cost Based Opitmizer.BONUS BONUS: For batch queries from 3rd party apps like peoplesoft, if wecan't remove bind variables, we can use bind variable peeking!

3. In PL/SQL, what is bulk binding, and when/how would it help performance?
Oracle's SQL and PL/SQL engines are separate parts of the kernel whichrequire context switching, like between unix processes. This is slow, anduses up resources. If we loop on an SQL statement, we are implicitelyflipping between these two engines. We can minimize this by loading ourdata into an array, and using PL/SQL bulk binding operation to do it all inone go!

4. Why is SQL*Loader direct path so fast?
SQL*Loader with direct path option can load data ABOVE the high water markof a table, and DIRECTLY into the datafiles, without going through the SQLengine at all. This avoids all the locking, latching, and so on, anddoesn't impact the db (except possibly the I/O subsystem) at all.

5. What are the tradeoffs between many vs few indexes? When would you wantto have many, and when would it be better to have fewer?
Fewer indexes on a table mean faster inserts/updates. More indexes meanfaster, more specific WHERE clauses possibly without index merges.

6. What is the difference between RAID 5 and RAID 10? Which is better forOracle?
RAID 5 is striping with an extra disk for parity. If we lose a disk we canreconstruct from that parity disk. RAID 10 is mirroring pairs of disks, andthen striping across those sets.
RAID 5 was created when disks were expensive. Its purpose was to provideRAID on the cheap. If a disk fails, the IO subsystem will perform VERYslowly during the rebuild process. What's more your liklihood of failureincreases dramatically during this period, with all the added weight of therebuild. Even when it is operating normally RAID 5 is slow for everythingbut reading. Given that and knowing databases (especially Oracle's redologs) continue to experience write activity all the time, we should avoidRAID5 in all but the rare database that is MOSTLY read activity. Don't putredologs on RAID5.
RAID10 is just all around goodness. If you lose one disk in a set of 10 forexample, you could lose any one of eight other disks and have no troubles.What's more rebuilding does not impact performance at all since you'resimply making a mirror copy. Lastly RAID10 perform exceedingly well in alltypes of databases.

7. When using Oracle export/import what character set concerns might comeup? How do you handle them?
Be sure to set NLS_LANG for example to "AMERCIAN_AMERICA.WE8ISO8859P1". Ifyour source database is US7ASCII, beware of 8-bit characters. Also be waryof multi-byte characters sets as those may require extra attention. Alsowatch export/import for messages about any "character set conversions" whichmay occur.

8. How do you use automatic PGA memory management with Oracle 9i and above?
Set the WORKAREA_SIZE_POLICY parameter to AUTO and set PGA_AGGREGATE_TARGET

9. Explain two easy SQL optimizations.
a. EXISTS can be better than IN under various conditionsb. UNION ALL is faster than UNION (not sorting)

10. Name three SQL operations that perform a SORT.
a. CREATE INDEXb. DISTINCTc. GROUP BYd. ORDER BYf. INTERSECTg. MINUSh. UNIONi. UNINDEXED TABLE JOIN

11. What is your favorite tool for day-to-day Oracle operation?
Hopefully we hear some use of command line as the answer!

12. What is the difference between Truncate and Delete? Why is one faster?Can we ROLLBACK both? How would a full table scan behave after?
Truncate is nearly instantaenous, cannot be rolled back, and is fast becauseOracle simply resets the HWM. When a full table scan is performed on atable, such as for a sort operation, Oracle reads to the HWM. So if youdelete every single solitary row in 10 million row table so it is now empty,sorting on that table of 0 rows would still be extremely slow.

13. What is the difference between a materialized view (snapshot) fastrefresh versus complete refresh? When is one better, and when the other?
Fast refresh maintains a change log table, which records change vectors, notunlike how the redo logs work. There is overhead to this, as with a tablethat has a LOT of indexes on it, and inserts and updates will be slower.However if you are performing refreshes often, like every few minutes, youwant to do fast refresh so you don't have to full-table-scan the sourcetable. Complete refresh is good if you're going to refresh once a day.Does a full table scan on the source table, and recreats the snapshot/mview.Also inserts/updates on the source table are NOT impacted on tables wherecomplete refresh snapshots have been created.

14. What does the NO LOGGING option do? Why would we use it? Why would webe careful of using it?
It disables the logging of changes to the redologs. It does not disable ALLLOGGING, however as Oracle continues to use a base of changes, for recoveryif you pull the plug on the box, for instance. However it will causeproblems if you are using standby database. Use it to speed up operations,like an index rebuild, or partition maintenance operations.

15. Tell me about standby database? What are some of the configurations ofit? What should we watch out for?
Standby databases allow us to create a copy of our production db, fordisaster recovery. We merely switch mode on the target db, and bring it upas read/write. Can setup as master->slave or master->master. The latterallows the former prod db to become the standby, once the failure cause isremedied. Watch out for NO LOGGING!! Be sure we're in archivelog mode.

Oracle Data Pump

What is Oracle's Data Pump?
Oracle simply states that Data Pump is a "very high-speed" mechanism for moving data and meta-data from one database to another. Is Data Pump a complete replacement for export (exp) and Import (imp)? Oracle clearly states that the only time you would/ should use the original (exp & imp) is when you need backward compatibility to an earlier version that does not have Data Pump export & import (expdp & impdp). Basically if you wanted to import a 10g export into a pre-10g database or import into a 10g database from a pre-10g database. As we go through this evaluation of Data Pump and its usage this will become more clear if there are any other situations. I venture to say there is.
Data Pump has three distinct components.
The uniqueness of Data Pump is that all processing is done through the database server and through DIRECTORY objects. To do this there are two internal packages to Oracle and an optional replacement for the original export and import command line utilities (exp & imp).
expdp & impdp
These to command line utilities are very close to the old standby export & import (exp & imp) utilities. They are not stand-alone utilities in the sense that they use the DBMS_DATAPUMP PL/SQL package to execute the export and import functions. They accept a variety of command line options that, like exp & imp, allow you to pick and choose the objects to be exported and imported.
DBMS_DATAPUMP
The Data Pump API and can be used independently of expdp & impdp. Is the package accessed to move data and / or metadata between databases.
DBMS_METADATA
The meta-data API in Oracle and can also be used independently of expdp & impdp. If you remember this is the package we were using in the last two articles for extracting meta-data. I am very interested in how it interfaces with Data Pump.

For more on Data Pumps:http://www.orafaq.com/node/67