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
----------------------------------------------------
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
----------------------------------------------------
No comments:
Post a Comment