Sumário Itens Encontrados: 546Chapter 1: Oracle Architecture and Storage 1Architecture 2The Instance 2Data Organization 6Data Types 9Character Data Types 9BFILE 10Numeric Data Types 11Date Data Types 12RAW and LONG RAW 12Other Data Types 13Summary 13Chapter 2: Using SQL 15The Processing Cycle for SQL Statements 15Connecting to a Database 16Establishing a Cursor 18Submitting SQL Statements 18Receiving Data 21Performance Considerations 21Retrieval Performance 22Using Bind Variables 23Parallel Operations 25Summary 26Chapter 3: Handling Multiple Users 27Goals 28Data Integrity 28Isolation 29Serialization 29Transactions 29Concurrent User Integrity Problems 30Locks 33Contention 34The Oracle Solution 35Multiversion Read Consistency 35Integrity Implications 37Performance Implications 37Isolation Levels 38Implementation Issues 39Write Contention 39Avoiding Changed Data 40Summary 41Chapter 4: Database Design Basics 43Database Design Phases 45Conceptual Database Design 45Logical Database Design 45Physical Design 46Practical Design 47Database Design Tools 48Database Design Techniques 49Database Design Case Study Example 53Normalization 54First Normal Form 56Second Normal Form 59Third Normal Form 60Other Keys 60Normalization Summary 62Defining Additional Entities (Tables) 62Denormalization 63Other Physical Design Options 64Object-Oriented Design Options of Oracle 66Summary 66Chapter 5: Oracle Security 67Security Concepts 67Confidentiality and Privacy 67Integrity 68Why Database-Level Security? 68Authentication 69Users and Passwords 69Smart Cards and Biometric Identification 70Oracle Syntax 70Authorization 73System Privileges 74Object Privileges 74Roles 75Stored Procedures 78Access Control 78Views 79Encryption 80Virtual Private Database 81Oracle Label Security Option 84Accountability 85Auditing 85Triggers 88Enterprise Security 88LDAP 89Enterprise Users 89Shared Schemas 89Database Security 90Network Security 90Security Practices 92Summary 92xivChapter 6: The Oracle Data Dictionary 93What Is the Data Dictionary? 93Structure of the Oracle Data Dictionary 94USER_TABLES 95ALL_TABLES 96DBA_TABLES 97TAB 98V_$FIXED_TABLE 99The Oracle Data Dictionary During Development 101Locating and Describing Data Dictionary Views 101Which Set of Views? 103Updating the Data Dictionary 107The Oracle Data Dictionary at Execution Time 108SQL to Generate SQL 108Dynamic SQL 110Summary 112Chapter 7: Installing Oracle 113Getting Oracle Software 113Installing Oracle Server Software 116System Requirements 116Installing the Software 117Accessing Your Oracle Database 122Installing Oracle Client Software 123Java Drivers 124Oracle Client Software 124Installing the Instant Client 125Which Driver? 126Summary 127Chapter 8: Introduction to SQL 129A Bit of History 129Standards 130SQL Statement Groupings 130Data Definition Language (DDL) 131CREATE 131ALTER 133DROP 134Caveat! 134Data Manipulation Language (DML) 135SQL: Set-Orientated and Nonprocedural 135SELECT Statement 135Multi-Table Access 147Subqueries 150Insert 153UPDATE 155DELETE 155COMMIT/ROLLBACK/SAVEPOINT 156Summary 156Chapter 9: Extended SQL 159Extended Conditions 159LIKE and REGEXP_LIKE 159IN and EXISTS 160ALL, SOME, and ANY 164Extended Set Operations 166UNION 166INTERSECT 168MINUS 169MERGE 171An Example 172Caveats 173TRUNCATE 174ROWNUM and Ranking 174Using ROWNUM 174Using Analytics 177Flashback Query 179How It Works 180Syntax 180An Example of Flashback Query 180Caveats 183Extended Flashback Capabilities 183Returning Data from Write Operations 183Additional Extensions 184Summary 184Chapter 10: Indexes 185Index Basics 186How an Index Works 186Keys 190Index Attributes 190B-Tree Indexes 193How It Works 193B-Tree Index Myths 194Reverse Key Indexes 196Function-Based Indexes 197Function-Based Indexes at Work 198Caveats 201Domain Indexes 202Bitmap Indexes 202The Structure of a Bitmap Index 203The Impact of a Bitmap Index 204Bitmap Join Index 206Index-Organized Tables 207Index Clusters 207Hash Cluster 209Design and Performance Tips 209Start Small 209Key Compression 210SQL Access Advisor 211Summary 211Chapter 11: Constraints 213Database Integrity Features 213Data Typing 214Constraints 215Triggers and Stored Procedures 215Types of Constraints 216NOT NULL 217PRIMARY KEY 219UNIQUE 221FOREIGN KEY 222CHECK 226DEFAULT 230REF Constraints 231Working with Constraints 232Alter Table 233Constraint States 234Constraints and Indexes 236Deferrable Constraints 238Direct Path Operations 238Constraints and Export/Import 239Effect on Mutating Table Triggers 239Constraints on Views 239Using Database Constraints for Application-Side Rule Checking 240Summary 240Chapter 12: Other Database Structures 241Tablespaces 241Temporary Tablespaces 242Undo Tablespaces 243Compression 243Partitioning 245Views 246Materialized Views 248Sequences 248Global Temporary Tables 250Recycle Bin 250Synonyms 253Why Use Synonyms? 253Working with Synonyms 253Database Links 255Advanced Queuing and Streams 255Streams AQ 255Streams 256Streams Summary 258Objects 258Stored Procedures, Functions, and Triggers 259Accessing Flat Files 260External Tables 260UTL_FILE 260SGA Buffer Pool Assignments 260Workflows 261Workspace Manager 262Summary 263Chapter 13: Functions 265What Is a Function? 265Defining Your Own Functions 265Permissions to Create Functions 266Where Can Functions Be Used? 266Defining a Java Function 267Viewing Information about Your Functions 267Deleting a Function 268Oracleâs Built-In SQL Functions 268Aggregate Functions 268Numeric Functions 275Character Functions 281Date and Time Functions 288Conversion Functions 295Collection Functions 303Other Oracle Functions 303XML Functions 309Summary 313Chapter 14: Distributed Queries, Transactions, and Databases 315Linking Distributed Oracle Databases 316Distributed Transactions and Two-Phase Commit 319Heterogeneous Services 320ODBC 321Transparent Gateways 322Summary 323Chapter 15: PL/SQL Basics 325Code Architecture 325The Block Header 326The Declaration Section 328The Execution Section 328The Exception Section 329How Exceptions Are Handled 329Error-Related Functions 330Scoping and Notation 330PL/SQL language 331Implementing Logic 335Conditional Logic 335Branching 338Iterative 339Doing Nothing 340Creating a PL/SQL Program 341The Purpose of the Sample Procedure 341Starting the Code 341Adding Variables 342Adding Logic 342Adding Exception Handling 343Using PL/SQL Code 344Preparing for Compilation 344Compiling PL/SQL Code 344Running PL/SQL Code 345Compilation, Source Code, and Dependencies 346Security 348Granting Access to PL/SQL units 348Program Unit Rights 348Native Compilation 349Uses of PL/SQL Units 350Summary 350Chapter 16: PL/SQL and SQL 351Basic SQL in PL/SQL 351Cursors 352Creating a Cursor 352Opening a Cursor 352Fetching Data 352Closing the Cursor 353Cursor Attributes 353Using a Single Cursor 353Declare Variables 354Declare Cursor 354Open a Cursor 354Fetch 355Clean Up 356Running the Procedure 356Special Data Types 357%TYPE 357%ROWTYPE 358FOR Cursor 359Syntax 359Modifying the Example 359Implicit Cursors 360REF CURSORs 362PL/SQL Records and Collections 363PL/SQL Records 363PL/SQL Records and SQL 364Associative Arrays 364Nested Tables 365Variable Arrays 366Working with Collections 366Collection Operations 367Which Collection Type? 369BULK COLLECT 369Using BULK COLLECT 370FORALL 373Exception Cases 373FORALL Enhancements 374Dynamic SQL 375EXECUTE IMMEDIATE 376Bulk Operations with Dynamic SQL 376More on Dynamic SQL 376Summary 376Chapter 17: PL/SQL Packages 379What Is a Package? 379Impact of Packages 380Organization 381Visibility 381Scope 381Dependencies 382Runtime Memory Usage 382Oracleâs Endorsement 383An Example 383The Package Specification 383The Package Body 384Running the Example 390Using Packages Together 391Your Second Package 392Package Code 392Changing the First Package 393Impact on the Second Package 394Summary 396Chapter 18: Introduction to Java Database Programming 397Java Architecture 398Java: The Language 398Java Virtual Machine and Bytecode 398What about Performance? 399J2SE and J2EE 400Oracleâs Java Architecture 406JDBC and Persistence Frameworks 408Java outside the Database 411Java inside the Database 412Building Sample Applications 413Setting the CLASSPATH 413Java Application Using JDBCâBasic Ingredients 414Java Application Using OracleAS TopLink 419Java Stored Procedures 427Summary 434Chapter 19: Triggers 437What Are Triggers? 437Types of Triggers 437Order of Execution 438Working with Triggers 438Naming Triggers 438Permissions for Creating Triggers 439Creating Triggers 439:new and :old 440Update Triggers 441Delete Triggers 441Controlling When the Trigger Fires 442Inserting or Updating? 443Special Circumstances 443INSTEAD OF Triggers 444Triggers for Referential Integrity 448Triggers in Other Languages 448Triggers and Mutating Tables 449Removing Triggers 451Deleting/Dropping Triggers 451Disabling Triggers 451Recompiling Triggers 452Viewing Triggers in the Data Dictionary 452Limitations of Triggers 453Summary 453Chapter 20: Regular Expressions and Expression Filter 455Regular Expressions 456Regular Expression Concepts 457Relevance to Database Applications 461Oracle Regular Expressions 462Usage Scenarios 470Architecture and Performance Considerations 475Expression Filter 476Expression Filter Concepts 478Relevance to Database Applications 484Summary 485Chapter 21: Object Relational Interactions with Oracle 487Object Relational 488ODBMS and RDBMS and ORDBMS 488Introducing Oracle Objects 489Object Types 490Object Type Methods 493Inheritance Support 497Schema Evolution 499Mapping to UML Concepts 500Using Oracle Object Types 501Existing Java Class as a Persistent Object Type 501Creating Instances of Object Types in the Database 504Using Java to Interact with Database Objects 506Object Views on Relational Data 511Oracle JPublisher (JPub) 515Configuration and Run Time 516Publishing SQL and DML Statements 517Publishing Java Stored Procedures 518Publishing Object Types 519Support for Web Services 520Summary 520Chapter 22: Oracle XML DB 521Overview of the XML DB Repository 521Protocol-Based Access to XML in the Oracle Repository 522SQL-Based Access to XML Data in the Oracle Repository 529Enabling a New Schema for Use with XDB Repositories 533ACL-Based Security on Oracle XML Repositories 533XMLType 536Creating XMLType Columns and Tables 537XML Views 538XML Schema 540SQL/XML and Query Rewrite 533SQL to XML 554Transforming XML 557A Content Management Example 558Creating the Content Management Application Front End 558Oracle XML Outside the DatabaseâOracle XDK 574Summary 577Chapter 23: HTML-DB 579What Is HTML-DB? 579The HTML-DB Environment 580Installing HTML-DB 581Preparing a Development Environment 588Creating a Workspace 588Creating an Application Developer 591Importing Data 593Creating an Application 599Functionality in Your HTML-DB Application 610Adding a New Page 618Modifying Page Attributes 622Running the Application 625Is That All There Is? 626The Next Step 627Summary 627Chapter 24: High-Speed Data Movement 629Export/Import and the âData Pumpâ 630Transportable Tablespaces 632Usage Considerations 632Transportable Tablespace Examples 632Streams 634Streams Phases and Processes 634Configuring and Monitoring Advanced Queues 635Summary 638Chapter 25: Data Loading and Management 639SQL*Loader 640External Table Database Feature 641Defining External Tables 641Loading from External Tables 642Transformations 643Change Data Capture 644Choantpetenrt s#Partitioning and the Speed of ETL 645Oracle Warehouse Builder 647Typical Build and Deploy Steps Using OWB 647An Example Using OWB 648Nonrelational Targets 652Summary 652Chapter 26: Business Intelligence Query 653Schema for Business Intelligence 654Dimensions and Hierarchies 658Creating Dimensions 660Summary Tables and Materialized Views 661Using the SQL Access Advisor (Summary Advisor) 663Creating and Editing Materialized Views 664Aggregation through SQL: Rollup and Cube 665Proactive Management of Complex Query Workloads 667Summary 669Chapter 27: Business Intelligence Analysis 671SQL Analytics and Statistics 671Ranking and Percentiles 672Windows Functions for Cumulative and Moving Aggregates 675Lag/Lead Analysis 678First/Last Analysis 679Linear Regression and Other Advanced Functions 680OLAP 682Data Mining 686Summary 688Chapter 28: Optimization 689What Is Optimization? 689The Oracle Optimizer 690Optimizer Modes 691Desupported Optimizer Modes 691Setting Optimizer Modes 692Statistics 692Statistics and Cost 692Types of Statistics 693Collecting Statistics 694Statistics and Change 694Production and Test Environments 695Histograms 696Working with Histograms 696Execution Plans 699The Execution Plan 699Data Operations 700Execution Statistics 702Overall Execution Statistics 703Detailed Statistics 704Execution Plan and StatisticsâAn Example 704Viewing Execution Plans and Statistics 709AUTOTRACE 709EXPLAIN PLAN 710SQL Trace and TKPROF 712Enterprise Manager 715V$SQL_PLAN and V$SQL_PLAN_STATISTICS 715Hints 716Syntax of a Hint 716Available Hints 716Parallel Operations Hints 720Now Donât Use Them 721Stored Outlines 722Creating Stored Outlines 722Using Stored Outlines 723Editing Stored Outlines 723Summary 723Index 725