Sunday, January 24, 2016

Oracle DBA Interview Questions and Answers (FAQs)

1. What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

2. What are the components of logical database structure of Oracle database?
There are tablespaces and database's schema objects.

3. What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

4. What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

5. Explain the relationship among database, tablespace and data file.
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

6. What is schema?
A schema is collection of database objects of a user.

7. What are Schema Objects?
Schema objects are the logical structuresthat directly refer to the database's data.Schema objects include tables, views,sequences, synonyms, indexes, clusters,database triggers, procedures, functionspackages and database links.

8. Can objects of the same schemareside in different tablespaces?

9. Can a tablespace hold objects fromdifferent schemes?

10. What is Oracle table?
A table is the basic unit of data storagein an Oracle database. The tables of adatabase hold all of the user accessibledata. Table data is stored in rows andcolumns.

11. What is an Oracle view?
A view is a virtual table. Every view hasa query attached to it. (The query is aSELECT statement that identifies thecolumns and rows of the table(s) theview uses.)

12. Do a view contain data?
Views do not contain or store data.

13. Can a view based on another view?

14. What are the advantages of views?
- Provide an additional level of tablesecurity, by restricting access to apredetermined set of rows and columnsof a table.- Hide data complexity.
- Simplify commands for the user.
- Present the data in a differentperspective from that of the base table.
- Store complex queries.

15. What is an Oracle sequence?
A sequence generates a serial list ofunique numbers for numerical columnsof a database's tables.

16. What is a synonym?
A synonym is an alias for a table, view,sequence or program unit.

17. What are the types of synonyms?
There are two types of synonyms privateand public.

18. What is a private synonym?
Only its owner can access a privatesynonym.

19. What is a public synonym?
Any database user can access a publicsynonym.

20. What are synonyms used for?
- Mask the real name and owner of anobject.
- Provide public access to an object
- Provide location transparency fortables, views or program units of aremote database.
- Simplify the SQL statements fordatabase users.

21. What is an Oracle index?
An index is an optional structureassociated with a table to have directaccess to rows, which can be created toincrease the performance of dataretrieval. Index can be created on one ormore columns of a table.

22. How are the index updates?
Indexes are automatically maintainedand used by Oracle. Changes to tabledata are automatically incorporated intoall relevant indexes.

23. What are clusters?
Clusters are groups of one or more tablesphysically stores together to sharecommon columns and are often usedtogether.

24. What is cluster key?
The related columns of the tables in acluster are called the cluster key.

25. What is index cluster?
A cluster with an index on the clusterkey.

26. What is hash cluster?
A row is stored in a hash cluster basedon the result of applying a hash functionto the row's cluster key value. All rowswith the same hash key value are storestogether on disk.

27. When can hash cluster used?
Hash clusters are better choice when atable is often queried with equalityqueries. For such queries the specifiedcluster key value is hashed. Theresulting hash key value points directlyto the area on disk that stores thespecified rows.

28. What is database link?
A database link is a named object thatdescribes a "path" from one database toanother.

29. What are the types of databaselinks?
Private database link, public databaselink & network database link.

30. What is private database link?
Private database link is created on behalfof a specific user. A private database linkcan be used only when the owner of thelink specifies a global object name in aSQL statement or in the definition of theowner's views or procedures.

31. What is public database link?
Public database link is created for thespecial user group PUBLIC. A publicdatabase link can be used when any userin the associated database specifies aglobal object name in a SQL statementor object definition.

32. What is network database link?
Network database link is created andmanaged by a network domain service.A network database link can be usedwhen any user of any database in thenetwork specifies a global object namein a SQL statement or object definition.

33. What is data block?
Oracle database's data is stored in datablocks. One data block corresponds to aspecific number of bytes of physicaldatabase space on disk.

34. How to define data block size?
A data block size is specified for eachOracle database when the database iscreated. A database users and allocatedfree database space in Oracle datablocks. Block size is specified in init.orafile and cannot be changed latter.

35. What is row chaining?
In circumstances, all of the data for arow in a table may not be able to fit inthe same data block. When this occurs,the data for the row is stored in a chainof data block (one or more) reserved forthat segment.

36. What is an extent?
An extent is a specific number ofcontiguous data blocks, obtained in asingle allocation and used to store aspecific type of information.

37. What is a segment?
A segment is a set of extents allocatedfor a certain logical structure.

38. What are the different types ofsegments?
Data segment, index segment, rollbacksegment and temporary segment.

39. What is a data segment?
Each non-clustered table has a datasegment. All of the table's data is storedin the extents of its data segment. Eachcluster has a data segment. The data ofevery table in the cluster is stored in thecluster's data segment.

40. What is an index segment?
Each index has an index segment thatstores all of its data.

41. What is rollback segment?A database contains one or morerollback segments to temporarily store"undo" information.

42. What are the uses of rollbacksegment?
To generate read-consistent databaseinformation during database recoveryand to rollback uncommittedtransactions by the users.

43. What is a temporary segment?
Temporary segments are created byOracle when a SQL statement needs atemporary work area to completeexecution. When the statement finishesexecution, the temporary segmentextents are released to the system forfuture use.

44. What is a datafile?
Every Oracle database has one or morephysical data files. A database's data filescontain all the database data. The data oflogical database structures such as tablesand indexes is physically stored in thedata files allocated for a database.

45. What are the characteristics ofdata files?
A data file can be associated with onlyone database. Once created a data filecan't change size. One or more data filesform a logical unit of database storagecalled a tablespace.

46. What is a redo log?
The set of redo log files for a database iscollectively known as the database redolog.

47. What is the function of redo log?
The primary function of the redo log isto record all changes made to data.

48. What is the use of redo loginformation?
The information in a redo log file is usedonly to recover the database from asystem or media failure preventsdatabase data from being written to adatabase's data files.

49. What does a control file contains?
- Database name- Names and locations of a database'sfiles and redolog files.
- Time stamp of database creation.

50. What is the use of control file?
When an instance of an Oracle databaseis started, its control file is used toidentify the database and redo log filesthat must be opened for databaseoperation to proceed. It is also used indatabase recovery.Data Base Administration

51. What is a database instance?
Explain.A database instance (Server) is a set ofmemory structure and backgroundprocesses that access a set of databasefiles.
The processes can be shared by allof the users.The memory structure that is used tostore the most queried data fromdatabase.
This helps up to improvedatabase performance by decreasing theamount of I/O performed against datafile.

52. What is Parallel Server?
Multiple instances accessing the samedatabase (only in multi-CPUenvironments)

53. What is a schema?
The set of objects owned by useraccount is called the schema.

54. What is an index? How it isimplemented in Oracle database?
An index is a database structure used bythe server to have direct access of a rowin a table. An index is automaticallycreated when a unique of primary keyconstraint clause is specified in createtable command

55. What are clusters?
Group of tables physically storedtogether because they share commoncolumns and are often used together iscalled cluster.

56. What is a cluster key?
The related columns of the tables arecalled the cluster key. The cluster key isindexed using a cluster index and itsvalue is stored only once for multipletables in the cluster.

57. What are the basic element of baseconfiguration of an Oracle database?
It consists ofone or more data or more control files.two or more redo log files.The Database containsmultiple users/schemasone or more rollback segmentsone or more tablespacesData dictionary tablesUser objects (table,indexes,viewsetc.,)
The server that access the databaseconsists ofSGA (Database buffer,Dictionary Cache Buffers, Redo logbuffers, Shared SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
CKPT (Check Point)
RECODispatcherUser Process with associatedPGS

58. What is a deadlock?
Explain.Two processes waiting to update therows of a table, which are locked byother processes then deadlock arises.In a database environment this will oftenhappen because of not issuing the properrow lock commands. Poor design offront-end application may cause thissituation and the performance of serverwill reduce drastically.These locks will be releasedautomatically when a commit/rollbackoperation performed or any one of thisprocesses being killed externally.Memory Management

59. What is SGA?
The System Global Area in an Oracledatabase is the area in memory tofacilitate the transfer of informationbetween users. It holds the most recentlyrequested structural information betweenusers. It holds the most recentlyrequested structural information aboutthe database. The structure is databasebuffers, dictionary cache, redo log bufferand shared pool area.

60. What is a shared pool?
The data dictionary cache is stored in anarea in SGA called the shared pool. Thiswill allow sharing of parsed SQLstatements among concurrent users.

61. What is mean by Program GlobalArea (PGA)?
It is area in memory that is used by asingle Oracle user process.

62. What is a data segment?
Data segment are the physical areaswithin a database block in which thedata associated with tables and clustersare stored.

63. What are the factors causing thereparsing of SQL statements in SGA?
Due to insufficient shared pool size.Monitor the ratio of the reloads takesplace while executing SQL statements. Ifthe ratio is greater than 1 then increasethe SHARED_POOL_SIZE.Database Logical & PhysicalArchitecture

64. What is Database Buffers?
Database buffers are cache in the SGAused to hold the data blocks that are readfrom the data segments in the databasesuch as tables, indexes and clustersDB_BLOCK_BUFFERS parameter inINIT.ORA decides the size.

65. What is dictionary cache?
Dictionary cache is information aboutthe database objects stored in a datadictionary table.

66. What is meant by recursive hints
Number of times processes repeatedlyquery the dictionary table is calledrecursive hints. It is due to the datadictionary cache is too small. Byincreasing the SHARED_POOL_SIZEparameter we can optimize the size ofdata dictionary cache.

67. What is redo log buffer
Changes made to the records are writtento the on-line redo log files. So that theycan be used in roll forward operationsduring database recoveries. Beforewriting them into the redo log files, theywill first brought to redo log buffers inSGA and LGWR will write into filesfrequently. LOG_BUFFER parameterwill decide the size.

68. How will you swap objects into adifferent table space for an existingdatabase
- Export the user- Perform import using the commandimp system/manager file=export.dmpindexfile=newrite.sql. This will create alldefinitions into newfile.sql.
- Drop necessary objects.
- Run the script newfile.sql after alteringthe tablespaces.
- Import from the backup for thenecessary objects.

69. List the Optional FlexibleArchitecture (OFA) of Oracledatabase? How can we organize thetablespaces in Oracle database to havemaximum performance
SYSTEM - Data dictionary tables.
DATA - Standard operational tables.
DATA2- Static tables used for standardoperations
INDEXES - Indexes for Standardoperational tables.
INDEXES1 - Indexes of static tablesused for standard operations.
TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations RollbackSegments,
RBS1,RBS2 - Additional/SpecialRollback segments.
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespacefor users.
USERS - User tablespace.

70. How will you force database to useparticular rollback segment

71. What is meant by free extent
A free extent is a collection ofcontinuous free blocks in tablespace.When a segment is dropped its extentsare reallocated and are marked as free.

72.Which parameter in Storage clausewill reduce number of rows per block
PCTFREE parameterRow size also reduces no of rows perblock.

73. What is the significance of havingstorage clause

We can plan the storage for a table ashow much initial extents are required,how much can be extended next, howmuch % should leave free for managingrow updating, etc.,

74. How does Space allocation tableplace within a block
Each block contains entries as followsFixed block headerVariable block headerRow Header, row date (multiple rowsmay exists)PCTEREE (% of free space for rowupdating in future)

75. What is the role of PCTFREEparameter is storage clause
This is used to reserve certain amount ofspace in a block for expansion of rows.

76. What is the OPTIMALparameter
It is used to set the optimal length of arollback segment.

77. What is the functionality ofSYSTEM table space
To manage the database leveltransactions such as modifications of thedata dictionary table that recordinformation about the free space usage.

78. How will you create multiplerollback segments in a database
- Create a database, which implicitlycreates a SYSTEM rollback segment in aSYSTEM tablespace.- Create a second rollback segment nameR0 in the SYSTEM tablespace.- Make new rollback segment available(after shutdown, modify init.ora file andstart database)- Create other tablespaces (RBS) forrollback segments.- Deactivate rollback segment R0 andactivate the newly created rollbacksegments.

79. How the space utilization takesplace within rollback segments
It will try to fit the transaction in a cyclicfashion to all existing extents. Once itfound an extent is in use then it forced toacquire a new extent (number of extentsis based on the optimal size)

80. Why query fails sometimes
Rollback segment dynamically extent tohandle larger transactions entry loads.A single transaction may wipeout allavailable free space in the rollbacksegment tablespace. This prevents otheruser using rollback segments.

81. How will you monitor the spaceallocation
By querying DBA_SEGMENTtable/view

82. How will you monitor rollbacksegment status
Querying the DBA_ROLLBACK_SEGSviewIN USE - RollbackSegment is on-line.AVAILABLE - RollbackSegment available but not on-line.OFF-LINE - RollbackSegment off-lineINVALID - RollbackSegment Dropped.NEEDS RECOVERY - Contains databut need recovery or corrupted.PARTLY AVAILABLE - Contains datafrom an unresolved transaction involvingadistributeddatabase.

83. List the sequence of events when alarge transaction that exceeds beyondits optimal value when an entry wrapsand causes the rollback segment toexpand into another extend.Transaction Begins.An entry is made in the RES header fornew transactions entryTransaction acquires blocks in an extentof RBSThe entry attempts to wrap into secondextent. None is available, so that theRBS must extent.The RBS checks to see if it is part of itsOPTIMAL size.RBS chooses its oldest inactive segment.Oldest inactive segment is eliminated.RBS extentsThe data dictionary tables for spacemanagement are updated.Transaction Completes.84. How can we plan storage for verylarge tables
Limit the number of extents in the tableSeparate table from its indexes.Allocate sufficient temporary storage.

85. How will you estimate the spacerequired by a non-clustered tables
Calculate the total header sizeCalculate the available data space perdata blockCalculate the combined column lengthsof the average rowCalculate the total average row size.Calculate the average number rows thatcan fit in a blockCalculate the number of blocks andbytes required for the table.After arriving the calculation, add 10 %additional space to calculate the initialextent size for a working table.

86. It is possible to use raw devices asdata files and what are the advantagesover file system files
Yes.The advantages over file system files arethat I/O will be improved because Oracleis bye-passing the kernel which writinginto disk. Disk corruption will be veryless.

87. What is a Control file
Database s overall physical architectureis maintained in a file called control file.It will be used to maintain internalconsistency and guide recoveryoperations. Multiple copies of controlfiles are advisable.88. How to implement the multiplecontrol files for an existing database
Shutdown the databaseCopy one of the existing controlfile tonew locationEdit Config ora file by adding newcontrol filenameRestart the database.

89. What is redo log file mirroring
How can be achieved
Process of having a copy of redo logfiles is called mirroring.This can be achieved by creating groupof log files together, so that LGWR willautomatically writes them to all themembers of the current on-line redo loggroup. If any one group fails thendatabase automatically switch over tonext group. It degrades performance.

90. What is advantage of having diskshadowing / mirroring
Shadow set of disks save as a backup inthe event of disk failure. In mostoperating systems if any disk failureoccurs it automatically switchover toplace of failed disk.Improved performance because most OSsupport volume shadowing can directfile I/O request to use the shadow set offiles instead of the main set of files. Thisreduces I/O load on the main set ofdisks.

91. What is use of rollback segmentsin Oracle database
They allow the database to maintain readconsistency between multipletransactions.

92. What is a rollback segment entry
It is the set of before image data blocksthat contain rows that are modified by atransaction.Each rollback segment entry must becompleted within one rollback segment.A single rollback segment can havemultiple rollback segment entries.

93. What is hit ratio
It is a measure of well the data cachebuffer is handling requests for data.Hit Ratio = (Logical Reads - PhysicalReads - Hits Misses)/ Logical Reads.

94. When will be a segment released
When Segment is dropped.When Shrink (RBS only)When truncated (TRUNCATE used withdrop storage option)

95. What are disadvantages of havingraw devices
We should depend on export/importutility for backup/recovery (fullyreliable)The tar command cannot be used forphysical file backup, instead we can usedd command, which is less flexible andhas limited recoveries.

96. List the factors that can affect theaccuracy of the estimations
- The space used transaction entries anddeleted records, does not become freeimmediately after completion due todelayed cleanout.- Trailing nulls and length bytes are notstored.- Inserts of, updates to and deletes ofrows as well as columns larger than asingle data block, can causefragmentation a chained row pieces.Database Security & Administration

97. What is user Account in Oracledatabase
A user account is not a physical structurein database but it is having importantrelationship to the objects in the databaseand will be having certain privileges.

98. How will you enforce securityusing stored procedures
Dont grant user access directly to tableswithin the application.Instead grant the ability to access theprocedures that access the tables.When procedure executed it will executethe privilege of procedures owner. Userscannot access tables except via theprocedure.

99. What are the dictionary tablesused to monitor a database space

100. What are the types of SQLstatement

101. What is a transaction
Transaction is logical unit between twocommits and commit and rollback.

102. What is difference betweenTRUNCATE & DELETE
TRUNCATE commits after deletingentire table i.e., cannot be rolled back.Database triggers do not fire onTRUNCATEDELETE allows the filtered deletion.Deleted records can be rolled back orcommitted.Database triggers fire on DELETE.

103. What is a join
 Explain thedifferent types of joins
Join is a query, which retrieves relatedcolumns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables byequating two common columns.
Non-Equi Join - Joining two tables byequating two common columns.
Outer Join - Joining two tables in such away that query can also retrieve rowsthat do not have corresponding joinvalue in the other table.

104. What is the sub-query
Sub-query is a query whose returnvalues are used in filtering conditions ofthe main query.

105. What is correlated sub-query
Correlated sub-query is a sub-query,which has reference to the main query.

Retrieves rows in hierarchical order empno, ename from emp where.

107. Difference between SUBSTR andINSTR
INSTR (String1, String2 (n, (m)),INSTR returns the position of the m-thoccurrence of the string 2 in string1. Thesearch begins from nth position ofstring1.SUBSTR (String1 n, m)SUBSTR returns a character string ofsize m in string1, starting from n-thposition of string1.

INTERSECT - returns all distinct rowsselected by both queries.MINUS - returns all distinct rowsselected by the first query but not by thesecond.UNION - returns all distinct rowsselected by either queryUNION ALL - returns all rowsselected by either query, including allduplicates.

109. What is ROWID
ROWID is a pseudo column attached toeach row of a table. It is 18 characterslong, blockno, rownumber are thecomponents of ROWID.110. What is the fastest way ofaccessing a row in a table

111. What is an integrity constraint
Integrity constraint is a rule that restrictsvalues to a column in a table.

112. What is referential integrityconstraint
Maintaining data integrity through a setof rules that restrict the values of one ormore columns of the tables based on thevalues of primary key or unique key ofthe referenced table.

113. What is the usage ofSAVEPOINTS
SAVEPOINTS are used to subdivide atransaction into smaller parts. It enablesrolling back part of a transaction.Maximum of five save points areallowed.

When ON DELETE CASCADE isspecified Oracle maintains referentialintegrity by automatically removingdependent foreign key values if areferenced primary or unique key valueis removed.

115. What are the data types allowedin a table

116. What is difference betweenCHAR and VARCHAR2
 What is themaximum SIZE allowed for eachtype
CHAR pads blank spaces to themaximum length.VARCHAR2 does not pad blank spaces.For CHAR the maximum length is 255and 2000 for VARCHAR2.

117. How many LONG columns areallowed in a table
 Is it possible to useLONG columns in WHERE clause orORDER BY
Only one LONG column is allowed. It isnot possible to use LONG column inWHERE or ORDER BY clause.

118. What are the pre-requisites tomodify datatype of a column and toadd a column with NOT NULLconstraint
- To modify the datatype of a columnthe column must be empty.- To add a column with NOT NULLconstrain, the table must be empty.

119. Where the integrity constraintsare stored in data dictionary
The integrity constraints are stored inUSER_CONSTRAINTS.

120. How will you activate/deactivateintegrity constraints
The integrity constraints can be enabledor disabled by ALTER TABLEENABLE CONSTRAINT / DISABLECONSTRAINT.121. If unique key constraint onDATE column is created, will itvalidate the rows that are insertedwith SYSDATE
It won't, Because SYSDATE formatcontains time attached with it.
122. What is a database link?
Database link is a named path throughwhich a remote database can beaccessed.

123. How to access the current valueand next value from a sequence?
Is itpossible to access the current value ina session before accessing next value?
Sequence name CURRVAL, sequencename NEXTVAL. It is not possible.Only if you access next value in thesession, current value can be accessed.

124. What is CYCLE/NO CYCLE in aSequence?
CYCLE specifies that the sequencecontinue to generate values afterreaching either maximum or minimumvalue. After pan-ascending sequencereaches its maximum value, it generatesits minimum value. After a descendingsequence reaches its minimum, itgenerates its maximum.
NO CYCLE specifies that the sequencecannot generate more values afterreaching its maximum or minimumvalue.

125. What are the advantages ofVIEW?
- To protect some of the columns of atable from other users.
- To hide complexity of a query.
- To hide complexity of calculations.

126. Can a view beupdated/inserted/deleted? If Yes -under what conditions?
A View can be updated/deleted/insertedif it has only one base table if the view isbased on columns from one or moretables then insert, update and delete isnot possible.

127. If a view on a single base table ismanipulated will the changes bereflected on the base table?

If changes are made to the tables andthese tables are the base tables of a view,then the changes will be reference on theview.

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect