Loading ...
Global Do...
News & Politics
22
0
Try Now
Log In
Pricing
Oracle9i DBA Fundamentals I Volume 2 • Student Guide D11321GC10 Production 1.0 May 2001 D32644 Authors Sarath Chandran Marie St. Gelais S Matt Taylor Jr Technical Reviewers Howard Bradley Ruth Baylis Paul Cartwright Trevor Davis Joel Goodman Scott Gossett Tomoki Ishii Peter Kilpatrick Stefan Lindblad Howard Ostrow Ashesh Parekh Radhanes Petronilla Venceslava Pretlova Andreas Reinhardt Ranbir Singh Peter Sharman Barry Trute Ric VanDyke Karla Villasenor John Watson Teppei Yagihashi Publisher Shane Mattimoe Copyright © Oracle Corporation, 2001. All rights reserved. This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Education Products, Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free. SQL*Loader, SQL*Net, SQL*Plus, Net8, Oracle Call Interface, Oracle7, Oracle8, Oracle 8i, Developer/2000, Developer/2000 Forms, Designer/2000, Oracle Enterprise Manager, Oracle Parallel Server, PL/SQL, Pro*C, Pro*C/C++, and Trusted Oracle are trademarks or registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners. Introduction Course Objectives I-2 Oracle 9i Enterprise Edition I-3 Database Administrator Tasks I-4 1 Oracle Architectural Components Objectives 1-2 Overview of Primary Componenets 1-3 Oracle Server 1-5 Oracle Instance 1-6 Establishing a Connection and Creating a Session 1-7 Oracle Database 1-9 Physical Structure 1-10 Memory Structure 1-11 System Global Area (SGA) 1-12 Shared Pool 1-15 Library Cache 1-16 Data Dictionary Cache 1-17 Database Buffer Cache 1-18 Redo Log Buffer Cache 1-21 Large Pool 1-22 Java Pool 1-23 Program Global Area (PGA) 1-24 Process Structure 1-25 User Process 1-26 Sever Process 1-27 Background Processes 1-28 Database Writer (DBWn) 1-29 Log Writer (LGWR) 1-30 System Monitor (SMON) 1-31 Process Monitor (PMON) 1-32 Checkpoint (CKPT) 1-33 Archiver (ARCn) 1-35 Logical Structure 1-37 Processing a SQL Statement 1-38 Summary 1-40 Practice 1 Overview 1-41 2 Getting Started With the Oracle Server Objectives 2-2 Database Administration Tools 2-3 Contents iii Oracle Universal Installer 2-4 Interactive Installation 2-5 Non-Interactive Installation Using Response Files 2-6 Oracle Database Configuration Assistant 2-9 Optimal Flexible Architecture (OFA) 2-10 Oracle Software and File Locations 2-11 Database Administrator Users 2-12 Authentication Methods for Database Administrators 2-13 Using Password File Authentication 2-14 SQL* Plus 2-16 Oracle Enterprise Manager 2-17 Oracle Enterprise Manager Architecture: Java-Based Console and Applications 2-18 Oracle Enterprise Manager Architecture: Managed Nodes 2-19 Oracle Enterprise Manager Console 2-20 DBA Tools 2-22 Summary 2-24 Practice 2 Overview 2-25 3 Managing an Oracle Instance Objectives 3-2 Initialization Parameter Files 3-3 PFILE initSID.ora 3-5 PFILE Example 3-6 SPFILE spfileSID.ora 3-7 Creating an SPFILE 3-8 SPFILE Example 3-10 Oracle Managed Files 3-12 Oracle Managed File Example 3-14 Starting UP a Database 3-15 STARTUP Command 3-18 The ALTER DATABASE Comand 3-20 Opening a Database in Restricted Mode 3-21 Opening a Database in Read-Only Mode 3-23 Shutting Down the Database 3-24 Shutdown Options 3-25 Managing an Instance by Monitoring Diagnostic Files 3-29 Alert Log File 3-30 Background Trace Files 3-32 User Trace File 3-33 iv Enabling or Disabling User Tracing 3-34 Summary 3-35 Practice 3 Overview 3-38 4 Creating a Database Objectives 4-2 Managing and Organizing a Database 4-3 Creation Prerequisites 4-4 Planning Database Files Locations 4-5 Creating a Database 4-7 Operating System Environment 4-8 Using the Database Configuration Assistant 4-9 Create a Database 4-11 Database Information 4-12 Typical or Custom Install 4-13 Other Parameters 4-15 Complete Database Creation 4-16 Creating a Database Manually 4-17 Preparing the Parameter File 4-18 Creating SPFILE 4-19 Starting the Instance 4-20 Creating the Database 4-21 Creating a Database Using OMF 4-24 Troubleshooting 4-25 After Database Creation 4-26 Summary 4-27 5 Data Dictionary Contents and Usage Objectives 5-2 Data Dictionary 5-3 Data Dictionary Contents 5-5 How the Data Dictionary Is Used 5-6 Data Dictionary View Categories 5-7 Dynamic Performance Tables 5-9 Querying the Data Dictionary and Dynamic Performance Views 5-10 Data Dictionary Examples 5-11 Summary 5-12 Practice 5 Overview 5-13 6 Maintaining the Control File Objectives 6-2 Control File 6-3 v Control File Contents 6-5 Multiplexing the Control File Using SPFILE 6-7 Multiplexing the Control File Using init.ora 6-8 Managing Control Files with OMF 6-9 Obtaining Control File Information 6-10 Summary 6-13 Practice 6 Overview 6-14 7 Maintaining Redo Log Files Objectives 7-2 Using Redo Log Files 7-3 Structure of Redo Log Files 7-4 How Redo Logs Work 7-6 Forcing Log Switches and Checkpoints 7-8 Adding Online Redo Log Groups 7-9 Adding Online Redo Log Members 7-10 Dropping Online Redo Log Groups 7-12 Dropping Online Redo Log Members 7-13 Clearing, Relocating, or Renaming Online Redo Log Files 7-15 Online Redo Log Configuration 7-17 Managing Online Redo Logs with OMF 7-19 Obtaining Group and Member Information 7-20 Archived Redo Log Files 7-22 Summary 7-26 Practice 7 Overview 7-28 8 Managing Tablespaces and Data files Objectives 8-2 Overview 8-3 Database Storage Hierarchy 8-4 SYSTEM and Non-SYSTEM Tablespaces 8-7 Creating Tablespaces 8-8 Space Management in Tablespaces 8-11 Locally Managed Tablespaces 8-12 Dictionary Managed Tablespaces 8-14 Changing the Storage Settings 8-15 Undo Tablespace 8-17 Temporary Tablespace 8-18 Default Temporary Tablespace 8-21 Restrictions on Default Temporary Tablespace 8-22 Offline Status 8-23 Read-Only Tablespaces 8-26 Dropping Tablespaces 8-29 vi Resizing a Tablespace 8-32 Enabling Automatic Extension of Data Files 8-33 Changing the Size of Data Files Manually 8-36 Adding Data files to a Tablespace 8-37 Moving Data Files: ALTER TABLESPACE 8-39 Configuring Oracle Managed Files for Tablespace Creation 8-42 Creating Tablespaces with OMF 8-43 Obtaining Tablespaces Information 8-44 Summary 8-45 Practice 8 Overview 8-47 9 Storage Structure and Relationships Objectives 9-2 Overview 9-3 Types of Segments 9-4 Storage Clause Precedence 9-8 Extent Allocation and Deallocation 9-9 Used and Free Extents 9-10 Database Block 9-11 Database Block Contents 9-12 Block Space Utilization Parameters 9-13 Data Block Management 9-15 Automatic Segment-Space Management 9-16 Configuring Automatic Segment-Space Management 9-18 Manual Data Block Management 9-19 Block Space Usage 9-20 Data Dictionary Views 9-21 Obtaining Storage Information 9-22 Summary 9-24 Practice 9 Overview 9-26 10 Managing Undo Data Objectives 10-2 Managing Undo Data Overview 10-3 Undo Segment 10-4 Undo Segments: Purpose 10-5 Read Consistency 10-6 Types of Undo Segments 10-7 Automatic Undo Management: Concepts 10-9 Automatic Undo Management: Configuration 10-10 Automatic Undo Management: Initialization Parameters 10-11 Automatic Undo Management: UNDO Tablespace 10-12 vii Automatic Undo Management: Altering and UNDO Tablespace 10-14 Automatic Undo Management: Switching UNDO Tablespaces 10-16 Automatic Undo Management: Dropping an UNDO Tablespace 10-18 Automatic Undo Management: Other Parameters 10-21 Undo Data Statistics 10-23 Automatic Undo Management: Sizing an UNDO Tablespaces 10-24 Automatic Undo Management Undo Quota 10-26 Obtaining Undo Segments Information 10-27 Summary 10-29 Practice 10 Overview 10-31 11 Managing Tables Objectives 11-2 Storing User Data 11-3 Oracle Data Types 11-6 ROWID Format 11-10 Structure of a Row 11-12 Creating a Table 11-13 Creating Temporary Tables 11-17 Creating a Table: Guidelines 11-18 Changing Storage Parameters 11-19 Manually Allocating Extents 11-22 Nonpartitioned Table Reorganization 11-23 Truncating a Table 11-24 Dropping a Table 11-26 Dropping a Column 11-27 Using the UNUSED Option 11-28 Obtaining Table Information 11-30 Summary 11-33 Practice 11 Overview 11-34 12 Managing Indexes Objectives 12-2 Classification of Indexes 12-3 B-Tree Index 12-5 Bitmap Index 12-7 Comparing B-Tree and Bitmap Indexes 12-9 Creating Normal B-Tree Indexes 12-10 Creating Indexes: Guidelines 12-14 Creating Bitmap Indexes 12-16 Changing Storage Parameters for Indexes 12-19 Allocating and Deallocating Index Space 12-21 viii Rebuilding Indexes 12-22 Online Rebuild of Indexes 12-24 Coalescing Indexes 12-25 Checking Index Validity 12-26 Dropping Indexes 12-28 Identifying Unused Indexes 12-30 Obtaining Index Information 12-31 Summary 12-32 Practice 12 Overview 12-34 13 Maintaining Data Integrity Objectives 13-2 Data Integrity 13-3 Types of Constraints 13-5 Constraint States 13-6 Constraint Checking 13-8 Defining Constraints as Immediate or Deferred 13-9 Primary and Unique Key Enforcement 13-10 Foreign Key Considerations 13-11 Defining Constraints While Creating a Table 13-13 Guidelines for Defining Constraints 13-18 Enabling Constraints 13-19 Using the EXCEPTIONS Table 13-24 Obtaining Constraint Information 13-27 Summary 13-30 Practice 13 Overview 13-32 14 Managing Password Security and Resources Objectives 14-2 Profiles 14-3 Password Management 14-5 Enabling Password Management 14-6 Password Account Locking 14-7 Password Expiration and Aging 14-8 Password History 14-9 Password Verification 14-10 User-Provided Password Function 14-11 Password Verification Function VERIFY_FUNCTION 14-12 Creating a Profile: Password Settings 14-13 Altering a Profile: Password Setting 14-17 Dropping a Profile: Password Setting 14-19 Resource Management 14-21 ix Enabling Resource Limits 14-22 Setting Resource Limits at Session Level 14-23 Setting Resource Limits at Call Level 14-24 Creating a Profile: Resource Limit 14-25 Managing Resource Using the Database Resource Manager 14-28 Resource Plan Directives 14-30 Obtaining Password and Resource Limits Information 14-32 Summary 14-34 Practice Overview 14-36 15 Managing Users Objectives 15-2 Users and Security 15-3 Database Schema 15-5 Checklist for Creating Users 15-6 Creating a New User: Database Authentication 15-7 Creating a New User: Operating System Authentication 15-10 Changing User Quota on Tablespace 15-12 Dropping a User 15-14 Obtaining User Information 15-15 Summary 15-16 Practice 15 Overview 15-17 16 Managing Privileges Objectives 16-2 Managing Privileges 16-3 System Privileges 16-4 System Privileges: Examples 16-5 Granting System Privileges 16-6 SYSDBA and SYSOPER Privileges 16-9 System Privilege Restrictions 16-10 Revoking System Privileges 16-11 Revoking System Privileges WITH ADMIN OPTION 16-12 Object Privileges 16-13 Granting Object Privileges 16-14 Revoking Object Privileges 16-17 Revoking Object Privileges WITH GRANT OPTION 16-20 Obtaining Privileges Information 16-21 Auditing 16-22 Auditing Guidelines 16-23 Auditing Categories 16-25 x Database Auditing 16-27 Auditing Options 16-29 Viewing Auditing Options 16-31 Obtaining Audit Records 16-32 Summary 16-33 Practice 16 Overview 16-35 17 Managing Roles Objectives 17-2 Roles 17-3 Benefits of Roles 17-4 Creating Roles 17-5 Predefined Roles 17-8 Modifying Roles 17-9 Assigning Roles 17-11 Establishing Default Roles 17-13 Application Roles 17-16 Enabling and Disabling Roles 17-17 Removing Roles from Users 17-21 Removing Roles 17-23 Guidelines for Creating Roles 17-25 Guidelines for Using Passwords and Default Roles 17-26 Displaying Role Information 17-27 Summary 17-28 Practice 17 Overview 17-30 18 Using Globalization Support Objectives 18-2 Globalization Support Features 18-3 Different Types of Encoding Schemes 18-5 Database Character Sets and National Character Sets 18-8 Guidelines for Choosing an Oracle Database Character Set 18-9 Guidelines for Choosing an Oracle National Character Set 18-11 Choosing a Unicode Solution Unicode Database 18-12 Choosing a Unicode Solution Unicode Datatype 18-13 Specifying Language-Dependent Behavior 18-14 Specifying Language-Dependent Behavior for the Server 18-15 Dependent Language and Territory Default Values 18-16 Specifying Language-Dependent Behavior for the Session 18-18 Character Sets in Client-Server Architecture 18-19 Specifying Language-Dependent Behavior for the Session 18-21 xi Linguistic Sorting 18-22 NLS Sorting 18-23 Using NLS Parameters in SQL Functions 18-26 Linguistic Index Support 18-30 Import and Loading Data Using NLS 18-31 Obtaining Information About Character Sets 18-32 Obtaining Information About NLS Settings 18-33 Globalization Support Utilities 18-37 Summary 18-39 Practice 18 Overview 18-40 Appendix A: How to Create an Oracle9i Database in an Unix Environment Appendix B: Managing Rollback Segments Appendix C: Practice for SQL*Plus xii DBA Fundamentals I 12-1 12 Copyright © Oracle Corporation, 2001. All rights reserved. Managing Indexes DBA Fundamentals I 12-2 12-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • List the different types of indexes and their uses • Create various types of indexes • Reorganize indexes • Drop indexes • Get index information from the data dictionary • Monitor the usage of an index DBA Fundamentals I 12-3 Classification of Indexes An index is a tree structure that allows direct access to a row in a table. Indexes can be classified based on their logical design or on their physical implementation. The logical classification groups indexes from an application perspective, while the physical classification is derived from the way the indexes are stored. Single Column and Concatenated Indexes A single column index has only one column in the index key—for example, an index on the employees number column of an employees table. A concatenated index, also known as a composite index, is created on multiple columns in a table. Columns in a concatenated index do not need to be in the same order as the columns in the table, nor do they need to be adjacent—for example, an index on the department and job columns of an employee table. The maximum number of columns in a composite key index is 32. However, the combined size of all the columns cannot exceed roughly one-third of the data block size. 12-3 Copyright © Oracle Corporation, 2001. All rights reserved. Classification of Indexes • Logical – Single column or concatenated – Unique or nonunique – Function-based – Domain • Physical – Partitioned or nonpartitioned – B-tree – Normal or reverse key – Bitmap DBA Fundamentals I 12-4 Classification of Indexes (continued) Unique and Nonunique Indexes A unique index guarantees that no two rows of a table have duplicate values in the column that defines the index. An index key in a unique index can point to only one row in the table. In a nonunique index, a single key can have multiple rows associated with it, and can be used to enforce uniqueness. Function-Based Indexes A function-based index is created when using functions or expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. Function-based indexes can be created as either a B-tree or a bitmap index. Domain Indexes A domain index is an application-specific (Text, Spatial) index that is created, managed, and accessed by routines supplied by an indextype. It is called a domain index because it indexes data in application-specific domains. Only single-column domain indexes are supported. You can build single-column domain indexes on columns having scalar, object, or LOB datatypes. Partitioned and Nonpartitioned Indexes Partitioned indexes are used for large tables to store index entries corresponding to an index in several segments. Partitioning allows an index to be spread across many tablespaces, decreasing contention for index lookup, and increasing manageability. Partitioned indexes are often used with partitioned tables to improve scalability and manageability. An index partition can be created for each table partition. This lesson discusses the creation and maintenance of nonpartitioned B-tree and bitmap indexes. DBA Fundamentals I 12-5 How Indexes Are Stored Although all the indexes use a B-tree structure, the term B-tree index is usually associated with an index that stores a list of ROWIDS for each key. Structure of a B-Tree Index At the top of the index is the root, which contains entries that point to the next level in the index. At the next level are branch blocks, which in turn point to blocks at the next level in the index. At the lowest level are the leaf nodes, which contain the index entries that point to rows in the table. The leaf blocks are doubly linked to facilitate scanning the index in an ascending as well as descending order of key values. Format of Index Leaf Entries An index entry is made up of the following components: • An entry header, which stores number of columns and locking information • Key column length-value pairs, which define the size of a column in the key followed by the value for the column (The number of such pairs is a maximum of the number of columns in the index.) • ROWID of a row, which contains the key values 12-5 Copyright © Oracle Corporation, 2001. All rights reserved. B-Tree Index Index entry header Key column length Key column value ROWID Root Branch Leaf Index entry DBA Fundamentals I 12-6 Index Leaf Entry Characteristics In a B-tree index on a nonpartitioned table: • Key values are repeated if there are multiple rows that have the same key value. • There is no index entry corresponding to a row that has all key columns that are NULL. Therefore a WHERE clause specifying NULL will always result in a full table scan. • Restricted ROWID is used to point to the rows of the table, since all rows belong to the same segment. Effect of DML Operations on an Index The Oracle server maintains all the indexes when DML operations are carried out on the table. Here is an explanation of the effect of a DML command on an index: • Insert operations result in the insertion of an index entry in the appropriate block. • Deleting a row results only in a logical deletion of the index entry. The space used by the deleted row is not available for new entries until all the entries in the block are deleted. • Updates to the key columns result in a logical delete and an insert to the index. The PCTFREE setting has no effect on the index except at the time of creation. A new entry may be added to an index block even if it has less space than that specified by PCTFREE. DBA Fundamentals I 12-7 8 Bitmap Indexes Bitmap indexes are more advantageous than B-tree indexes in certain situations: • When a table has millions of rows and the key columns have low cardinality—that is, there are very few distinct values for the column. For example, bitmap indexes may be preferable to B-tree indexes for the gender and marital status columns of a table containing passport records. • When queries often use a combination of multiple WHERE conditions involving the OR operator. • When there is read-only or low update activity on the key columns. Structure of a Bitmap Index A bitmap index is also organized as a B-tree, but the leaf node stores a bitmap for each key value instead of a list of ROWIDs. Each bit in the bitmap corresponds to a possible ROWID, and if the bit is set, it means that the row with the corresponding ROWID contains the key value. As shown in the diagram, the leaf node of a bitmap index contains the following: • An entry header, containing the number of columns and lock information • Key values consisting of length and value pairs for each key column (In the example, the key consists of only one column, and the first entry has a key value of Blue.) 12-7 Copyright © Oracle Corporation, 2001. All rights reserved. Bitmap Index <Blue, 10.0.3, 12.8.3, 1000100100010010100> <Green, 10.0.3, 12.8.3, 0001010000100100000> <Red, 10.0.3, 12.8.3, 0100000011000001001> <Yellow, 10.0.3, 12.8.3, 0010001000001000010> key start ROWID end ROWID bitmap Table Index Block 10 Block 11 Block 12 File 3 DBA Fundamentals I 12-8 Structure of a Bitmap Index (continued) • Start ROWID, which in the example contains a file number 3, a block number 10, and a row number 0 • End ROWID, which in the example includes a block number 12 and a row number 8 • A bitmap segment consisting of a string of bits (The bit is set when the corresponding row contains the key value and is unset when the row does not contain the key value. The Oracle server uses a patented compression technique to store bitmap segments.) The start ROWID is the ROWID of the first row pointed to by the bitmap segment of the bitmap—that is, the first bit of the bitmap corresponds to that ROWID, the second bit of the bitmap corresponds to the next row in the block, and the end ROWID is a pointer to the last row in the table covered by the bitmap segment. Bitmap indexes use restricted ROWIDs. Using a Bitmap Index The B-tree is used to locate the leaf nodes that contain bitmap segments for a given value of the key. Start ROWID and the bitmap segments are used to locate the rows that contain the key value. When changes are made to the key column in the table, bitmaps must be modified. This results in locking of the relevant bitmap segments. Because locks are acquired on the whole bitmap segment, a row that is covered by the bitmap cannot be updated by other transactions until the first transaction ends. DBA Fundamentals I 12-9 Comparing B-Tree and Bitmap Indexes Bitmap indexes are more compact than B-tree indexes when used with low-cardinality columns. Updates to key columns in a bitmap index are more expensive because bitmaps use bitmap- segment-level locking, whereas in a B-tree index, locks are on entries corresponding to individual rows of the table. Bitmap indexes can be used to perform operations such as Bitmap Boolean. The Oracle server can use two bitmap segments to perform a bitwise boolean and get a resulting bitmap. This allows efficient use of bitmaps in queries that use the boolean predicate. In summary, B-tree indexes may be more suitable in an OLTP environment for indexing dynamic tables, whereas bitmap indexes may be useful in data warehouse environments where complex queries are used on large, static tables. 12-9 Copyright © Oracle Corporation, 2001. All rights reserved. Comparing B-Tree and Bitmap Indexes B-tree Suitable for high-cardinality columns Updates on keys relatively inexpensive Inefficient for queries using OR predicates Useful for OLTP Bitmap Suitable for low-cardinality columns Updates to key columns very expensive Efficient for queries using OR predicates Useful for data warehousing DBA Fundamentals I 12-10 Creating Normal B-Tree Indexes An index can be created either in the account of the user who owns the table or in a different account, although it is generally created in the same account as the table. The syntax above creates an index on the EMPLOYEES table using the LAST_NAME column. 12-10 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Normal B-Tree Indexes CREATE INDEX hr.employees_last_name_idx ON hr.employees(last_name) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx; DBA Fundamentals I 12-11 Syntax (continued) where: UNIQUE is used to specify a unique index (Nonunique is the default.) schema is the owner of the index/table index is the name of the index table is the name of the table column is the name of the column ASC/ DESC indicates whether the index should be created in ascending or decending order. TABLESPACE identifies the tablespace where the index will be created PCTFREE is the amount of space reserved in each block (in percentage of total space minus the block header) at the time of creation for accommodating new index entries INITRANS specifies the number of transaction entries preallocated in each block (The default and the minimum value is 2.) MAXTRANS limits the number of transaction entries that can be allocated to each block (The default is 255.) STORAGE clause identifies the storage clause that determines how extents are allocated to the index LOGGING specifies that the creation of the index and subsequent operations on the index are logged in the redo log file (This is the default.) NOLOGGING specifies that the creation and certain types of data loads are not logged in the redo log file NOSORT specifies that the rows are stored in the database in ascending order, and therefore, the Oracle server does not have to sort the rows while creating the index DBA Fundamentals I 12-12 Syntax (continued) Note • If MINIMUM EXTENT has been defined for the tablespace, the extent sizes for the index are rounded up to the next higher multiple of the MINIMUM EXTENT value. • If the [NO]LOGGING clause is omitted, the logging attribute of the index defaults to the logging attribute of the tablespace in which it resides. • PCTUSED cannot be specified for an index. Because index entries must be stored in the correct order, the user cannot control when an index block is used for inserts. • If the NOSORT keyword is used when the data is not sorted on the key, the statement terminates with an error. This option is likely to fail if the table has had several DML operations on it. • The Oracle server uses existing indexes to create a new index, if possible. This happens when the key for the new index corresponds to the leading part of the key of an existing index. DBA Fundamentals I 12-13 Using Oracle Enterprise Manager to Create an Index Launch Schema Manager from the Console. 1. Launch the Console • %oemapp console • Choose to Launch standalone 2. Expand your working database from the databases folder 3. Expand Schema folder and select Index in the navigator tree 4. Choose Object—>Create. 5. Select Index from the list of values and then click Create 6. Enter General, Partitions, Storage, and Options information in the property sheet. 7. Click Create. While using Schema Manager, the user also has the option to let the tool automatically define the storage and block utilization parameters based on an estimate of the initial volume, the growth rate, the insert activity on the table, and the order in which rows are inserted. Note: You can also launch the Console from Windows NT Start menu DBA Fundamentals I 12-14 Guidelines When Creating Indexes Consider the following while creating an index: • Indexes speed up query performance and slow down DML operations. Always minimize the number of indexes needed on volatile tables. • Place indexes in a separate tablespace, not in a tablespace that has rollback segments, temporary segments, and tables. • There could be significant performance gain for large indexes by avoiding redo generation. Consider using the NOLOGGING clause for creating large indexes. • Because index entries are smaller compared to the rows they index, index blocks tend to have more entries per block. For this reason, INITRANS should generally be higher on indexes than on the corresponding tables. Indexes and PCTFREE The PCTFREE parameter for an index works differently from that of a table. This parameter is used only during creation of the index to reserve space for index entries that may need to be inserted into the same index block. Index entries are not updated. When a key column is updated, this involves a logical delete of the index entry and an insert. 12-14 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Indexes: Guidelines • Balance query and DML needs • Place in separate tablespace • Use uniform extent sizes: Multiples of five blocks or MINIMUM EXTENT size for tablespace • Consider NOLOGGING for large indexes • INITRANS should generally be higher on indexes than on the corresponding tables. DBA Fundamentals I 12-15 Indexes and PCTFREE (continued) Use a low PCTFREE for indexes on columns that are monotonically increasing, such as a system-generated invoice number. In these cases, new index entries are always appended to the existing entries and there is no need to insert a new entry between two existing index entries. Where the value for an indexed column of an inserted row can take on any value, that is, the new value can fall within the current range of values—you should provide for a higher PCTFREE. An example of an index requiring a high PCTFREE is an index on the customer code column of an invoice table. In this case, it is useful to specify a value of PCTFREE as indicated by the following equation: Maximum number of rows – Initial number of rows X 100 Maximum number of rows The maximum value can cater to a specific time period, such as a year. DBA Fundamentals I 12-16 Syntax Use the following command to create a bitmap index: CREATE BITMAP INDEX [schema.] index ON [schema.] table (column [ ASC | DESC ] [ , column [ASC | DESC ] ] ...) [ TABLESPACE tablespace ] [ PCTFREE integer ] [ INITRANS integer ] [ MAXTRANS integer ] [ storage-clause ] [ LOGGING| NOLOGGING ] [ NOSORT ] Notice that a bitmap index cannot be unique. 12-16 Copyright © Oracle Corporation, 2001. All rights reserved. Creating Bitmap Indexes Use the parameter CREATE_BITMAP_AREA_SIZE to specify the amount of memory allocated for bitmap creation. CREATE BITMAP INDEX orders_region_id_idx ON orders(region_id) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx; DBA Fundamentals I 12-17 CREATE_BITMAP_AREA_SIZE The initialization parameter CREATE_BITMAP_AREA_SIZE determines the amount of space that will be used for storing bitmap segments in memory. The default value is 8 MB. A larger value may lead to a faster index creation. If cardinality is very small, this value can be set to a small value. For example, if cardinality is only 2, then the value can be in the order of kilobytes rather than megabytes. As a general rule, for a higher cardinality, more memory is needed for optimal performance. DBA Fundamentals I 12-18 Using Oracle Enterprise Manager to Create a Bitmap Index Launch Schema Manager from the Console. 1. Launch the Console • %oemapp console • Choose to Launch standalone 2. Expand your working database from the databases folder 3. Expand Schema folder and select Index in the navigator tree 4. Select Object—>Create from the menu 5. Select Index from the list of values and then click Create. 6. Enter General, Partitions, Storage, and Options information in the property sheet, and make sure that the Bitmap option is selected in the General page. 7. Click Create. DBA Fundamentals I 12-19 Changing Storage Parameters for Indexes Some of the storage parameters and block utilization parameters can be modified by using the ALTER INDEX command. Syntax ALTER INDEX [schema.]index [ storage-clause ] [ INITRANS integer ] [ MAXTRANS integer ] The implications of changing the storage parameters for an index are the same as the implications of changing them for a table. A common use of this change is to increase the MAXEXTENTS for an index. Block utilization parameters may be changed to guarantee higher levels of concurrency on an index block. 12-19 Copyright © Oracle Corporation, 2001. All rights reserved. Changing Storage Parameters for Indexes ALTER INDEX employees_last_name_idx STORAGE(NEXT 400K MAXEXTENTS 100); DBA Fundamentals I 12-20 Using Oracle Enterprise Manager to Change Storage Parameters Launch Schema Manager from the Console. 1. Launch the Console • %oemapp console • Choose to Launch standalone 2. Expand your working database from the databases folder 3. Expand Schema folder and expand on Index folder in the navigator tree 4. Expand the user name (or schema). 5. Select the index. 6. Modify the values in the Storage tab of the property sheet. 7. Click Apply. Note: You can also launch the Console from Windows NT Start menu DBA Fundamentals I 12-21 Manual Allocation of Space to an Index It may be necessary to add extents to an index before a period of high insert activity on a table. Adding extents prevents dynamic extension of indexes and the resulting degradation in performance. Manual Deallocation of Space from an Index Use the DEALLOCATE clause of the ALTER INDEX command to release unused space above the high-water mark in an index. Syntax Use the following command to allocate or deallocate index space: ALTER INDEX [schema.]index {ALLOCATE EXTENT ([SIZE integer [K|M]] [ DATAFILE ‘filename’ ]) | DEALLOCATE UNUSED [KEEP integer [ K|M ] ] } Manual allocation and deallocation of space for an index follow the same rules as those that are used when using these commands against a table. Note: Index space is deallocated when the table on which the index built is truncated. Truncating a table results in truncation of the associated index. 12-21 Copyright © Oracle Corporation, 2001. All rights reserved. Allocating and Deallocating Index Space ALTER INDEX orders_region_id_idx ALLOCATE EXTENT (SIZE 200K DATAFILE ‘/DISK6/indx01.dbf’); ALTER INDEX orders_id_idx DEALLOCATE UNUSED; DBA Fundamentals I 12-22 Rebuilding Indexes Index rebuilds have the following characteristics: • A new index is built using an existing index as the data source. • Sorts are not needed when an index is built using an existing index, resulting in better performance. • The old index is deleted after the new index is built. During the rebuild, sufficient space is needed to accommodate both the old and the new index in their respective tablespaces. • The resulting index does not contain any deleted entries. Therefore, this index uses space more efficiently. • Queries can continue to use the existing index while the new index is being built. Possible Rebuild Situations Rebuild an index in the following situations: • The existing index needs to be moved to a different tablespace. This may be necessary if the index is in the same tablespace as the table or if objects need to be redistributed across disks. 12-22 Copyright © Oracle Corporation, 2001. All rights reserved. Rebuilding Indexes Use the ALTER INDEX command to: • Move an index to a different tablespace • Improve space utilization by removing deleted entries • Change a reverse key index to a normal B-tree index and vice versa ALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02; DBA Fundamentals I 12-23 Possible Rebuild Situations (continued) • An index contains many deleted entries. This is a typical problem with sliding indexes, such as an index on the order number of an orders table, where completed orders are deleted and new orders with higher numbers are added to the table. If a few old orders are outstanding, there may be several index leaf blocks with all but a few deleted entries. • An existing normal index needs to be converted into a reverse key index. This may be the case when migrating applications from an earlier release of the Oracle server. • The table of the index has been moved to another tablespace using the ALTER TABLE ... MOVE TABLESPACE command. Syntax Use the following command to rebuild an index: ALTER INDEX [schema.] index REBUILD [ TABLESPACE tablespace ] [ PCTFREE integer ] [ INITRANS integer ] [ MAXTRANS integer ] [ storage-clause ] [ LOGGING| NOLOGGING ] [ REVERSE | NOREVERSE ] The ALTER INDEX ... REBUILD command cannot be used to change a bitmap index to B-tree and vice versa. The REVERSE or NOREVERSE keyword, can only be specified for B- tree indexes. DBA Fundamentals I 12-24 Rebuilding Indexes Online Building or rebuilding an index can be a time-consuming task, especially if the table is very large. Before Oracle8i, creating or rebuilding indexes required a lock on the table and prevented concurrent DML operations. Oracle8i offers a method of creating or re-creating an index while allowing concurrent operations on the base table, but performing large DML operations during this procedure is not recommended . Note: There are still DML locks, which means you cannot perform other DDL operations during an online index build. Restrictions • You cannot rebuild an index on a temporary table • You cannot rebuild an entire partitioned index. You must rebuild each partition or subpartition. • You cannot also deallocate unused space. • You cannot change the value of the PCTFREE parameter for the index as a whole. 12-24 Copyright © Oracle Corporation, 2001. All rights reserved. Online Rebuild of Indexes • Rebuilding indexes can be done with minimal table locking • Some restrictions still apply ALTER INDEX orders_id_idx REBUILD ONLINE; DBA Fundamentals I 12-25 Coalescing Indexes When you encounter index fragmentation, you can rebuild or coalesce the index. Before you perform either task, you should consider the cost and benefits of each option and choose the one that works best for your situation. Coalesce on an index is a block rebuild that is done online. In situations where you have B-tree index leaf blocks that can be freed up for reuse, you can merge those leaf blocks using the following SQL statement: ALTER INDEX hr.employees_idx COALESCE; The figure above shows the effect of ALTER INDEX … COALESCE on the index hr.employees_idx. Before performing the COALESCE operation, the first two leaf blocks are 50% full. This means the index is fragmented and can be coalesced to completely filling the first block, reducing fragmentation. 12-25 Copyright © Oracle Corporation, 2001. All rights reserved. Coalescing Indexes Before coalescing After coalescing ALTER INDEX orders_id_idx COALESCE; DBA Fundamentals I 12-26 Analyzing an Index Analyze the index to perform the following: • Check all the index blocks for block corruption. Note that this command does not verify whether index entries correspond to data in the table. • Populate the INDEX_STATS view with information about the index. Syntax ANALYZE INDEX [ schema.]index VALIDATE STRUCTURE After running this command, query INDEX_STATS to obtain information about the index as shown in the following example: 12-26 Copyright © Oracle Corporation, 2001. All rights reserved. Checking Index Validity ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE; INDEX_STATS DBA Fundamentals I 12-27 Checking Indexes and Their Validity (continued) SELECT blocks, pct_used, distinct_keys lf_rows, del_lf_rows FROM index_stats; BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS ------ --------- -------- ------------ 25 11 14 0 1 row selected. Reorganize the index if it has a high proportion of deleted rows. For example: when the ratio of DEL_LF_ROWS to LF_ROWS exceeds 30%. DBA Fundamentals I 12-28 When Should Indexes Be Dropped? Indexes may need to be dropped in the following scenarios: • An index that is no longer needed by applications can be removed. • An index may be dropped prior to performing bulk loads. Dropping an index prior to large data loads and re-creating them after the load: – Improves performance of the load – Uses index space more efficiently • Indexes that are used only periodically do not need to be maintained unnecessarily, especially if they are based on volatile tables. This is generally the case in an OLTP system, where ad hoc queries are generated at year-end or quarter-end to gather information for review meetings. • An index may be marked INVALID when there is an instance failure during certain types of operations such as loading. In this case, the index needs to be dropped and re- created. • The index is corrupt. Indexes that are required for constraints cannot be dropped, therefore, the dependent constraint must be disabled or dropped first. 12-28 Copyright © Oracle Corporation, 2001. All rights reserved. Dropping Indexes • Drop and re-create an index before bulk loads. • Drop indexes that are infrequently needed and build them when necessary. • Drop and re-create invalid indexes. DROP INDEX hr.deptartments_name_idx; DBA Fundamentals I 12-29 Using Oracle Enterprise Manager to Drop an Index Launch Schema Manager from the Console. 1. Launch the Console • %oemapp console • Choose to Launch standalone You can also launch the Console from Windows NT Start menu 2. Expand your working database from the databases folder 3. Expand Schema folder and expand the Index folder in the navigator tree 4. Expand the user name (or schema). 5. Select the index. 6. Select Object—>Remove. 7. Select Yes in the dialog box. Note: An index cannot be dropped if it is used to implement an integrity constraint that is enabled. Constraints are discussed in the lesson “Maintaining Data Integrity.” DBA Fundamentals I 12-30 Identifying Unused Indexes Beginning with Oracle9i, statistics about the usage of an index can be gathered and displayed in V$OBJECT_USAGE. If the information gathered indicates that an index is never used, the index can be dropped. In addition, eliminating unused indexes cuts down on overhead that the Oracle server has to do for DML, thus performance is improved. Each time the MONITORING USAGE clause is specified, V$OBJECT_USAGE will be reset for the specified index. The previous information is cleared or reset, and a new start time is recorded. V$OBJECT_USAGE Columns INDEX_NAME: The index name. TABLE_NAME: The corresponding table. MONITORING: Indicates whether monitoring is ON or OFF. USED: Indicates YES or NO whether index has been used during the monitoring time. START_MONITORING: Time monitoring began on index. END_MONITORING: Time monitoring stopped on index. 12-30 Copyright © Oracle Corporation, 2001. All rights reserved. Identifying Unused Indexes • To start monitoring the usage of an index • To stop monitoring the usage of an index ALTER INDEX summit.orders_id_idx MONITORING USAGE ALTER INDEX summit.orders_id_idx NOMONITORING USAGE DBA Fundamentals I 12-31 12-31 Copyright © Oracle Corporation, 2001. All rights reserved. Obtaining Index Information Information about indexes can be obtained by querying the data dictionary. • DBA_INDEXES: Provides information on the indexes • DBA_IND_COLUMNS: Provides information on the columns indexed • DBA_IND_EXPRESSIONS: Provides information on function based indexes • V$OBJECT_USAGE: Provides information on the usage of an index DBA Fundamentals I 12-32 12-32 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Create different types of indexes • Reorganize indexes • Drop indexes • Get index information from the data dictionary • Begin and end monitoring usage of indexes DBA Fundamentals I 12-33 Quick Reference Context Reference Initialization parameters CREATE_BITMAP_AREA_SIZE Dynamic performance views None Data dictionary tables/views DBA_INDEXES DBA_IND_COLUMNS DBA_OBJECTS IND$ INDEX_STATS Commands CREATE INDEX CREATE UNIQUE INDEX CREATE BITMAP INDEX CREATE INDEX ... REVERSE ALTER INDEX ... STORAGE ALTER INDEX ... INITRANS ... MAXTRANS ALTER INDEX ... ALLOCATE EXTENT ALTER INDEX ... DEALLOCATE UNUSED ALTER INDEX .... REBUILD ALTER INDEX .... REBUILD ... REVERSE ALTER INDEX .... REBUILD ... NOREVERSE ANALYZE INDEX ... VALIDATE STRUCTURE DROP INDEX Packaged procedures and functions None DBA Fundamentals I 12-34 12-34 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 12 Overview This practice covers the following topics: • Creating an index on columns of a table • Moving the index to another tablespace • Dropping an index • Obtain index information DBA Fundamentals I 12-35 Practice 12: Managing Indexes 1 You are considering creating indexes on the NAME and REGION columns of the CUSTOMERS table. What types of index are appropriate for the two columns? Create the indexes, naming them CUST_NAME_IDX and CUST_REGION_IDX, respectively, and placing them in the appropriate tablespaces. Hint: A B-tree index is suitable for a column with many distinct values, and a bitmap index is suitable for columns with only a few distinct values. 2 Move the CUST_REGION_IDX index to another tablespace. Hint: The index can be rebuilt specifying a different tablespace. 3 Note the files and blocks used by the extents by CUST_REGION_IDX index. Hint: Use the view DBA_EXTENTS to get this information. 4 Re-create the CUST_REGION_IDX index without dropping and re-creating it, and retain it in the same tablespace as before. Does the new index use the same blocks that were used earlier? Hint: Rebuild the index. The new index does not reuse the same space as seen from the location of the extent after rebuild. This is because Oracle server builds a temporary index, drops the old one, and renames the temporary index. 5 a As user SYSTEM, run the script lab12_05a.sql to create and populate the NUMBERS table. b Query the table NUMBERS to find the number of distinct values in the two columns in the table. c Create B-tree indexes NUMB_OE_IDX and NUMB_NO_IDX on the ODD_EVEN and NO columns of the NUMBERS table, respectively, and check the total sizes of the indexes. Put the indexes in tablespace INDX01. Hint: Check the total blocks allocated to the extents from DBA_SEGMENTS. d Create bitmap indexes NUMB_OE_IDX and NUMB_NO_IDX on the ODD_EVEN and NO columns of the NUMBERS table, respectively, and check the total sizes of the indexes. Put the indexes in tablespace INDX01. What can you conclude about the relationship between cardinality and sizes of the two types of indexes? Hint: The existing indexes need to be dropped before creating the new indexes. Now re-execute the query to check the sizes of the indexes. DBA Fundamentals I 12-36 DBA Fundamentals I 13-1 13 Copyright © Oracle Corporation, 2001. All rights reserved. Maintaining Data Integrity DBA Fundamentals I 13-2 13-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Implement data integrity constraints • Maintain integrity constraints • Obtain constraint information from the data dictionary DBA Fundamentals I 13-3 Methods to Guarantee Data Integrity Data integrity means that data in a database adheres to business rules. There are three primary ways in which data integrity can be maintained: • Application code • Database triggers • Declarative integrity constraints Mapping the business rules using one of the three methods is a design decision made by the designer. The database administrator is primarily concerned with implementing the methods chosen by the designer and balancing the performance needs against integrity requirements. Application code can be implemented either as stored procedures within the database or as applications running on the client. This lesson focuses on the use of integrity constraints. 13-3 Copyright © Oracle Corporation, 2001. All rights reserved. Data Integrity Application code Table Data Integrity constraint Database trigger DBA Fundamentals I 13-4 Methods to Guarantee Data Integrity (continued) Database Triggers Database triggers are PL/SQL programs that are executed when a certain event such as an insert or an update of a column occurs on a table. Triggers can be enabled or disabled—that is, they can be set to execute when the event occurs, or they can be set not to execute even though they are defined. Database triggers are usually created only to enforce a complex business rule that cannot be defined as an integrity constraint. Note: Database triggers are covered in other Oracle courses. Integrity Constraints Integrity constraints are the preferred mechanism for enforcing business rules because they: • Provide improved performance • Are easy to declare and modify—they do not require extensive coding • Centralize rules • Are flexible (enabled or disabled) • Are fully documented in the data dictionary The following sections explain the behavior of integrity constraints and discuss how they are implemented by the Oracle server. DBA Fundamentals I 13-5 Types of Constraints By default, all columns in a table allow nulls. Null means absence of a value. A NOT NULL constraint requires a column of a table to contain values. A UNIQUE key constraint requires that every value in a column or set of columns (key) be unique. No two rows of a table can have duplicate values in a specified column or set of columns. Each table in the database can have at most one PRIMARY KEY constraint. A PRIMARY KEY constraint ensures that both of the following are true: • No two rows of a table have duplicate values in the specified column. • Primary key columns do not contain nulls. A CHECK integrity constraint on a column or a set of columns requires that a specified condition be true or unknown for every row of the table. Although the NOT NULL and CHECK constraints do not directly require DBA attention, the primary key, unique, and foreign key constraints must be managed to ensure high availability and acceptable performance levels. 13-5 Copyright © Oracle Corporation, 2001. All rights reserved. Types of Constraints Constraint NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK Description Specifies that a column cannot contain null values Designates a column or combination of columns as unique Designates a column or combination of columns as the table’s primary key Designates a column or combination of columns as the foreign key in a referential integrity constraint Specifies a condition that each row of the table must satisfy DBA Fundamentals I 13-6 Constraint States An integrity constraint can be enabled (ENABLE) or disabled (DISABLE). If a constraint is enabled, data is checked as it is entered or updated in the database. Data that does not confirm to the constraint’s rule is prevented from being entered. If a constraint is disabled, then data that does not confirm can be entered into the database. An integrity constraint can be in one of the following states: •DISABLE NOVALIDATE •DISABLE VALIDATE •ENABLE NOVALIDATE •ENABLE VALIDATE Disable Novalidate A constraint that is disable novalidate is not checked. Data in the table, as well as new data that is entered or updated, may not conform to the rules defined by the constraint. Disable Validate If a constraint is in this state, then any modification of the constrained columns is not allowed. In addition, the index on the constraint is dropped and the constraint is disabled. 13-6 Copyright © Oracle Corporation, 2001. All rights reserved. Constraint States ENABLE NOVALIDATE ENABLE VALIDATE Existing data New data DISABLE NOVALIDATE DISABLE VALIDATE = = DBA Fundamentals I 13-7 Constraint States (continued) Enable Novalidate If a constraint is in this state, new data that violates the constraint cannot be entered. However, the table may contain data that is invalid—that is, data that violates the constraint. Enabling constraints in the novalidated state is most useful in data warehouse configurations that are uploading valid OLTP data. Enable Validate If a constraint is in this state, no row violating the constraint can be inserted into the table. However, while the constraint is disabled such a row can be inserted. This row is known as an exception to the constraint. If the constraint is in the enable novalidate state, violations resulting from data entered while the constraint was disabled remain. The rows that violate the constraint must be either updated or deleted in order for the constraint to be put in the validated state. When a constraint changes to enable validate from a disabled state, the table is locked and all data in the table is checked for conformity. This may cause DML operations such as a data load to wait, so it is advisable to move first from a disabled state to enable novalidate, and then to enable validate. Transitions between these states are governed by the following rules: •ENABLE implies VALIDATE, unless NOVALIDATE is specified. •DISABLE implies NOVALIDATE, unless VALIDATE is specified. •VALIDATE and NOVALIDATE do not have default implications for the ENABLE and DISABLE states. • When a unique or primary key moves from the DISABLE state to the ENABLE state and there is no existing index, a unique index is created automatically. Similarly, when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index, the unique index is dropped. • When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data must be checked. However, moving from VALIDATE to NOVALIDATE simply forgets that the data was ever checked. • Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDATE state does not block reads, writes, or other DDL statements. DBA Fundamentals I 13-8 Constraint Checking You can defer checking constraints for validity until the end of the transaction. Nondeferred or Immediate Constraints Nondeferred constraints, also known as immediate constraints, are enforced at the end of every DML statement. A constraint violation causes the statement to be rolled back. If a constraint causes an action such as delete cascade, the action is taken as part of the statement that caused it. A constraint that is defined as nondeferrable cannot be modified to be enforced at the end of a transaction. Deferred Constraints Deferred constraints are constraints that are checked only when a transaction is commited. If any constraint violations are detected at commit time, the entire transaction is rolled back. These constraints are most useful when both the parent and child rows in a foreign key relationship are entered at the same time, as in the case of an order entry system, where the order and the items in the order are entered at the same time. A constraint that is defined as deferrable can be specified as one of the following: • Initially immediate specifies that by default it should function as an immediate constraint, unless explicitly set otherwise. • Initially deferred specifies that by default the constraint should only be enforced at the end of the transaction. 13-8 Copyright © Oracle Corporation, 2001. All rights reserved. Constraint Checking DML statement Check nondeferred constraints COMMIT Check deferred constraints DBA Fundamentals I 13-9 Changing the Enforcement of Constraints The SET CONSTRAINTS statement makes constraints either DEFERRED or IMMEDIATE for a particular transaction. You can use this statement to set the mode for a list of constraint names or for constraints. The SET CONSTRAINTS mode lasts for the duration of the transaction or until another SET CONSTRAINTS statement resets the mode. The SET CONSTRAINTS statement is disallowed inside triggers. The ALTER SESSION statement also has clauses to SET CONSTRAINTS to IMMEDIATE or DEFERRED. These clauses imply setting ALL deferrable constraints (list of constraint names cannot be specified). The ALTER SESSION SET CONSTRAINTS statement applies to a current session only. ALTER SESSION SET CONSTRAINT[S] = {IMMEDIATE|DEFERRED|DEFAULT} SET CONSTRAINT | CONSTRAINTS {constraint |ALL } {IMMEDIATE|DEFERRED} 13-9 Copyright © Oracle Corporation, 2001. All rights reserved. Defining Constraints as Immediate or Deferred • Use the SET CONSTRAINTS statement to make constraints either DEFERRED or IMMEDIATE • The ALTER SESSION statement also has clauses to SET CONSTRAINTS to DEFERRED or IMMEDATE DBA Fundamentals I 13-10 13-10 Copyright © Oracle Corporation, 2001. All rights reserved. Primary and Unique Key Enforcement Is an index available for use? Yes No No Yes Yes No Create nonunique index Create unique index Do not use index Use existing index Key enabled? Constraint deferrable? No Yes Constraint Deferrable? Is the index non-unique? No How to Enforce Primary and Unique Key Constraints Primary and unique keys are enforced using indexes. You can control the location and type of index that is used for enforcing these constraints. The Oracle server uses the following procedure to implement unique and primary key constraints: • If the constraint is disabled, no indexes are needed. • If the constraint is enabled and the columns in the constraint form the leading part of an index, the index is used to enforce the constraint whether the index itself was created as unique or non-unique. • If the constraint is enabled and there is no index that uses the constraint columns as a leading part of the index, then an index with the same name as the constraint is created using the following rules: – If the key is deferrable, a non-unique index on the key column is created. – If the key is non-deferrable, a unique index is created. • If an index is available for use and constraint is non-deferrable, use existing index. If the constraint is deferrable and the index is non-unique, use existing index. DBA Fundamentals I 13-11 Foreign Key Considerations You need to consider several factors in maintaining tables that are in a foreign key relationship. DDL Involving Parent Table The foreign key must be dropped before dropping the parent table. Use the following command to perform both actions using a single statement: DROP TABLE table CASCADE CONSTRAINTS The parent table cannot be truncated without dropping or disabling the foreign key. The foreign key must be dropped before the tablespace containing the parent is dropped. The following command can be used to achieve this: DROP TABLESPACE tablespace INCLUDING CONTENTS CASCADE CONSTRAINTS 13-11 Copyright © Oracle Corporation, 2001. All rights reserved. Foreign Key Considerations Ensure the tablespace containing the parent key key is online Perform DML on child table Use the CASCADE CONSTRAINTS clause Drop tablespace containing parent table Disable or drop foreign key Truncate parent table Cascade constraints Drop parent table Appropriate Solution Desired Action DBA Fundamentals I 13-12 Foreign Key Considerations (continued) If the DELETE CASCADE option is not used when rows are deleted from the parent table, the Oracle server needs to ensure that there are no rows in the child table with the corresponding foreign key. Similarly, an update to the parent key is permitted only when there are no child rows with the old key value. If there is no index on the foreign key on the child table, the Oracle server locks the child table and prevents changes to ensure referential integrity. If there is an index on the table, the referential integrity is maintained by locking the index entries and avoiding more restrictive locks on the child table. If both tables need to be updated concurrently from different transactions, create an index on the foreign key columns. When data is inserted into or the foreign key column is updated in the child table, the Oracle server checks the index on the parent table that is used for enforcing the referenced key. Therefore, the operation succeeds only if the tablespace containing the index is online. Note that the tablespace containing the parent table does not need to be online to perform DML operations on the child table. Oracle9i no longer requires a share lock on unindexed foreign keys when doing an update or delete on the primary key. It still obtains the table-level share lock, but then releases it immediately after obtaining it. If multiple primary keys are update or deleted, the lock is obtained and released once per row. DBA Fundamentals I 13-13 Defining Constraints While Creating a Table A constraint can be defined either when a table is created or when a table is altered. Use the constraint_clause clause in a CREATE TABLE or ALTER TABLE statement to define a constraint. You must have the requisite privileges to define an integrity constraint. To create a referential integrity constraint, the parent table must be in your own schema, or you must have the REFERENCES privilege on the columns of the referenced key in the parent table. 13-13 Copyright © Oracle Corporation, 2001. All rights reserved. Defining Constraints While Creating a Table CREATE TABLE hr.employee( id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX STORAGE(INITIAL 100K NEXT 100K) TABLESPACE indx, last_name VARCHAR2(25) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER(7)) TABLESPACE users; DBA Fundamentals I 13-14 Defining Constraints While Creating a Table (continued) The column_constraint syntax is part of the table definition. At the time the table is created, the constraint can be defined using the following syntax: column datatype [CONSTRAINT constraint] {[NOT] NULL |UNIQUE [USING INDEX index_clause] |PRIMARY KEY [USING INDEX index_clause] |REFERENCES [schema.]table [(column)] [ON DELETE CASCADE] |CHECK (condition) } constraint_state :== [NOT DEFERRABLE|DEFERRABLE [INITIALLY {IMMEDIATE|DEFERRED}] ] [DISABLE|ENABLE [VALIDATE|NOVALIDATE]] where: CONSTRAINT identifies the integrity constraint by the name constraint stored in data dictionary USING INDEX specifies that the parameters defined in the index-clause should be used for the index the Oracle server uses to enforce a unique or primary key constraint (The name of the index is the same as the name of the constraint.) DEFERRABLE indicates that constraint checking can be deferred until the end of the transaction by using the SET CONSTRAINT command NOT DEFERRABLE indicates that this constraint is checked at the end of each DML statement (A NOT DEFERRABLE constraint cannot be deferred by sessions or transactions. NOT DEFERRABLE is the default.) DBA Fundamentals I 13-15 Defining Constraints While Creating a Table (continued) INITIALLY IMMEDIATE indicates that at the start of every transaction, the default is to check this constraint at the end of every DML statement (If no INITIALLY clause is specified, INITIALLY IMMEDIATE is the default.) INITIALLY DEFERRED implies that this constraint is DEFERRABLE and specifies that, by default, the constraint is checked only at the end of each transaction DISABLE disables the integrity constraint (If an integrity constraint is disabled, the Oracle server does not enforce it.) DBA Fundamentals I 13-16 Defining Constraints While Creating a Table (continued) Using Enterprise Manager to define Constraints 1. Launch the Console: %oemapp console 2. Choose to Launch the Console standalone. 3. Expand your working database from the databases folder 4. Right-click on the your working database and click Connect. 5. Supply the username, password and service name for your working database and click OK. 6. Expand the Schema folder. 7. Select the Table folder and select Create from the Object menu 8. Select Table from the list and click Create 9. Supply values for the table name, schema owner, tablespace and define the columns for the table 10. Click the Constraints tab and define integrity constraints and click Create DBA Fundamentals I 13-17 Defining Constraints While Creating a Table (continued) Table Constraint A table constraint is part of the table definition. This can define on any type of constraint except a NOT NULL constraint. This is defined using the following syntax: [CONSTRAINT constraint] {PRIMARY KEY (column [, column ]... ) [USING INDEX index_clause] |UNIQUE (column [, column ]... ) [USING INDEX index_clause] |FOREIGN KEY (column [, column ]... ) REFERENCES [schema.]table [(column [, column ]... )] [ON DELETE CASCADE] |CHECK (condition) } [constraint_state] Note • It is a good practice to adopt a standard naming convention for constraints. This is especially true with CHECK constraints because the same constraint can be created several times with different names. • Table constraints are needed in the following cases: – When a constraint names two or more columns – When a table is altered to add any constraint other than the NOT NULL constraint • Defining a constraint from the type NOT NULL after creating a table is only possible with: ALTER TABLE table MODIFY column CONSTRAINT constraint NOT NULL; Defining Constraints After Creating a Table: Example ALTER TABLE hr.employee ADD(CONSTRAINT employee_dept_id_fk FOREIGN KEY(dept_id) REFERENCES hr.department(id) DEFERRABLE INITIALLY DEFERRED); Note: The EXCEPTIONS clause, discussed under “Enabling Constraints” later in this lesson, can be used to identify rows violating a constraint that is added using the ALTER TABLE command. DBA Fundamentals I 13-18 Guidelines for Defining Constraints The following guidelines are useful when defining constraints: • Place indexes used for enforcing primary key and unique constraints in a tablespace different from that of the table. This can be done either by specifying the USING INDEX clause or by creating the table, creating the index, and altering the table to add or enable the constraint. • If data is frequently loaded in bulk into a table, it is preferable to disable the constraints, perform the load, and then enable the constraints. If a unique index is used for enforcing a primary key or unique constraint, this index needs to be dropped when the constraint is disabled. Performance can be enhanced by using a nonunique index for enforcement of primary key or unique constraints in such situations: either create the key as deferrable or create the index before defining or enabling the key. • If a table contains a self-referencing foreign key, use one of the following methods to load data: – Define or enable the foreign key after the initial load – Define the constraint as a deferrable constraint The second method is useful if data loads are performed frequently. 13-18 Copyright © Oracle Corporation, 2001. All rights reserved. Guidelines for Defining Constraints • Primary and unique constraints: – Place indexes in a separate tablespace – Use nonunique indexes if bulk loads are frequent • Self-referencing foreign keys: – Define or enable foreign keys after initial load – Defer constraint checking DBA Fundamentals I 13-19 Enabling Constraints A constraint that is currently disabled can be enabled in one of the two ways: ENABLE NOVALIDATE or ENABLE VALIDATE Enable NOVALIDATE For PRIMARY KEY and UNIQUE constraints which have an existing index, enabling a constraint NOVALIDATE is much faster than enabling a constraint VALIDATE because existing data is not checked for constraint violation if the constraint is deferrable. If this option is used for enabling a constraint, no locks are required on the table. This method is appropriate where there is a lot of DML activity on a table, as in the case of an OLTP environment. The following command can be used to enable a constraint ENABLE NOVALIDATE: ALTER TABLE [ schema. ] table ENABLE NOVALIDATE {CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column [, column ] ... ) } [ USING INDEX index_clause ] 13-19 Copyright © Oracle Corporation, 2001. All rights reserved. Enabling Constraints ENABLE NOVALIDATE ALTER TABLE hr.departments ENABLE NOVALIDATE CONSTRAINT dept_pk; • No locks on table • Primary and unique keys must use nonunique indexes DBA Fundamentals I 13-20 Enabling Constraints (continued) Restrictions The USING INDEX clause is applicable only for primary key or unique constraints that were created as deferrable, and when one of the following is true: • The constraints were created disabled • The constraints were disabled and the index was dropped. However, if the index needs to be created, using this method of enabling a constraint does not offer any significant benefit over ENABLE VALIDATE because the Oracle server locks the table to build the index. Note: Disabling constraints is covered in the course Introduction to SQL and PL/SQL. DBA Fundamentals I 13-21 Enabling Constraints (continued) Using Enterprise Manager to modify Constraints 1. Launch the Console: %oemapp console 2. Choose to Launch the Console standalone. 3. Expand your working database from the databases folder 4. Right-click on the your working database and click Connect. 5. Supply the username, password and service name for your working database and click OK. 6. Expand the Schema folder. 7. Expand the Table folder and select the Table in which Constraints are to be modified 8. Click the Constraints tab, make the requisite modifications 9. Click Apply Note: You can also launch the Console from Windows NT Start menu DBA Fundamentals I 13-22 Enabling Constraints Enabling a constraint VALIDATE checks existing data for constraint violation. This is the default when a constraint is enabled. If executed when the constraint is disabled, it has the following effects: • The table is locked and changes to the table are prevented until validation of existing data is complete. • The Oracle server creates an index if one does not exist on the index columns. It creates a unique index while enabling a primary key or unique constraint that is nondeferrable. A nonunique index is built for a deferrable primary key or a unique constraint. If this command is executed when a constraint is enforced, it does not require any table locks during validation. The enforced constraint guarantees that no violations are introduced during validation. This has the following advantages: • All constraints are enabled concurrently. • Each constraint is internally parallelized. • Concurrent activity on the table is permitted. 13-22 Copyright © Oracle Corporation, 2001. All rights reserved. Enabling Constraints ENABLE VALIDATE ALTER TABLE hr.employees ENABLE VALIDATE CONSTRAINT emp_dept_fk; • Locks table • Can use unique or nonunique indexes • Needs valid table data DBA Fundamentals I 13-23 Enabling Constraints (continued) The following command is used to enable a constraint ENABLE VALIDATE: ALTER TABLE [ schema. ] table ENABLE [ VALIDATE ]{CONSTRAINT constraint | PRIMARY KEY | UNIQUE ( column [, column ] ... ) } [ USING INDEX index_clause ] [ EXCEPTIONS INTO [ schema. ] table ] Note • The VALIDATE option is the default and does not need to be specified when enabling a constraint that is disabled. • If data in the table violates the constraint, then the statement is rolled back and the constraint remains disabled. • The use of the EXCEPTIONS clause is discussed in the following section. DBA Fundamentals I 13-24 How to Identify Row Violation The EXCEPTIONS clause helps to identify any row that violates an enabled constraint. Use the following procedure to detect constraint violations, rectify them, and reenable a constraint: 1. If the EXCEPTIONS is not already created, run the utlexcpt1.sql script: SQL> @?/rdbms/admin/utlexcpt1 Statement processed. SQL> DESCRIBE exceptions Name Null? Type -------------------------- ------- ---------------- ROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) CONSTRAINT VARCHAR2(30) Note: The exact name and location of the utlexcpt1.sql script is specific to the operating system. For more information, see your operating system specific Oracle documentation. 13-24 Copyright © Oracle Corporation, 2001. All rights reserved. Using the EXCEPTIONS Table • Create the EXCEPTIONS table by running the utlexcpt1.sql script • Execute the ALTER TABLE statement with EXCEPTIONS option • Use subquery on EXCEPTIONS to locate rows with invalid data • Rectify the errors • Reexecute ALTER TABLE to enable the constraint. DBA Fundamentals I 13-25 How to Identify Row Violation (continued) 2. Execute the ALTER TABLE command using the EXCEPTIONS clause: SQL> ALTER TABLE hr.employee 2 ENABLE VALIDATE CONSTRAINT employee_dept_id_fk 3 EXCEPTIONS INTO system.exceptions; ALTER TABLE hr.employee * ORA-02298: cannot enable (hr.EMP_DEPT_FK) - parent keys not found If the EXCEPTIONS table is not qualified with the name of the owner, it must belong to the owner of the table being altered. Rows are inserted into the EXCEPTIONS table. If you are rerunning the command, truncate the EXCEPTIONS table to remove all existing rows. 3. Identify invalid data by using a subquery on the EXCEPTIONS table: SQL> SELECT rowid, id, last_name, dept_id 2 FROM hr.employee 3 WHERE ROWID in (SELECT row_id 4 FROM exceptions) 5 FOR UPDATE; ROWID ID LAST_NAME DEPT_ID ------------------ ---- --------- ------- AAAAeyAADAAAAA1AAA 1003 Pirie 50 1 row selected. 4. Correct the errors in the data: SQL> UPDATE hr.employee 2 SET id=10 3 WHERE rowid=’AAAAeyAADAAAAA1AAA’; 1 row processed. SQL> COMMIT; Statement processed. DBA Fundamentals I 13-26 How to Identify Row Violation (continued) 5. Truncate the EXCEPTIONS table and reenable the constraint: SQL> TRUNCATE TABLE exceptions; Statement processed. SQL> ALTER TABLE hr.employee 2 ENABLE VALIDATE CONSTRAINT employee_dept_id_fk 3 EXCEPTIONS INTO system.exceptions; Statement processed. DBA Fundamentals I 13-27 Obtaining Constraint Information Use the following query to obtain the names, types, and status of all constraints on HR’s EMPLOYEE table: SQL> SELECT constraint_name, constraint_type, deferrable, 2 deferred, validated 3 FROM dba_constraints 4 WHERE owner='HR' 5 AND table_name='EMPLOYEE'; CONSTRAINT_NAME C DEFERRABLE DEFERRED VALIDATED ---------------- - --------------- ----------- ---------- EMPLOYEE_DEPT.. R DEFERRABLE DEFERRED VALIDATED EMPLOYEE_ID_PK P DEFERRABLE IMMEDIATE VALIDATED SYS_C00565 C NOT DEFERRABLE IMMEDIATE VALIDATED 3 rows selected. 13-27 Copyright © Oracle Corporation, 2001. All rights reserved. Obtaining Constraint Information Data Dictionary Views • DBA_CONSTRAINTS • DBA_CONS_COLUMNS DBA Fundamentals I 13-28 Obtaining Constraint Information (continued) The following table shows the columns in the DBA_CONSTRAINTS view that are not self- evident: Columns in Constraints To get the columns in the constraints on HR’s EMPLOYEES table, use the following query: SQL> SELECT c.constraint_name, c.constraint_type, 2 cc.column_name 3 FROM dba_constraints c, dba_cons_columns cc 4 WHERE c.owner='HR' 5 AND c.table_name='EMPLOYEE' 6 AND c.owner = cc.owner 7 AND c.constraint_name = cc.constraint_name 8 ORDER BY cc.position; CONSTRAINT_NAME C COLUMN_NAME ---------------- - --------------- EMPLOYEE_DEPT... R DEPT_ID EMPLOYEE_ID_PK P ID SYS_C00565 C LAST_NAME 3 rows selected. If set, this flag will be used in the optimizer RELY The date when the constraint was last enabled or disabled LAST_CHANGE Indicates that the constraint is to be rewritten to avoid such situations as Year 2000 problems. BAD Indicates whether the constraint name is system-generated (Valid values are ‘USERNAME’ and ‘GENERATED NAME.’) GENERATED Defines the owner and name of the referenced constraint for foreign keys R_OWNER R_CONSTRAINT_NAME Show the condition specified for a check constraint SEARCH_CONDITION The type of constraint is P if Primary Key, U if Unique, R if foreign key, or C if Check constraint. NOT NULL constraints are stored as check constraints. CONSTRAINT_TYPE Description Name DBA Fundamentals I 13-29 Obtaining Constraint Information (continued) Finding Primary Key–Foreign Key Relationships To find foreign keys on hr’s EMPLOYEE table and the parent constraints, use the following query: SQL> SELECT c.constraint_name AS "Foreign Key", 2 p.constraint_name AS "Referenced Key", 3 p.constraint_type, 4 p.owner, 5 p.table_name 6 FROM dba_constraints c, dba_constraints p 7 WHERE c.owner='HR' 8 AND c.table_name='EMPLOYEE' 9 AND c.constraint_type='R' 10 AND c.r_owner=p.owner 11 AND c.r_constraint_name = p.constraint_name; Foreign Key Referenced Key C OWNER TABLE_NAME ------------ -------------- - ---------- ---------- EMPLOYEES_DEPT.. DEPT_PK P HR DEPARTMENT 1 row selected. DBA Fundamentals I 13-30 13-30 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Implement data integrity • Use an appropriate strategy for creating and maintaining constraints • Obtain information from the data dictionary DBA Fundamentals I 13-31 Quick Reference None Packages, procedures, and functions ALTER TABLE…ENABLE VALIDATE CONSTRAINT…EXCEPTIONS INTO ALTER TABLE…ENABLE NOVALIDATE CONSTRAINT ALTER TABLE…DISABLE CONSTRAINT ALTER TABLE ADD CONSTRAINT…EXCEPTIONS INTO CREATE TABLE…CONSTRAINT COMMANDS DBA_CONS_COLUMNS DBA_CONSTRAINTS Data dictionary views None Dynamic performance views None Initialization parameters Reference Context DBA Fundamentals I 13-32 13-32 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 13 Overview This practice covers the following topics: • Creating constraints • Enabling unique constraints • Creating an Exceptions table • Identifying existing constraint violations in a table, correcting the errors and re-enabling the constraints DBA Fundamentals I 13-33 Practice 13: Maintaining Data Integrity 1 Examine the script lab13_01.sql. Run the script to create the constraints. 2 Query the data dictionary to: a Check for constraints, whether they are deferrable, and their status. Hint: Use the DBA_CONSTRAINTS view to get this information b Check the names and types of indexes created to validate the constraints. Hint: The indexes are only created for primary key and unique constraints and have the same name as the constraints 3 Insert two records with the following values into the PRODUCTS table: 4 Enable the unique constraint on the PRODUCT table. Was it successful? Why or why not? 5 a Ensure that new rows added to the table do not violate the constraint on the PRODUCT table. Hint: This can be done by enabling the constraint NOVALIDATE. b Query the data dictionary to verify the effect of the change. c Test that the constraint disables inserts that violate the change by adding a row with the following values: 6 Take the necessary steps to identify existing constraint violations in the PRODUCTS table, modify product codes as needed, and guarantee that all existing as well as new data do not violate the constraint. (Assume that the table has several thousands of rows and it is too time-consuming to verify each row manually.) Hint: Use the following steps: a Create the EXCEPTIONS table. b Run the command to enable the constraint and trap the exceptions. c Use the ROWIDs in the EXCEPTIONS table to list the rows in the PRODUCTS table that violate the constraint. (Do not list LOB columns.) d Rectify the errors. e Enable the constraint. 2400 NT Monitor 4000 3620 UNIX Monitor 4000 LIST_PRICE PRODUCT_DESCRIPTION PRODUCT_ID 3000 Monitor 4000 LIST_PRICE PRODUCT_DESCRIPTION PRODUCT_ID DBA Fundamentals I 13-34 Practice 13: Maintaining Data Integrity 7 Run the script lab13_07.sql to insert rows into the table. Were the inserts successful? Roll back the changes. 8 Now examine the script lab13_08. Notice that this script also performs the inserts in the same sequence. Run the script and check if it executes successfully. 9 Truncate the CUSTOMERS table. Was it successful? Why or why not? DBA Fundamentals I 14-1 14 Copyright © Oracle Corporation, 2001. All rights reserved. Managing Password Security and Resources DBA Fundamentals I 14-2 14-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Manage passwords using profiles • Administer profiles • Control use of resources using profiles • Obtain information about profiles, password management, and resources DBA Fundamentals I 14-3 14-3 Copyright © Oracle Corporation, 2001. All rights reserved. Profiles • A profile is a named set of password and resource limits • Profiles are assigned to users by the CREATE USER or ALTER USER command • Can be enabled or disabled • Can relate to the DEFAULT profile What Is a Profile? A profile is a named set of the following password and resource limits: • Password aging and expiration • Password history • Password complexity verification • Account locking • CPU time • I/O operations • Idle time • Connect time • Memory space (private SQL area for MTS only) • Concurrent sessions After a profile has been created, the database administrator can assign it to each user. If resource limits are enabled, the Oracle server limits the database usage and resources to the defined profile of the user. DBA Fundamentals I 14-4 DEFAULT Profile The Oracle server automatically creates a DEFAULT profile when the database is created. The users who have not been explicitly assigned a specific profile conform to all the limits of the DEFAULT profile. All limits of the DEFAULT profile are initially unlimited. However, the database administrator can change the values so that limits are applied to all users by default. Profile Usage • Restrict users from performing some operations that require heavy use of resources • Ensure that users log off the database when they have left their session idle for some time • Enable group resource limits for similar users • Easily assign resource limits to users • Manage resource usage in large, complex multiuser database systems • Control the use of passwords Profile Characteristics • Profile assignments do not affect current sessions. • Profiles can be assigned only to users and not to roles or other profiles. • If you do not assign a profile when creating a user, the user is automatically assigned the DEFAULT profile. DBA Fundamentals I 14-5 14-5 Copyright © Oracle Corporation, 2001. All rights reserved. Password Management User Password expiration and aging Password verification Password history Account locking Setting up profiles Password Management Features For greater control over database security, Oracle password management is controlled by database administrators with profiles. This lesson describes the available password management features: • Account locking: Enables automatic locking of an account when a user fails to log into the system in the specified number of attempts • Password aging and expiration: Enables the password to have a lifetime, after which it expires and must be changed • Password history: Checks the new password to ensure that the password is not reused for a specified amount of time or a specified number of password changes • Password complexity verification: Makes a complexity check on the password to verify that it is complex enough to provide protection against intruders who might try to break into the system by guessing the password DBA Fundamentals I 14-6 14-6 Copyright © Oracle Corporation, 2001. All rights reserved. Enabling Password Management • Set up password management by using profiles and assign them to users. • Lock, unlock, and expire accounts using the CREATE USER or ALTER USER command. • Password limits are always enforced. How to Enable Password Management Create the profile to limit the password settings, and assign the profile to the user by using the CREATE USER or ALTER USER command. Password limit settings in profiles are always enforced. When password management is enabled, the user account can be locked or unlocked by using the CREATE USER or ALTER USER command. Note: Refer to the Managing Users lesson for details regarding the CREATE USER command. DBA Fundamentals I 14-7 14-7 Copyright © Oracle Corporation, 2001. All rights reserved. Password Account Locking Parameter FAILED_LOGIN_ATTEMPTS PASSWORD_LOCK_TIME Description Number of failed login attempts before lockout of the account Number of days the account is locked after the specified number of failed login attempts PASSWORD_LOCK_TIME Account Locking The Oracle server automatically locks an account after the FAILED_LOGIN_ATTEMPTS value is reached. The account is either automatically unlocked after a specified time PASSWORD_LOCK_TIME or it must be unlocked by the database administrator using the ALTER USER command. The database account can be explicitly locked with the ALTER USER command. When this happens, the account is not automatically unlocked. Note: The ALTER USER command will be demonstrated later in this lesson. DBA Fundamentals I 14-8 14-8 Copyright © Oracle Corporation, 2001. All rights reserved. Password Expiration and Aging Parameter Lifetime of the password in days after which the password expires Grace period in days for changing the password after the first successful login after the password has expired PASSWORD_LIFE_TIME PASSWORD_GRACE_TIME Parameter Password Aging and Expiration The PASSWORD_LIFE_TIME parameter sets the maximum lifetime after which the password must be changed. The database administrator can specify a grace period PASSWORD_GRACE_TIME, which begins after the first attempt to log in to the database after password expiration. A warning message is generated every time the user tries to log in until the grace period is over. The user is expected to change the password within the grace period. If the password is not changed, the account is locked. The user’s account status is changed to EXPIRED by explicitly setting the password to be expired. DBA Fundamentals I 14-9 14-9 Copyright © Oracle Corporation, 2001. All rights reserved. Password History Parameter PASSWORD_REUSE_TIME PASSWORD_REUSE_MAX Description Number of days before a password can be reused Maximum number of times a password can be reused Password History Password history checks ensure that a user cannot reuse a password for a specified time interval. These checks can be implemented using one of the following: • PASSWORD_REUSE_TIME to specify that a user cannot reuse a password for a given number of days • PASSWORD_REUSE_MAX to force a user to define a password that is not identical to earlier passwords When one parameter is set to a value other than DEFAULT or UNLIMITED, the other parameter must be set to UNLIMITED. DBA Fundamentals I 14-10 14-10 Copyright © Oracle Corporation, 2001. All rights reserved. Password Verification Parameter PASSWORD_VERIFY_FUNCTION Description PL/SQL function that makes a password complexity check before a password is assigned Password Verification Before assigning a new password to a user, a PL/SQL function can be invoked to verify the validity of the password. The Oracle server provides a default verification routine or the database administrator can write a PL/SQL function. DBA Fundamentals I 14-11 14-11 Copyright © Oracle Corporation, 2001. All rights reserved. User-Provided Password Function Function must be created in the SYS schema and must have the following specification: function_name( userid_parameter IN VARCHAR2(30), password_parameter IN VARCHAR2(30), old_password_parameter IN VARCHAR2(30)) RETURN BOOLEAN How to Define a Function to Verify a Password When a new password verification function is added, the database administrator must consider the following restrictions: • The procedure must use the specification indicated in the slide. • The procedure returns the value TRUE for success and FALSE for failure. • If the password function raises an exception, an error is returned and the ALTER USER or CREATE USER command is terminated. • The password function is owned by SYS. • If the password function becomes invalid, an error message is returned and the ALTER USER or CREATE USER command is terminated. Note: Refer to the Managing Users lesson for details regarding CREATE USER. DBA Fundamentals I 14-12 14-12 Copyright © Oracle Corporation, 2001. All rights reserved. Password Verification Function VERIFY_FUNCTION • Minimum length is four characters. • Password should not be equal to username. • Password should have at least one alphabetic, one numeric, and one special character. • Password should differ from the previous password by at least three letters. The Default Verification Function The Oracle server provides a complexity verification function, in the form of a default PL/SQL function called VERIFY_FUNCTION of the script utlpwdmg.sql, which must be run in the SYS schema. During the execution of the script utlpwdmg.sql, the Oracle server creates VERIFY_FUNCTION and changes the DEFAULT profile with the following ALTER PROFILE command: ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 10 PASSWORD_REUSE_TIME 1800 PASSWORD_REUSE_MAX UNLIMITED FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1/1440 PASSWORD_VERIFY_FUNCTION verify_function; DBA Fundamentals I 14-13 14-13 Copyright © Oracle Corporation, 2001. All rights reserved. Creating a Profile: Password Settings CREATE PROFILE grace_5 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_TIME 30 PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_GRACE_TIME 5; How to Create a Profile Use the following CREATE PROFILE command to administer passwords: CREATE PROFILE profile LIMIT [FAILED_LOGIN_ATTEMPTS max_value] [PASSWORD_LIFE_TIME max_value] [ {PASSWORD_REUSE_TIME |PASSWORD_REUSE_MAX} max_value] [PASSWORD_LOCK_TIME max_value] [PASSWORD_GRACE_TIME max_value] [PASSWORD_VERIFY_FUNCTION {function|NULL|DEFAULT} ] DBA Fundamentals I 14-14 How to Create a Profile (continued) Where PROFILE: is the name of the profile to be created FAILED_LOGIN_ATTEMPTS: specifies the number of failed attempts to log in to the user account before the account locked PASSWORD_LIFE_TIME: limits the number of days the same password can be used for authentication. The password expires if it is not changed within this period, and further connections are rejected. PASSWORD_REUSE_TIME: specifies the number of days before a password can be reused. If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED. PASSWORD_REUSE_MAX: specifies the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED. PASSWORD_LOCK_TIME: specifies the number of days an account will be locked after the specified number of consecutive failed login attempts PASSWORD_GRACE_TIME: specifies the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires. PASSWORD_VERIFY_FUNCTION: allows a PL/SQL password complexity verification script to be passed as an argument to the CREATE PROFILE statement DBA Fundamentals I 14-15 How to Use Oracle Enterprise Manager to Create a Profile Launch Security Manager from the Console. 1. Launch the Console • %oemapp console • Choose to Launch standalone You can also launch the Console from Windows NT Start menu 2. Expand your working database from the databases folder 3. Expand Security folder and select the Profiles folder 4. Select Create from the Object menu 5. Select Profile in the list and click Create. 6. Enter a name for the Profile and complete other fields or accept the default values 7. Select the Password tab and enter the account password parameters 8. Click Create. Assigning a Profile With the CREATE USER command or the ALTER USER command, a profile can be assigned. Each user can be assigned only one profile at a time. Note: Refer to the Managing Users lesson for details regarding CREATE USER. DBA Fundamentals I 14-16 How to Use Oracle Enterprise Manager to Assign a Profile to a User To generate the ALTER USER command with Oracle Enterprise Manager use the following steps: Launch Security Manager from the Console. 1. Launch the Console • %oemapp console • Choose to Launch standalone 2. Expand your working database from the databases folder 3. Expand the Security folder 4. From the Profiles folder, select Object—>Assign a Profile to User(s). 5. In the Assign Profile dialog box select the user(s) 6. Click OK Note: You can also launch the Console from Windows NT Start menu DBA Fundamentals I 14-17 14-17 Copyright © Oracle Corporation, 2001. All rights reserved. Altering a Profile: Password Setting ALTER PROFILE default FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 10; Altering a Profile The ALTER PROFILE command is used to change the password limits assigned to a profile: ALTER PROFILE profile LIMIT [FAILED_LOGIN_ATTEMPTS max_value] [PASSWORD_LIFE_TIME max_value] [ {PASSWORD_REUSE_TIME |PASSWORD_REUSE_MAX} max_value] [PASSWORD_LOCK_TIME max_value] [PASSWORD_GRACE_TIME max_value] [PASSWORD_VERIFY_FUNCTION {function|NULL|DEFAULT} ] If you want to set the password parameters to less than a day: 1 hour: PASSWORD_LOCK_TIME = 1/24 10 minutes: PASSWORD_LOCK_TIME = 10/1400 5 minutes: PASSWORD_LOCK_TIME = 5/1440 DBA Fundamentals I 14-18 How to Use Oracle Enterprise Manager to Alter a Profile Launch Security Manager from the Console. 1. Launch the Console • %oemapp console • Choose to Launch standalone 2. Expand your working database from the databases folder 3. Expand the Security folder 4. Expand the Profiles folder. 5. Select the profile. 6. Click the Password tab in the detail side of the Console and change the details on the password parameters 7. Click Apply. Guidelines Changes to a profile do not affect current sessions. Changes are used in subsequent sessions only. Note: You can also launch the Console from Windows NT Start menu DBA Fundamentals I 14-19 14-19 Copyright © Oracle Corporation, 2001. All rights reserved. Dropping a Profile: Password Setting DROP PROFILE developer_prof; DROP PROFILE developer_prof CASCADE; Dropping a Profile Drop a profile using the DROP PROFILE command: DROP PROFILE profile [CASCADE] where: profile: is the name of the profile to be dropped CASCADE: revokes the profile from users to whom it is assigned (The Oracle server automatically assigns the DEFAULT profile to such users. Specify this option to drop a profile that is currently assigned to users.) Guidelines • The DEFAULT profile cannot be dropped. • When a profile is dropped, this change applies to subsequently created sessions only and not to the current sessions.
