Series: Subject: Topic:

Oracle DBA Interview Questions

Showing Questions 1 - 20 of 252 Questions
First | Prev | | Next | Last Page
Sort by: 

What is difference between spfile and init.Ora file???

Asked By: hasanvtu | Asked On: Nov 1st, 2006

Answered by: Amitkumar S Solanki on: Oct 17th, 2014

SPFile is dynamic file and PFile is static

Answered by: syed fahad on: Sep 13th, 2014

PFILE is a txt file and SPFILE is a binary(when u freshly create a database first database is read spfile after we open the database it will create pfile with the help of this command : create pfile from spfile)

What parameter should be consider while defining the size of sga

Asked By: neha.awasthi1 | Asked On: May 18th, 2011

Answered by: Vinay Bhatia on: Oct 12th, 2014


Answered by: Vishal on: Sep 25th, 2014

Depending on the RAM we need to decide the SGA

Which process writes data from data files to database buffer cache?

Asked By: Interview Candidate | Asked On: May 7th, 2005

Answered by: Anonymous on: Oct 9th, 2014

It is the server process which will do this job.

Answered by: Rengaraj on: Sep 18th, 2014

User Process will writes data from data files to database buffer cache

What is database link

Asked By: Interview Candidate | Asked On: Jul 7th, 2005

Answered by: Carlos Andrés Morales Machuca on: Sep 24th, 2014

Is a connection with another database, usually other database Oracle, but is possible with other brand using Heterogeneous Services.

Answered by: Abhinav on: Nov 5th, 2012

A database link is a path to another database with the help of which a user can access the schema objects of later database.

Oracle admin scenario based question

Asked By: Pragati.rch | Asked On: Apr 19th, 2008

I have applied the following commands:shutdown abortstartupnow what will happen, will the database will give an error / it will work.

Answered by: vishal on: Sep 25th, 2014

Database would not start normally,
We need to do Instance recovery using SMON.


Answered by: syed fahad on: Sep 13th, 2014

If a database is shut down with shutdown database command than all the SGA and background process will stop it releases memory after we startup the database the SGA and background process will start

What is the use of control file

Asked By: Interview Candidate | Asked On: Jul 7th, 2005

Answered by: syed fahad on: Sep 13th, 2014

Control file which stores all information of database like
1)Name and location of datafile and redo logfile
2)Scn number information
3)Db_name information
4Checkpoint information

Answered by: pavan dba on: Jun 12th, 2014

Control file contains:
The database name
Names and locations of associated datafiles and redo log files
The time stamp of the database creation
The current log sequence number
Checkpoint information

Can you start a database without spfile in Oracle 9i?

Asked By: Interview Candidate | Asked On: Aug 24th, 2005

Answered by: syed fahad on: Sep 13th, 2014

Yes we can start the database ( pfile is a txt file and spfile is a binary file in 9i spfile is not used by oracle only pfile is maintain to start the database )

Answered by: Saddam Mulla on: Mar 2nd, 2014

Yes we can start oracle database without spfile but required pfile
(Spfile is a system file and pfile is a manual crest file)

How do you kill a process in UNIX

Asked By: Pragati.rch | Asked On: Apr 19th, 2008

Answered by: syed fahad on: Sep 13th, 2014

Kill -9 (process id)
we can check the process is with the help of (ps -ef |grep pmon)

Answered by: hemu on: Mar 15th, 2014

Kill -9 (process id)
pkill (process name)

Startup mount and startup nomount

Asked By: Malik | Asked On: Sep 14th, 2011

What is the difference between startup mount and startup nomount? When its used?

Answered by: syed fahad on: Sep 13th, 2014

1) startup nomount :This will start the sga and background process(it will read pfile and spfile)
2) startup mount : This will only mount the datafile and controlfile locations...

Answered by: pattiprashanth on: Jul 8th, 2014

Startup NoMount: when you trigger "startup nomount". oracle reads pfile/spfile parameters and view the controlfiles in this stage. Startup Mount: when you trigger "startup mount". oracle reads contro...

Why we use materialized view? Or when do I go for an materialized view

Asked By: Rakesh057 | Asked On: Jul 19th, 2014

Please give any scenario (or) example of its very help full?

Answered by: Deepak Subramoniam on: Aug 3rd, 2014

Folks materialized views are used for the static tables where the updates are very rare.
in materialized the data set will also have referencein the sga which results in a better performance

Answered by: venkat Sidda on: Jul 28th, 2014

Performance of materialized view is greater than View

Version upgrade

Asked By: Rish Wish | Asked On: Jan 13th, 2014

How would you plan for an Oracle upgrade? What are the prerequisites?

Answered by: deepak subramoniam on: Aug 3rd, 2014

Ensure the nls Characterset of the parent and child database are same
Ensure the editions of both the database are same.
Ensure UTL_SMTP,UTL_HTTP,UTL_FILE Packages are available. (for 10g to 11g se)
wm_concat needs to be changed in the 11g because in 11g the function will not be supported by Oracle

Answered by: rohith on: Jul 21st, 2014

if u want to any up-gradation in oracle first we need to check version compatibility like if u want to upgrade from 10g to 11g the 10g version should be or then install the 11g sof...

Layer exception handling

Asked By: shanthiavari | Asked On: Jun 10th, 2008

In exception handling we have some not_found and others. In inner layer we have some not_found and others. While executing which one whether outer layer or inner layer will check first?

Answered by: AK Badola on: Dec 17th, 2012

Inner layer

How will you move a table from one schema to other ?

Asked By: Dinesh gunwant | Asked On: Oct 21st, 2011

Answered by: Nony on: Oct 7th, 2012

Alter table move tablespace USERS

Answered by: chandrasekar.s on: Jun 1st, 2012

Using alter table command we can move one table from one schema to other

  1. SQL>create TABLE t1
  2. unrecoverable AS SELECT * FROM other_schema.t1;

How to estimate size of database?

Asked By: PARTHA SINHA | Asked On: Jan 25th, 2007

Answered by: sanu on: Sep 26th, 2012

SQL> select a.datafile_size + b.temp_size + c.redo_size + d.controlfile_size "Total_size in GB" from ( select sum(bytes)/1024/1024/1024 as datafile_size from dba_data_files) a, ( select nvl(sum(by...

Answered by: prati on: Mar 7th, 2007

Select sum ( bytes) from dba_data_files ;select sum(bytes) from v$log;select sum ( bytes ) from dba_temp_files ;Total sum of all the three will give the database size .

What is the difference between view and materialised view?

Asked By: madhuri_v123 | Asked On: Aug 23rd, 2012

Answered by: hanuman on: Sep 4th, 2012

View does not consume space and materialized view consume space

Answered by: manoj100 on: Aug 26th, 2012

A view is a simple select statement executed each time user runs query against it A materialized view is a physical persistent object used for performance improvement of summary data or replication. ...

Online backup of a control file

Asked By: Pragati.rch | Asked On: Apr 19th, 2008

Can you take online backup of a control file if yes, how?

Answered by: venu on: Sep 1st, 2012

  1. SQL> ALTER DATABASE backup controlfile TO trace AS <PATH>
Specify the path where you want to store the controlfile backup.

Answered by: neha.awasthi1 on: May 18th, 2011

We can do this by suding RMAN:configure control file auto backup on;

Where can you check the state of the database after the startup command.

Asked By: Interview Candidate | Asked On: Oct 2nd, 2004

Answered by: venu on: Sep 1st, 2012

  1. SELECT STATUS FROM v$instance
will give the required result.

Answered by: Wildy on: Jul 18th, 2011

Select instance_name, status, database_status, startup_time from v$instance;

Which functions can be improved by using transportable tablespaces?

Asked By: Interview Candidate | Asked On: Oct 2nd, 2004

Answered by: venu on: Sep 1st, 2012

The transportabel tablespaces enable us to transport data objects across different platforms.

Backing up or recovering a particular user/schema objects or data objects or tablespaces or entire databases is possible, which cannot be done using other backup techniques.

Answered by: Sriniv@s.. on: Jun 2nd, 2012

Moving data using transportable tablespace can be much faster than performing either export/import or unload/load of the same data.Bcz transporting a tablespace only requires the copying of datafiles & integrating the tablespace structural information.

What is SCN number in Oracle? Plz any one give me the explanation for SCN

Asked By: suri | Asked On: Sep 18th, 2006

Answered by: venu on: Sep 1st, 2012

SCN is generated by oracle when a transaction is successfully commited. This is referred to as SystemChangeNumber. We can think of it as a transaction id generated when we make some transaction in an ...

Answered by: SANTHOSH on: Aug 15th, 2012

SCN no is a number which is generated by the oracle, when we made any changes to the data that statements will be recorded in redo log files and every committed statement will be given this SCN no by oracle.

When a user comes to you and asks that a particular SQL query is taking more time. How will you solve this?

Asked By: anjibabu padala | Asked On: May 10th, 2007

Answered by: venu on: Sep 1st, 2012

We can also try the explainplan and tkprof utilities.

Answered by: Madhusmita Dash on: Jun 21st, 2012

I think we should check the indexing structure.May be for that it took more time.

First | Prev | | Next | Last Page





twitter fb Linkedin GPlus RSS


Interview Question

 Ask Interview Question?


Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.