-
What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
Tirumal Devulapalli
- Feb 23rd, 2006
CBO (Cost Based Optimizer):Generates an execution plan for a SQL statementoptimizer_index_cost_adj parameter can be set to help CBO to decide an execution plan, which effects the speed of SQL query.we...
-
What does database do during mounting process?
midhunam
- Mar 7th, 2010
When the startup command enters the mount stage, it opens and reads the control file. The control file is a binary file that tracks important database information, such as the location of the databa...
cmanne
- Jan 4th, 2006
During database mount process, Oracle would check for the existence of controlfiles mentioned in init.ora file but it wont check the contents of the controlfile which is done during the opening of database.
-
Physical and Logical Backup
What kind of backup you take Physical / Logical? Which one is better and Why?
mselva84
- Feb 22nd, 2010
Logical Backups: In this backup Oracle Export utility stores data in Binary file at OS level.Physical Backups: In this backup physically Datafiles are copied from one location to another( Disk, Tape etc.)
bishma
- Aug 30th, 2008
Logical backup means backing up the individual database objects such as tables, views , indexes.. using the utility called EXPORT, provided by Oracle. The objects exported in this way can be imported ...
-
What are the different types of segments
-
PCT Free
What is PCT Free and PCT Used?What is PCT increase parameter in segment?What is growth factor?
mkguduru
- Jan 21st, 2010
PCTFREE: Percentage of blocks to be reserved for future updates.After this parameter is met the block is considered to be full and it is not available to insert new rows. PCTUSED: It allows the block to reconsidered for the insertion of new rows.
refakat
- Aug 12th, 2009
This is the parameter which specify in percent? that a block can only used for insert or come in the freelist(list of blocks in segment ready for insert operation) when used space in a block is less ...
-
What is hash cluster
hmounir
- Dec 30th, 2009
A special type of clusterd table, a hash cluster, operates much like a regular clusterd table, except that instead of using a cluster index, a hash cluster uses a hashing function to store and retrieve rows in a table.
soccer_champ
- Jun 20th, 2005
A hash cluster uses a hash algorithm, which could either be system generated or user defined, to calculate the location of a row, both for retreival and for DML.
-
Explain the relationship among database, tablespace and data file.What is schema
hmounir
- Dec 27th, 2009
The datafiles in an Oracle database are grouped together into one or more tablespaces. An Oracle tablespace consists of one or more datafiles; a datafile can be a part of one and only one tablespace.Schema can own any type of object in the database: Indexes, Sequences, Views, so forth.
imran44
- Jan 9th, 2009
The database is actually a collection of objects, which are made up of tablespaces and each tablespace must have datafile associate with it. The tablespace cannot be created without datafile. The tabl...
-
What are the types of synonyms
hmounir
- Dec 27th, 2009
1- a private synonym is defined in the schema of a user and is available only to the user.2- a public synonym is usually created by dba and is available for use by any database user.
praveena
- Sep 12th, 2005
Two types of synonims.1.public synonim,2.private synoniim.private synonym is confined to a particular user.public can be used by all the user's.
-
What is the correct sequence among FETCH, EXECUTE, And PARSE
Vishal
- Sep 18th, 2007
1. Parse: a. Search for identical statementb. Check syntax, object names, and privilegesc. Lock objects used during parsed. Create and store execution plan2. Execute: Process the Statement3. Fetch: Return rows to User Process
-
Temp DB Full
What do you do when tempdb is full?
hari_97us
- Nov 26th, 2009
You need to clean up the space and add more space in order to prevent this error in future.
-
Oracle Compress Parameter
My database size is 100GB. I am using compress parameter. Will my database size reduce, remain same, Increase. What will Happen.
krishna miryala
- Nov 5th, 2009
When "Y",export will mark the table to be loaded as one extent for the import utility. This option will use only if you want to do import. export size will not change if we use compress=y.
-
Dump Destination
What is dump destination? What are bdump, cdump and udump? Explain in detail.
anurag2202
- Oct 30th, 2009
The dump destination is: Oracle_home/admin/<SID>The UDUMP specifies user dump directory where all the user related information is stored in the form of trace file.The BDUMP specifies background ...
aravindhreturns
- Jul 6th, 2009
UDUMP is specifying the user dump directory where all user error logs (trace files) will be placed.BDUMP is specifying the background dump directory where all database error logs (trace files) will be...
-
Naming exported files
When exporting using filesize parameter, if three files is generated. How the Oracle name this three files?
jp1985verma
- Oct 22nd, 2009
When Export reaches the value you have specified for the maximum FILESIZE, Export stops writing to the current file, opens another export file with the next name specified by the FILE parameter, and c...
-
How to find how many database reside in Oracle server in query?
cmanne
- Sep 2nd, 2009
1. Check the oratab file to get the list of databases on a server.Caveat: If the database is created manually, then the oratab won't updated automatically which should be done manually as oratab i...
Krishna Miryala
- Apr 15th, 2007
$ ps -eaf|grep pmon
-
Corrupted Rollback Segment
How will you rectify if one of the rollback segments gets corrupted
-
DML operations on standby database
Can you perform DML operations when the standby database is in read only in Standby Database?
cmanne
- Sep 2nd, 2009
In a nutshell, One can do SELECTS only on a Physical/Logical Standby database. The basic principle behind Oracle Standby database is to provide protection against media failures. So no DMLs are allowe...
aravindhreturns
- Jul 7th, 2009
I agree with omelkoz, But I need to add a small point here. Specifying STANDBY is to to prevent all users other than SYS from making DML and DDL changes to any table or sequence being maintained through SQL apply operations. Not everybody can execute statements on a standby. :)
-
How to Estimate the size of Tablespace???
cmanne
- Sep 2nd, 2009
During the design phase of database, The tablesapce size is estimated by calculating the size of typical row of various tables that are going to reside in that tablespace multiplied by the number of e...
cmanne
- Sep 2nd, 2009
Select tablespace_name, sum(bytes)/1024/1024fromdba_data_filesgroup by tablespace_nameorder by tablespace_name;
-
How do you find wheather the instance was started with pfile or spfile
Thhere are 3 different ways :-1) SELECT name, value FROM v$parameter WHERE name = 'spfile'; //This query will return NULL if you are using PFILE2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you are using pfile and not spfile3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; // if the count is non-zero then the instance is using a spfile, and if the count...
-
How to find which tablespace belongs to which datafile ?
ravichandiran
- Nov 16th, 2007
run this query in the sql promptsql> select tablespace_name,file_name from dba_data_files;
-
Why in 10G, when you use real time apply feature in conjunction with Maximum Protection, you can achive zero data loss, but not zero database downtime??
santosh Panigrahi
- Jul 12th, 2009
The 2 basic feature of Oracle 10g makes database Maximum protection and Zero downtime (1) Undo Retention Guarantee.(2) Stand by server compatibility.
senthilora
- Sep 24th, 2007
Maximum Protection mode - guarantees Zero data loss. But it can not guarantee Zero down time.Any transaction happening in Primary Database has to be propagated and commited in Standby Datab...
Oracle DBA Interview Questions
Ans