What Are the Components of Physical Database Structure of Oracle Database

What are the components of physical database structure of Oracle database?

Or acl edat abasei scomp mpri sedoft hr eet ypesoffil es.Oneormo moredat afil es,t woar e morer edol ogfil es,andoneormo morecont r olfil es. What are the components of logical database structure of Oracle database?

 Ther ear et abl espacesanddat abase' sschemaobj ect s. What is a tablespace?

 Adat abasei sdi vi dedi nt oLogi calSt or ageUni tcal l edt abl espaces.At abl espacei s usedt ogroupedr el at edl ogi calst ruct ur est oget her . What is SYSTEM tablespace and when is it created?

Eve ryOr acl edat abasec ont ai nsat abl es pacename medSYSTEM,whi chi s aut oma mat i cal l ycr eat edwh whent hedat abasei scr eat ed.TheSYSTEM t abl espace al wayscont ai nst hedat adi ct i onaryt abl esf ort heent i redat abase. Explain the relationship among database, tablespace and data file ?

Eachdat abasesl ogi cal l ydi vi dedi nt ooneormo moret abl espacesoneormo mor edat afil es ar ee xpl i c i t l yc r e at e df ore ac ht abl e s pac e . What is schema?

 Aschemai scol l ect i onofdat abaseobj ect sofauser . What are Schema Obects?

Schema maobj ect sar ethel ogi calst ruct ur esthatdi r ect l yref ert ot hedat abase' sdat a. Schema maobj ect si ncl udet abl es,vi ews,sequences,synonyms ms,i ndexes,cl ust ers, databaset ri ggers,pr ocedur es,f unct i onspackagesanddatabasel i nks. !an obects of the same schema reside in different tablespaces?

 Yes. !an a tablespace hold obects from different schemes?

 Yes. What is Oracle table?

 At abl ei st hebasi cuni tofdat as t or agei nanOr acl edat abase.Thet abl esofa databasehol dal loft heuseraccessi bl edata.Tabl edatai sst oredi nr owsand col umns. What is an Oracle "iew?

 Avi ew i savi r t ualt abl e.Ev er yvi ew hasaquer yat t achedt oi t .( Thequer yi sa SELECTst at eme mentt hati dent i fiest hecol umnsandr owsoft het abl e( s)t hevi ew uses. )

What is #artial $ac%up ?

 APar t i alBackupi sanyoper at i ngsyst em backupshor tofaf ul lbackup,t aken  whi l et hedat abasei sopenorshutdown. What is Mirrored on&line 'edo (og ?

 Ami r r or edonl i ner edol ogconsi s t sofcopi esofonl i ner edol ogfil esphysi cal l y l ocat edon se par at edi sks,changesmadet ooneme memb mbero ft hegr oupar ema madet o al lmemb mbers . What is )ull $ac%up ?

 Af ul lbackupi sanoper at i ngsys t em backupofal ldat afil es,onl i ner edol ogfil es andcont r olfil et hatconst i t ut eORACLE dat abaseandt hepar ame met er . !an a *iew based on another *iew ?

 Yes. !an a Tablespace hold obects from different Schemes ?

 Yes. !an obects of the same Schema reside in different tablespace ?

 Yes. What is the use of !ontrol )ile ?

 Whenani ns t anceofanORACLEdat abasei ss t ar t ed,i t scont r olfil ei susedt o i dent i f yt hedat abaseandr edol ogfil est hatmustbeopenedf ordat abaseoper at i on t oproceed.I ti sal sousedi ndat abaserecovery. +o *iew contain +ata ?

 Vi ewsdonotcont ai nors t or edat a. What are the 'eferential actions supported by )O'E-. /EY integrity constraint ?

UPDATE andDELETE Rest ri ct-Ar ef er ent i ali nt egri t yrul et hatdi sal l owst he updat eordel et i onofr ef er enceddat a.DELETECascade-Whenare f er encedr ow i s del et edal lassoci at eddependentr ows wsar edel et ed. What are the type of Synonyms?

 Ther ear et wot ypesofSynonymsPr i vat eandPubl i c. What is a 'edo (og ?

 ThesetofRedoLogfil esYSDATE, UI D, USER orUSERENVSQLf unct i ons,ort he pseudocol umn mnsLEVELorROW OWNUM.

What is an ndex Segment ?

EachI ndexhasanI ndexsegmentt hatst oresal lofi t sdata. Explain the relationship among +atabase, Tablespace and +ata file?

Eachdat abasesl ogi cal l ydi vi dedi nt ooneormoret abl espacesoneormor edat afil es ar ee xpl i c i t l ycr e a t e df ore a cht abl e s pac e What are the different type of Segments ?

Dat aSegment ,I ndexSegment ,Rol l backSegmentandTempor arySegment . What are !lusters ?

Cl ust er sar egr oupsofoneormoret abl esphysi cal l yst orest oget hert oshare commoncol umnsandar eof t enusedt oge t her . What is an ntegrity !onstrains ?

 Ani nt egr i t ycons t r ai nti sadecl ar at i vewayt odefineabusi nessr ul ef oracol umnof at abl e . What is an ndex ?

 AnI ndexi sanopt i onalst r uct ur eassoci at edwi t hat abl et ohav edi r ectaccesst o r ows,whi chcan becr eat edt oi ncr easetheperf ormanceofdatar et ri eval .I ndexcan  becr eat edononeormor ecol umnsofat abl e. What is an Extent ?

 AnExt enti saspeci ficnumberofcont i guousdat abl ocks,obt ai nedi nasi ngl e al l ocati on,andusedt ost oreaspeci fict ypeofi nf ormat i on. What is a *iew ?

 Avi ew i savi r t ualt abl e.Ev er yvi ew hasaQuer yat t achedt oi t .( TheQuer yi sa SELECTst at ementt hati dent i fiest hecol umnsandr owsoft het abl e( s)t hevi ew us es . ) What is Table ?

 At abl ei st hebasi cuni tofdat as t or agei nanORACLEdat abase.Thet abl esofa databasehol dal loft heuseraccessi bl edata.Tabl edatai sst oredi nr owsand col umns. !an a "iew based on another "iew?

 Yes. What are the ad"antages of "iews?

-Pr o v i deanaddi t i onall e v eloft abl es ec ur i t y ,byr e s t r i c t i ngac ce sst oa pr edet er mi nedse tofr owsandcol umnsofat abl e. -Hi dedatacompl exi t y. -Si mpl i f ycommandsf ort heuser . -Pr esentt hedat ai nadi ffer entper spect i vef r om t hatoft hebaset abl e. -St orecompl exqueri es.

What is an Oracle se0uence?

 Asequencegener at esaser i all i s tofuni quenumber sf ornume r i calcol umnsofa dat abas e ' st abl e s . What is a synonym?

 Asynonym i sanal i asf orat abl e,vi ew,sequenceorpr ogr am uni t . What are the types of synonyms?

 Ther ear et wot ypesofsynonymspr i vat eandpubl i c. What is a pri"ate synonym?

Onl yi t sownercanacces sapr i vat esynonym. What is a public synonym?

 Anydat abaseusercanaccessapubl i csynonym. What are synonyms used for?

-Maskt her ealnameandownerofanobj ec t . -Pr ovi depubl i caccesst oanobj ect -Pr ovi del ocat i ont r ansparencyf ort abl es,vi ewsorprogram uni t sofar emot e database. -Si mpl i f yt heSQLst at ement sf ordat abaseuser s. What is an Oracle index?

 Ani ndexi sanopt i onalst r uct ur eassoci at edwi t hat abl et ohav edi r ectaccesst o r ows,whi chcan becr eat edt oi ncr easetheperf ormanceofdatar et ri eval .I ndexcan  becr eat edononeormor ecol umnsofat abl e. 1ow are the index updates?

I ndexesar eaut omat i cal l ymai nt ai nedandusedbyOr acl e.Changest ot abl edat a areaut omati cal l yi ncorporat edi nt oal lr el evanti ndexes. What is a Tablespace?

 Adat abasei sdi vi dedi nt oLogi calSt or ageUni tcal l edt abl espace.At abl espacei s usedt ogr oupedr el at edl ogi calst ruct ur est oget her What is 'ollbac% Segment ?

 ADat abasecont ai nsoneormor eRol l backSegme nt st ot empor ar i l ys t or e" undo" i nf ormat i on. What are the !haracteristics of +ata )iles ?

 Adat afil ecanbeassoci at edwi t honl yonedat abase.Oncecr eat edadat afil ecan' t changesi ze.Oneormoredat afil esf orm al ogi caluni tofdatabasest oragecal l eda tablespace. 1ow to define +ata $loc% si2e ?

 Adat abl ocksi z ei sspeci fiedf oreachORACLEdat abasewhent hedat abasei s

cr eat ed.Adat abaseuser sandal l ocat edf r eedat abasespacei nORACLE dat a  bl ocks.Bl ocksi z ei sspeci fiedi nI NI T. ORAfil eandcan’ tbechangedl at t er . What does a !ontrol file !ontain ?

 ACont r olfil er ecor dst hephysi calst r uct ur eoft hedat abase.I tcont ai nst he f ol l o wi ngi nf or mat i o n. Dat abaseName Namesandl ocat i onsofadat abase' sfil esandr edol ogfil es.  Ti mes t ampofdat abasecr eat i on. What is difference between 3.43E constraint and #'M5'Y /EY constraint ?

 Acol umndefinedasUNI QUE cancont ai nNul l swhi l eacol umndefinedas PRI MARYKEYcan' tcont ai nNul l s. What is ndex !luster ?

 ACl us t erwi t hani ndexont heCl us t erKey When does a Transaction end ?

 Wheni ti scommi t t edorRol l backed. What is the effect of setting the "alue 65((7'OWS6 for O#TM8E'7-O5( parameter of the 5(TE' SESSO. command ? What are the factors that affect O#TM8E' in choosing an Optimi2ation approach ?

 AnswerTheOPTI MI ZER_MODE i ni t i al i z at i onpar ame t erSt at i s t i csi nt heDat a Di ct i onaryt heOPTI MI ZER_GOALpar ame t eroft heALTER SESSI ON co mmand hi nt si nt hest at ement . What is the effect of setting the "alue 6!1OOSE6 for O#TM8E'7-O5(, parameter of the 5(TE' SESSO. !ommand ?

 TheOpt i mi z erchoosesCos t _basedappr oachandopt i mi z eswi t ht hegoalofbest t hr oughputi fst ati st i csf orat l eastoneoft het abl esaccessedbytheSQLst at ement exi sti nt hedat adi ct i onary .Ot her wi set heOPTI MI ZERchoosesRULE_based approach. 1ow does one create a new database? 9for +$5:

Onecancr eat eandmodi f yOr acl edat abasesusi ngt heOr acl e" dbca"( Dat abase Co nfig ur a t i o n As s i s t ant )ut i l i t y .Thedbc aut i l i t yi sl o cat e di nt he $ORACLE_HOME/bi ndi r ect ory .TheOr acl eUni ver salI nst al l er( oui )normal l yst art s i taf t e ri ns t al l i ngt hedat abas ese r v ers of t war e . Onecanal socreat edatabasesmanual l yusi ngscri pt s.Thi sopt i on,however ,i s f al l i ngoutoff ashi on,asi ti squi t ei nvol vedanderr orpr one.Lookatt hi sexampl e f orcr eat i ngandOr acl e9idatabase: CONNECTSYSASSYSDBA

 ALTER SYSTEM SETDB_CREATE_FI LE_DEST=' /u01/or adat a/' ;  ALTER SYSTEM SETDB_CREATE_ONLI NE_LOG_DEST_1=' /u02/or adat a/' ;  ALTER SYSTEM SETDB_CREATE_ONLI NE_LOG_DEST_2=' /u03/or adat a/' ; CREATE DATABASE; What database bloc% si2e should  use? 9for +$5:

Or acl er ecommendst hatyourdat abasebl ocksi z emat ch,orbemul t i pl esofyour oper at i ngsyst em bl ocksi ze.Onecanusesmal l erbl ocksi ze s,butt heper f ormance costi ssi gni ficant .Yourchoi ceshoul ddependont het ypeofappl i cat i onyouare runni ng.I fyouhavemanysmal lt r ansact i onsaswi t hOLTP,useasmal l erbl ock si ze.Wi t hf ewerbutl ar gert ransact i ons,aswi t haDSSappl i cat i on,useal ar ger  bl ocksi z e.I fyouar eusi ngav ol umemanager ,consi deryour" oper at i ngsys t em  bl ocksi z e"t obe8K.Thi si sbecausev ol umemanagerpr oduct suse8K bl ocks( and t hi si snotconfigurabl e) . What are the different approaches used by Optimi2er in choosing an execution plan ?

Rul ebasedandCost based. What does 'O(($5!/ do ?

ROLLBACK r et r act sanyoft hec hangesr esul t i ngf r om t heSQLst at ement si nt he t r a ns ac t i o n. 1ow does one coalesce free space ? 9for +$5:

SMON coal escesf r eespace( ext ent s)i nt ol arger ,cont i guousext ent severy2hour s andevent hen,onl yf orashortperi odoft i me. SMON wi l lnotcoal escef r eespacei fat abl espace' sdef aul tst orageparamet er " pct i ncr ease"i ssett o0.Wi t hOr acl e7. 3onecanmanual l ycoal esceatabl espace usi ngt heALTERTABLESPACE . . .COALESCE;co mmand,unt i lt henuse : SQL>al t ersessi onsetevent s' i mmedi at et r acenamecoal escel eveln' ;  Wher e' n'i st het abl espacenumberyouge tf r om SELECTTS#,NAME FROM SYS. TS$;  Youcange ts t at usi nf or mat i onaboutt hi spr ocessbysel ect i ngf r om t he SYS. DBA_FREE_SPACE_COALESCED di ct i onaryvi ew. 1ow does one pre"ent tablespace fragmentation? 9for +$5:

 Al waysse tPCTI NCREASE t o0or100. Bi zarr eval uesf orPCTI NCREASE wi l lcont ri but et of r agment at i on.Forexampl ei f  youse tPCTI NCREASEt o1youwi l lseet hatyourext ent sar egoi ngt ohavewei r d andwackysi ze s:100K,100K,101K,102K,et c.Suchext ent sofbi zarr esi zear e r arel yr eusedi nt hei rent i r et y .PCTI NCREASE of0or100gi vesy ouni cer ound ext entsi zest hatcan easi l yber eused.E. g. .100K,100K,200K,400K,et c. Uset hesameext entsi zef oral lt hesegment si nagi vent abl espace.Local l y Managedt abl espaces( avai l abl ef r om 8ionwards)wi t huni f orm ext entsi zesvi rt ual l y el i mi nat esanyt abl espacef r agment at i on.Not et hatt henumberofext ent sper

seg mentdoesnotcauseanyperf or mancei ss ueanymor e,unl es st heyr uni nt o t housandsandt housandswher eaddi t i onalI /O mayber equi r edt of et cht he addi t i onalbl ockswher eext entmapsoft hesegmentares t ore d. Where can one find the high water mar% for a table? 9for +$5:

 Ther ei snosi ngl esyst em t abl e,whi chcont ai nst hehi ghwat ermar k( HWM)f ora t abl e.At abl e' sHWM canbecal cul at edusi ngt her esul t sf r om t hef ol l owi ngSQL st at ement s: SELECTBLOCKS FROM DBA_SEGMENTS  WHEREOWNER=UPPER( owner )AND SEGMENT_NAME =UPPER( t abl e) ;  ANALYZE TABLEowner . t abl eESTI MATE STATI STI CS; SELECTEMPTY_ BLOCKS FROM DBA_TABLES  WHEREOWNER=UPPER( owner )AND SEGMENT_NAME =UPPER( t abl e) ;  Thus,t het abl es'HWM =( quer yr esul t1)-( quer yr esul t2)-1 NOTE:Youcanal souset heDBMS_SPACE packageandcal cul at et heHWM =  TOTAL_BLOCKS-UNUSED_BLOCKS-1. What is !OST&based approach to optimi2ation ?

Consi deri ngavai l abl eaccesspathsanddet ermi ni ngt hemosteffici entexecut i on pl anbasedonst at i st i csi nt hedat adi ct i onaryf ort het abl esaccessedbyt he st at ementandt hei rassoci at edcl ust er sandi ndexes. What does !OMMT do ?

COMMI Tmakesper manentt hechange sr es ul t i ngf r om al lSQLst at ement si nt he t r ansact i on.Thechangesmadebyt heSQLst at ement sofat r ansact i onbecome  vi si bl et oot herusersessi onst r ansact i onst hats t ar tonl yaf t ert r ansact i oni s commi t t ed. 1ow are extents allocated to a segment? 9for +$5:

Or acl e8andabov er oundsoff ext ent st oamul t i pl eof5bl ockswhenmor et han5  bl ocksar er equest ed.I foner eques t s16Kor2bl ocks( assumi nga8Kbl ocksi z e) , Or acl edoesn' tr oundi tupt o5bl ocks,buti tal l ocat es2bl ocksor16K as request ed.I foneasksf or8bl ocks,Oracl ewi l lroundi tupt o10bl ocks. Spaceal l ocat i onal sodependsupont hesi zeofcont i guousf reespaceavai l abl e.I f oneasksf or8bl ocksandOr acl efindsacont i guousf r eespacet hati sexact l y8  bl ocks,i twoul dgi v ei tyou.I fi twer e9bl ocks,Or acl ewoul dal sogi v ei tt oyou. Cl earl yOr acl edoesn' tal waysr oundext ent st oamul t i pl eof5bl ocks.  Theexcept i ont ot hi sr ul ei sl ocal l ymanagedt abl espaces.I fat abl espacei scr eat ed  wi t hl ocalext entmanagementandt heext entsi z ei s64K,t henOr acl eal l ocat es64K or8bl ocksassumi ng8Kbl ocksi ze.Or acl edoesn' tr oundi tupt ot hemul t i pl eof5  whenat abl espacei sl ocal l ymanaged. !an one rename a database user 9schema:? 9for +$5:

No,t hi si sl i st edasEnhancementRequest158508.Wor kar ound:

Doauser l evelexportofuserA cr eat enew userB I mportsy s t em/managerf r omuse r =At ouse r =B Dr opuse rA +efine Transaction ?

 ATr ansact i oni sal ogi caluni tofwor kt hatcompr i sesoneormor eSQLs t at eme nt s execut edbyasi ngl euser . What is 'ead&Only Transaction ?

 AReadOnl yt r ansact i onensur est hatt her esul t sofeachquer yexecut edi nt he t r ansact i onareconsi st antwi t hr espectt ot hesamepoi nti nt i me. What is a deadloc% ? Explain ;

 Twopr ocesseswat i ngt oupdat et her owsofat abl ewhi char el ockedbyt heot her pr ocesst hendeadl ockari ses.I nadat abaseenvi r onmentt hi swi l lof t enhappen  becauseofnoti ssui ngpr operr ow l ockcommands.Poordesi gnoff r ont end appl i cat i onmaycauset hi ssi t uati onandt heperf ormanceofserverwi l lr educe dr as t i cal l y .  Thesel ockswi l lber el easedaut omat i cal l ywhenacommi t /r ol l backoper at i on perf ormedoranyoneoft hi spr ocessesbei ngki l l edext ernal l y. What is a Schema ?

 Thesetofobj ect sownedbyuseraccounti scal l edt heschema. What is a cluster /ey ?

 Ther el at edcol umnsoft het abl esar ecal l edt hecl us t erkey .Thecl us t erkeyi s i ndexedusi ngacl ust eri ndexandi t sval uei sst oredonl yoncef ormul t i pl et abl esi n t hecl us t e r . What is #arallel Ser"er ?

Mul t i pl ei nst ancesaccess i ngt hesamedat abase( Onl yI nMul t i CPU envi r onment s) What are the basic element of $ase configuration of an oracle +atabase ?

I tc ons i s t sof oneormor edat afil es. oneormor econt r olfil es. t woormor er edol ogfil es.  TheDat abasecont ai ns mul t i pl euser s/sc hemas oneormor er ol l backsegment s oneormor et abl espaces Datadi ct i onaryt abl es Us erobj e ct s( t abl e , i nde xe s, v i e wse t c . , )  Theser vert hataccesst hedat abaseconsi s t sof SGA( Dat abasebuffer ,Di ct i onaryCacheBuffer s,Redol ogbuffer s,Shar edSQL

pool) SMON ( Sys t em MONi t o) PMON ( Pr oc es sMONi t or ) LGWR ( LoG Wr i t e) DBWR ( Dat aBaseWr i t e)  ARCH ( ARCHi v er ) CKPT( Chec kPoi nt ) RECO Di spat cher UserPr ocesswi t hassoci at edPGS What is clusters ?

Gr oupoft abl esphysi cal l yst or edt oget herbecauset heyshar ecommoncol umns andar eof t enusedt oget heri scal l edCl ust er . What is an ndex ? 1ow it is implemented in Oracle +atabase ?

 Ani ndexi sadat abasest r uct ur eusedbyt heser v ert ohav edi r ectaccessofar ow i n at abl e.Ani ndexi saut omat i cal l ycr eat edwhenauni queofpri marykeyconst r ai nt cl ausei sspeci fiedi ncr eat et abl ecomman ( Ver7. 0) What is a +atabase instance ? Explain

 Adat abasei ns t ance( Ser v er )i sase tofme mor ys t r uct ur eandbackgr ound pr ocessesthataccessasetofdatabasefil es.  Thepr ocesscanbeshar edbyal luser s.Thememor yst r uct ur et hatar eusedt o st or emostquer i eddat af r om dat abase.Thi shel psupt oi mpr ovedat abase per f ormancebydecr easi ngt heamountofI /O perf ormedagai nstdat afil e. What is the use of 5.5(Y8E command ?

 Toper f or m oneoft hesef unct i ononani ndex,t abl e,orcl us t er : -Tocol l ectst at i st i csaboutobj ectusedbyt heopt i mi zerandst oret hem i nt hedat a di c t i o nar y . -Todel et est at i st i csaboutt heobj ectusedbyobj ectf r om t hedat adi ct i onary . -T ov al i dat et hes t r uc t ur eo ft heobj e c t . -Toi dent i f ymi grat edandchai nedrowsoft het abl eorcl ust er . What is default tablespace ?

 TheTabl espacet ocont ai nschemaobj ect scr eat edwi t houtspeci f yi ngat abl espace name. What are the system resources that can be controlled through #rofile ?

 Thenumberofconcur r entsessi onst heusercanes t abl i sht heCPU pr ocessi ngt i me avai l abl et ot heuser' ssessi ont heCPU pr ocessi ngt i meavai l abl et oasi ngl ecal lt o ORACLE madebyaSQLst at ementt heamountofl ogi calI /O avai l abl et ot heuse r ' s sessi ont heamoutofl ogi calI /O avai l abl et oasi ngl ecal lt oORACLE madebyaSQL st at ementt heal l owedamountofi dl et i mef ort heuser' ssessi ont heal l owed amountofconnectt i mef ort heuser' ssessi on.

What is Tablespace 4uota ?

 Thecol l ect i veamountofdi skspaceavai l abl et ot heobj ect si naschemaona par t i c ul art abl e s pac e . What are the different (e"els of 5uditing ?

St at ementAudi t i ng,Pri vi l egeAudi t i ngandObj ectAudi t i ng. What is Statement 5uditing ?

St at ementaudi t i ngi st heaudi t i ngoft hepowerf ulsyst em pri vi l egeswi t houtr egard t ospeci fical l ynamedobj ect s. What are the database administrators utilities a"ailable ?

SQL*DBA-Thi sal l owsDBAt omoni t orandcont r olanORACLE dat abase.SQL* Loader-I tl oadsdat af r om st andardoper at i ngsyst em fil es( Fl atfil es)i nt oORACLE databaset abl es.Export( EXP)andI mport( i mp)ut i l i t i esal l ow yout omoveexi st i ng dat ai nORACLE f or matt oandf r om ORACLE dat abase . 1ow can you enable automatic archi"ing ?

Shutt hedat abase Backupt hedat abase Modi f y/I ncl udeLOG_ARCHI VE_START_TRUE i ni ni t . or afil e. St artupt hedat abase. What are roles? 1ow can we implement roles ?

Rol esar et heeasi estwayt ogr antandmanagecommonpri vi l egesneededby di ffer entgroupsofdatabaseuser s.Cr eat i ngrol esandassi gni ngpr ovi dest or ol es.  Assi gneachr ol et ogr oupofuser s.Thi swi l lsi mpl i f yt hej obofassi gni ngpr i vi l eges t oi ndi v i dualus e r s . What are 'oles ?

Rol esar enamedgroupsofr el at edpri vi l egest hatar egrant edt ousersorot her r o l e s . What are the use of 'oles ?

REDUCED GRANTI NG OFPRI VI LEGES-Rat hert hanexpl i ci t l yg r ant i ngt hesame setofpri vi l egest omanyusersadat abaseadmi ni st rat orcan grantt hepri vi l egesf or agr oupofr el at eduser sgrant edt oar ol eandt hengrantonl yt her ol et oeach memberoft hegr oup. DYNAMI C PRI VI LEGE MANAGEMENT-Whent hepr i vi l egesofagr oupmus t change,onl yt hepri vi l egesoft her ol eneedt obemodi fied.Thesecuri t ydomai nsof al luser sgrant edt hegroup' sr ol eaut omat i cal l yr eflectt hechangesmadet ot he r o l e . SELECTI VE AVAI LABI LI TYOFPRI VI LEGES-Ther ol esgr ant edt oause rcanbe s el e ct i v e l yenabl e( a vai l abl ef orus e)ordi s abl e d( no ta v ai l abl ef orus e) .Thi sal l o ws s pe c i ficc ont r o lo faus e r ' spr i v i l e g esi nanygi v e ns i t uat i on.  APPLI CATI ON AWARENESS-Adat abaseappl i cat i oncanbedesi gnedt o

aut omat i cal l yenabl eanddi sabl esel ect i ver ol eswhenauserat t empt st ouset he appl i c at i on. What is #ri"ilege 5uditing ?

Pri vi l egeaudi t i ngi stheaudi t i ngoft heuseofpowerf ulsyst em pri vi l egeswi t hout r egardt ospeci fical l ynamedobj ect s. What is Obect 5uditing ?

Obj ectaudi t i ngi st heaudi t i ngofaccessest ospeci ficschemaobj ect swi t hout r e g ar dt ous e r . What is 5uditing ?

Moni t ori ngofuseraccesst oai di nt hei nvest i gat i onofdatabaseuse. 1ow does one see the uptime for a database? 9for +$5 :

Lookatt hef ol l owi ngSQLquery: SELECTt o_c har( s t ar t up_t i me, ' DDMONYYYYHH24:MI :SS' )" DB St ar t upTi me" FROM sy s. v_ $i nst ance ; MarcoBer gman pr ovi dedt hef ol l owi ngal t ernati vesol ut i on: SELECTt o_c har( l ogon_t i me, ' DyddMonHH24:MI :SS' )" DB St ar t upTi me" FROM sy s. v_ $sess i on  WHERESi d=1/*t hi si spmon* / / Usersst i l lrunni ngonOracl e7cant ryoneoft hef ol l owi ngqueri es: Col umnSTARTED f or mata18head' STARTUPTI ME' Sel ec tC. I NSTANCE, t o_dat e( JUL. VALUE,' J' ) ||t o_char( floor( SEC. VALUE/3600) ,' 09' ) ||' : ' -||Subst r( t o_char( mod( SEC. VALUE/60,60) ,' 09' ) ,2,2) ||Subst r( t o_char( floor( mod( SEC. VALUE/60,60) ) ,' 09' ) ,2,2) ||' . ' ||Subst r( t o_char( mod( SEC. VALUE,60) ,' 09' ) ,2,2)STARTED f r om SYS. V_$I NSTANCE JUL, SYS. V_$I NSTANCESEC, SYS. V_$THREAD C  Wher eJUL. KEYl i ke' %JULI AN%' andSEC. KEYl i ke' %SECOND%' ; Sel ectt o_dat e( JUL. VALUE,' J' ) ||t o_char( t o_dat e( SEC. VALUE,' SSSSS' ) ,'HH24: MI : SS' )STARTED f r om SYS. V_$I NSTANCE JUL, SYS. V_$I NSTANCE SEC  wher eJUL. KEYl i ke' %JULI AN%' andSEC. KEYl i ke' %SECOND%' ; sel ectt o_char( t o_dat e( JUL. VALUE,' J' )+( SEC. VALUE/86400) ,Ret urnaDATE ' DDMONYYHH24: MI : SS' )STARTED

f r om V$I NSTANCE JUL,  V$I NSTANCE SEC  wher eJUL. KEYl i ke' %JULI AN%' andSEC. KEYl i ke' %SECOND%' ; Where are my TEM#)(ES,  don
 Tempfil es,unl i kenor maldat afil es,ar enotl i s t edi nv$dat afil eordba_dat a_fil es. I nst eadquer yv$t empfil eordba_t emp_fil es: SELECT*FROM v$t empfil e; SELECT*FROM dba_t emp_fil es; 1ow do  find used>free space in a TEM#O'5'Y tablespace? 9for +$5 :

Unl i kenormalt abl espaces,t ruet emporaryt abl espacei nf ormat i oni snotl i st edi n DBA_FREE_SPACE.I nst eaduset heV$TEMP_SPACE_HEADER vi ew: SELECTt abl es pace _name,SUM ( byt esused) ,SUM ( byt esf r ee ) FROM V$t emp_space_heade r GROUPBYt abl espace_name; What is a profile ?

Eachdatabaseuseri sassi gnedaPr ofil et hatspeci fiesl i mi t at i onsonvari ous syst em r esour cesavai l abl et otheuser . 1ow will you enforce security using stored procedures?

Don' tgrantuseraccessdi r ect l yt ot abl eswi t hi nt heappl i cat i on.I nst eadgrantt he abi l i t yt oaccesst hepr ocedur est hataccessthet abl es.Whenpr ocedur eexecut edi t  wi l lexecut et hepr i vi l egeofpr ocedur esowner .User scannotaccesst abl esexceptvi a t hepr ocedur e. 1ow can one see who is using a temporary segment? 9for +$5 :

Forev er yuserusi ngt empor aryspace,t her ei sanent ryi nSYS. V$_LOCK wi t ht ype ' TS' .  Al lt empor ar ysegment sar ename d' ffff. bbbb'wher e' ffff'i st hefil ei ti si nand' bbbb' i sfir stbl ockoft hesegment .I fyourt emporaryt abl espacei ssett oTEMPORARY,al l sort saredonei nonel arget emporarysegment .Forusagest at s,see SYS. V_$SORT_ SEGMENT Fr om Or acl e8. 0,onecanj ustquer ySYS. v$sort _usage.Lookatt heseexampl es: sel ects. username,u. " USER" ,u. t abl espace,u. cont ent s,u. ext ent s,u. bl ocks f r om sys. v_$sessi ons,sys. v_$sort _usageu  wher es. addr=u. sessi on_addr / s e l e c ts . o sus e r ,s . pr o c e ss ,s . us e r name ,s . s e r i al #, Sum ( u. bl ocks) * vp. val ue/1024sort _si ze f r om sys. v_$sessi ons,sys. v_$sort _usageu,sys. v_$paramet erVP  wher es. saddr=u. sessi on_ addr andvp. name=' db_bl ock_si z e'

ands. osuserl i ke' &1' groupbys. osuser ,s. pr ocess,s. username,s. seri al #,vp. val ue / 1ow does one get the "iew definition of fixed "iews>tables?

Quer yv$fixe d_vi ew_defini t i on.Exampl e:SELECT*FROM v$fixed_v i ew_defini t i on  WHEREvi ew_name=' V$SESSI ON' ; What are the dictionary tables used to monitor a database spaces ?

DBA_FREE_SPACE DBA_ SEGMENTS DBA_DATA_FI LES. 1ow can we specify the 5rchi"ed log file name format and destination?

Byse t t i ngt hef ol l owi ngval uesi ni ni t . or afil e.LOG_ARCHI VE_FORMAT=ar ch %S/s/T/t ar c( %S-Logsequencenumberandi sz er ol ef tpaded,%s-Logsequence numbernotpadded.%T-Thr eadnumberl ef z er opadedand%t-Thr eadnumber notpadded) .Thefil enamecr eat edi sar ch0001ar ei f%Si sused. LOG_ARCHI VE_DEST=pat h. What is user 5ccount in Oracle database?

 Anuseraccounti snotaphysi cals t r uct ur ei nDat abasebuti ti shavi ngi mpor t ant rel at i onshi pt ot heobj ect si nt hedatabaseandwi l lbehavi ngcert ai npri vi l eges. When will the data in the snapshot log be used?

 Wemustbeabl et ocr eat eaaf t err ow t r i ggeront abl e( i . e. ,i tshoul dbenotbe al r eadyavai l abl e)Af t ergi vi ngtabl epri vi l eges.Wecannotspeci f ysnapshotl ogname  becauseor acl eusest henameoft hemas t ert abl ei nt henameoft hedat abase obj ect st hatsupporti t ssnapshotl og.Themast ert abl enameshoul dbel esst hanor equalt o23charact er s.( Thet abl enamecr eat edwi l lbeMLOGS_t abl ename,and t r i gge rnamewi l lbeTLOGSname) . What dynamic data replication?

Updat i ngorI nser t i ngrecor dsi nr emot edat abaset hr oughdat abaset ri gger s.I tmay f ai li fr emot edatabasei shavi nganypr obl em. What is Two&#hase !ommit ?

 Twophasecommi ti smechani sm t hatguar ant eesadi s t r i but edt r ansact i onei t her commi t sonal li nvol vednodesorr ol l sbackonal li nvol vednodest omai nt ai ndata consi st encyacr ossthegl obaldi st ri but eddatabase.I thast wophase,aPr epare PhaseandaCommi tPhase. 1ow can you Enforce 'eferential ntegrity in snapshots ?

 Ti met her ef er encest ooccurwhenmas t ert abl esar enoti nuse.Pef or mt he r ef er encet hemanual l yi mmdi at el yl ocki ngt hemast ert abl es.Wecanj oi nt abl esi n snopshot sbycreat i ngacompl exsnapshot st hatwi l lbasedont hemast ert abl es.

What is a S4(  .ET?

SQL*NETi sORACLE' smec hani sm f ori nt er f aci ngwi t ht hecommuni cat i on prot ocol susedbythenet workst hatf aci l i t at edi st ri but edprocessi nganddi st ri but ed databases.I ti susedi nCl i nt ServerandServer Servercommuni cat i ons. What is a S.5#S1OT ?

Snapshot sarer eadonl ycopi esofamast ert abl el ocat edonaremot enodewhi chi s peri odi cal l yref r eshedt oreflectchangesmadet ot hemast ert abl e. What is the mechanism pro"ided by O'5!(E for table replication ?

Snapshot sandSNAPSHOTLOGs What is snapshots?

Snapshoti sanobj ectusedt odynami cal l yrepl i cat edatabet weendi st ri but e dat abaseatspeci fiedt i mei nt erval s.I nver7. 0t heyarer eadonl y. What are the "arious type of snapshots?

Si mpl eandCompl ex. +escribe two phases of Two&phase commit ?

Prepar ephase-Thegl obalcoor di nat or( i ni t i at i ngnode)askapart i ci pant st o prepar e( t opr omi set ocommi torrol l backt hetr ansact i on,eveni ft herei saf ai l ur e) Commi t-Phase-I fal lpart i ci pant srespondt othecoor di nat ort hatt heyar e prepar ed,t hecoordi nat orasksal lnodest ocommi tt het r ansact i on,i fal l part i ci pant scannotprepar e,t hecoordi nat orasksal lnodest or ol lbackt he t r a ns ac t i o n. What is snapshot log ?

I ti sat abl et hatmai nt ai nsar ecordofmodi ficati onst ot hemast ert abl ei na snapshot .I ti sst oredi nt hesamedatabaseasmast ert abl eandi sonl yavai l abl ef or si mpl esnapshot s.I tshoul dbecreat edbef orecr eat i ngsnapshot s. What are the benefits of distributed options in databases?

Dat abaseonot herser ve r scanbeupdat edandt hoset r ansact i onscanbegrouped t og e t he rwi t ho t he r si nal o gi c aluni t . Dat abaseuse sat wophasecommi t . What are the options a"ailable to refresh snapshots ?

COMPLETE -Tabl esar ecompl et el yr ege ner at edusi ngt hesnapshot squer yandt he mast ert abl esever yt i met hesnapshotr ef er enced. FAST-I fsi mpl esnapshotusedt henasnapshotl ogcanbeusedt osendt he changest ot hesnapshott abl es. FORCE -Def aul tval ue.I fpossi bl ei tperf ormsaFASTr ef r esh;Ot herwi sei twi l l perf orm acompl et er ef r esh. What is a S.5#S1OT (O- ?

 Asnapshotl ogi sat abl ei nt hemas t erdat abaset hati sassoci at edwi t ht hemas t er

t abl e.ORACLE usesasnapshotl ogt ot r ackt her owst hathavebeenupdat edi nt he mast ert abl e.Snapshotl ogsareusedi nupdat i ngt hesnapshot sbasedont he mast ert abl e. What is +istributed database ?

 Adi s t r i but eddat abasei sane t wor kofdat abasesmanagedbymul t i pl edat abase server st hatappearst oauserassi ngl el ogi caldat abase.Thedataofal ldatabases i nt hedi st r i but eddat abasecanbesi mul t aneousl yacce ssedandmodi fied. 1ow can we reduce the networ% traffic?

-Repl i cat i onofdat ai ndi st ri but edenvi r onment . -Usi ngsnapshot st orepl i cat edata. -Usi ngr emot epr ocedur ecal l s. +ifferentiate simple and complex, snapshots ?

-Asi mpl esnapshoti sbasedonaquer yt hatdoesnotcont ai nsGROUPBYcl ause s, CONNECTBYcl auses ,JOI Ns,subquer yorsnashotofoper at i ons. -Acompl exsnapshot scont ai nat l eastanyoneoft heabove. What are the $uilt&ins used for sending #arameters to forms?

 Youcanpasspar ame t erval uest oaf or m whenanappl i cat i onexecut est he cal l _f orm,New_f orm,Open_f orm orRun_pr oduct . !an you ha"e more than one content can"as "iew attached with a window?

 Yes.Eachwi ndow youcr eat emus thav eat l eastonecont entcanvasvi ew assi gnedt o i t .You canal socr eat eawi ndow t hathasmani pul at edcont entcanvasvi ew.Atrun t i meonl yoneoft hecont entcanvasvi ewsassi gnt oawi ndow i sdi spl ayedatat i me. s the 5fter report trigger fired if the report execution fails?

 Yes. +oes a $efore form trigger fire when the parameter form is suppressed?

 Yes. What is S-5?

 TheSys t em Gl obalAr eai nanOr acl edat abasei st hear eai nme mor yt of aci l i t at e t het r ansf erofi nf ormat i onbet weenuser s.I thol dst hemostr ecent l yrequest ed st ruct ur ali nf ormat i onbet weenusers .I thol dst hemostr ecent l yrequest ed st ruct ur ali nf ormat i onaboutt hedat abase.Thest ruct ur ei sdat abasebuffer s, di ct i onarycache,r edol ogbufferandsharedpoolarea. What is a shared pool?

 Thedat adi ct i onar ycachei ss t or edi nanar eai nSGAcal l edt heshar edpool .Thi s  wi l lal l ow shar i ngofpar sedSQLst at eme nt samongconcur r entuser s. What is mean by #rogram -lobal 5rea 9#-5:?

I ti sareai nmemoryt hati susedbyasi ngl eOr acl euserpr ocess.

What is a data segment?

Dat asegmentar et hephysi calar easwi t hi nadat abasebl ocki nwhi cht hedat a associ at edwi t ht abl esandcl ust ersarest ored. What are the factors causing the reparsing of S4( statements in S-5?

Duet oi nsuffici entshar edpoolsi ze . Moni t ort herat i ooft herel oadst akespl acewhi l eexecut i ngSQLst at ement s.I ft he r at i oi sgr eat ert han1t heni ncr easet heSHARED_POOL_SI ZE. What are clusters?

Cl ust er sar egr oupsofoneormoret abl esphysi cal l yst orest oget hert oshare commoncol umnsandar eof t enusedt oge t her . What is cluster %ey?

 Ther el at edcol umnsoft het abl esi nacl ust erar ecal l edt hecl us t erkey . +o a "iew contain data?

 Vi ewsdonotcont ai nors t or edat a. What is user 5ccount in Oracle database?

 Auseraccounti snotaphysi cals t r uct ur ei ndat abasebuti ti shavi ngi mpor t ant rel at i onshi pt ot heobj ect si nt hedatabaseandwi l lbehavi ngcert ai npri vi l eges. 1ow will you enforce security using stored procedures?

Don' tgrantuseraccessdi r ect l yt ot abl eswi t hi nt heappl i cat i on.I nst eadgrantt he abi l i t yt oaccesst hepr ocedur est hataccessthet abl es.Whenpr ocedur eexecut edi t  wi l lexecut et hepr i vi l egeofpr ocedur esowner .User scannotaccesst abl esexceptvi a t hepr ocedur e. What are the dictionary tables used to monitor a database space?

DBA_FREE_SPACE DBA_ SEGMENTS DBA_DATA_FI LES. !an a property clause itself be based on a property clause?

 Yes f a parameter is used in a 0uery without being pre"iously defined, what diff; exist between; report @;A and @;B when the 0uery is applied?

 Whi l ebot hr epor t s2. 0and2. 5cr eat et hepar ame t er ,r epor t2. 5gi vesame ssage t hatabi ndpar amet erhasbeencr eat ed. What are the s0l clauses supported in the lin% property sheet?

 Wher es t ar twi t hhavi ng. What is trigger associated with the timer?

 Whent i me r expi r ed.

What are the trigger associated with image items?

 Wheni mageact i vat edfir eswhent heoper at or sdoubl ecl i cksonani mage i t emwheni magepr esse dfir eswhenanoper at orc l i cksordoubl ecl i cksonani mage i t em What are the different windows e"ents acti"ated at runtimes?

 When_wi ndow_act i vat ed  When_wi ndow_cl osed  When_wi ndow_deact i vat ed  When_wi ndow_r esi z ed  Wi t hi nt hi st r i gger s,youcanexami net hebui l ti nsys t em var i abl esyst em. eve nt _wi ndow t odet er mi net henameoft hewi ndow f orwhi cht het r i ggerfir ed. When do you use data parameter type?

 Whent heval ueofadat apar ame t erbei ngpassedt oacal l edpr oducti sal wayst he nameoft her ecor dgr oupdefinedi nt hecurr entf orm.Dat aparamet er sar eusedt o passdat at opr oduct si nvokedwi t ht her un_productbui l t i nsubpr ogram. What is difference between open7form and call7form?

 whenonef or mi nv okesanot herf or m byexecut i ngopen_f or mt hefir stf or mr emai ns di spl ayed,andoper at orscannavi gat ebet weent hef ormsasdesi r ed.whenonef orm i nvokesanot herf orm byexecut i ngcal l _f orm,t hecal l edf orm i smodalwi t hr espect t ot hecal l i ngf orm.Thati s,anywi ndowst hatbel ongt othecal l i ngf orm are di sabl ed,andoper at orscannotnavi gat et ot hem unt i lt heyfir stexi tt hecal l edf orm. What is new7form built&in?

 Whenonef or mi nv okesanot herf or m byexecut i ngnew_f or m or acl ef or m exi t st he fir stf orm andrel easesi t smemorybef orel oadi ngt henew f orm cal l i ngnew f orm compl et el yrepl acet hefir stwi t ht hesecond.I ft her earechangespendi ngi nt he fir stf orm,t heoper at orwi l lbepr ompt edt osavet hem bef or et henew f orm i sl oaded. What is the 6(O* of *alidation6 #roperty of an item? What is the use of it?

 WhenLOVf orVal i dat i oni sse tt oTr ue,Or acl eFor mscompar est hecur r entval ueof t het exti t em t ot heval uesi nt hefir stcol umndi spl ayedi nt heLOV.Whenevert he  val i dat i oneventoccur s.I ft heval uei nt het exti t em mat chesoneoft heval uesi n t hefir stcol umnoft heLOV,val i dat i onsucceeds,t heLOVi snotdi spl ayed,and pr ocessi ngcont i nuesnormal l y.I ft heval uei nt het exti t em doesnotmat choneof t heval uesi nt hefirs tcol umnoft heLOV,Or acl eFormsdi spl ayst heLOVanduses t het exti t em val ueasthesear chcri t eri at oaut omat i cal l yr educet hel i st . What is the diff; when )lex mode is mode on and when it is off?

 Whenflexmodei son,r epor t saut omat i cal l yr esi z est hepar entwhent hechi l di s r e si z e d. What is the diff; when confine mode is on and when it is off?

 Whenconfinemodei son,anobj ectcannotbemov edout si dei t spar enti nt he l ay out .

What are "isual attributes?

 Vi sualat t r i but esar et hef ont ,col or ,pat t er npr opr i e t i est hatyouse tf orf or m and menuobj ect st hatappeari nyourappl i cati oni nt erf ace. Which of the two "iews should obects according to possession?

 vi ew bys t r uct ur e. What are the two types of "iews a"ailable in the obect na"igator9specific to report @;B:?

 Vi ew byst r uct ur eandvi ew byt ype. What are the "bx controls?

 Vbxcont r olpr ovi deasi mpl eme t hodofbui l di ngandenhanci nguseri nt er f aces.The cont r ol scanuset oobt ai nuseri nput sanddi spl aypr ogram out put s. vbxcont r ol  wher eor i gi nal l ydevel opasext ensi onsf ort hemsvi sualbasi cenvi r onment sand i ncl udesuchi t emsassl i der s,ri desandknobs. What is the use of transactional triggers?

Usi ngt ransact i onalt ri ggerswecancont r olormodi f ythedef aul tf unct i onal i t yoft he oracl ef orms. 1ow do you create a new session while open a new form?

Usi ngopen_f orm bui l t i nset t i ngthesessi onopt i onEx.Open_f orm( ' St ocks' , act i ve, sess i on) .wheni nvoket hemul t i pl ef ormswi t hopenf orm andcal l _f orm i nt hesame appl i cat i on,st at ewhet hert hef ol l owi ngaret rue/Fal se What are the ways to monitor the performance of the report?

User eport spr ofil eexecut abl est at ement .UseSQLtr acef aci l i t y. f two groups are not lin%ed in the data model editor, What is the hierarchy between them?

 Twogr oupt hati sabov ear et hel ef tmostr ankhi ghert hant hegr oupt hati st or i ght orbe l o wi t . Where is the external 0uery executed at the client or the ser"er?

 Att heser v er . Where is a procedure return in an external pl>s0l library executed at the client or at the ser"er?

 Att hecl i ent . What is coordination E"ent?

 Anyev entt hatmakesadi ffer entr ecor di nt hemas t erbl ockt hecur r entr ecor di sa coor di nat i oncausi ngevent . What is the difference between O(E Ser"er C Ole !ontainer?

 AnOl eser v erappl i cat i oncr eat esol eObj ect st hatar eembeddedorl i nkedi nol e

Cont ai ner sex.Ol eser ver sar ems_wor d& ms_e xcel .OLE cont ai ner sprovi deapl ace t ost ore,di spl ayandmani pul at eobj ect st hatar ecreat edbyol eserverappl i cat i ons. Ex.oracl ef ormsi sanexampl eofan ol eCont ai ner . What is an obect group?

 Anobj ectgr oupi sacont ai nerf oragr oupofobj ect s;youdefineanobj ectgr oup  whenyouwantt opackager el at edobj ect s,sot hatyoucopyorr ef er encet hem i n ot hermodul es. What is an (O*?

 AnLOVi sascr ol l abl epopupwi ndow t hatpr ovi dest heoper at orwi t hei t herasi ngl e ormul t ic ol umns e l e c t i o nl i s t . 5t what point of report execution is the before 'eport trigger fired?

 Af t ert hequer yi sexecut edbutbef or et her epor ti sexecut edandt her ecor dsar e di s pl a y ed. What are the built &ins used for Modifying a groups structure?

 ADDGROUP_COLUMN ( f unct i on)  ADD_GROUP_ROW ( pr ocedur e) DELETE_GROUP_ROW( pr oc edure) What is an user exit used for?

 Awayi nwhi cht opasscont r ol( andpossi bl yar gume nt s)f or m Or acl er epor tt o anot herOr acl eproduct sof3GLandt henr et urncont r ol(and)backt oOr acl e r e por t s . What is the 3ser&.amed Editor?

 Ausernamededi t orhast hesamet extedi t i ngf unct i onal i t yast hedef aul tedi t or ,  but ,becausei ti saname dobj ect ,youcanspeci f yedi t orat t r i but essuchas  wi ndowsdi spl aysi z e,posi t i on,andt i t l e. My database was terminated while in $5!/3# MO+E, do  need to reco"er? 9for +$5:

I fadat abasewast er mi nat edwhi l eoneofi t st abl espaceswasi nBACKUPMODE ( ALTERTABLESPACE xyzBEGI N BACKUP; ) ,i twi l lt el lyout hatmedi ar eco ve ryi s r equi r edwhenyout ryt ores t artt hedat abase.TheDBAi st henr equi r edt orec over t hedatabaseandappl yal larchi vedl ogst ot hedatabase.However ,f r om Or acl e7. 2,  youcansi mpl yt aket hei ndi vi dualdat afil esoutofbackupmodeandr es t ar tt he database.  ALTER DATABASE DATAFI LE ' /pat h/fil ename'END BACKUP; Onecanse l ec tf r om V$BACKUPt oseewhi chdat afil esar ei nbackupmode.Thi s normal l ysav esas i gni ficantamountofdat abasedownt i me.  Thi r uVadi v el ucont r i but edt hef ol l owi ng: Fr om Or acl e9ionwar ds,t hef ol l owi ngcommandcanbeusedt ot akeal loft he dat afil esoutofhotbackupmode:

 ALTER DATABASE END BACKUP;  Theabovecommandsneedt obei ssuedwhent hedat abasei smount ed. What is a Static 'ecord -roup?

 As t at i cr ecor dgr oupi snotassoci at edwi t haquer y ,r at her ,youdefinei t ss t r uct ur e andr ow val uesatdesi gnt i me,andt heyr emai nfixedatr unt i me. What is a record group?

 Ar ecor dgr oupi sani nt er nalOr acl eFor mst hats t r uct ur et hathasacol umn/r ow f r ameworksi mi l art oadatabasetabl e.However ,unl i kedatabasetabl es,r ecord gr oupsar esepar at eobj ect st hatbel ongt ot hef orm modul ewhi cht heyar edefined. My database is down and  cannot restore; What now? 9for +$5 :

Recov er ywi t houtanybackupi snormal l ynotsupport ed,howev er ,Or acl e Consul t i ngcansomet i mesext r actdat af r om anoffli nedat abaseusi ngaut i l i t y cal l edDUL( Di skUnLoad) .Thi sut i l i t yr eadsdatai nt hedatafil esandunl oadsi t i nt oSQL*Loaderorexpor tdumpfil es.DULdoesnotcar eaboutr ol l backsegment s, corrupt edbl ocks,et c,andcant husnotguarant eet hatt hedatai snotl ogi cal l y corr upt .I ti si nt endedasanabsol ut el astr esortandwi l lmostl i kel ycostyour companyal otofmoney! ! ! <"e lost my 'E+O(O- files, how can  get my +$ bac%? 9for +$5:

 Thef ol l owi ngI NI T. ORApar ame t ermayber equi r edi fyourcur r entr edol ogsar e cor r upt edorbl ownaway .Caut i oni sadvi sedwhenenabl i ngt hi spar amet erasyou mi ghtendupl osi ngyourent i r edat abase.Pl easecont actOr acl eSupportbef ore us i ngi t ._ al l o w_ r e s e t l o gs _ c or r upt i o n =t r ue What is a property clause?

 Apr oper t ycl ausei saname dobj ectt hatcont ai nsal i stofpr oper t i esandt hei r set t i ngs.Onceyoucr eat eapr opert ycl auseyoucan baseotherobj ectoni t .An obj ectbasedonapr opert ycani nheri tt heset t i ngofanypr opert yi nt hecl auset hat makessensef ort hatobj ect . What is a physical page ? C What is a logical page ?

 Aphysi calpagei sasi z eofapage.Thati sout putbyt hepr i nt er .Thel ogi calpagei s t hesi zeofonepageoft heact ualr eportasseeni nt hePr evi ewer . <"e lost some 'ollbac% Segments, how can  get my +$ bac%? 9for +$5:

Rest artyourdatabasewi t ht hef ol l owi ngI NI T. ORAparamet eri foneofyour r ol l backsegment si scor rupt ed.You cant hendr opt hecorr upt edr ol l backsegment s andcreat ei tf r om scrat ch. Caut i oni sadvi sedwhenenabl i ngt hi spar amet er ,asuncommi t t edt r ansact i onswi l l  bemar kedascommi t t ed.Onecanver ywel lendupwi t hl os tori nconsi s t entdat a! ! ! Pl easecont actOr acl eSupportbef oreusi ngi t ._Corrupt ed_r ol l back_segment s= ( r bs01,r bs01,r bs03,r bs04)

What are the differences between E$3 and 'M5.? 9for +$5:

Ent erpri seBackupUt i l i t y( EBU)i saf unct i onal l yri ch,hi ghperf ormancei nt erf ace f orbacki ngupOr acl e7dat abases.I ti ssomet i mesr ef er r edt oasOEBU f orOr acl e Ent erpri seBackupUt i l i t y .TheOr acl eRecover yManager( RMAN)ut i l i t yt hatshi ps  wi t hOr acl e8andabov ei ssi mi l art oOr acl e7' sEBU ut i l i t y .Howev er ,t her ei sno di r ec tupgr adepat hf r om EBU t oRMAN. 1ow does one create a 'M5. reco"ery catalog? 9for +$5:

St artbycr eat i ngadat abaseschema( usual l ycal l edrman) .Assi gnanappr opri at e t abl espacet oi tandgranti tt her ecovery_cat al og_ownerr ol e.Lookatt hi sexampl e: sql pl ussys SQL>cr eat euserr mani dent i fiedbyr man; SQL>al t eruserrmandef aul tt abl espacet ool st emporaryt abl espacet emp; SQL>al t eruserr manquot aunl i mi t edont ool s; SQL>gr antconnect ,r esour ce,r ecov er y_cat al og_ownert or man; SQL>exi t ; Next ,l ogi nt ormanandcr eat et hecat al ogschema.Pri ort oOr acl e8it hi swasdone  byr unni ngt hecat r man. sqlscr i pt .r mancat al ogr man/r man RMAN>creat ecat al ogt abl espacet ool s; RMAN>exi t ;  Youcannow cont i nuebyr egi s t er i ngyourdat abasesi nt hecat al og.Lookatt hi s exampl e: r mancat al ogr man/r mant ar ge tbackdba/backdba RMAN>r egi st erdat abase; 1ow can a group in a cross products be "isually distinguished from a group that does not form a cross product?

 Agr oupt hatf or mspar tofacr osspr oductwi l lhaveat hi ckerbor der . What is the frame C repeating frame?

 Af r amei sahol derf oragr oupoffiel ds.Ar epeat i ngf r amei susedt odi spl ayase tof r ecordswhent heno.ofr ecordsthataret odi spl ayedi snotknownbef ore. What is a combo box?

 Acomboboxs t yl el i s ti t em combi nest hef eat ur esf oundi nl i s tandt exti t em.Unl i ke t hepopl i s tort het e xtl i s ts t y l el i s ti t e ms ,t heco mbobo xs t y l el i s ti t e m wi l lbo t h di spl ayfixedval uesandacceptoneoper at orent er edval ue. What are three panes that appear in the run time pl>s0l interpreter?

1.Sour cepane. 2.i nt erpret erpane. 3.Navi gat orpane. What are the two panes that 5ppear in the design time pl>s0l interpreter?

1.Sour cepane. 2.I nt erpr et erpane

What are the two ways by which data can be generated for a parameters list of "alues?

1.Usi ngst at i cval ues. 2.Wri t i ngsel ectst at ement . What are the "arious methods of performing a calculation in a report ?

1.Perf orm t hecal cul at i oni nt heSQLst at ement si t sel f . 2.Useacal cul at ed/summaryc ol umni nt hedat amodel . What are the default extensions of the files created by menu module?

. mmb, . mmx What are the default extensions of the files created by forms modules?

. f mb-f or m modul ebi nary . f mx-f orm modul eexe cut abl e To display the page no; for each page on a report what would be the source C logical page no; or C of physical page no;?

& physi calpageno. t is possible to use raw de"ices as data files and what is the ad"antages o"er file; system files ?

 Yes.Theadvant agesov erfil esys t em fil es.I /O wi l lbei mpr ov edbecauseOr acl ei s  byepassi ngt heker nnelwhi chwr i t i ngi nt odi sk.Di skCor r upt i onwi l lbev er yl ess. What are disad"antages of ha"ing raw de"ices ?

 Weshoul ddependonexpor t /i mpor tut i l i t yf orbackup/r ecov er y( f ul l yr el i abl e)The t arcommandcannotbeuse df orphysi calfil ebackup,i nst eadwecanusedd commandwhi chi sl essflexi bl eandhasl i mi t edr ecover i es. What is the significance of ha"ing storage clause ?

 Wecanpl ant hest or agef orat abl eashow muchi ni t i alext ent sar er equi r ed,how muchcanbeext endednext ,how much% shoul dl eav ef r eef ormanagi ngr ow updat i onse t c . , What is the use of .!TY#E option in ED# command ?

 Typeexpor tshoul dbeper f or me dCOMPLETE, CUMULATI VE, I NCREMENTAL.Li s t t hesequenceofevent swhenal arget r ansact i ont hatexceedsbeyondi t sopt i mal  val uewhenanent r ywr apsandcausest her ol l backsegme ntt oexpandi nt oanot i on Co mpl e t e s .e .wi l lbewr i t t e n. What is the use of )(E option in M# command ?

 Thenameoft hefil ef r om whi chi mpor tshoul dbeper f or me d.

What is a Shared S4( pool?

 Thedat adi ct i onar ycachei ss t or edi nanar eai nSGAcal l edt heShar edSQLPool .  Thi swi l lal l ow shar i ngofpar sedSQLst at eme nt samongconcur r entuser s. What is hot bac%up and how it can be ta%en?

 Taki ngbackupofar chi vel ogfil eswhendat abasei sopen.Fort hi st he  ARCHI VELOG modeshoul dbeenabl ed.Thef ol l owi ngfil esneedt obebackedup.Al l dat afil e s.Al lAr c hi v el og ,r e dol ogfil e s.Al lc ont r o lfil e s. (ist the Optional )lexible 5rchitecture 9O)5: of Oracle database? or 1ow can we organi2e the tablespaces in Oracle database to ha"e maximum performance ?

SYSTEM -Dat adi ct i onaryt abl es . DATA-St andardoper at i onalt abl es. DATA2-St at i ct abl esusedf orst andardoper at i ons I NDEXES-I ndexesf orSt andardoper at i onalt abl es. I NDEXES1-I ndexesofst at i ct abl esusedf orst andardoper at i ons.  TOOLS-Tool st abl e.  TOOLS1-I ndexesf ort ool st abl e. RBS-St andardOper at i onsRol l backSegment s, RBS1, RBS2-Addi t i onal /Speci alRol l backsegment s.  TEMP-Tempor ar ypur poset abl espace  TEMP_USER-Tempor ar yt abl espacef oruser s. USERS-Use rt abl es pace. 1ow to implement the multiple control files for an existing database ?

Shut downt hedat abaseCopyoneoft heexi st i ngcont r olfil et onew l ocat i onEdi t Configorafil ebyaddi ngnew cont r olfil e.nameRes t artt hedat abase. What is ad"antage of ha"ing dis% shadowing> Mirroring ?

Shadow setofdi skssaveasabackupi nt heeventofdi skf ai l ur e.I nmostOper at i ng Sy s t e mi fanydi s kf ai l ur eo c cur si taut omat i c al l ys wi t c ho v ert opl ac eoff ai l e ddi s k. I mpro ve dper f or mancebecausemostOSsuppor tvol umes hadowi ngcandi r ec tfil e I /O r equestt ouset heshadow setoffil esi nst eadoft hemai nsetoffil es.Thi s r educesI /O l oadont hemai nsetofdi sks. 1ow will you force database to use particular rollbac% segment ?

SETTRANSACTI ONUSEROLLBACKSEGMENTr bs _name. Why 0uery fails sometimes ?

Rol l backsegmentdynami cal l yext entt ohandl el argert r ansacti onsent ryl oads.A si ngl et r ansact i onmaywi peoutal lavai l abl ef r eespacei nt heRol l backSegment  Tabl espace.Thi spr ev ent sot heruserusi ngRol l backsegme nt s. What is the use of 'E!O'+ (E.-T1 option in ED# command ?

Recordl engt hi nbyt es.

1ow will you monitor rollbac% segment status ?

Quer yi ngt heDBA_ROLLBACK_SEGSvi ew I N USE -Rol l backSegmenti sonl i ne.  AVAI LABLE -Rol l backSegme ntavai l abl ebutnotonl i ne. OFFLI NE -Rol l backSegmentoffl i ne I NVALI D -Rol l backSegmentDr opped. NEEDSRECOVERY-Cont ai nsdat abutnee dr ec ov er yorcor upt ed. PARTLYAVAI LABLE -Cont ai nsdat af r om anunr esol vedt r ansact i oni nvol vi nga di st ri but eddat abase. What is meant by 'edo (og file mirroring ? 1ow it can be achie"ed?

Pr ocessofhavi ngacopyofr edol ogfil esi scal l edmi rr ori ng.Thi scanbeachi evedby cr eat i nggroupofl ogfil est oget her ,sothatLGWR wi l laut omati cal l ywri t est hem t o al lt hemember soft hecurr entonl i ner edol oggr oup.I fanyonegroupf ai l st hen databaseaut omat i cal l yswi t chovert onextgroup.I tdegradesperf ormance. Which parameter in Storage clause will reduce no; of rows per bloc%?

PCTFREE par ame t er Row si zeal sore ducesnoofr owsperbl ock. What is meant by recursi"e hints ?

Numberoft i mespr ocessesr epeat edl yqueryt hedi ct i onaryt abl ei scal l edr ecur si ve hi nt s.I ti sduet othedat adi ct i onarycachei st oosmal l .Byi ncreasi ngthe SHARED_POOL_SI ZE parame t erwecanopt i mi z et hesi z eofDat aDi ct i onaryCache. What is the use of #5')(E option in ED# command ?

Nameoft heparamet erfil et obepassedf ore xport . What is the difference between loc%s, latches, en0ueues and semaphores? 9for +$5:

 Al at chi sani nt er nalOr acl eme chani sm usedt opr ot ectdat as t r uct ur esi nt heSGA f r om si mul t aneousacces s.At omi char dwar ei nst r uct i onsl i keTESTANDSETi s usedt oi mpl ementl at ches.Lat chesar emorer est ri ct i vet hanl ocksi nt hatt heyar e al waysexcl usi ve.Lat chesar eneverqueued,butwi l lspi norsl eepunt i lt heyobt ai n aresour ce,ort i meout . Enqueuesandl ocksar edi ffer entnamesf ort hesamet hi ng.Bot hsuppor tqueui ng andconcurr ency.Theyarequeuedandservi cedi nafir st i nfir st out( FI FO)order . Semaphore sareanoper at i ngsyst em f aci l i t yusedt ocont r olwai t i ng.Semaphor es arecont r ol l edbyt hef ol l owi ngUni xpar amet er s:semmni ,semmnsandsemmsl .  Typi calse t t i ngsar e: semmns=sum oft he" proc es ses "par amet erf oreachi nst ance ( seei ni t . oraf oreachi nst ance) se mmni=numberofi nst ance sr unni ngsi mul t aneousl y; semmsl=s emmns What is a logical bac%up?

Logi calbackupi nvol vesr eadi ngasetofdat abaserecor dsandwri t i ngt hem i nt oa

fil e.Exportut i l i t yi susedf ort aki ngbackupandI mportut i l i t yi susedt orecover f r om backup. Where can one get a list of all hidden Oracle parameters? 9for +$5:

Or acl ei ni t i al i zat i onorI NI T. ORAparamet er swi t han underscor ei nf r ontarehi dden orunsupport edparamet er s.Onecangetal i stofal lhi ddenparamet er sby execut i ngt hi squery: s el e ct* f r om SYS. X$KSPPI  wher esubst r ( KSPPI NM, 1, 1)=' _' ;  Thef ol l owi ngquer ydi spl ayspar ame t ername swi t ht hei rcur r entval ue: sel ecta. ksppi nm " Paramet er" ,b. ksppst vl" Sessi onVal ue" ,c. ksppst vl" I nst ance  Val ue" f r om x$ksppia,x$ksppcvb,x$ksppsvc  wher ea. i ndx=b. i ndxanda. i ndx=c. i ndx andsubst r ( ksppi nm, 1, 1) =' _' or derbya. ksppi nm; Remember :Thoushal lnotpl aywi t hundocument edpar ame t er s! What is a database E*E.T and how does one set it? 9for +$5:

Or acl et r aceevent sar eusef ulf ordebuggi ngt heOr acl edatabaseserver .The f ol l owi ngt woexampl esaresi mpl yt odemonst r at esynt ax.Ref ert ol at ernot eson t hi spagef oranexpl anat i onofwhatt hesepart i cul arevent sdo. Ei t heraddi ngt hem t ot heI NI T. ORAparamet erfil ecan act i vat eevent s.E. g. event =' 1401t r acenameerr orst ack,l evel12' . . .or ,byi ss ui nganALTERSESSI ON SETEVENTS command:E. g. al t e rs e s si ons e te v e nt s' 10046t r a c enamec ont e xtf or e v e r ,l e v e l4' ;  Theal t ersessi onme t hodonl yaffect st heuser ' scur r entsessi on,wher easchanges t ot heI NI T. ORAfil ewi l laffectal lsessi onsoncet hedatabasehasbeenr est art ed. What is a 'ollbac% segment entry ?

I ti st hesetofbef orei magedatabl ockst hatcont ai nr owst hataremodi fiedbya t r ansact i on.EachRol l backSegmentent rymustbecompl et edwi t hi noner ol l back segment .Asi ngl erol l backsegmentcanhavemul t i pl erol l backsegmentent ri es. What database e"ents can be set? 9for +$5:

 Thef ol l owi ngevent sar ef r equent l yusedbyDBAsandOr acl eSuppor tt odi agnose pr obl ems: "10046t r acenamecont extf or ev er ,l eve l4Tr aceSQLst at ement sandshow bi nd  var i abl esi nt r aceout put . "10046t r acenamecont extf or ever ,l evel8Thi sshowswai tevent si nt heSQLt r ace fil e s "10046t r acenamecont extf or ever ,l evel12Thi sshowsbot hbi ndvari abl enames andwai tevent si nt heSQLt r acefil es "1401t r acenameer r ors t ack,l evel121401t r acenameerr ors t ack,l eve l41401 t r acenameproce sss t at eDumpsoutt r acei nf ormat i oni fanORA1401" i nser t ed

 val uet ool ar gef orcol umn"er r oroccur s.The1401canber epl acedbyanyot her Or acl eServererr orcodet hatyouwantt ot r ace. "60t r acenameer r or st ackl ev el10Show wher ei nt hecodeOr acl eget sadeadl ock ( ORA60) ,andmayhel pt odi agnoset hepr obl em.  Thef ol l owi ngl i st sofevent sar eexampl esonl y .Theymi ghtbev er si onspeci fic,so pl easecal lOr acl ebef oreusi ngthem: "10210t r acenamecont extf orev er ,l evel1010211t r acenamecont extf orever ,l evel 1010231t r acenamecont extf or ever ,l eve l10Theseev ent spr eventdat abasebl ock corrupti ons "10049t r acenamecont extf or ever ,l evel2Memorypr ot ectcur sor "10210t r acenamecont extf or ever ,l evel2Dat abl ockcheck "10211t r acenamecont extf orev er ,l evel2I ndexbl ockcheck "10235t r acenamecont extf or ev er ,l eve l1Memoryheapcheck "10262t r acenamecont extf or ever ,l evel300Al l ow 300byt esmemoryl eakf or connect i ons Not e:Youcanuset heUni xoerrcommandt ogett hedescr i pt i onofanev ent .On Uni x,youcant ype" oer ror a10053"f r om t hecommandpr omptt ogetev entdet ai l s. 1ow can one dump internal database structures? 9for +$5:

 Thef ol l owi ng( mos t l yundocume nt ed)commandscanbeusedt oobt ai ni nf or mat i on abouti nt ernaldatabasest ruct ur es. oDumpcont r olfil ec ont ent s al t erses si onsetev ent s' i mmedi at et r acenameCONTROLFl ev el10' / oDumpfil eheader s al t ersessi onsetevent s' i mmedi at et r acenameFI LE_HDRSl evel10' / oDumpr edol ogheader s al t erses si onsetev ent s' i mmedi at et r acenameREDOHDR l ev el10' / oDumpt hesys t em s t at e NOTE:Take3succe ss i v eSYSTEMSTATE dumps,wi t h10mi nut ei nt er val sal t er ses si onse tev ent s' i mmedi at et r acenameSYSTEMSTATE l ev el10' / oDumpt hepr oces ss t at e al t erses si onsetev ent s' i mmedi at et r acenamePROCESSSTATE l ev el10' / oDumpLi brar yCachedet ai l s al t ersessi onsetevent s' i mmedi at et r acenamel i br arycachel evel10' / oDumpopt i mi zerst at i st i cswheneveraSQLst at ementi spar sed( hi nt :change st at ementorflushpool )al t ersessi onsetevent s' 10053t r acenamecont extf orever , l e ve l1' / oDumpadat abasebl ock( Fi l e/Bl ockmustbeconve r t edt oDBAaddre ss )Conve r t fil eandbl ocknumbert oaDBA( dat abasebl ockaddr ess) .

Eg:vari abl exvar char2; exec: x: =dbms_ut i l i t y . make_data_bl ock_addr ess( 1, 12) ; pr i ntx al t erses si onsetev ent s' i mmedi at et r acenamebl ockdumpl ev el50360894' / What are the different %ind of export bac%ups?

Ful lback-Compl et edat abase I ncr ement al-Onl yaffect edt abl esf r om l asti ncr ement aldat e/f ul lbackupdat e. Cumul at i vebackup-Onl yaffect edt abl ef r om t hel astcumul at i vedat e/f ul lbackup dat e . 1ow free extents are managed in *er ;A and *er F;A ?

Fr eeext ent scannotbemer gedt oget heri nVer6. 0. Fr eeext ent sar eperi odi cal l ycoal esceswi t ht henei ghbori ngf r eeext enti nVer7. 0 What is the use of 'E!O'+ option in ED# command?

ForI ncr ement alexport s,t heflagi ndi r ect swhet herarecor dwi l lbest oresdata di ct i onaryt abl esrecordi ngtheexport .