book

Oracle Database 11g The Complete Reference

by Kevin Loney

October 2008

Intermediate to advanced

1656 pages

35h 9m

English

McGraw Hill Computing

Read now

Unlock full access

Databases and InstancesInside the DatabaseStoring the DataGuarding the DataProgrammatic StructuresChoosing Architectures and Options
Overview of Licensing and Installation OptionsUsing OUI to Install the Oracle Software
Choosing an Upgrade MethodBefore UpgradingRunning the Pre-Upgrade Information ToolUsing the Database Upgrade AssistantPerforming a Manual Direct UpgradeUsing Export and ImportExport and Import Versions to UsePerforming the UpgradeUsing the Data-Copying Method
The Cooperative ApproachEveryone Has "Data"The Familiar Language of OracleTables of InformationStructured Query LanguageA Simple Oracle QueryWhy It Is Called "Relational"Some Common, Everyday ExamplesWhat Are the Risks?The Importance of the New VisionChanging EnvironmentsCodes, Abbreviations, and Naming StandardsHow to Reduce the ConfusionNormalizationEnglish Names for Tables and ColumnsEnglish Words for the DataCapitalization in Names and DataNormalizing NamesGood Design Has a Human TouchUnderstanding the Application TasksOutline of TasksUnderstanding the DataThe Atomic Data ModelsThe Atomic Business ModelThe Business ModelData EntryQuery and ReportingToward Object Name NormalizationLevel-Name IntegrityForeign KeysSingular NamesBrevityObject Name ThesaurusIntelligent Keys and Column ValuesThe Commandments

StyleCreating the NEWSPAPER TableUsing SQL to Select Data from Tablesselect, from, where, and order byLogic and ValueSingle-Value TestsLIKESimple Tests Against a List of ValuesCombining LogicAnother Use for where: SubqueriesSingle Values from a SubqueryLists of Values from a SubqueryCombining TablesCreating a ViewExpanding the View
Building a Simple Reportremarkset headsepttitle and btitlecolumnbreak oncompute avgset linesizeset pagesizeset newpagespool/* */Some Clarification on Column HeadingsOther FeaturesCommand Line Editorset pausesavestoreEditinghostAdding SQL*Plus CommandsstartChecking the SQL*Plus EnvironmentBuilding Blocks
DatatypesWhat Is a String?NotationConcatenation (||)How to Cut and Paste StringsRPAD and LPADLTRIM, RTRIM, and TRIMCombining Two FunctionsUsing the TRIM FunctionAdding One More FunctionLOWER, UPPER, and INITCAPLENGTHSUBSTRINSTRASCII and CHRUsing order by and where with String FunctionsSOUNDEXNational Language SupportRegular Expression SupportReview
Search StringsREGEXP_SUBSTRREGEXP_INSTRREGEXP_LIKEREPLACE and REGEXP_REPLACEREGEXP_COUNT
The Three Classes of Number FunctionsNotationSingle-Value FunctionsAddition (+), Subtraction (-), Multiplication (*), and Division (/)NULLNVL: NULL-Value SubstitutionABS: Absolute ValueCEILFLOORMODPOWERSQRT: Square RootEXP, LN, and LOGROUND and TRUNCSIGNSIN, SINH, COS, COSH, TAN, TANH, ACOS, ATAN, ATAN2, and ASINAggregate FunctionsNULLs in Group-Value FunctionsExamples of Single- and Group-Value FunctionsAVG, COUNT, MAX, MIN, and SUMCombining Group-Value and Single-Value FunctionsSTDDEV and VARIANCEDISTINCT in Group FunctionsList FunctionsFinding Rows with MAX or MINPrecedence and ParenthesesReview
Date ArithmeticSYSDATE, CURRENT_DATE, and SYSTIMESTAMPThe Difference Between Two DatesAdding MonthsSubtracting MonthsGREATEST and LEASTNEXT_DAYLAST_DAYMONTHS_BETWEEN Two DatesCombining Date FunctionsROUND and TRUNC in Date CalculationsTO_DATE and TO_CHAR FormattingThe Most Common TO_CHAR ErrorNEW_TIME: Switching Time ZonesTO_DATE CalculationsDates in where ClausesDealing with Multiple CenturiesUsing the EXTRACT FunctionUsing the TIMESTAMP Datatypes
Elementary Conversion FunctionsAutomatic Conversion of DatatypesA Warning About Automatic ConversionSpecialized Conversion FunctionsTransformation FunctionsTRANSLATEDECODEReview
The Use of group by and havingAdding an order byOrder of ExecutionViews of GroupsRenaming Columns with AliasesThe Power of Views of GroupsUsing order by in ViewsLogic in the having ClauseUsing order by with Columns and Group FunctionsJoin ColumnsMore Grouping Possibilities
Advanced SubqueriesCorrelated SubqueriesCoordinating Logical TestsUsing EXISTS and Its Correlated SubqueryOuter JoinsPre-Oracle9 i Syntax for Outer JoinsCurrent Syntax for Outer JoinsReplacing NOT IN with an Outer JoinReplacing NOT IN with NOT EXISTSNatural and Inner JoinsUNION, INTERSECT, and MINUSIN SubqueriesRestrictions on UNION, INTERSECT, and MINUS
Complex GroupingsUsing Temporary TablesUsing ROLLUP, GROUPING, and CUBEFamily Trees and connect byExcluding Individuals and BranchesTraveling Toward the RootsThe Basic Rules
insertInserting a Timeinsert with selectUsing the APPEND Hint to Improve insert Performancerollback, commit, and autocommitUsing savepointsImplicit commitAuto rollbackMultitable Insertsdeleteupdateupdate with Embedded selectupdate with NULLUsing the merge CommandHandling Errors
if, then, elseReplacing Values via DECODEDECODE Within DECODEGreater Than and Less Than in DECODEUsing CASEUsing PIVOT
Creating a TableCharacter Width and NUMBER PrecisionRounding During InsertionConstraints in create tableDesignating Index TablespacesNaming ConstraintsDropping TablesAltering TablesThe Rules for Adding or Modifying a ColumnCreating Read-Only TablesAltering Actively Used TablesCreating Virtual ColumnsDropping a ColumnCreating a Table from a TableCreating an Index-Organized TableCreating a ViewStability of a ViewUsing order by in ViewsCreating a Read-Only ViewIndexesCreating an IndexEnforcing UniquenessCreating a Unique IndexCreating a Bitmap IndexWhen to Create an IndexCreating Invisible IndexesVariety in Indexed ColumnsHow Many Indexes to Use on a TablePlacing an Index in the DatabaseRebuilding an IndexFunction-Based IndexesClustersSequences
Creating a Partitioned TableList PartitioningCreating SubpartitionsCreating Range and Interval PartitionsIndexing PartitionsManaging Partitioned Tables
Users, Roles, and PrivilegesCreating a UserPassword ManagementStandard RolesFormat for the grant CommandRevoking PrivilegesWhat Users Can GrantMoving to Another User with connectcreate synonymUsing Ungranted PrivilegesPassing PrivilegesCreating a RoleGranting Privileges to a RoleGranting a Role to Another RoleGranting a Role to UsersAdding a Password to a RoleRemoving a Password from a RoleEnabling and Disabling RolesRevoking Privileges from a RoleDropping a RoleGranting UPDATE to Specific ColumnsRevoking Object PrivilegesSecurity by UserGranting Access to the PublicGranting Limited Resources
Initial ConfigurationCreate an Application ContextCreate a Logon TriggerCreate a Security PolicyApply the Security Policy to TablesTest VPDHow to Implement Column-Level VPDHow to Disable VPDHow to Use Policy Groups
Transparent Data Encryption of ColumnsSetupAdditional Setup for RAC DatabasesOpening and Closing the WalletEncrypting and Decrypting ColumnsEncrypting a TablespaceSetupCreating an Encrypted Tablespace
Tablespaces and the Structure of the DatabaseTablespace ContentsRECYCLEBIN Space in TablespacesRead-Only Tablespacesnologging TablespacesTemporary TablespacesTablespaces for System-Managed UndoBigfile TablespacesEncrypted TablespacesSupporting Flashback DatabasePlanning Your Tablespace UsageSeparate Active and Static TablesSeparate Indexes and TablesSeparate Large and Small ObjectsSeparate Application Tables from Core Objects
The Control FileLoading Variable-Length DataStarting the LoadLogical and Physical RecordsControl File Syntax NotesManaging Data LoadsRepeating Data LoadsTuning Data LoadsDirect Path LoadingAdditional Features
Creating a DirectoryData Pump Export OptionsStarting a Data Pump Export JobStopping and Restarting Running JobsExporting from Another DatabaseUsing EXCLUDE, INCLUDE, and QUERYData Pump Import OptionsStarting a Data Pump Import JobStopping and Restarting Running JobsEXCLUDE, INCLUDE, and QUERYTransforming Imported ObjectsGenerating SQL
Database LinksHow a Database Link WorksUsing a Database Link for Remote QueriesUsing a Database Link for Synonyms and ViewsUsing a Database Link for Remote UpdatesSyntax for Database LinksUsing Synonyms for Location TransparencyUsing the User Pseudo-Column in Views
FunctionalityRequired System PrivilegesRequired Table PrivilegesRead-Only vs. Updatablecreate materialized view SyntaxTypes of Materialized ViewsRowID vs. Primary Key-Based Materialized ViewsUsing Prebuilt TablesIndexing Materialized View TablesUsing Materialized Views to Alter Query Execution PathsUsing DBMS_ADVISORRefreshing Materialized ViewsWhat Kind of Refreshes Can Be Performed?Fast Refresh with CONSIDER FRESHAutomatic RefreshesManual Refreshescreate materialized view log SyntaxAltering Materialized Views and LogsDropping Materialized Views and Logs
Adding Text to the DatabaseText Queries and Text IndexesText QueriesAvailable Text Query ExpressionsSearching for an Exact Match of a WordSearching for an Exact Match of Multiple WordsSearching for an Exact Match of a PhraseSearches for Words That Are Near Each OtherUsing Wildcards During SearchesSearching for Words That Share the Same StemSearching for Fuzzy MatchesSearches for Words That Sound Like Other WordsUsing the ABOUT OperatorIndex SynchronizationIndex Sets
Accessing the External DataCreating an External TableExternal Table Creation OptionsLoading External Tables on CreationAltering External TablesAccess ParametersAdd ColumnDefault DirectoryDrop ColumnLocationModify ColumnParallelProject ColumnReject LimitRename ToLimitations, Benefits, and Potential Uses of External Tables
Time-Based Flashback ExampleSaving the DataSCN-Based Flashback ExampleWhat If the Flashback Query Fails?What SCN Is Associated with Each Row?Flashback Version QueriesPlanning for Flashbacks
The flashback table CommandPrivileges RequiredRecovering Dropped TablesEnabling and Disabling the Recycle BinFlashing Back to SCN or TimestampIndexes and StatisticsThe flashback database Command
High-level ConfigurationIsolation and LinksCreating a Workload DirectoryCapturing the WorkloadDefining FiltersStarting the CaptureStopping the CaptureExporting AWR DataProcessing the WorkloadReplaying the WorkloadControlling and Starting the Replay ClientsInitializing and Running the ReplayExporting AWR Data
PL/SQL OverviewDeclarations SectionExecutable Commands SectionConditional LogicLoopsCASE StatementsException Handling Section
Highly Available DatabasesOracle Data Guard ArchitectureCreating the Standby Database ConfigurationManaging Roles—Switchovers and FailoversMaking Low-Impact DDL ChangesCreating Virtual ColumnsAltering Actively Used TablesAdding NOT NULL ColumnsOnline Object ReorganizationsDropping a Column
Required System PrivilegesRequired Table PrivilegesTypes of TriggersRow-Level TriggersStatement-Level TriggersBEFORE and AFTER TriggersINSTEAD OF TriggersSchema TriggersDatabase-Level TriggersCompound TriggersTrigger SyntaxCombining DML Trigger TypesSetting Inserted ValuesMaintaining Duplicated DataCustomizing Error ConditionsCalling Procedures Within TriggersNaming TriggersCreating DDL Event TriggersCreating Database Event TriggersCreating Compound TriggersEnabling and Disabling TriggersReplacing TriggersDropping Triggers
Required System PrivilegesRequired Table PrivilegesProcedures vs. FunctionsProcedures vs. Packagescreate procedure Syntaxcreate function SyntaxReferencing Remote Tables in ProceduresDebugging ProceduresCreating Your Own FunctionsCustomizing Error ConditionsNaming Procedures and Functionscreate package SyntaxViewing Source Code for Procedural ObjectsCompiling Procedures, Functions, and PackagesReplacing Procedures, Functions, and PackagesDropping Procedures, Functions, and Packages
Using EXECUTE IMMEDIATEUsing Bind VariablesUsing DBMS_SQLOPEN_CURSORPARSEBIND_VARIABLE and BIND_ARRAYEXECUTEDEFINE_COLUMNFETCH_ROWS, EXECUTE_AND_FETCH, and COLUMN_VALUECLOSE_CURSOR
Tune the SQLSteps for Tuning the PL/SQLUse DBMS_PROFILER to Identify ProblemsUse PL/SQL Features for Bulk Operationsforallbulk collect
Working with Object TypesSecurity for Object TypesIndexing Object Type AttributesImplementing Object ViewsManipulating Data via Object ViewsUsing INSTEAD OF TriggersMethodsSyntax for Creating MethodsManaging Methods
Varying ArraysCreating a Varying ArrayDescribing the Varying ArrayInserting Records into the Varying ArraySelecting Data from Varying ArraysNested TablesSpecifying Tablespaces for Nested TablesInserting Records into a Nested TableWorking with Nested TablesAdditional Functions for Nested Tables and Varying ArraysManagement Issues for Nested Tables and Varying ArraysVariability in CollectorsLocation of the Data
Available DatatypesSpecifying Storage for LOB DataManipulating and Selecting LOB ValuesInitializing ValuesUsing insert with SubqueriesUpdating LOB ValuesUsing String Functions to Manipulate LOB ValuesUsing DBMS_LOB to Manipulate LOB ValuesDeleting LOBs
Row Objects vs. Column ObjectsObject Tables and OIDsInserting Rows into Object TablesSelecting Values from Object TablesUpdates and Deletes from Object TablesThe REF FunctionUsing the DEREF FunctionThe VALUE FunctionInvalid ReferencesObject Views with REFsA Quick Review of Object ViewsObject Views Involving ReferencesObject PL/SQLObjects in the Database
Java vs. PL/SQL: An OverviewGetting StartedDeclarationsExecutable CommandsConditional LogicLoopsException HandlingReserved WordsClasses
Using the JDBC ClassesUsing JDBC for Data Manipulation
Loading the Class into the DatabaseHow to Access the ClassCalling Java Stored Procedures DirectlyWhere to Perform Commands
A Note about NomenclatureNew Views Introduced in Oracle Database 11gThe Road Maps: DICTIONARY (DICT) and DICT_COLUMNSThings You Select From: Tables (and Columns), Views, Synonyms, and SequencesCatalog: USER_CATALOG (CAT)Objects: USER_OBJECTS (OBJ)Tables: USER_TABLES (TABS)Columns: USER_TAB_COLUMNS (COLS)Views: USER_VIEWSSynonyms: USER_SYNONYMS (SYN)Sequences: USER_SEQUENCES (SEQ)Recycle Bin: USER_RECYCLEBIN and DBA_RECYCLEBINConstraints and CommentsConstraints: USER_CONSTRAINTSConstraint Columns: USER_CONS_COLUMNSConstraint Exceptions: EXCEPTIONSTable Comments: USER_TAB_COMMENTSColumn Comments: USER_COL_COMMENTSIndexes and ClustersIndexes: USER_INDEXES (IND)Indexed Columns: USER_IND_COLUMNSClusters: USER_CLUSTERS (CLU)Cluster Columns: USER_CLU_COLUMNSAbstract Datatypes and LOBsAbstract Datatypes: USER_TYPESLOBs: USER_LOBSDatabase Links and Materialized ViewsDatabase Links: USER_DB_LINKSMaterialized ViewsMaterialized View Logs: USER_MVIEW_LOGSTriggers, Procedures, Functions, and PackagesTriggers: USER_TRIGGERSProcedures, Functions, and Packages: USER_SOURCEDimensionsSpace Allocation and Usage, Including Partitions and SubpartitionsTablespaces: USER_TABLESPACESSpace Quotas: USER_TS_QUOTASSegments and Extents: USER_SEGMENTS and USER_EXTENTSPartitions and SubpartitionsFree Space: USER_FREE_SPACEUsers and PrivilegesUsers: USER_USERSResource Limits: USER_RESOURCE_LIMITSTable Privileges: USER_TAB_PRIVSColumn Privileges: USER_COL_PRIVSSystem Privileges: USER_SYS_PRIVSRolesAuditingMiscellaneousMonitoring: The V$ Dynamic Performance TablesCHAINED_ROWSPLAN_TABLEInterdependencies: USER_DEPENDENCIES and IDEPTREEDBA-Only ViewsOracle Label SecuritySQL*Loader Direct Load ViewsGlobalization Support ViewsLibrariesHeterogeneous ServicesIndextypes and OperatorsOutlinesAdvisorsSchedulers
New Tuning Features in Oracle Database 11gNew Tuning Features in Oracle 11gTuning—Best PracticesDo as Little as PossibleDo It as Simply as PossibleTell the Database What It Needs to KnowMaximize the Throughput in the EnvironmentDivide and Conquer Your DataTest CorrectlyGenerating and Reading Explain PlansUsing set autotrace onUsing explain planMajor Operations Within Explain PlansTABLE ACCESS FULLTABLE ACCESS BY INDEX ROWIDRelated HintsOperations That Use IndexesWhen Indexes Are UsedOperations That Manipulate Data SetsOperations That Perform JoinsHow Oracle Handles Joins of More than Two TablesParallelism and Cache IssuesImplementing Stored OutlinesReview
Database Parameter Settings for SQL Result CacheThe DBMS_RESULT_CACHE PackageDictionary Views for the SQL Result CacheAdditional Details for SQL Result CacheOracle Call Interface (OCI) Client Query CacheOracle Call Interface (OCI) Client Query Cache Restrictions
Case Study 1: Waits, Waits, and More WaitsCase Study 2: Application-Killing QueriesUsing the 10053 Trace EventCase Study 3: Long-Running Batch Jobs
Oracle Database VaultNew Concepts for Oracle Database VaultDisabling Oracle Database VaultEnabling Oracle Database VaultDatabase Vault Installation NotesOracle Content Database SuiteRepositoryDocument ManagementUser SecurityOracle Records Database
Preinstallation StepsInstalling RACStorageInitialization ParametersStarting and Stopping RAC InstancesTransparent Application FailoverAdding Nodes and Instances to the Cluster
Creating a DatabaseUsing the Oracle Enterprise ManagerStarting and Stopping the DatabaseSizing and Managing Memory AreasThe Initialization Parameter FileAllocating and Managing Space for the ObjectsImplications of the storage ClauseTable SegmentsIndex SegmentsSystem-Managed UndoTemporary SegmentsFree SpaceSizing Database ObjectsMonitoring an Undo TablespaceAutomating Storage ManagementConfiguring ASMSegment Space ManagementTransporting TablespacesGenerating a Transportable Tablespace SetPlugging in the Transportable Tablespace SetPerforming BackupsData Pump Export and ImportOffline BackupsOnline BackupsRecovery ManagerWhere to Go from Here
Document Type Definitions, Elements, and AttributesXML SchemaUsing XSU to Select, Insert, Update, and Delete XML ValuesInsert, Update, and Delete Processing with XSUXSU and JavaCustomizing the Query ProcessUsing XMLTypeOther Features

Content preview from Oracle Database 11g The Complete Reference

CHAPTER 34Triggers

A trigger defines an action the database should take when some database-related event occurs. Triggers may be used to supplement declarative referential integrity, to enforce complex business rules, or to audit changes to data. The code within a trigger, called the trigger body, is made up of PL/SQL blocks (see Chapter 32).

The execution of triggers is transparent to the user. Triggers are executed by the database when specific types of data manipulation commands are performed on specific tables. Such commands may include inserts, updates, and deletes. Updates of specific columns may also be used as triggering events. Triggering events may also include DDL commands and database events such as shutdowns and logins.

Because of ...