Database Interview Questions

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)

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

Q50:  List components of an Oracle instance?

Ans:

An Oracle instance is comprised of memory structures and background processes.

The Systems Global Area (SGA) and shared pool are memory structures. The process monitor is a background process (DBWn, LGWR, ARCn, and PMON). The Oracle database consists of the physical components such as data files; redo log files, and the control file.

Q51: Which background process and associated database component guarantees that committed data is saved even when the changes have not been recorded in the data files?

Ans:

LGWR (log writer) and online redo log files. The log writer process writes data to the buffers when a transaction is committed. LGWR writes to the redo log files in the order of events (sequential order) in case of a failure.

Q52: What is the maximum number of database writer processes allowed in an Oracle instance?

Ans:

The maximum is ten. Every Oracle instance begins with only one database writer process, DBW0. Additional writer processes may be started by setting the initialization parameter DB_WRITER_PROCESSES (DBW1 through DBW9).

Q53: Which background process is not started by default when you start up the Oracle instance?

Ans:

ARCn. The ARCn process is available only when the archive log is running (LOG_ARCHIVE_START initialization parameter set to true). DBWn, LGWR, CKPT, SMON, and PMON are the default processes associated with all instances (start by default).

Q54: Describe a parallel server configuration?

Ans:

In a parallel server configuration multiple instances known as nodes can mount one database. In other words, the parallel server option lets you mount the same database for multiple instances. In a multithreaded configuration, one shared server process takes requests from multiple user processes.

Q55: Choose the right hierarchy, from largest to smallest, from this list of logical database structures?

Ans:

Database, tablespace, segment, extent, data blocks.

Q56: Which background process is responsible for writing the dirty buffers to the database files?

Ans:

The purpose if the DBWn is to write the contents of the dirty buffer to the database file.

This occurs under two circumstances – when a checkpoint occurs or when the server process searches the buffer cache for a set threshold.

Q57: Which component in the SGA has the dictionary cache?

Ans:

The dictionary cache is part of the shared pool. The shared pool also contains the library cache and control structures.

Q58: When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?

Ans:

The process monitor (PMON) releases the locks on tables and rows held by the user during failed processes and it reclaims all resources held by the user. PMON cleans up after failed user processes.

Q59: What is a dirty buffer?

Ans:

A dirty buffer refers to blocks in the database buffer cache that are changed, but are not yet written to the disk.

Q60: If you are updating one row in a table using the ROWID in the WHERE clause (assume that the row is not already in the buffer cache), what will be the minimum amount of information read to the database buffer cache?

Ans:

The block is the minimum amount of information read/copied to the database buffer cache.

Q61: What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?

Ans:

To reduce I/O contention, the DBWn process does not write the changed buffers immediately to the disk. They are written only when the dirty buffers reach a threshold or when there are not enough free buffers available or when the checkpoint occurs.

Q62: Which memory structures are shared? Name two.

Ans:

The library cache contains the shared SQL areas, private SQL areas, PL/SQL procedures, and packages, and control structures. The large pool is an optional area in the SGA.

Q63: When a SELECT statement is issued, which stage checks the user’s privileges?

Ans:

Parse checks the user’s privileges, syntax correctness, and the column names against the dictionary. Parse also determines the optional execution plan and finds a shared SQL area for the statement.

Q64: Which memory structure records all database changes made to the instance?

Ans:

The redo log files holds information on the changes made to the database data. Changes are made to the database through insert, update, delete, create, alter, or drop commands.

Q65: What is the minimum number of redo log files required in a database?

Ans:

The minimum number of redo log files required in a database is two because the LGWR (log writer) process writes to the redo log files in a circular manner.

Q65: When is the system change numbers assigned?

Ans:

System changed numbers (SCN) are assigned when a transaction is committed. The SCN is a unique number acting as an internal timestamp, used for recovery and read-consistent queries. In other words, the SCN number is assigned to the rollback statement to mark it as a transaction committed.

Q66: Name the parts of the database buffer pool?

Ans:

The database buffer pool consists of the keep buffer pool; recycle buffer pool, and the default buffer pool.

The keep buffer pool retains the data block in memory.

The recycle buffer pool removes the buffers from memory when it’s not needed.

The default buffer pool contains the blocks that are not assigned to the other pools.

Q67: List all the valid database start-up option?

Ans:

STARTUP MOUNT, STARTUP NOMOUNT, and STARTUP FORCE.

STARTUP NOMOUNT is used for creating a new database or for creating new control files. STARTUP MOUNT is used for performing specific maintenance operations such as renaming data files, enabling or disabling archive logging, renaming, adding or dropping redo log files, or for performing a full database recovery. Finally, STARTUP FORCE is used to start a database forcefully, (if you have problems starting up an instance.) STARTUP FORCE shuts down the instance if it is already running and then restarts it.

Q68: Which two values from the V$SESSION view are used to terminate a user session?

Ans:

The session identifier (SID) and the serial number (SERIAL #) uniquely identify each session and both are needed to kill a session. Ex. SQL > ALTER SYSTEM KILL SESSION ‘SID’,’ SERIAL #’;

Q69: To use operating system authentication to connect the database as an administrator, what should the value of the parameter REMOTE_LOGIN_PASSWORDFILE be set to?

Ans:

The value of the REMOTE_LOGIN_PASSWORDFILE parameter should be set to NONE to use OS authentication. To use password file authentication, the value should be either EXCLUSIVE or SHARED.

Q70: What information is available in the alert log files?

Ans:

The alert log store information about block corruption errors, internal errors, and the non-default initialization parameters used at instance start-up. The alert log also records information about database start-up, shutdown, archiving, recovery, tablespace modifications, rollback segment modifications, and the data file modifications.

Q71: Which parameter value is use to set the directory path where the alert log file is written?

Ans:

The alert log file is written in the BACKGROUND_DUMP_DEST directory. This directory also records the trace files generated by the background processes. The USER_DUMP_DEST directory has the trace files generated by user sessions. The CORE_DUMP_DEST directory is used primarily on UNIX platforms to save the core dump files. ALERT_DUMP_DEST is not a valid parameter.

Q72: Which SHUTDOWN option requires instance recovery when the database is started the next time?

Ans:

SHUTDOWN ABORT requires instance recovery when the database is started the next time. Oracle will also roll back uncommitted transactions during start-up. This option shuts down the instance without dismounting the database.

Q73: Which SHUTDOWN option will wait for the users to complete their uncommitted transactions?

Ans:

When SHUTDOWN TRANSACTIONAL is issued, Oracle waits for the users to either commit or roll back their pending transactions. Once all users have either rolled back or committed their transactions, the database is shut down. When using SHUTDOWN IMMEDIATE, the user sessions are disconnected and the changes are rolled back. SHUTDOWN NORMAL waits for the user sessions to disconnect from the database.

Q74: How do you make a database read-only?

Ans:

To put a database into read-only mode, you can mount the database and open the database in read-only mode. This can be accomplished in one step by using STARTUP OPEN READ ONLY.

Q75: Which role is created by default to administer databases?

Ans:

The DBA role is created when you create the database and is assigned to the SYS and SYSTEM users.

Q76: Which privilege do you need to connect to the database, if the database is started up by using STARTUP RESTRICT?

Ans:

RESTRICTED SESSION privilege is required to access a database that is in restrict mode. You can start up the database in restrict mode by using STARTUP RESTRICT, or change the database to restricted mode by using ALTER SYSTEM ENABLE RESTRICTED SESSION.

Q77: At which stage of the database start-up is the control file opened?

Ans:

The control file is opened when the instance mounts the database. The data files and redo log files are opened after the database is opened. When the instance is started, the background processes are started.

Q78: Which command will “bounce” the database-that is, shut down the database and start up the database in a single command?

Ans:

STARTUP FORCE will terminate the current instance and start up the database. It is equivalent to issuing SHUTDOWN ABORT and STARTUP OPEN.

Q79: When performing the command SHUTDOWN TRANASACTIONAL, what actions oracle performs internally?

Ans:

SHUTDOWN TRANSACTIONAL waits for all user transactions to complete. Once no transactions are pending, it disconnects all sessions and proceeds with the normal shutting down process. The normal shut down process performs a checkpoint, closes data files and redo log files, dismounts the database, and shuts down the instance.

Q80: When you issue the command ALTER SYSTEM ENABLE RESTRICTED SESSION, what happens to the users who are connected to the database?

Ans:

If you enable the RESTRICTED SESSION when users are connected, nothing happens to the already connected sessions. Future sessions are started only if the user has the RESTRICTED SESSION privilege.

Q81: Which view has information about users who are granted SYSDBA or SYSOPER privilege?

Ans:

A dynamic view of V$PWFILE_USERS has the username and a value of TRUE in column SYSDBA if the SYSDBA privilege is granted, or a value of TRUE in column SYSOPER if the SYSOPER privilege is granted.

Q82: What is the recommended configuration for control files?

Ans:

Oracle allows multiplexing of control files. If you have two control files on two disks, one disk failure will not damage both control files.

Q83: How many control files are required to create a database?

Ans:

You do not need any control files to create a database; the control files are created when you create a database, based on the filenames specified in the CONTROL_FILES parameter of the parameter file

Q84: Which environment variable or registry entry variable is used to represent the instance name?

Ans:

The Oracle_SID environment variable is used to represent the instance name. When you connect to the database without specifying a connect string, Oracle connects you to this instance.

Q85: Which initialization parameter cannot be changed after creating the database?

Ans:

The block size of the database cannot be changed after database creation. The database name can be changed after re-creating the control file with a new name, and the CONTROL_FILES parameter can be changed if the files are copied to a new location.

Q86: Which script creates the data dictionary views?

Ans:

The catalog.sql script creates the data dictionary views. The base tables for these views are created by the script sql.bsq, which is executed when you issue the CREATE DATABASE command.

Q87: How do you correct a procedure that has become invalid when one of the tables it is referring to was altered to drop a constraint?

Ans:

The invalid procedure, trigger, package, or view can be recompiled by using the ALTER COMPILE command.

Q88: How many data files can be specified in the DATAFILE clause when creating a database?

Ans:

You can specify more than one data file; the files will be used for the SYSTEM tablespace. The files specified cannot exceed the number of data files specified in the MAXDATAFILES clause.

Q89: Who owns the data dictionary?

Ans:

The SYS user owns the data dictionary. The SYS and SYSTEM users are created when the database is created.

Q90:

What is the default password for the SYS user?

The default password for the SYS user is CHANGE_ON_INSTALL, and for SYSTEM it is MANAGER. You should change these passwords once the database is created.

Q91: What is the prefix for dynamic performance views?

Ans:

The dynamic performance views have a prefix of V$. The actual views have the prefix of V_$, and the synonyms have a V$ prefix. The views are called dynamic performance views because they are continuously updated while the database is open and in use, and their contents related primarily to performance.

Q92: What piece of information is not available in the control file?

Ans:

The instance name is not available. The control files include the following:

Database name the control file belongs to, database creation timestamp, data files, redo log files, tablespace names, current log sequence number, most recent checkpoint information, and Recovery Manager’s backup information.

Q93: When you create a control file, the database has to be:

Ans:

Not mounted.

Q94: Which data dictionary view provides the names of the control files?

V$CONTROLFILES shows the names of the control files.

Q95: The initialization parameter file has LOG_CHECKPOINT_INTERVAL = 60; what does this mean?

Ans:

LOG_CHECKPOINT_INTERVAL ensures that no more than a specified number of redo log blocks (OS blocks) need to be read during instance recovery. LOG_CHECKPOINT_TIMEOUT ensures that no more than a specified number of seconds worth of redo log blocks need to be read during instance recovery.

Q96: What will happen if ARCn could not write to a mandatory archive destination?

Ans:

Oracle will write a message to the alert file and all database operations will be stopped. Database operation resumes automatically after successfully writing the archived log file. If the archive destination becomes full you can make room for archives either by deleting the archive log files after copying them to a different location, or by changing the parameter to point to a different archive location.

Q97: What are the valid status codes in the V$LOGFILE view?

Ans:

Valid status codes V$LOGFILE views include STALE, INVALID, DELETED, or the status can be blank. STALE means the file contents are incomplete; INVALID means the file is not accessible; DELETED means the file is no longer used; and blank status means the file is in use.

Q98: If you have two redo log groups with four members each, how many disks does Oracle recommend to keep the redo log files?

Ans:

You should keep a minimum of two redo log groups, with a recommended two members in each group. Oracle recommends that you keep each member of a redo log group on a different disk. The maximum number of redo log groups is determined by the MAXLOGFILES database parameter. The MAXLOGMEMBERS database parameter specifies the maximum number of members per group.

Q99: When does the SMON process automatically coalesce the tablespaces?

Ans:

When the PCTINCREASE default storage of the tablespace is set to 0. You can manually coalesce a tablespace by using ALTER TABLESPACE COALESCE.

Q100: How would you drop a tablespace if the tablespace were not empty?

Ans:

Use DROP TABLESPACE INCLUDING CONTENTS.

The INCLUDING CONTENTS clause is used to drop a tablespace that is not empty. Oracle does not remove the data files that belong to the tablespace; you need to do it manually using an OS command. Oracle updates only the control file.

Q101: Which command is used to enable the auto-extensible feature for a file, if the file is already part of a tablespace?

Ans:

To enable auto=extension, use ALTER DATABASE DATAFILE AUTOEXTEND ON NEXT MAXSIZE .

Q102: How would you determine how much sort space is used by a user session?

Ans:

The V$SORT_USAGE shows the active sorts in the database; it shows the space used, username, SQL address, and hash value. It also provides the number of EXTENTS and number of BLOCKS used by each sort session, and the username. The V$SORT can be joined with V$SESSION or V$SQL to obtain more information on the session or the SQL statement causing the sort.

Q103: When a table is updated, where is the before image information (which can be used for undoing the changes) stored?

Ans:

Rollback segment. Before any DML operation, the undo information (before-image of data) is stored in the rollback segments. This information is used to undo the changes and to provide a read-consistent view of the data.

Q104: Which parameter specifies the number of transaction slots in a data block?

Ans:

INITRANS specifies the number of transaction slots in a data block. A transaction slot is used by Oracle when the data block is being modified. INITRANS reserves space for the transactions in the block.

Q105: Which data dictionary view would you query to see the free extents in a tablespace?

Ans:

DBA_FREE_SPACE shows the free extents in a tablespace. DBA_EXTENTS shows all the extents that are allocated to a segment.

Q106: Which portion of the data block stores information about the table having rows in this block?

Ans:

Row Data. The table directory portion of the block stores information about the table having rows in the block. The row directory stores information such as row address and size of the actual rows stored in the row data area.