Oracle 9i Fundamentals I Exam Cram 2

April Wells is Senior Oracle DBA, Oracle APPS DBA, and DB2 DBA for CS Stars, an insurance claims processing and risk management ASP in Amarillo, Texas. She holds an Oracle 8, 8i, and 9i OCP and has taken numerous database and Oracle-specific classes. Her background includes experience with IMS databases; DB2; SQL Server; Oracle 8, 8i, and 9i; and dBase; working as a webmaster and Linux System Administrator as well as a Cobol programmer; Oracle Forms designer; and Visual Basic programmer. April has presented at IOUG and has recently published Oracle 11i E-Business Suite from the Front Lines, Grid Database Design, and several articles on databases for InformIT. She is working on two more books (Grid Applications Design and Oracle 9i/10i Data Warehouse Field Guide). She graduated with a BS in Information Science from the University of Pittsburgh's School of Library and Information Sciences and earned her MBA from the Texas A&M University System.

Introduction.

Self-Assessment.

1. Oracle Architecture Components.

    Database Versus Instance.

      Oracle Server.

      Database.

      Instance.

      Data Blocks.

      Block Size Choices.

      Disk Blocks.

      Extents.

      Segments.

    Datafiles.

      Tablespaces.

      Synonym.

      Schema.

      Schema Objects.

      Table.

      Index.

      View.

      Redo Log Files.

      Control Files.

     Memory Structures.

      System Global Area.

    Shared SQL Area.

      Shared PL/SQL Area.

    Program Global Areas (PGA) Overview.

      Private SQL Area.

      SQL Work Areas.

    Process Architecture.

    Background Processes.

      Database Writer (DBWn).

      Log Writer (LGWR).

      Checkpoint (CKPT).

      System Monitor (SMON).

      Process Monitor (PMON).

      Archiver (ARCn).

      Recoverer (RECO).

      Job Queue Processes (Jnnn).

      Dispatcher (Dnnn).

      Lock Manager Server (LMS).

      Queue Monitor (QMNn).

    Connecting to an Instance.

      Connection.

      Session.

    Exam Prep Questions.

    Need to Know More?

2. Getting Started with the Oracle Server.

    Administrative Tools.

    Oracle Universal Installer (OUI).

      OUI Interactive.

      OUI Noninteractive.

      Multiple Oracle Homes.

      Creating an Oracle Home.

    Oracle Database Configuration Assistant (DBCA).

    Optimal Flexible Architecture (OFA).

    Password File Authentication.

      Locked and Expired Default Accounts.

      Elimination of Default Passwords.

      Password File.

    DBA Tools.

      SQLPlus.

      Oracle Enterprise Manager (OEM).

      OMS Repository.

    Exam Prep Questions.

3. Creating a Database.

    Prerequisites for Database Creation.

    Planning Database File Locations.

    Creating the Database.

    Oracle Database Configuration Assistant.

      Create a Database.

      Database Configuration Options.

    Create a Database Manually.

      Step 1: Decide on Your Instance's System Identifier (SID).

      Step 2: Create the Initialization Parameter File.

      Step 3: Connect to and Start the Instance.

      Step 4: Issue the CREATE DATABASE Statement.

      Step 5: Run Scripts to Build Data Dictionary Views.

      Step 6: Run Scripts to Install Additional Options.

      Step 7: Create a Server Parameter File.

      Step 8: Back Up the Database.

      Step 9: Create Additional Tablespaces.

    What to Do If Database Creation Fails.

    Exam Prep Questions.

    Need to Know More?

4. Managing an Instance.

    Initialization Parameter Files.

      Working with PFILE.

      Working with SPFILE.

      Specifying Often-Used Initialization Parameters.

    Oracle Managed File (OMF) System.

      Initialization File Parameters for OMF.

      OMF Naming Conventions.

    Database Startup.

      STARTUP NOMOUNT.

      STARTUP MOUNT.

      STARTUP OPEN.

    Working with the STARTUP Command.

      Automating Startup.

      Altering the Database State.

      Setting Restricted Mode.

      Setting Read-Only Mode.

    Shutting Down the Database.

      SHUTDOWN NORMAL.

      SHUTDOWN TRANSACTIONAL.

      SHUTDOWN IMMEDIATE.

      SHUTDOWN ABORT.

      Shutting Down and Starting Up Using OEM.

    Managing the Instance by Monitoring the Diagnostic Files.

      Alert Log.

      Background Trace Files.

      User Trace Files.

    Exam Prep Questions.

    Need to Know More?

5. Data Dictionary Content and Usage.

    Oracle's Data Dictionary.

    How the Data Dictionary Is Used.

      Program Units.

      Views on Base Tables.

    Accessing Data Dictionary and Dynamic Performance Views.

      Fast Access of the Data Dictionary.

    Creating the Data Dictionary.

      Running the Catalog Scripts.

      Running the catproc.sql Script.

    Common Views and Their Uses.

    Exam Prep Questions.

    Need to Know More?

6. Storage Structure and Relationships.

    Segments.

      Table Segments.

      Table Partition Segment.

      Cluster Segments.

      Index Segments.

      Index-Organized Table.

      Index Partition.

      Undo Segment.

      Temporary Segment.

      LOB Segment.

      Nested Table Segment.

      Bootstrap Segment.

    Extents.

    Database Data Blocks.

      Automatic Segment Space Management.

      Manual Segment Space Management.

      Data Block Layout.

    Block Utilization Parameter.

      INITRANS.

      MAXTRANS.

      PCTFREE.

      PCTUSED.

    Storage Structure Information in the Data Dictionary.

      DBA_TABLESPACES.

      DBA_DATA_FILES.

      DBA_SEGMENTS.

      DBA_EXTENTS.

      DBA_FREE_SPACE.

    Exam Prep Questions.

    Need to Know More?

7. Globalization Support.

    Globalization Support.

    Character Sets and Encoding Schemes.

      Single-Byte Character Sets.

      Varying-Width Multibyte Character Sets.

      Fixed-Width Multibyte Character Sets.

      Unicode Character Set.

    Database Character Set.

    National Character Set.

      Should You Use Unicode?

    Language-Dependent Behavior Specification.

      Initialization Parameters.

      NLS_LANGUAGE.

      NLS_TERRITORY.

    Setting Environment Variables.

      Working with NLS_LANG.

      Using ALTER SESSION to Set Environment Variables.

    Linguistic Sorting.

      Binary Sorting.

      Monolingual Sorting.

      Multilingual Sorting.

    Language-Dependent Application Behavior.

      Linguistic Index Support.

      Importing and Loading Data Using NLS.

    Globalization Support Information in the Data Dictionary.

      V$NLS_PARAMETERS.

    Globalization Support Utilities.

      Oracle Locale Builder.

      Character Set Scanner.

    Exam Prep Questions.

8. Maintaining Control Files.

    What Are Control Files?

      Uses of the Control Files.

      Contents of the Control Files.

    Multiplexing Control Files.

      Multiplexing Using SPFILE.

      Multiplexing Using PFILE.

    The CREATE CONTROLFILE Command.

      CREATE CONTROLFILE Parameters and Options.

      CREATE CONTROLFILE Troubleshooting.

    Dropping Control Files.

    Backing Up the Control File.

    Obtaining Control File Information.

    Control File-Related Parameters.

    Managing the Control File with OMF.

    Exam Prep Questions.

9. Maintaining Redo Log Files.

    Purpose of Online Redo Log Files.

    Structure of Online Redo Log Files.

      Online Redo Log Groups.

      Online Redo Log Members.

      Creating the Initial Redo Log Files.

      How Redo Logs Work.

    Control Log Switches and Checkpoints.

      Log Switches.

      Checkpoints.

      Force Log Switch.

      Force Checkpoint.

    Multiplexing and Maintaining Online Redo Log Files.

      Adding a Log File Member to a Group.

      Optimum Number of Online Redo Log Files.

      Location, Location, Location.

      Sizing the Redo Log Files.

      Dropping Redo Log Groups.

      Clearing Online Redo Logs.

      Relocating and Renaming Online Redo Logs.

    Using OEM to Maintain Redo Log Groups and Members.

      Adding Redo Log Groups.

      Dropping Redo Log Groups.

      Relocating and Renaming Redo Log Members.

    Managing Redo Log Files with OMF.

    Data Dictionary Redo Information.

    Archived Redo Log Files.

      NOARCHIVELOG Mode.

      ARCHIVELOG Mode.

      LOG_ARCHIVE_START.

      Archiver Information in the Data Dictionary.

      Obtaining Archiver Information from OEM.

    Exam Prep Questions.

10. Managing Tablespaces and Data Files.

    Logical Structure of the Database.

      Managing Tablespace Status.

    Types of Tablespaces.

      Undo Tablespace.

      Temporary Tablespaces.

      Default Temporary Tablespaces.

    Creating Tablespaces.

     Dropping Tablespaces.

    Space Management in Tablespaces.

      Locally Managed Tablespaces.

      Dictionary Managed Tablespaces.

    Changing the Size of the Tablespace.

      Resizing a Tablespace.

      Moving Data Files with ALTER TABLESPACE.

       Moving Data Files with ALTER DATABASE.

    Manipulating Tablespaces and Data Files with OEM.

      Changing the Storage Settings.

      Creating a Temporary Tablespace.

      Bringing a Tablespace Offline or Online.

      Dropping a Tablespace.

      Enabling or Disabling AUTOEXTEND.

      Adding a Data File.

      Creating a Tablespace.

    Implementing Oracle Managed Files (OMF).

    Data Dictionary Tablespace and Data File Information.

    Exam Prep Questions.

    Need to Know More?

11. Managing Undo Data.

    Logical Structure of Undo Segments in the Database.

    Purpose of Undo Data.

      Transaction Rollback.

      Transaction Recovery.

      Read Consistency.

      Transaction Read Consistency.

    Types of Undo Data.

      SYSTEM.

      Non-SYSTEM.

      Deferred.

    Implementing Automatic Undo Management.

      Altering the Undo Tablespace.

      Switching Undo Tablespaces.

      Dropping an Undo Tablespace.

      Automatic Undo Management Parameters.

      Sizing the Undo Tablespace.

      Undo Quota.

    Data Dictionary Information on Undo.

    Exam Prep Questions.

12. Managing Tables.

    Methods of Storing Data.

      Regular Tables.

      Partitioned Tables.

      Index-Organized Tables.

      Cluster Tables.

    Oracle Data Types.

      User-Defined Data Types.

      Built-In Data Types.

      Collection Data.

      Relationship Data Types.

      Extended Versus a Restricted ROWID.

      Restricted ROWID.

      Extended ROWID.

    Structure of a Row.

    Creating Regular and Temporary Tables.

      Temporary Table Creation.

    External Tables.

    Managing Storage Structures.

      Manually Allocating Extents.

    Regular Table Operations.

      Reorganizing Tables.

      Redefining Tables.

      Moving Tables.

      Truncating Tables.

      Dropping Tables.

    Column Operations.

      Dropping a Column.

      Unused Columns.

      Adding Columns.

      Altering Columns.

    Data Dictionary Information on Tables.

    Exam Prep Questions.

13. Managing Indexes.

    Different Types of Indexes and Their Uses.

      Logical Index Classifications.

      Physical Index Classifications.

    Creating Indexes.

Criteria for Creating an Index.

Creating a B-Tree Index.

Using OEM to Create a B-Tree Index.

Creating a Bitmap Index.

    Changing Storage Parameters for Indexes.

    Reorganizing Indexes.

      Rebuilding Indexes.

      Coalescing Indexes.

      Validating Indexes.

      Drop Indexes.

    Index Information in the Data Dictionary.

    Exam Prep Questions.

14. Maintaining Data Integrity.

    Defining Data Integrity.

      Application Code.

      Database Triggers.

      Integrity Constraints.

      Guidelines for Using Constraints.

    Implementing Data Integrity Constraints.

      Foreign Keys.

       Defining Constraints at Table Creation Time.

      Table Constraints.

    Maintaining Integrity Constraints.

      Enabling Constraints.

      Using the EXCEPTIONS Table.

    Constraint Information in the Data Dictionary.

    Exam Prep Questions.

15. Managing Users.

    Creating New Users.

      Database Authentication.

      External Authentication.

      User Account Defaults.

    Altering Users.

    Dropping Users.

    User Information in the Data Dictionary.

    Exam Prep Questions.

16. Managing Password Security and Resources.

    Manage Passwords Using Profiles.

      DEFAULT Profile.

      Profile Usage.

      Passwords.

    Administering Profiles.

      Creating Profiles.

      Assigning Profiles.

      Altering Profiles.

      Dropping Profiles.

    Controlling Resources Using Profiles.

    Managing Resource Plans and Consumer Groups.

      Oracle Database Resource Manager.

      Enabling Database Resource Manager.

    Data Dictionary Information About Profiles, Password, Management, and Resources.

    Exam Prep Questions.

17. Managing Privileges.

    System Privileges.

      Special System Roles.

    Object Privileges.

    Auditing Capabilities.

      Moving the Audit Trail Out of the SYSTEM Tablespace.

      Auditing Categories.

      Enabling and Disabling Auditing.

      Audit Options.

      Statement Execution.

      Audit Data Generation.

      Reviewing Audit Information.

      Fine-Grained Auditing.

    Data Dictionary Information.

    Exam Prep Questions.

18. Managing Roles.

    Creating and Modifying Roles.

      Granting Privileges to Roles.

      Application Roles.

    Granting Roles.

      Default Roles.

    Controlling Availability of Roles.

    Removing Roles.

    Using Predefined Roles.

      Enabling and Disabling Roles.

    Role Creation Guidelines.

      Using Passwords with Roles.

    Role Information in the Data Dictionary.

    Exam Prep Questions.

19. Practice Exam 1.

20. Exam 1 Answers.

21. Practice Exam 2.

22. Exam 2 Answers.

Appendix A. CD Contents and Installation Instructions.

    Multiple Test Modes.

      Study Mode.

      Certification Mode.

      Custom Mode.

      Adaptive Mode.

      Missed Question Mode.

      Non-Duplicate Mode.

    Question Types.

    Random Questions and Order of Answers.

    Detailed Explanations of Correct and Incorrect Answers.

    Attention to Exam Objectives.

    Installing the CD.

      Creating a Shortcut to the MeasureUp Practice Tests.

    Technical Support.

Appendix B. Glossary.

Appendix C. Need to Know More?

    Part 1: Oracle Architectural Components.

    Part 2: Getting Started with the Oracle Server.

    Part 3: Creating a Database.

    Part 4: Managing an Instance.

    Part 5: Data Dictionary Content and Usage.

    Part 6: Storage Structure and Relationships.

    Part 7: Globalization Support.

    Part 8: Maintaining Control Files.

    Part 9: Maintaining Redo Log Files.

    Part 10: Managing Tablespaces and Data Files.

    Part 11: Managing Undo Data.

    Part 12: Managing Tables.

    Part 13: Managing Indexes.

     Part 14: Maintaining Data Integrity.

    Part 15: Managing Users.

    Part 16: Managing Password Security and Resources.

    Part 17: Managing Privileges.

    Part 18: Managing Roles.

Index.