Oracle Combo (PL/SQL & Administration) Course

Course Combo Refund Policy: Once payment is received, there are no refunds on instructor-led course bundles, due to a discount being given. However, you may reschedule any of the included classes if needed.

Oracle SQL & PL/SQL (Days 1 - 5)

Section 1: An Introduction to SQL

Chapter 1 An introduction to relational databases and SQL
An introduction to client/server systems
The hardware components of a client/server system
The software components of a client/server system
Other client/server architectures
An introduction to the relational database model
How a table is organized
How tables are related
How columns in a table are defined
An introduction to SQL and SQL-based systems
A brief history of SQL
A comparison of Oracle, DB2, Microsoft SQL Server, and MySQL
The SQL statements
An introduction to the SQL statements
How to work with database objects
How to query a single table
How to join data from two or more tables
How to add, update, and delete data in a table
How to work with views
SQL coding guidelines
An introduction to PL/SQL
How to work with stored procedures
How to work with user-defined functions
How to work with triggers
How to use SQL from an application program
Three data access models
Java code that retrieves data from an Oracle database

Chapter 2 How to use Oracle SQL Developer and other tools
How to work with an Oracle database
How to start and stop the database service
How to use the Database Home Page
How to use SQL*Plus
How to use SQL Developer to work with a database
How to create a database connection
How to export or import database connections
How to navigate through the database objects
How to view the column definitions for a table
How to view the data for a table
How to edit the column definitions
How to use SQL Developer to run SQL statements
How to enter and execute a SQL statement
How to work with the Snippets window
How to handle syntax errors
How to open and save SQL statements
How to enter and execute a SQL script
How to use the SQL Reference manual
How to view the manual
How to look up information

 

Section 2 The essential SQL skills

Chapter 3 How to retrieve data from a single table
An introduction to the SELECT statement
The basic syntax of the SELECT statement
SELECT statement examples
How to code the SELECT clause
How to code column specifications
How to name the columns in a result set
How to code string expressions
How to code arithmetic expressions
How to use scalar functions
How to use the Dual table
How to use the DISTINCT keyword to eliminate duplicate rows
How to use the ROWNUM pseudo column to limit the number of rows
How to code the WHERE clause
How to use the comparison operators
How to use the AND, OR, and NOT logical operators
How to use the IN operator
How to use the BETWEEN operator
How to use the LIKE operator
How to use the IS NULL condition
How to code the ORDER BY clause
How to sort a result set by a column name
How to sort a result set by an alias, an expression, or a column number
How to code the row limiting clause
How to limit the number of rows
How to return a range of rows

Chapter 4 How to retrieve data from two or more tables
How to work with inner joins
How to code an inner join
When and how to use table aliases
How to work with tables from different schemas
How to use compound join conditions
How to use a self-join
Inner joins that join more than two tables
How to use the implicit inner join syntax
How to work with outer joins
How to code an outer join
Outer join examples
Outer joins that join more than two tables
How to use the implicit outer join syntax
Other skills for working with joins
How to combine inner and outer joins
How to join tables with the USING keyword
How to join tables with the NATURAL keyword
How to use cross joins
How to work with unions
The syntax of a union
Unions that combine data from different tables
Unions that combine data from the same table
How to use the MINUS and INTERSECT operators

Chapter 5 How to code summary queries
How to work with aggregate functions
How to code aggregate functions
Queries that use aggregate functions
How to group and summarize data
How to code the GROUP BY and HAVING clauses
Queries that use the GROUP BY and HAVING clauses
How the HAVING clause compares to the WHERE clause
How to code complex search conditions
How to summarize data using Oracle extensions
How to use the ROLLUP operator
How to use the CUBE operator

Chapter 6 How to code subqueries
An introduction to subqueries
How to use subqueries
How subqueries compare to joins
How to code subqueries in search conditions
How to use subqueries with the IN operator
How to compare the result of a subquery with an expression
How to use the ALL keyword
How to use the ANY and SOME keywords
How to code correlated subqueries
How to use the EXISTS operator
Other ways to use subqueries
How to code subqueries in the FROM clause
How to code subqueries in the SELECT clause
Guidelines for working with complex queries
A complex query that uses subqueries
A procedure for building complex queries
Two more skills for working with subqueries
How to code a subquery factoring clause
How to code a hierarchical query

Chapter 7 How to insert, update, and delete data
How to create test tables
How to create the tables for this book
How to create a copy of a table
How to commit and rollback changes
How to commit changes
How to rollback changes
How to insert new rows
How to insert a single row
How to insert default values and null values
How to use a subquery to insert multiple rows
How to update existing rows
How to update rows
How to use a subquery in an UPDATE statement
How to delete existing rows
How to delete rows
How to use a subquery in a DELETE statement

Chapter 8 How to work with data types and functions
The built-in data types
Data type overview
The character data types
The numeric data types
The temporal data types
The large object data types
How to convert data from one type to another
How to convert characters, numbers, and dates
Common number format elements
Common date/time format elements
How to convert characters to and from their numeric codes
How to work with character data
How to use the common character functions
How to parse a string
How to sort a string in numerical sequence
How to sort mixed-case columns in alphabetical sequence
How to work with numeric data
How to use the common numeric functions
How to search for floating-point numbers
How to work with date/time data
How to use the common date/time functions
How to parse dates and times
How to perform a date search
How to perform a time search
Other functions you should know about
How to use the CASE function
How to use the COALESCE, NVL, and NVL2 functions
How to use the GROUPING function
How to use the ranking functions

 

Section 3 Database design and implementation

Chapter 9 How to design a database
How to design a data structure
The basic steps for designing a data structure
How to identify the data elements
How to subdivide the data elements
How to identify the tables and assign columns
How to identify the primary and foreign keys
How to enforce the relationships between tables
How normalization works
How to identify the columns to be indexed
How to normalize a data structure
The seven normal forms
How to apply the first normal form
How to apply the second normal form
How to apply the third normal form
When and how to denormalize a data structure

Chapter 10 How to create tables, indexes, and sequences
How to work with tables
How to create a table
How to code a primary key constraint
How to code a foreign key constraint
How to code a check constraint
How to alter the columns of a table
How to alter the constraints of a table
How to rename, truncate, and drop a table
How to work with indexes
How to create an index
How to drop an index
How to work with sequences
How to create a sequence
How to use a sequence
How to alter a sequence
How to drop a sequence
The script used to create the AP tables
An introduction to scripts
How the DDL statements work
How to automatically generate ID values
How to use a sequence as a default value
How to use the GENERATED clause
How to use SQL Developer
How to work with the columns of a table
How to work with the data of a table
How to work with the constraints of a table
How to work with indexes
How to work with sequences

Chapter 11 How to create views
An introduction to views
How views work
Benefits of using views
How to work with views
How to create a view
How to create an updatable view
How to create a read-only view
How to use the WITH CHECK OPTION clause
How to insert or delete rows through a view
How to alter or drop a view
How to use SQL Developer
How to get information about a view
How to drop a view
How to alter or create a view

Chapter 12 How to manage database security
An introduction to database security
How to create an admin user
How to use SQL Developer to view database objects for a schema
How to create end users
How to use SQL*Plus to test end users
System privileges and object privileges
How to manage database security
How to create, alter, and drop users
How to create and drop roles
How to grant privileges
How to revoke privileges
How to work with private synonyms
How to work with public synonyms
A script that creates roles and users
How to view the privileges for users and roles
How to use SQL Developer
How to work with users
How to grant and revoke roles
How to grant and revoke system privileges

 

Section 4 The essential PL/SQL skills

Chapter 13 How to write PL/SQL code
An introduction to PL/SQL
An anonymous PL/SQL block in a script
A summary of statements for working with PL/SQL and scripts
How to code the basic PL/SQL statements
How to print data to an output window
How to declare and use variables
How to code IF statements
How to code CASE statements
How to code loops
How to use a cursor
How to use collections
How to handle exceptions
A list of predefined exceptions
Other scripting and PL/SQL skills
How to drop database objects without displaying errors
How to use bind variables
How to use substitution variables
How to use dynamic SQL
How to run a script from a command line

Chapter 14 How to manage transactions and locking
How to work with transactions
How to commit and rollback transactions
How to work with save points
How to work with concurrency and locking
How concurrency and locking are related
The four concurrency problems that locks can prevent
How to set the transaction isolation level
How to prevent deadlocks

Chapter 15 How to create stored procedures and functions
How to code stored procedures
How to create and call a stored procedure
How to code input and output parameters
How to code optional parameters
How to raise errors
A stored procedure that inserts a row
A stored procedure that drops a table
How to drop a stored procedure
How to code user-defined functions
How to create and call a function
A function that calculates balance due
How to drop a function
How to work with packages
How to create a package
How to drop a package
Advantages of packages
How to use SQL Developer
How to view and drop procedures, functions, and packages
How to edit and compile procedures and functions
How to grant and revoke privileges
How to debug procedures and functions

Chapter 16 How to create triggers
How to work with triggers
How to create a BEFORE trigger for a table
How to use a trigger to enforce data consistency
How to use a trigger to work with a sequence
How to create an AFTER trigger for a table
How to use an INSTEAD OF trigger for a view
How to use a trigger to work with DDL statements
How to use a trigger to work with database events
How to enable, disable, rename, or drop a trigger
Other skills for working with triggers
How to code a compound trigger
A trigger that causes the mutating-table error
How to solve the mutating-table problem
How to use SQL Developer
How to view, rename, or drop a trigger
How to enable or disable a trigger
How to edit a trigger
How to debug a trigger

 

Section 5 Advanced data types

Chapter 17 How to work with timestamps and intervals
An introduction to time zones
Database time zone vs. session time zone
How to change the default date format
How to change the default time zone
Session settings vs. database settings
How to use functions to work with time zones
How to work with timestamps
An introduction to the TIMESTAMP types
How to work with the TIMESTAMP type
How to work with the TIMESTAMP WITH LOCAL TIME ZONE type
How to work with the TIMESTAMP WITH TIME ZONE type
Common format elements for timestamps
How to use functions to work with timestamps
How to work with intervals
An introduction to the INTERVAL types
How to work with the INTERVAL YEAR TO MONTH type
How to work with the INTERVAL DAY TO SECOND type
How to use functions to work with intervals

Chapter 18 How to work with large objects
An introduction to large objects
The LOB types
APIs for working with LOBs
How to use SQL to work with large objects
How to work with CLOBs
How to work with NCLOBs
How to work with BLOBs
How to work with BFILEs
How to specify LOB storage options
How to migrate to the new LOB types
How to use Java to work with large objects
The main method for the sample application
How to write an image to a table
How to read an image from a table
A utility class for working with databases
How to use PL/SQL to work with large objects
The methods of the DBMS_LOB package
An example that uses the DBMS_LOB package

 

Oracle Database Administration (Days 6 - 10)

Section 1 Oracle Database 12c: SQL Fundamentals 1

Chapter 1 Introducing Oracle Database 12c RDBMS 3
Relational Database Management Systems 4
Characteristics of a Relational Database 5
Oracle’s Implementation of RDBMS and ORDBMS 8
The Oracle Database 12c 9
Oracle Database 12c Implementations 9
Connecting to Oracle Database 13
Database Management Tools 14
Oracle Database 12c in the Cloud 19
Familiarizing SQL*Plus 19
Summary 30
Exam Essentials 31
Review Questions 32

Chapter 2 Introducing SQL 35
SQL Fundamentals 36
Oracle Datatypes 38
Operators and Literals 42
Writing Simple Queries 46
Using the SELECT Statement 46
Filtering Rows 51
Sorting Rows 62
Sorting NULLs 64
Limiting Rows 67
Using Expressions 68
Accepting Values at Runtime 72
Using Ampersand Substitution Variables 73
Saving a Variable for a Session 75
Using Positional Notation for Variables 77
Summary 78
Exam Essentials 78
Review Questions 80

Chapter 3 Using Single-Row Functions 89
Single-Row Function Fundamentals 90
Functions for NULL Handling 91
Using Single-Row Character Functions 95
Character Function Overview 95
Character Function Descriptions 96
Using Single-Row Numeric Functions 108
Numeric Function Overview 108
Numeric Function Descriptions 109
Using Single-Row Date Functions 119
Date Format Conversion 119
Date Function Overview 120
Date Function Descriptions 121
Using Single-Row Conversion Functions 131
Conversion Function Overview 131
Conversion Function Descriptions 133
Using Other Single-Row Functions 154
Miscellaneous Function Overview 154
Miscellaneous Function Descriptions 155
Summary 167
Exam Essentials 168
Review Questions 169

Chapter 4 Using Group Functions 175
Group Function Fundamentals 176
Utilizing Aggregate Functions 177
Grouping Data with GROUP BY 178
Group Function Overview 182
Group Function Descriptions: Part 1 184
Group Function Descriptions: Part 2 194
Limiting Grouped Data with HAVING 207
Creating Superaggregates with CUBE and ROLLUP 208
Nesting Functions 215
Summary 218
Exam Essentials 218
Review Questions 219

Chapter 5 Using Joins and Subqueries 227
Writing Multiple-Table Queries 228
Inner Joins 229
Cartesian Joins 239
Outer Joins 240
Other Multiple-Table Queries 245
Using Set Operators 247
The UNION Operator 248
The UNION ALL Operator 249
The INTERSECT Operator 250
The MINUS Operator 250
Putting It All Together 250
Using Subqueries 252
Single-Row Subqueries 253
Multiple-Row Subqueries 254
Subquery Returns No Rows 256
Correlated Subqueries 258
Scalar Subqueries 259
Multiple-Column Subqueries 267
Subqueries in Other DML Statements 268
Summary 269
Exam Essentials 270
Review Questions 271

Chapter 6 Manipulating Data 281
Using DML Statements 282
Inserting Rows into a Table 283
Updating Rows in a Table 289
Deleting Rows from a Table 294
Merging Rows 297
Understanding Transaction Control 299
Savepoints and Partial Rollbacks 302
Data Visibility 304
Summary 306
Exam Essentials 307
Review Questions 308

Chapter 7 Creating Tables and Constraints 319
Database Objects Overview 320
Schema Objects 322
Using Sequences 324
Built-in Datatypes 325
Character Datatypes 325
Numeric Datatypes 329
Date and Time Datatypes 330
Date Arithmetic 334
Binary Datatypes 336
Row ID Datatypes 337
Creating Tables 338
Naming Tables and Columns 339
Specifying Default Values for Columns 341
Adding Comments 348
Creating a Table from Another Table 348
Modifying Tables 350
Adding Columns 350
Modifying Columns 353
Renaming Columns 354
Dropping Columns 354
Hiding Columns from Table 356
Dropping Tables 357
Renaming Tables 357
Making Tables Read-Only 358
Managing Constraints 361
Creating Constraints 362
Dropping Constraints 367
Enabling and Disabling Constraints 367
Deferring Constraint Checks 369
Summary 373
Exam Essentials 374
Review Questions 375

 

Section 2 Oracle Database 12c: Installation and Administration 381

Chapter 8 Introducing Oracle Database 12c Components and Architecture 383
Oracle Database Fundamentals 385
Relational Databases 385
Oracle Database 12c Objects 386
Interacting with Oracle Database 12c 388
Oracle Database 12c Architecture 391
User and Server Processes 394
The Oracle Instance 396
Oracle Storage Structures 410
Summary 424
Exam Essentials 425
Review Questions 426

Chapter 9 Creating and Operating Oracle Database 12c 431
Oracle Database 12c Software Installation 432
Planning the Oracle Database 12c Software Install 433
Using the Oracle Universal Installer 440
Using DBCA to Create an Oracle 12c Database 447
Invoking the Database Configuration Assistant 447
Configuring an Oracle Database Using the DBCA 472
Deleting an Oracle Database Using the DBCA 472
Managing Database Templates Using the DBCA 473
Working with Oracle Database Metadata 475
Data Dictionary Views 476
Dynamic Performance Views 477
Managing Initialization-Parameter Files 479
Locating the Default Parameter File 484
Modifying Initialization-Parameter Values 485
Starting Up and Shutting Down an Oracle Instance 490
Starting Up an Oracle Database 12c Instance 491
Shutting Down an Oracle Database 12c Instance 495
Monitoring the Database Alert Log 497
Summary 503
Exam Essentials 504
Review Questions 506

Chapter 10 Understanding Storage and Space Management 511
Understanding the Physical and Logical Storage 512
Contents of a Data Block 514
Managing Tablespaces 516
Identifying Default Tablespaces 516
Creating and Maintaining Tablespaces 516
Obtaining Tablespace Information 531
Managing Data Files 534
Performing Operations on Data Files 535
Using the Oracle Managed Files Feature 539
Querying Data File Information 544
Managing Space 547
Automatic Space Management Features 547
Monitoring Tablespace Free Space 551
Using Segment Advisor 554
Avoiding Out-of-Space Errors 559
Summary 562
Exam Essentials 563
Review Questions 565

Chapter 11 Managing Data Concurrency and Undo 569
Managing Data Changes Using DML 570
Understanding “Change” 571
Differentiating Undo and Redo 572
Configuring and Monitoring Undo 576
Managing Data Concurrency 584
Understanding Locks and Transactions 585
Maximizing Data Concurrency 587
Detecting and Resolving Lock Conflicts 590
Summary 595
Exam Essentials 595
Review Questions 597

Chapter 12 Understanding Oracle Network Architecture 603
Introducing Network Configurations 604
Single-Tier Architecture 605
Two-Tier Architecture 605
n-Tier Architecture 606
An Overview of Oracle Net Features 607
Connectivity 608
Manageability 608
Scalability 609
Security 611
Accessibility 613
Configuring Oracle Net on the Server 613
Understanding the Oracle Listener 614
Managing Oracle Listeners 618
Dynamically Registering Services 634
Oracle Net Logging and Tracing on the Server 635
Configuring Oracle Net for the Client 637
Client-Side Names Resolution Options 637
The Host Naming Method 638
The Oracle Easy Connect Method 639
The Local Naming Method 641
Troubleshooting Client-Side Connection Problems 646
An Overview of Oracle Shared Server 648
Dedicated Server vs. Shared Server 648
Advantages and Disadvantages of Shared Server 650
Oracle Shared Server Infrastructure 651
Configuring the Oracle Shared Server 653
Managing a Shared Server 658
Understanding Database Resident Connection Pooling 661
Configuring DRCP 662
Comparing Connection Architectures 663
Communicating Between Databases 666
Introduction to Database Links 666
Creating Database Links 667
Summary 669
Exam Essentials 670
Review Questions 672

Chapter 13 Implementing Security and Auditing 677
Creating and Managing User Accounts 678
Configuring Authentication 679
Assigning Tablespaces and Quotas 681
Assigning a Profile and Account Settings 684
Removing a User from the Database 686
Managing Default User Accounts 687
Granting and Revoking Privileges 687
Granting Object Privileges 688
Granting System Privileges 692
Role Privileges 701
Applying the Principle of Least Privilege 705
Controlling Resource Usage by Users 711
Implementing Password Security Features 714
Auditing Database Activity 718
Managing Statement Auditing 720
Managing Privilege Auditing 724
Managing Object Auditing 725
Using SQL Developer for Audit Management 727
Purging the Audit Trail 729
Managing Fine-Grained Auditing 732
Implementing Unified Auditing 735
Summary 745
Exam Essentials 746
Review Questions 747

Chapter 14 Maintaining the Database and Managing Performance 753
Proactive Database Maintenance 754
Managing Optimizer Statistics 755
Gathering Performance Statistics 775
Automatic Database Diagnostic Monitoring 782
The Advisory Framework 793
Monitoring Server-Generated Alerts 798
Understanding Automatic Diagnostic Repository 801
Managing Performance 806
Sources of Tuning Information 807
Tuning Memory 812
Automatic Shared Memory Management 813
Automatic SQL Execution Memory Management 815
Managing Memory Using EM Cloud Control 817
Summary 819
Exam Essentials 820
Review Questions 821

Chapter 15 Using Backup and Recovery 825
Understanding and Configuring Recovery Components 827
Understanding Control Files 827
Understanding Checkpoints 831
Understanding Redo Log Files 832
Understanding Archived Redo Log (ARCHIVELOG) Files 839
Understanding the Fast Recovery Area 844
Performing Backups 848
Understanding Backup Terminology 848
Backing Up the Control File 850
Backing Up the Database 851
Using RMAN to Perform Backups 852
Managing Backups 861
Understanding Types of Database Failures 864
Statement Failures 865
User-Process Failures 865
Network Failures 866
User-Error Failures 866
Instance Failures 867
Media Failures 868
Performing Recovery Operations 868
Understanding Instance Startup 868
Keeping an Instance from Failing 870
Recovering from Instance Failure 870
Tuning Instance Recovery 871
Recovering from User Errors 873
Recovering from the Loss of a Control File 881
Using the Data Recovery Advisor 883
Recovering from the Loss of a Redo Log File 884
Recovering from the Loss of a Non-System-Critical Data File 886
Recovering from the Loss of a System-Critical Data File 892
Table Recovery Using RMAN 893
Summary 894
Exam Essentials 895
Review Questions 897

Chapter 16 Controlling Resources and Jobs 903
Resource Management with the Resource Manager 904
Functions of the Resource Manager 904
Understanding Resource Manager Components 905
Configuring the Database Resource Manager 911
Accessing and Creating Resource Plans 916
Updating and Deleting Resources 924
Monitoring the Resource Manager 927
Task Automation with Oracle Scheduler 930
Scheduler Architecture 930
Using Oracle Scheduler 934
Relating Tasks with Job Chains 943
Scheduling Jobs on Remote Systems 947
Prioritizing Jobs with Oracle Scheduler 948
Summary 951
Exam Essentials 951
Review Questions 952

Chapter 17 Upgrading to Oracle Database 12c 957
Determining the Database Upgrade Method 958
Using Direct Upgrade 959
Migrating to Oracle Database 12c 961
Preparing for a Database Upgrade 963
Running the Pre-Upgrade Information Tool 964
Preparing the Oracle Home 974
Reducing Upgrade Downtime 975
Upgrading the Database 976
Using Database Upgrade Assistant 976
Performing a Manual Upgrade 987
Completing the Post-Upgrade Tasks 991
Migrating to Unified Auditing 994
Downgrading to an Earlier Release 995
Summary 997
Exam Essentials 997
Review Questions 999

Chapter 18 Using Grid Infrastructure and Data Movement Tools 1005
Tools for Moving Data 1006
Migrating Data Using Data Pump 1008
Data Pump Architecture 1009
Using Data Pump Clients 1012
Using the Data Pump Wizard 1033
Upgrading an 11g R2 Database to 12c
Using Full Transportable Export 1037
Loading Data with SQL*Loader 1039
Specifying SQL*Loader Command-Line Parameters 1040
Specifying Control File Options 1042
Populating External Tables 1046
Loading External Tables Using Data Pump 1046
Loading External Tables Using Loader 1048
Introducing Grid Infrastructure 1050
Installing Oracle Grid Infrastructure 1051
Managing Oracle ASM Storage 1061
Using Oracle Restart 1068
Summary 1076
Exam Essentials 1077
Review Questions 1078