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?
Yes.
9. Can a tablespace hold objects fromdifferent schemes?
Yes.
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?
Yes.
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 files.one 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)
ARCH (ARCHiver)
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
SET TRANSACTION USEROLLBACK SEGMENT rbs_name.
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
DBA_FREE_SPACEDBA_SEGMENTSDBA_DATA_FILES.SQL*Plus
Statements
100. What are the types of SQLstatement
Data Definition Language: CREATE,ALTER, DROP,
TRUNCATE,REVOKE, NO AUDIT & COMMIT.Data Manipulation Language:
INSERT,UPDATE, DELETE, LOCK TABLE,EXPLAIN PLAN & SELECT.Transactional
Control: COMMIT &ROLLBACKSession Control: ALTERSESSION &SET ROLESystem
Control: ALTER SYSTEM.
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.
106. Explain CONNECT BY PRIOR
Retrieves rows in hierarchical order eg.select 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.
108. Explain UNION, MINUS,UNION ALL and INTERSECT
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
Using ROWID.CONSTRAINTS
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.
114. What is ON DELETECASCADE
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
CHAR, VARCHAR2, NUMBER,DATE, RAW, LONG and LONG RAW.
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