Sumário Itens Encontrados: 327PART ICritical Database Concepts1 Oracle Database 10g Architecture Options 3Databases and Instances 5Inside the Database 5Choosing Architectures and Options 92 Installing Oracle Database 10g and Creating a Database 11Overview of Licensing and Installation Options 133 Upgrading to Oracle Database 10g 27Choosing an Upgrade Method 29Before Upgrading 30Using the Database Upgrade Assistant 30Performing a Manual Direct Upgrade 31Using Export and Import 34Using the Data-Copying Method 35After Upgrading 364 Planning Oracle ApplicationsâApproaches, Risks, and Standards 37The Cooperative Approach 39Everyone Has âDataâ 40The Familiar Language of Oracle 41Some Common, Everyday Examples 45What Are the Risks? 47The Importance of the New Vision 48How to Reduce the Confusion 50Capitalization in Names and Data 57Normalizing Names 58Good Design Has a Human Touch 58Understanding the Data 62Toward Object Name Normalization 65Intelligent Keys and Column Values 68The Commandments 68PART IISQL and SQL*Plus5 The Basic Parts of Speech in SQL 73Style 75Creating the NEWSPAPER Table 76Using SQL to Select Data from Tables 76select, from, where, and order by 80Logic and Value 82Another Use for where: Subqueries 91Combining Tables 95Creating a View 976 Basic SQL*Plus Reports and Commands 101Building a Simple Report 104Other Features 114Checking the SQL*Plus Environment 120Building Blocks 1227 Getting Text Information and Changing It 123Datatypes 124What Is a String? 124Notation 126Concatenation ( || ) 127How to Cut and Paste Strings 128Using order by and where with String Functions 145Review 1488 Searching for Regular Expressions 149Search Strings 150REGEXP_SUBSTR 1549 Playing the Numbers 163The Three Classes of Number Functions 164Notation 164Single-Value Functions 168Aggregate Functions 175List Functions 182Finding Rows with MAX or MIN 183Precedence and Parentheses 185Review 18610 Dates: Then, Now, and the Difference 189Date Arithmetic 190ROUND and TRUNC in Date Calculations 199TO_DATE and TO_CHAR Formatting 200Dates in where Clauses 211Dealing with Multiple Centuries 212Using the EXTRACT Function 213Using the TIMESTAMP Datatypes 21411 Conversion and Transformation Functions 217Elementary Conversion Functions 220Specialized Conversion Functions 225Transformation Functions 226Review 22812 Grouping Things Together 229The Use of group by and having 230Views of Groups 234The Power of Views of Groups 236More Grouping Possibilities 24113 When One Query Depends upon Another 243Advanced Subqueries 244Outer Joins 249Natural and Inner Joins 255UNION, INTERSECT, and MINUS 25614 Some Complex Possibilities 261Complex Groupings 262Using Temporary Tables 263Using ROLLUP, GROUPING, and CUBE 264Family Trees and connect by 26815 Changing Data: insert, update, merge, and delete 279insert 280rollback, commit, and autocommit 283Multitable Inserts 285delete 289update 291Using the merge Command 29316 DECODE and CASE: if, then, and else in SQL 297if, then, else 298Replacing Values via DECODE 301DECODE Within DECODE 302Greater Than and Less Than in DECODE 305Using CASE 30717 Creating and Managing Tables, Views, Indexes, Clusters, and Sequences 311Creating a Table 312Dropping Tables 320Altering Tables 321Creating a Table from a Table 326Creating an Index-Organized Table 327Using Partitioned Tables 328Creating a View 333Indexes 336Clusters 342Sequences 34418 Basic Oracle Security 345Users, Roles, and Privileges 346What Users Can Grant 353Granting Limited Resources 367PART IIIBeyond the Basics19 Advanced SecurityâVirtual Private Databases 371Initial Configuration 372Create an Application Context 373Create a Logon Trigger 375Create a Security Policy 376Apply the Security Policy to Tables 377Test VPD 378How to Implement Column-Level VPD 379How to Disable VPD 380How to Use Policy Groups 38120 Working with Tablespaces 383Tablespaces and the Structure of the Database 384Planning Your Tablespace Usage 38921 Using SQL*Loader to Load Data 391The Control File 392Starting the Load 394Control File Syntax Notes 398Managing Data Loads 400Tuning Data Loads 402Additional Features 40422 Using Data Pump Export and Import 405Creating a Directory 406Data Pump Export Options 406Starting a Data Pump Export Job 409Data Pump Import Options 413Starting a Data Pump Import Job 41623 Accessing Remote Data 423Database Links 424Using Synonyms for Location Transparency 431Using the User Pseudo-Column in Views 432Dynamic Links: Using the SQL*Plus copy Command 434Connecting to a Remote Database 43524 Using Materialized Views 437Functionality 438Required System Privileges 438Required Table Privileges 439Read-Only vs. Updatable 439create materialized view Syntax 440Using Materialized Views to Alter Query Execution Paths 445Using DBMS_ADVISOR 447Refreshing Materialized Views 449create materialized view log Syntax 455Altering Materialized Views and Logs 456Dropping Materialized Views and Logs 45725 Using Oracle Text for Text Searches 459Adding Text to the Database 460Text Queries and Text Indexes 461Index Sets 47426 Using External Tables 477Accessing the External Data 478Creating an External Table 479Altering External Tables 489Limitations, Benefits, and Potential Uses of External Tables 49027 Using Flashback Queries 493Time-Based Flashback Example 494Saving the Data 496SCN-Based Flashback Example 497What If the Flashback Query Fails? 499What SCN Is Associated with Each Row? 499Flashback Version Queries 500Planning for Flashbacks 50228 FlashbackâTables and Databases 503The flashback table Command 504The flashback database Command 50729 An Introduction to PL/SQL 513PL/SQL Overview 514Declarations Section 514Executable Commands Section 518Exception Handling Section 53130 Triggers 535Required System Privileges 536Required Table Privileges 536Types of Triggers 537Trigger Syntax 538Enabling and Disabling Triggers 548Replacing Triggers 549Dropping Triggers 55031 Procedures, Functions, and Packages 555Required System Privileges 556Required Table Privileges 558Procedures vs. Functions 558Procedures vs. Packages 558create procedure Syntax 559create function Syntax 561create package Syntax 568Viewing Source Code for Procedural Objects 572Compiling Procedures, Functions, and Packages 572Replacing Procedures, Functions, and Packages 573Dropping Procedures, Functions, and Packages 57332 Using Native Dynamic SQL and DBMS_SQL 575Using EXECUTE IMMEDIATE 576Using Bind Variables 578Using DBMS_SQL 579PART VObject-Relational Databases33 Implementing Types, Object Views, and Methods 587Working with Abstract Datatypes 588Implementing Object Views 593Methods 59934 Collectors (Nested Tables and Varying Arrays) 603Varying Arrays 604Nested Tables 610Additional Functions for Nested Tables and Varying Arrays 615Management Issues for Nested Tables and Varying Arrays 61535 Using Large Objects 619Available Datatypes 620Specifying Storage for LOB Data 621Manipulating and Selecting LOB Values 623Row Objects vs. Column Objects 648Object Tables and OIDs 648Object Views with REFs 656Object PL/SQL 661Objects in the Database 66237 An Introduction to Java 667Java vs. PL/SQL: An Overview 668Getting Started 669Declarations 669Executable Commands 670Classes 67938 JDBC Programming 685Getting Started 686Using the JDBC Classes 68839 Java Stored Procedures 697Loading the Class into the Database 700How to Access the Class 702PART VIIClustered OracleâThe Grid40 Oracle Real Application Clusters 709Preinstallation Steps 710Installing RAC 710Starting and Stopping RAC Instances 714Transparent Application Failover 716Adding Nodes and Instances to the Cluster 717Managing the Cluster Registry and Services 71841 Grid Architecture and Management 719Hardware and Operating System Configuration Issues 720Adding Servers to the Grid 723Sharing Data Across the Grid 724Managing the Grid 724Launching OEM 72636 Advanced Object-Oriented Concepts 647A Note About Nomenclature 732New Views Introduced in Oracle Database 10g 733New Columns Introduced in Oracle Database 10g 738The Road Maps: DICTIONARY (DICT) and DICT_COLUMNS 745Things You Select From: Tables (and Columns), Views, Synonyms, and Sequences 746Recycle BinâUSER_RECYCLEBIN and DBA_RECYCLEBIN 755Constraints and Comments 755Abstract Datatypes, ORDBMS-Related Structures, and LOBs 764Database Links and Materialized Views 767Triggers, Procedures, Functions, and Packages 770Dimensions 773Space Allocation and Usage, Including Partitions and Subpartitions 774Users and Privileges 780Roles 782Auditing 783Miscellaneous 785Monitoring: The V$ Dynamic Performance Tables 78543 The Hitchhikerâs Guide to Tuning Applications and SQL 791New Tuning Features in Oracle Database 10g 792TuningâBest Practices 794Generating and Reading Explain Plans 803Major Operations Within Explain Plans 808Implementing Stored Outlines 831Review 83344 Case Studies in Tuning 835Case Study 1: Waits, Waits, and More Waits 836Case Study 2: Application-Killing Queries 839Case Study 3: Long-Running Batch Jobs 84145 The Hitchhikerâs Guide to Oracle Application Server 10g 845What Is Oracle Application Server 10g? 847Communication Services 854Content Management Services 859Business Logic Services 859Presentation Services 861Business Intelligence Services 863Portal Services 865Web Services 866Developer Toolkits 867Persistence Layer Services 872Caching Services 874System Services 876Development Tools 87842 The Hitchhikerâs Guide to the Oracle10g Data Dictionary 731Starting and Stopping the Database 887Sizing and Managing Memory Areas 888Allocating and Managing Space for the Objects 891Monitoring an Undo Tablespace 900Automating Storage Management 901Segment Space Management 902Transporting Tablespaces 903Performing Backups 904Where to Go from Here 91947 The Hitchhikerâs Guide to XML in Oracle 921Document Type Definitions, Elements, and Attributes 922XML Schema 926Using XSU to Select, Insert, Update, and Delete XML Values 928Using XMLType 934Other Features 936Alphabetical Reference 93746 The Hitchhikerâs Guide to Database Administration 885Creating a Database 886