Database Interview Questions - Basics I

Database

Q 1: What are the steps involved in database startup?
Ans: Start an Instance, Mount the database and Open the Database.

Q2: What are the steps involved in database shutdown?
Ans : Close the database ; Dismount the database and Shutdown the instance.

Q3: What is Archived Redo log?
Ans: Acrhived Redo Log consists of Redo Log files that have archived before being reused

Q4: What is a temporary segment?
Ans: Temporary segments are created by ORACLE when a sql statement needs a temporary work area to complete execution. When the statement finishes execution , the temporary segment extents are released to the system for future use.

Q5: What does a control file contain?
Ans: A control file records the physical structure of the database, database name,time stamp of database creation,max datafile limit,max redo log group,max log file members, database archive information,backup information..

Q6:What is a Tablespace?
Ans: A database is divided into Logical storage unit called tablespace. A tablespace used to grouped related logical structures together

Q7: Explain the relation ship among the database,tablespace and datafiles?
Ans: Each database logically divided in to one or more tablespaces. One or more datafiles are explicitly created for each tablespace.

Q8:What is Logical structure of the database?
Ans: Database àTablespacesàsegmentsàExtentsàOracle Blocks

Q9: What is the physical structure of the database?
Ans: Datafiles,Control File and redo log files.

Q10: What are Different types of segments?
Ans: Temp Segement, Undo Segment, Table Segment and Index Segment.

Q11: Can you name few DBMS Package names and their use?
Ans: DBMS_JOBS, DBMS_STATS,DBMS_SQLTUNE..

Q12: How Do you find whether the instance was started with PFILE or SPFILE?
Ans: Sho parameter spfile or query from v$parameter.

Q13: What Column differentiates the v$views to the gv$views and how?
Ans: The INST_ID Column which indicates the instance in a RAC environment the information came from ..

Q14: Explain an Ora -1555
Ans: You get this error when you get a snap shot too old within roolback.It can usually be solved by increasing the undo retention or increasing the size of rollbacks

Q15:What is Diffeernce between a temporary tablespace and a permanent tablespace?
Ans: A temporary tablespace is used for Sort Oprations of no sufficient space found in PGA. Permanent tablespace stores the true objects of dataase ..

Q16:How Do you add a datafile to a tablespace?
Ans: alter tablespace add datafile size ;

Q17: How Do you resize a datafile?
Ans: alter database datafile resize ;

Q18: What view Would you use to look at th size of a datafile?
Ans: dba_data_files,V$datafile

Q19: What view Would you use to determine free space in a tablespace?
Ans: dba_free_space

Q20: How can you gather statistics on a table?
Ans: The analyze command or using DBMS_STATS package.

Q21: List out the views names we can use to find about undo usage?
Ans: V$undostat,dba_undo_segs

Q22: What is view we can use to find out database locks?
Ans: v$lock

Q23: Using which view will check for session and process details?
Ans: V$session,v$process

Q24: Using which view will you find out , how much % Operation or job completed sofar it is running in database?
Ans: v$session_longops

Q25: How will you find out running job names from database?
Ans: Querying from dba_jobs,Dba_jobs_running views

Q26:How will find out Database creation time details? What is the column name?
Ans: v$database; created is column

Q27: How will you find out instance startup time?
Ans: v$instance,startup_time

Q28: How do you find archive is enabled for database?
Ans: archive log list or v$database ,log_mode Cloumn

Q29: How will you find active/inactive session count from database?
Ans: select status,count(*) from v$session group by status;

Q30: explain about temporary tablespace groups?
Ans: It is 10g new feature . it is a group of temporary tablespaces. We can get more details from dba_tablespace_groups if it is enabled.

Q31: What is undo_retention?
Ans : Time specification for undo segments hold the data ..and these reusable are reusable..

Q32: How will you find undo management is auto or manual?
Ans : Sho parameter undo_management

Q33: If your database contain 4 undo tablespaces..how will you find out which undo tablespace is used by database?
Ans: Sho parameter undo_tablespace

Q34: If you database contain 5 temporary tablespaces how will you find out what is default temporary tablespace to databse?
Ans: By querying database_properties

Q35. What is alert log file and how will you find out the location of file from database?
Ans : Sho parameter dump

UNIX:

Q36: How can you determine if an Oracle Instance is up from the Operating system level?
Ans: ps –efgrep smon/pmon

Q37: How can u find dead processes?
Ans: ps –efgrep zombie or who –d or ps –efgrep defunc

Q38: Give the command to display space usage on the UNIX File system?
Ans: df –lk or df –lh or df –k/h

Q39: Explain sar and vmstat?
Ans: sar used for to get system activity report and Vmstat reports on virtuval memory statistics for processes .

Q40: How do you find the Load average of a server?
Ans: using uptime command.

Q41: How do you find out swap usage on a server?
Ans: free –g

Q42: How will you kill a process id at OS level ?
Ans: kill -9

Q43: How will you find out the count of Oracle processes running on server?
Ans: ps –efgrep wc –l

Q44: How will you check if any Rman backup is running?
Ans: ps –efgrep rman

Q45: How will check cron schedule job details?
Ans: Crontab –l

Q46 : How will find ASM instance is running on server?
Ans : ps –efgrep +ASM

Q47 : I have a very big file at OS and I want see last 50 lines of the file only..Which command will you use?
Ans : tail -50

Q48: How can you find , file last accessed ( modified )date?
Ans : ls –l Q49: How will you find out howmany instances are running on a server?
Ans : ps –efgrep smon (gives all instance names which are running)

Q50 : What is the command we can use to find trace of process at OS level?(means what process is doing)
And : strace –p