Tableof
•
Contents
• Index
• Examples

Oracle9iFundamentalsIExamCram™2(Exam
1Z0-031)
ByAprilWells

Publisher :Que
PubDate :December03,2004
ISBN :0-7897-3265-3
Pages :544

Whetheryou'restudyingfortheOracle9iFundamentalsIexam
orjustneedtotaketheNewFeaturesupdateexam,Oracle9i
FunamentalsIExamCram2canhelpyougetcertified.This
efficientguidefollowstheexamobjectivesclearlyandconcisely.
NewFeaturesmaterialsarealsoincludedinthebook,aswellasa
full-customizablepracticetestengineonCD.Oracle9i
FundamentalsIExamCram2isyoursmartestwaytoget
certified.

Oracle9iFundamentalsIExamCram™2(Exam
1Z0-031)
ByAprilWells

Publisher :Que


PubDate :December03,2004
Tableof
•
ISBN :0-7897-3265-3
Contents
Pages :544
• Index

• Examples



















Copyright

Oracle9iCramSheet
THEITERATIVESYSTEMDEVELOPMENTCYCLE
BASICTERMINOLOGY
THESELECTSTATEMENT
JOININGTABLES
SPECIALIZEDQUERIES
OPERATORS
CONDITIONS
PSEUDOCOLUMNS
EXPRESSIONS
FUNCTIONS
CHANGINGDATA(DML)
ANotefromSeriesEditorEdTittel
AbouttheAuthor
AbouttheTechnicalEditors
Acknowledgements
WeWanttoHearfromYou!
Introduction
HowtoPrepareforanExam
ExamFormat
TestingSituation
UsingtheBook
Self-Assessment
IntheRealWorld

















































































TheIdealCandidate
PutYourselftotheTest
AssessingYourReadinessfortheExam
OnwardThroughtheFog
Chapter1.OracleArchitectureComponents
DatabaseVersusInstance
Datafiles
MemoryStructures
SharedSQLArea
ProgramGlobalAreas(PGA)Overview
ProcessArchitecture
BackgroundProcesses
ConnectingtoanInstance
ExamPrepQuestions
NeedtoKnowMore?
Chapter2.GettingStartedwiththeOracleServer
AdministrativeTools
OracleUniversalInstaller(OUI)

OracleDatabaseConfigurationAssistant(DBCA)
OptimalFlexibleArchitecture(OFA)
PasswordFileAuthentication
DBATools
ExamPrepQuestions
Chapter3.CreatingaDatabase
PrerequisitesforDatabaseCreation
PlanningDatabaseFileLocations
CreatingtheDatabase
OracleDatabaseConfigurationAssistant
CreateaDatabaseManually
WhattoDoIfDatabaseCreationFails
ExamPrepQuestions
NeedtoKnowMore?
Chapter4.ManaginganInstance
InitializationParameterFiles
OracleManagedFile(OMF)System
DatabaseStartup
WorkingwiththeSTARTUPCommand
ShuttingDowntheDatabase
ManagingtheInstancebyMonitoringtheDiagnosticFiles
















































































ExamPrepQuestions
NeedtoKnowMore?
Chapter5.DataDictionaryContentandUsage
Oracle'sDataDictionary
HowtheDataDictionaryIsUsed
AccessingDataDictionaryandDynamicPerformanceViews
CreatingtheDataDictionary
CommonViewsandTheirUses
ExamPrepQuestions
NeedtoKnowMore?
Chapter6.StorageStructureandRelationships
Segments
Extents
DatabaseDataBlocks
BlockUtilizationParameter
StorageStructureInformationintheDataDictionary
ExamPrepQuestions
NeedtoKnowMore?
Chapter7.GlobalizationSupport
GlobalizationSupport
CharacterSetsandEncodingSchemes
DatabaseCharacterSet

NationalCharacterSet
Language-DependentBehaviorSpecification
SettingEnvironmentVariables
LinguisticSorting
Language-DependentApplicationBehavior
GlobalizationSupportInformationintheDataDictionary
GlobalizationSupportUtilities
ExamPrepQuestions
Chapter8.MaintainingControlFiles
WhatAreControlFiles?
MultiplexingControlFiles
TheCREATECONTROLFILECommand
DroppingControlFiles
BackingUptheControlFile
ObtainingControlFileInformation
ControlFile-RelatedParameters
ManagingtheControlFilewithOMF














































































ExamPrepQuestions
Chapter9.MaintainingRedoLogFiles
PurposeofOnlineRedoLogFiles
StructureofOnlineRedoLogFiles
ControlLogSwitchesandCheckpoints
MultiplexingandMaintainingOnlineRedoLogFiles
UsingOEMtoMaintainRedoLogGroupsandMembers
ManagingRedoLogFileswithOMF
DataDictionaryRedoInformation
ArchivedRedoLogFiles
ExamPrepQuestions
Chapter10.ManagingTablespacesandDataFiles
LogicalStructureoftheDatabase
TypesofTablespaces
CreatingTablespaces
DroppingTablespaces
SpaceManagementinTablespaces
ChangingtheSizeoftheTablespace
ManipulatingTablespacesandDataFileswithOEM
ImplementingOracleManagedFiles(OMF)
DataDictionaryTablespaceandDataFileInformation
ExamPrepQuestions
NeedtoKnowMore?
Chapter11.ManagingUndoData
LogicalStructureofUndoSegmentsintheDatabase
PurposeofUndoData
TypesofUndoData
ImplementingAutomaticUndoManagement

DataDictionaryInformationonUndo
ExamPrepQuestions
Chapter12.ManagingTables
MethodsofStoringData
OracleDataTypes
StructureofaRow
CreatingRegularandTemporaryTables
ExternalTables
ManagingStorageStructures
RegularTableOperations








































































ColumnOperations
DataDictionaryInformationonTables
ExamPrepQuestions
Chapter13.ManagingIndexes
DifferentTypesofIndexesandTheirUses
CreatingIndexes
ChangingStorageParametersforIndexes
ReorganizingIndexes
IndexInformationintheDataDictionary
ExamPrepQuestions

Chapter14.MaintainingDataIntegrity
DefiningDataIntegrity
ImplementingDataIntegrityConstraints
MaintainingIntegrityConstraints
ConstraintInformationintheDataDictionary
ExamPrepQuestions
Chapter15.ManagingUsers
CreatingNewUsers
AlteringUsers
DroppingUsers
UserInformationintheDataDictionary
ExamPrepQuestions
Chapter16.ManagingPasswordSecurityandResources
ManagePasswordsUsingProfiles
AdministeringProfiles
ControllingResourcesUsingProfiles
ManagingResourcePlansandConsumerGroups
DataDictionaryInformationAboutProfiles,PasswordManagement,and
Resources
ExamPrepQuestions
Chapter17.ManagingPrivileges
SystemPrivileges
ObjectPrivileges
AuditingCapabilities
DataDictionaryInformation
ExamPrepQuestions
Chapter18.ManagingRoles
CreatingandModifyingRoles















































































GrantingRoles
ControllingAvailabilityofRoles
RemovingRoles
UsingPredefinedRoles
RoleCreationGuidelines
RoleInformationintheDataDictionary
ExamPrepQuestions
Chapter19.PracticeExam1
Chapter20.Exam1Answers
Chapter21.PracticeExam2
Chapter22.Exam2Answers
AppendixA.CDContentsandInstallationInstructions
MultipleTestModes
QuestionTypes
RandomQuestionsandOrderofAnswers
DetailedExplanationsofCorrectandIncorrectAnswers

AttentiontoExamObjectives
InstallingtheCD
TechnicalSupport
Glossary
AppendixC.NeedtoKnowMore?
Chapter1:OracleArchitecturalComponents
Chapter2:GettingStartedwiththeOracleServer
Chapter3:CreatingaDatabase
Chapter4:ManaginganInstance
Chapter5:DataDictionaryContentandUsage
Chapter6:StorageStructureandRelationships
Chapter7:GlobalizationSupport
Chapter8:MaintainingControlFiles
Chapter9:MaintainingRedoLogFiles
Chapter10:ManagingTablespacesandDataFiles
Chapter11:ManagingUndoData
Chapter12:ManagingTables
Chapter13:ManagingIndexes
Chapter14:MaintainingDataIntegrity
Chapter15:ManagingUsers
Chapter16:ManagingPasswordSecurityandResources
Chapter17:ManagingPrivileges
Chapter18:ManagingRoles




Index



Copyright
Copyright©2005byQuePublishing
Allrightsreserved.Nopartofthisbookshallbereproduced,
storedinaretrievalsystem,ortransmittedbyanymeans,
electronic,mechanical,photocopying,recording,orotherwise,
withoutwrittenpermissionfromthepublisher.Nopatent
liabilityisassumedwithrespecttotheuseoftheinformation
containedherein.Althougheveryprecautionhasbeentakenin
thepreparationofthisbook,thepublisherandauthorassume
noresponsibilityforerrorsoromissions.Norisanyliability
assumedfordamagesresultingfromtheuseoftheinformation
containedherein.
LibraryofCongressCatalogCardNumber:2004108927
PrintedintheUnitedStatesofAmerica
FirstPrinting:October2004
070605044321

Trademarks
Alltermsmentionedinthisbookthatareknowntobe
trademarksorservicemarkshavebeenappropriately
capitalized.QuePublishingcannotattesttotheaccuracyofthis
information.Useofaterminthisbookshouldnotberegarded
asaffectingthevalidityofanytrademarkorservicemark.

WarningandDisclaimer
Everyefforthasbeenmadetomakethisbookascompleteand


asaccurateaspossible,butnowarrantyorfitnessisimplied.
Theinformationprovidedisonan"asis"basis.Theauthorand

thepublishershallhaveneitherliabilitynorresponsibilitytoany
personorentitywithrespecttoanylossordamagesarising
fromtheinformationcontainedinthisbookorfromtheuseof
theCDorprogramsaccompanyingit.

BulkSales
QuePublishingoffersexcellentdiscountsonthisbookwhen
orderedinquantityforbulkpurchasesorspecialsales.For
moreinformation,pleasecontact
U.S.CorporateandGovernmentSales
1-800-382-3419

ForsalesoutsidetheU.S.,pleasecontact
InternationalSales


Credits
Publisher
PaulBoger
ExecutiveEditor
JeffRiley
AcquisitionsEditor
CarolAckerman


DevelopmentEditor
GusMiklos
ManagingEditor
CharlotteClapp
ProjectEditor

AndyBeaster
CopyEditor
GeneilBreeze
Indexer
RebeccaSalerno
Proofreader
KatherinBidwell
TechnicalEditors
RachelCarmichel
KimberlyFloss
PublishingCoordinator
PamaleeNelson
MultimediaDeveloper
DanScherf


InteriorDesigner
GaryAdair
CoverDesigner
AnneJones
PageLayout
MichelleMitchell

Dedication
Asalways,IdedicatethisbooktoLarry,Adam,andAmandya,
myfamily,whowerealwaysthere,alwayssupportive,and
alwayspatientevenwhenIwasn't.Youhavebeen
understandingwhenIhavebeenshorttemperedandwhenI
wroteevenwheneveryoneelsewashavingfun.Havingyou
therehasmeanteverythingtome.



Oracle9iCramSheet

ThisCramSheetcontainslast-minuteremindersyoucanreview
brieflybeforetheexamstarts.Youcannottakethiscramsheet
withyouintotheexam!


THEITERATIVESYSTEMDEVELOPMENT
CYCLE
1. StrategyandAnalysisAnalyzeuserrequirementsand
producesystemspecification.
2. DesignTechnicaldesigndocumentation,aproductof
StrategyandAnalysis.
3. BuildandDocumentConvertDesignintoexecutable
softwarecodeanddatastructures.
4. TransitionUseracceptancetestingandbugremoval.
5. ProductionApplicationsreleaseandsupportpassedto
productionsupport.


BASICTERMINOLOGY
SQLStructuredQueryLanguage.
DMLDataManipulationLanguage(ROLLBACKallowed).
DDLDataDefinitionLanguage(ROLLBACKnotallowed).


THESELECTSTATEMENT
TheSELECTstatementisusedtoretrievedatafromadatabase.

ASELECTstatementisalsoknownasaquerybecauseitqueries
adatabase.
SimpleSELECT:

SELECT*|<column>[AS<name>|"name"][,...]FROM[<schema>.]<

Addanalias:
SELECT[<alias>.]*|[<alias>.]<column>[,...]
FROM[<schema>.]<table>[<alias>];

Retrieveunique(DISTINCT)items:

SELECTDISTINCT|UNIQUE[(]<column>[,...][)]FROM[<schema>.]

TheWHEREClause
TheoptionalWHEREclauseisusedtofilterrowsinaquery,an
UPDATEcommand,oraDELETEcommand.
Simplecomparison:


SELECT*FROM[<schema>.]<table>
[WHERE[<schema>.][[<table>.]|[<alias>.]]<column>
<comparison>[...]<column>];

Conjunctivecomparisonusinglogicalconditions:
SELECT*FROM[<schema>.]<table>
[WHERE[<schema>.][[<table>.]|[<alias>.]]<column>
<comparison>[...]<column>
AND[NOT]|OR[NOT][...]<column><comparison>[...]<column>
AND[NOT]|OR[NOT]...];


TheUPDATEandDELETEcommands:
UPDATE[<schema>.]<table>SET...
[WHERE[<schema>.][[<table>.]|[<alias>.]]<column>
<comparison>[...]<column>...];
DELETEFROM[<schema>.]<table>
[WHERE[<schema>.][[<table>.]|[<alias>.]]<column>
<comparison>[...]<column>...];

TheORDERBYClause
TheoptionalORDERBYclauseisusedtosortrowsreturnedbya
query,anditcanalsobeapartoftheOVERclauseforanalysis.
Simplesorting:


SELECT*FROM[<schema>.]<table>[WHERE]
[ORDERBY{[<alias>.]<column>|}[,...]];

Ascending(thedefault)anddescendingsorts:
SELECT*FROM[<schema>.]<table>[WHERE]
[ORDERBY{[<alias>.]<column>[ASC|DESC]
|[ASC|DESC]}[,...[ASC|DESC]]];

SortingNULLvalues(bydefaultreturnedlast):

SELECT*FROM[<schema>.]<table>[WHERE]
[ORDERBY{[<alias>.]<column>[ASC|DESC][NULLS{FIRST|LAST}]
|[ASC|DESC][NULLS{FIRST|LAST}]}[,...[ASC|DESC

TheOVERclause:

SELECT{<column>[ASOVER()[ORDERBY]...]}[,...]
FROM<table>[WHERE][ORDERBY];

TheGROUPBYClause
TheoptionalGROUPBYclauseisusedtosummarize,aggregate,
andanalyzegroupingsreturnedfromqueries.AnySELECTlist
elementsnotincludedinaggregationfunctionsmustbe
includedintheGROUPBYlistofelements.Thisincludesboth


columnsandexpressions.AtleastoneelementoftheSELECT
listofelementsmustbesubjectedtoanaggregationfunction.
SimpleGROUPBY:
SELECT*FROM[<schema>.]<table>[WHERE]
[GROUPBY[<alias>.]<column>[,...]][ORDERBY];

FilteringgroupswiththeoptionalHAVINGclause(similar
syntaxtothatoftheWHEREclause):

SELECT*FROM[<schema>.]<table>[WHERE]
[GROUPBY...[HAVING<column><comparison><column>[,...]]
[ORDERBY];

ExtendingtheGROUPBYclausewithOLAPfunctionality
usingtheROLLBUP,CUBE,andGROUPINGSETSclauses:
SELECT*FROM[<schema>.]<table>[WHERE]
[GROUPBY...[HAVING...]
[ROLLUP(<column>,[...])|CUBE(<column>[,...])
|GROUPINGSETS((<group>)[,...])][ORDERBY];

JOININGTABLES
Tablescanbejoinedsuchthatresultsfromoneormoretables
appearinthesamequeryresult.Joinscanbeanouterjoin,a
CartesianProduct(acrossjoin),atablejoinedtoitself(aself
join),orasemijoin.
OraclejoinsyntaxisplacedintotheWHEREclauseusingthe
(+)operator(outerjoinoperator),determiningthesideof
anouterjoindeficientininformation:
SELECT*FROM<table>
[WHERE<column><comparison><column>[(+)]]
|[WHERE<column>[(+)]<comparison><column>]
AND[NOT]|OR[NOT]...][GROUPBY][ORDERBY];

ANSIjoinsyntaxisplacedintotheFROMclauseusingthe
JOINkeyword:
SELECT*FROM<table>[JOIN<table>[JOIN<table>...]]
[WHERE][GROUPBY][ORDERBY];

ANSIjoinsyntaxallowstheCROSSJOINclausetocreatea
CartesianProductbetweentwotables:

SELECT*
FROM<table>[[CROSS]JOIN<table>[[CROSS]JOIN<table>...
[WHERE][GROUPBY][ORDERBY];


ANSIjoinsyntaxcanusetheNATURALkeywordtocreate
bothinnerandouterjoins,joiningtablesoncolumnswith
thesamenameinthedifferenttables:

SELECT*
FROM<table>[[NATURAL]JOIN<table>[[NATURAL]JOIN<table>
[WHERE][GROUPBY][ORDERBY];

ANSIjoinsyntaxcanutilizetheUSINGclausetospecify
exactcolumnnamestojoinon,avoidingjoinsonsamenamedcolumnswithdifferentmeanings:
SELECT*
FROM<table>[JOIN<table>USING(<column>[,...])
[JOIN<table>...]USING(<column>[,...])]
[WHERE][GROUPBY][ORDERBY];

ANSIjoinsyntaxcanutilizetheONclausetospecifyexact
columnnamestojoinon,specificallywhendifferent-named
columnsarerequiredinthejoin,usuallyrequiringatableor
aliasreference:

SELECT*
FROM<table>a[JOIN<table>b
ON(a.<column>=b.<column>[AND[NOT]|OR[NOT]...][,.
[JOIN<table>c]ON(...)]
[WHERE][GROUPBY][ORDERBY];


SPECIALIZEDQUERIES
Subqueriescanreturnsinglevalues(scalar),multiple
columnsormultiplerows,orboth.Subqueriescanbeused
inaSELECTclauseelementslist,theFROMclause,theWHERE
clause,theORDERBYclause,anINSERTstatementVALUES
clause,anUPDATEstatementsetclause,oraCASE

statementexpression.Alsotablesandviewscanbecreated
usingsubqueries.
TheWITHclauseallowspreparedexecutionofsubquery
results.Resultscanthenbeutilizedbytheprimarycalling
query:
WITHquery1AS(subquery),query2AS(subquery)
SELECT*FROMquery1JOINquery2JOINquery3;

Hierarchicalqueriesallowhierarchicalrepresentationsof
hierarchicaldata.Thehierarchycanbeaccessedfromthe
rootnodeorastartingpointwithinthehierarchy(theSTART
WITHclause).TheCONNECTBYclauseallowslinkingbetween
acolumninthecurrentrowandanothervalueinaparent
(thePRIORoperator)row:
SELECT<column>,LEVELFROM<table>STARTWITH<condition>
CONNECTBY<current_row>
=PRIOR
<current_column>;

Flashbackqueriesallowaqueryflashbacktoaspecificpoint


intime,basedonatimestamporanSCN:
SELECT*FROM...ASOF{TIMESTAMP|SCN};

ATop-Nquerycanbeusedtoretrieveasmallnumberof
rowsfromalargerowset.
SELECT*FROM(SELECT*FROM<table>ORDERBY...)
WHEREROWNUM

OPERATORS
Stringconcatenationoperator||concatenatestwostrings
together;forexample,'Mynameis'||''||'Joe'returns
thestringMynameisJoe.
SetoperatorsSetoperatorsallowconcatenationofresults
fromtwoqueries.UNIONALLreturnsallrowsincluding
duplicates,UNIONreturnsalluniquerows,INTERSECT
returnsallrowscommontobothqueries,andMINUSreturns
allrowsinoneandnottheotherquery.
HierarchicalqueryoperatorThePRIORoperatorplacedwith
theCONNECTBYclauselinksthecurrentrowwithaparent
row.


CONDITIONS
Comparisonconditions:
Equi(=),Anti(!=,<>,^=),andRange(<,>,=
<,>=).
[NOT]LIKEPatternmatching.
[NOT]INSetmembershipbetweenexpressions.
[NOT]EXISTSSetmembershipproducingaBoolean
result.
[NOT]BETWEENRangesearchbetweentwovalues,
inclusiveofendpoints.
ANY,SOME,andALLSetmembershipconditions.
IS[NOT]NULLNULLvaluetestcondition.
Logicalconditions,inorderofprecedence:
NOTAnexpressionbeingfalseyieldstrue.
ANDTwoexpressionsbeingtrueyieldtrue.

OROneoftwoexpressionsbeingtrueyields
true.