Index
Please note that index links point to page beginnings from the print edition. Locations are approximate in e-readers, and you may need to page down one or more times after clicking a link to get to the indexed material.
& (ampersand character), 229
* (asterisk character), 205
(backslash symbol), 221
% (percentage symbol), 220
| (pipe symbol), 205
|| (double pipe symbol), 211
' ' (two single quotes), 211–212
_ (underscore character), 220
A
ACCEPT commands, 27
access permissions, 144
account status, 512
ACID test, 145–146
active database duplication, 822–825
auxiliary instance configuration, 823–824
backupsets used for, 825
network connectivity for, 824
RMAN DUPLICATE command for, 824–825
Active Directory, 71
ACTIVE redo log group, 742
active session pool method, 632
active sessions, 632
ad hoc queries, 231
adaptive execution plan, 607
ADDM (Automatic Database Diagnostic Monitor), 390, 577–578, 586
ADD_MONTHS function, 252–253
administration roles, 513
administrative server, 838
Adobe Flash Player plug-in, 414
ADR. See automatic diagnostic repository
ADR Command Interpreter (ADRCI) tool, 759–761
ADVISE FAILURE command, 769, 770
Data Recovery, 579–580, 769–771
Mean Time to Recover, 579
Segment, 493–494, 498, 580, 584
SQL Repair, 580
agents, Scheduler, 653
aggregate functions. See group functions
alert log, 399, 427, 431–432, 435, 761
Alert system, 580–584
condition monitoring by, 580–581
configuring alerts in, 583–584
notifications sent by, 581
setting thresholds in, 581–583
two-minute drill on, 586–587
types of alerts in, 581
aliases, 206
column, 210
table, 310
ALL keyword, 134
ALL operator, 351
ALL views, 886
ALL_OBJECTS view, 77
ALLOCATE CHANNEL command, 846
allocation units (AUs), 41, 46–47
ALTER INDEX command, 99
ALTER PLUGGABLE DATABASE command, 914–915
ALTER SEQUENCE command, 118
ALTER SYNONYM command, 114
ALTER SYSTEM command, 420, 919
ALTER SYSTEM KILL SESSION command, 171
ALTER USER command, 516
ALTER VIEW command, 112
alternative quote operator, 212
ambiguous column names, 310–311
AMM (Automatic Memory Management), 600–601, 602, 615
ampersand substitution, 229–234
column name, 231–232
DEFINE and UNDEFINE commands and, 232–233
double ampersand, 230–231, 232–234
exercise on using, 234
expression and text, 232
single ampersand, 229–230
two-minute drill on, 236
VERIFY command and, 233–234
AND operator, 96, 222–223, 225
ANSI compliance, 81
ANSI SQL join syntax, 309–310
ANY operator, 351
Application Express (APEX), 856
application programming interfaces (APIs), 575
architecture
Data Pump, 554–555
dedicated server, 446–447
distributed systems, 377
Flashback Database, 801–803
Grid Infrastructure, 40–41
multitenant container, 884–890
Resource Manager, 623–626
Scheduler, 646–647
shared server, 447–450
single-instance database, 374–377
tablespace, 926–927
two-minute drill on, 403
archival backups, 706–708
archive log files, 399, 672–673
archivelog mode, 672–673
backup possibilities in, 690–691
block media recovery in, 767
configuration of, 676–677, 679
exercise on enabling, 677–678
Flashback Database and, 803
archiver processes, 672–673
ARCn (Archiver process), 391, 672
arithmetic operators, 209–210
ascending indexes, 98
ascending sort order, 225–226
ASM. See Automatic Storage Management
ASM Filter Driver, 45
ASM_DISKSTRING parameter, 44
ASMLib kernel library, 45
atomicity, principle of, 145
AUDIT command, 530
Audit Vault, 530
enabling, 534–536
Fine Grained, 531–532
mandatory, 534
multitenant environment, 937–938
reasons for, 530
techniques for, 530–534
two-minute drill on, 539
unified, 533–534, 535–538, 875–876, 937
value-based, 532
AUDIT_TRAIL parameter, 530
external, 514–515
operating system, 513–514
password and password file, 513–514
authorization, 508
autocommit (implicit commit), 153
automated tasks. See autotasks
Automatic Database Diagnostic Monitor (ADDM), 390, 577–578, 586
automatic diagnostic repository (ADR), 756–762
alerts, problems, and incidents, 761–762
Command Interpreter tool, 759–761
directory structure, 756–759
two-minute drill on, 771–772
automatic locking, 166–167
Automatic Memory Management (AMM), 600–601, 602, 615
automatic shared memory management (ASMM), 599
Automatic Storage Management (ASM), 40–45
architecture, 41–43
converting to, 873
disks, 43–45
self test on, 52–54
automatic undo management, 154
Automatic Workload Repository (AWR), 574–577
exercise on monitoring, 576–577
managing the settings for, 575–576
snapshots created by, 574–576, 577
statistics gathering process, 574–575
tablespace monitoring, 492
two-minute drill on, 586
autotasks, 584–586
controlling, 585–586
overview of, 584
two-minute drill on, 587
auxiliary database, 822, 823–824
AVG function, 283
AWR snapshots
ADDM reports using, 577
managing settings for, 575–576
statistics gathering and, 574–575
See also Automatic Workload Repository
B
background processes, 374, 386–395
ARCn, 391
CKPT, 390
DBWn, 387–388
exercise on, 392–395
LGWR, 389
LREG, 391
MMAN, 390–391
MMNL, 390
MMON, 390
PMON, 386–387
RECO, 392
SMON, 386
two-minute drill on, 403
backup and recovery, 664–684
archivelog mode, 672–673, 676–678
checkpointing mechanism, 668–669
configuring a database for, 673–675
failure categories and, 664–665
Fast Recovery Area for, 665, 675–676
instance recovery, 666–668
multitenant, 938–940
online redo log files, 670–671
Oracle Data Guard for, 666
Oracle Secure Backup for, 666
Recovery Manager for, 665, 686–722
self-test questions/answers on, 679–684
two-minute drill on, 678–679
user- vs. server-managed, 686
See also backups; recovery; RMAN
BACKUP DURATION parameter, 844, 846
backups
archival, 706–708
backupset, 705–706
CDB and PDB, 938–939
disk, 687
duplication from, 825–826
encrypted, 834–837
multisection, 708–710
open, 687–688
Oracle Secure, 838–840
password file, 743
read-only tablespace, 706
server-managed, 686
table recovery from, 798–799
user-managed, 686
backupsets, 687
active duplication using, 825
archivelog mode, 691
creating backups of, 705–706
duplexed, 705
parallelizing, 844–845
basic parameters, 419–420
basic table compression, 491, 492
BETWEEN operator, 217, 218, 219, 321
BFILE data type, 82
big-endian platforms, 817
bitmap indexes, 95–97
black box, 242
BLOB data type, 82
corruption of, 765–769, 772–773
header and data areas in, 475
media recovery for, 766–769
Boolean operators, 214, 222–224, 225
AND operator, 222–223
NOT operator, 223–224
OR operator, 223
bootstrap, ASM, 42
buffer cache, 146, 379–380, 598, 943
buffers
memory, 602–604
C
cardinality estimates, 606
Cartesian products, 304, 307–308, 331–333, 335
case conversion functions, 243–245
exercise on using, 244–245
INITCAP, 244
LOWER, 243–244
UPPER, 244
CASE expression, 271–273
case sensitivity, 206–207
CDBs. See container databases
CHANGE command, 708
CHANGE FAILURE command, 769, 770
channels
tuning, 846
CHAR data type, 81
character manipulation functions, 245–248
CONCAT, 245
exercise on using, 255
INSTR, 247
LENGTH, 245–246
LPAD AND RPAD, 246
REPLACE, 248
SUBSTR, 248
TRIM, 247
character-based conditions, 215–216
character-to-date conversions, 256–257, 264–265
character-to-number conversions, 256, 265
check constraints, 103
check sums, 766
checkpointing mechanism, 668–669, 678
CKPT (Checkpoint Process), 390
client tools
SQL*Plus, 68–73
SQL Developer, 73–76
client-server paradigm, 442–443
client-side network configuration, 455–457
connectivity testing, 457
directory and external naming, 456–457
Easy Connect name resolution, 455
local naming name resolution, 455–456
CLOB data type, 82
cloning PDBs, 902–904
Cloud Control. See Enterprise Manager Cloud Control
clusters, 473
COALESCE function, 268–269
columns
aliases for, 210
altering in tables, 87
attributes as, 190
creating tables with, 84–85
grouping by multiple, 291–292
key, 101
mandatory, 102
not null and nullable, 213
pseudocolumns as, 785–786
qualifying ambiguous names for, 310–311
relational models and, 197–199
substituting names of, 231–232
command-line utilities
Oracle Secure Backup, 838
SQL*Plus, 410
COMMIT statement
transaction control using, 149–150
two-phase commits, 392
committed transactions, 789
common privileges, 932–933
common queue, 448–449
common roles, 933
common users, 930–931, 934–935
COMPACT keyword, 495
comparison operators, 217–222
equality and inequality, 217–219
multiple-row subquery, 348, 349
NULL comparison with IS NULL, 222
pattern comparison with LIKE, 220–222
range comparison with BETWEEN, 219
set comparison with IN, 219–220
comparison subqueries, 343–344
compatibility, database, 816–817
COMPATIBLE parameter, 816, 817
complete recovery, 724, 725–727, 744–745
of critical datafiles, 727
of noncritical datafiles, 725–726
complex views, 110–111
composite indexes, 98
composite inequality operators, 218
composite key unique constraints, 101
composite sorting, 227
compound queries
set operators and, 352, 353–361
compressed indexes, 97
compression
backup process using, 691, 710
storing data using, 491–492
CONCAT function, 245
concatenation operator, 211
condition monitoring, 580–581
conditional clauses, 217
conditional functions, 266–273
CASE expression, 271–273
COALESCE, 268–269
two-minute drill on, 274
CONFIGURE CHANNEL command, 846
CONNECT command, 425
connection pooling, 460–461
connectivity testing, Oracle Net, 457
consistency, principle of, 145
consistent shutdowns, 428–429
consolidated replay, 950–951
constraints, 100–107
check, 103
defining, 103–105
exercise on managing, 107
explanation of, 100
foreign key, 102–103
immediate vs. deferred, 106
primary key, 102
states of, 105–106
two-minute drill on, 121
types of, 101–103
unique, 101
container databases (CDBs), 561, 884–887
backing up, 938–939
Database Replay and, 949–951
DBCA for creating, 896–898
establishing connections to, 908–911
exercise on creating, 899–900
migrating pre-12.1 non CDB-databases to, 907–908
multitenant architecture and, 884–890
parameter changes for, 918–919
performance tuning in, 941–944
plugging non-CDBs into, 904–906
plugging unplugged PDBs into, 906–907
privileges granted in, 932–933
resource management for, 944–949
roles managed in, 933
root container in, 884, 888–889
seed container in, 884, 901–902
self-test questions/answers on, 893–894, 921–924, 955–959
service names and, 908–910
services created for, 910
shutting down, 916
SQL*Plus for creating, 898
starting up, 912–913
switching connections within, 910–911
tablespace management in, 927–928
two-minute drill on, 892–893, 920–921, 951–954
users managed in, 930–931
views available in, 886
See also multitenant environments; pluggable databases
control queue, 554–555
control table, 555
control transactions. See transactions
configuration process, 673–674, 679
restoration of, 737–738
SQL*Loader and, 549–550
CONTROL_FILES parameter, 16, 427
conversion functions, 255–265
character-to-date, 256–257, 264–265
date-to-character, 256, 260–264
explicit data type, 257
implicit data type, 255–257
number-to-character, 256, 258–260
overview of using, 257–258
self test on, 275–278
TO_CHAR, 258–264
TO_DATE, 264–265
TO_NUMBER, 265
two-minute drill on, 274
CONVERT command, 818
correlated subqueries, 349–350
corruption, block, 765–769, 772–773
COUNT function, 281–282
CPU resources
method for allocating, 628–631
ratio CPU method for, 631
CREATE CATALOG command, 698
CREATE DATABASE command, 17
CREATE DIRECTORY command, 546
CREATE PROFILE command, 528
CREATE ROLE command, 522
CREATE SCHEMA command, 200
CREATE SCRIPT command, 699
CREATE SEQUENCE command, 115–116
CREATE SYNONYM command, 113
CREATE TABLE command, 552
CREATE TABLESPACE command, 927
CREATE USER command, 200, 515–516, 930
CREATE VIEW command, 111–112
CreateDBCatalog.sql script, 28
CreateDBFiles.sql script, 28
CREATE_JOB procedure, 647–648, 650
CREATE_SCHEDULE procedure, 649–650
CREATE_WINDOW procedure, 655
credentials, database, 20
critical datafile recovery, 727
cross joins, 304, 307–308, 331–333, 335
CROSSCHECK commands, 710–711
crow’s foot notation, 190, 191, 194
cumulative incremental backups, 703–704
current group, 397
CURRENT redo log group, 742
CURRVAL pseudocolumn, 117
D
data
grouping of, 287–292
normalization of, 189, 192–193
performance monitoring, 593–594
table metadata and, 187
views for comprehending, 109
data concurrency management
exercise on working with, 172–173
locking mechanism and, 164–173
data definition language. See DDL commands
data dictionary, 16, 17, 186–187, 376, 400–401
data dictionary cache, 383
Data Guard, 377
Data Manipulation Language. See DML commands
data modeling, 190
architecture, 554–555
capabilities of, 556
command-line utilities, 556–557
exercise on using, 560–561
job suspension switch, 497
migrating data using, 860–862, 908
multitenant environments and, 561–565
tablespace transport, 557–560, 818
two-minute drill on, 566–567
Data Recovery Advisor (DRA), 579–580, 769–771
exercise on using, 771
identifying failures with, 769–770
implementing repairs with, 770
views available in, 770
data save, 548
data types
exercise on, 83
large object, 82
Data Vault, 868
database administrators (DBAs), 18
database buffer cache, 146, 379–380, 598, 943
Database Configuration Assistant (DBCA), 16–37
configuring database options with, 31–32
container database creation with, 896–898
creating a database with, 16–25
demonstration schema creation, 199, 203
exercises on using, 22–25, 29–30, 31, 32
generating database creation scripts with, 25–30
managing database design templates with, 30–31
overview of DBCA dialog steps, 19–22
self-test questions/answers on, 33–37
two-minute drill on using, 33
undo tablespace size and, 160
database connections
SQL*Plus, 71–73
SQL Developer, 75–76
Database Control Table Creation Wizard, 91
database creation scripts, 25–30
Database Express, 412–415
database listeners and, 410
lock contention identified in, 169–170
Performance Hub page, 594–595
prerequisites for using, 415
SHOW SQL button, 477
tablespace creation, 476–477
undo configuration in, 158
database links, 891
creating, 445–446
Database Express, 410, 413–414
registration process, 391, 446
showing the status of, 413
starting, 424–425
upgrading, 870–871
database management tools, 410–416
Database Express, 412–415
exercise on using, 415–416
SQL*Plus, 410–411
SYSDBA privileges, 411–412
two-minute drill on, 434
database objects. See objects
Database Resident Connection Pool (DRCP), 460–461, 463
database security. See security
database servers
components of, 377–378
dedicated, 446–447
shared, 446–452
steps in creating, 17
database software. See Oracle Database software
database templates
creating databases from, 19
managing using DBCA, 30–31
Database Upgrade Assistant (DBUA), 858, 870, 871–874, 878
databases
configuring for Oracle Restart, 60–62
creating with the DBCA, 16–25
date storage in, 251
duplicating, 821–827
instances vs., 16
links between, 461–462
migrating data between, 859, 860–862
options configuration for, 31–32
registering, 59
startup and shutdown, 425–430
steps in creating, 17–18
tempfile recovery, 744
upgrading, 868–882
See also Oracle Database
operating system blocks and, 472
renaming tablespaces and, 479–480
SWITCH command to recover, 732–734
date functions, 251–254
ADD_MONTHS, 252–253
LAST_DAY, 253
MONTHS_BETWEEN, 252
NEXT_DAY, 253
ROUND, 253–254
SYSDATE, 251
TRUNC, 254
date-based conditions, 216–217
dates
arithmetic for, 252
conversion functions for, 256–257, 260–265
database storage of, 251
date-to-character conversions, 256, 260–264
DBA role, 524
DBA views, 886
DBAs (database administrators), 18
DBA_AUTOTASK_CLIENT view, 585
DBA_DIRECTORIES view, 546
DBA_FLASHBACK_ARCHIVE view, 800
DBA_OBJECTS view, 77
DBA_OUTSTANDING_ALERTS view, 581
DBA_RESUMABLE view, 497
DBA_ROLES view, 524
DBA_SCHEDULER_JOBS view, 646
DBA_SEGMENTS view, 472
DBA_TABLESPACES view, 433
DB_BLOCK_CHECKING parameter, 766
DB_BLOCK_CHECKSUM parameter, 766
DB_BLOCK_SIZE parameter, 21, 22
DBCA. See Database Configuration Assistant
DBMS_ADVISOR package, 613–614
DBMS_AUTO_TASK_ADMIN package, 585
DBMS_FLASHBACK package, 782, 789
DBMS_HM package, 764–765
DBMS_RESOURCE_MANAGER_PRIVS package, 626–627
DBMS_RESOURCE_MANAGER package, 626, 628, 945
DBMS_SCHEDULER package, 647, 654
DBMS_SERVICE package, 910
DBMS_SQLTUNE package, 610–611
DBMS_XDB_CONFIG package, 653
DBUA (Database Upgrade Assistant), 858, 870, 871–874, 878
DBWn (Database Writer), 387–388, 668–669
DCL commands, 149
DDL commands
altering tables using, 87
data dictionary and, 401
locking mechanism and, 166–167
recording in log file, 432
transactions using, 149
TRUNCATE command, 141–142
DDL log, 432
decision support system (DSS), 552
dedicated server architecture, 446–447
DEFAULT clause, 85
DEFAULT profile, 528
default RMAN values, 692–693
DEFERRED constraints, 106
deferred segment creation, 488, 489
DEFINE command, 233
DELETE command, 139–141
exercise on using, 140–141
transactions using, 148
TRUNCATE command vs., 141
demonstration schemas, 199–204
descending indexes, 98
descending sort order, 226
DESCRIBE table command, 186–188
diagnosing errors, 756–775
automatic diagnostic repository for, 756–762
Data Recovery Advisor for, 769–770
and handling block corruption, 765–769
Oracle Health Monitor for, 762–765
self-test questions/answers on, 773–775
two-minute drill on, 771–773
DIAGNOSTIC_DEST parameter, 427, 431, 756–757
differential incremental backups, 703
digital shredding, 780
direct path loads, 548, 550, 555
directly attached storage (DAS) devices, 43, 44
directories
creating, 546–547
structure of, 5–6
directory naming, 456–457
directory objects, 546–547, 566
disk backups, 687
disk space requirement, 5
disks, ASM, 43–45
dispatchers, 448–449
DISPLAY variable, 18
DISTINCT clause, 205, 206, 214
distributed systems architectures, 377
distributed transactions, 392
DML commands, 132–144
DELETE, 139–141
failure of, 143–144
INSERT, 132–136
MERGE, 142–143
subqueries and, 346
two-minute drill on, 173
undo data and, 154–155
UPDATE, 136–139
dot notation
ambiguous column names and, 310–311
double ampersand substitution, 230–231, 232–234
DEFINE and UNDEFINE commands, 232–233
VERIFY command, 233–234
DRA. See Data Recovery Advisor
DROP INDEX command, 99
DROP SYNONYM command, 113
DROP TABLE command, 87–88, 792–793
DROP TABLESPACE command, 483
DROP VIEW command, 112
dropped tables
flashback technology for, 791–798
viewing in recycle bin, 795, 797
dropping tablespaces, 483
dual-mode encryption, 837
dump files, 555
duplexed backupsets, 705
DUPLICATE command, 824–825
duplicating databases, 821–827
active database for, 822–825
backups used for, 825–826
exercise on process of, 826–827
PDBs using RMAN, 940–941
reasons and uses for, 822
self-test questions/answers on, 829–832
two-minute drill on, 828–829
durability, principle of, 146
dynamic parameter files, 416–417
dynamic parameters, 417–419
dynamic performance views, 432–433, 435, 841
dynamic sampling, 606
dynamic server registration, 446
E
Easy Connect name resolution, 455
enabling
auditing, 534–536
roles, 524–525
encrypted backups, 834–837
dual-mode encryption for, 837
password encryption for, 837
transparent encryption for, 834–836
two-minute drill on, 847
enqueue mechanism, 166
Enterprise Manager (EM), 20
Database Express Performance Hub, 594–595
monitoring performance using, 592–595, 614
Enterprise Manager Cloud Control, 20, 581, 756, 761, 763, 942
Enterprise Manager Database Express, 20, 410
entities, 190–194
equality operator, 215, 217, 218
errors
block corruption, 765–769, 772–773
DML statement, 143–144
flashback technologies and, 780
preventing with views, 109
spelling or punctuation, 210
undo data, 156
See also failures
ESCAPE character, 221
EXCLUSIVE lock mode, 167
execution time controls, 633–634
EXISTS condition, 352
expdp command, 564
explicit data type conversion, 257
explicit index creation, 98
exporting
from existing to new PDBs, 563–564
full transportable importing and, 564–565
from non-CDBs to PDBs, 561–563
from PDBs to non-CDBs, 564
tablespaces using Data Pump, 557–561
expressions, 209
CASE, 271–273
column aliasing and, 210
exercise on constructing, 214
literal, 211
substituting, 232
extent maps, 41
management of, 483–484
external authentication, 514–515
external naming, 457
external tables, 552–554, 555, 566
extract-transform-load (ETL) cycle, 552
F
failures
block corruption, 765–769, 772–773
categories of, 664–665
login, 527
See also errors
fast intra-CDB links, 891
Fast Recovery Area (FRA), 665, 804
configuration of, 675–676, 678–679
flashback logs using, 803
fast switch, 732
feeder system, 552
FETCH clause, 228
FILESPERSET parameter, 845, 846
Fine Grained Auditing (FGA), 531–532
first normal form (1NF), 192
flash recovery area, 707
flashback buffer, 801–802
Flashback Data Archive (FBDA), 780, 799–801
Flashback Database, 778, 801–808
architecture, 801–803
configuring, 803–804
exercise on using, 807–808
method for using, 805–806
monitoring, 804–805
RMAN and, 807
SQL*Plus and, 806–807
description of using, 793–795
exercise on working with, 795–796
implementation of, 792–793
Flashback Query, 778–779, 781–791
basic use of, 781–783
table query, 783–785
transaction query, 163–164, 787–790
versions query, 785–787
flashback retention target, 803–804
Flashback Table, 779, 780, 783–785
flashback technologies, 778–814
data archive, 799–801
database flashback, 801–808
descriptive overview of, 778–780
dropped tables and, 791–798
querying data using, 781–791
self-test questions/answers on, 809–814
table queries, 783–785
transaction queries, 779, 787–790
two-minute drill on, 808–809
versions queries, 785–787
when to use, 780–781
Flashback Transaction, 779, 780, 781, 787–790
Flashback Versions Query, 785–787
FLOAT data type, 82
FOR UPDATE clause, 153
foreground processes, 374
foreign key constraints, 102–103
foreign keys, 190
nullable columns and, 213
referential integrity and, 194–197
format masks
date, 261–262
numeric, 259
format specifiers, 694–695
forward recovery process, 376
FRA. See Fast Recovery Area
FROM clause
SELECT statement and, 205
WHERE clause and, 215
full checkpoints, 669
FULL OUTER JOIN clause, 329
full table scan, 92–93
full transportable export/import, 564–565
fully qualified domain names (FQDNs), 20
function-based indexes, 98
functions
case conversion, 243–245
character manipulation, 245–248
conditional, 266–273
conversion, 255–265
date, 251–254
defining, 242
group, 280–287
multiple-row, 243
numeric, 248–251
single-row, 243
types of, 242–243
G
GoldenGate, Oracle, 377
granting
privileges, 517, 518–519, 521–522
roles, 522–523
Grid Infrastructure (GI), 40–64
architecture, 40–41
installation process, 45–51
launch process, 51
Linux installation, 48–50
Oracle Restart provided by, 56
self-test questions/answers on, 52–54
two-minute drill on ASM and, 51
Windows installation, 47–48
GROUP BY clause, 289–292
multiple attributes used by, 291
two-minute drill on, 297
group functions, 280–287
AVG, 283
COUNT, 281–282
definition of, 280–281
exercise on using, 287
LISTAGG, 286–287
MAX and MIN, 285
nested, 292
rules for, 287
self test on, 297–302
STDDEV, 284–285
SUM, 282–283
two-minute drill on, 296
VARIANCE, 284–285
grouping data, 287–296
based on multiple columns, 291–292
creating groups of data, 287–289
GROUP BY clause for, 289–292, 294, 296
HAVING clause for, 294–296
nested group functions and, 292
reporting purposes served by, 289
restricting group results, 293–294
self-test questions/answers on, 297–302
two-minute drill on, 297
group-level results, 293
H
hash clusters, 84
hash join technique, 93
HAVING clause, 294–296
exercise on using, 296
two-minute drill on, 297
WHERE clause and, 293–294
Health Monitor, 762–765
heap tables, 84
help facility, 59
hierarchical paradigm, 198–199
High Availability Services (HAS), 40, 50
HR demonstration schema, 199, 200–203
Hybrid Columnar Compression (HCC), 491, 492
Hypertext Transfer Protocol (HTTP), 412, 653
I
idle time, 634
if-then-else logic, 266, 270, 271
image copies, 730–736
fast recovery using, 731–736
recovery process for, 730
RMAN backups to, 687
strategy using, 730–731
IMMEDIATE constraints, 106
IMMEDIATE keyword, 916
immediate shutdown mode, 428
implicit commit, 153
implicit data type conversion, 255–257
implicit index creation, 98
importing
from existing into new PDBs, 563–564
full transportable exporting and, 564–565
into non-CDBs from PDBs, 564
into PDBs from non-CDBs, 561–563
tablespaces using Data Pump, 557–561
IN operator, 217, 219–220, 351
INACTIVE redo log group, 740–742
Incident Manager, 761
incidents, database, 761–762
incomplete recovery, 724, 727–730, 744–745
database flashback vs., 803
restore point creation, 727–728
server-managed, 728–729
inconsistent shutdowns, 429
incremental backups, 688, 701–704
cumulative, 703–704
differential, 703
exercise on using, 704
Level 0, 702
recovering using, 730–731
two-minute drill on, 711, 712–713
incremental checkpoints, 668–669
indentation of SQL statements, 208
index clusters, 84
index organized tables, 84
indexes, 92–100
bitmap, 95–97
B*Tree, 93–95
constraints requiring, 102
creating and using, 98–99
exercise on creating, 99–100
functions performed by, 92
modifying and dropping, 99
necessity of, 92–93
options applied to, 97–98
segments as, 473
shrink operation and, 494–496
two-minute drill on, 121
types of, 93–98
inequality operators, 217, 218–219
INITCAP function, 244
initialization parameter files, 28–29, 416–424
basic parameters and, 419–420
static and dynamic parameters and, 417–419
two-minute drill on, 434
initialization parameters, 21, 28–29, 422–424
inline views, 345
inner joins, 304, 305–307, 319, 325
inner queries. See subqueries
input datafiles, 549
INSERT command, 132–136
exercise on using, 135–136
SELECT statement and, 133, 134
SQL*Loader and, 548
transactions using, 148
installation process
Grid Infrastructure, 45–51
Oracle Database software, 7–12
Oracle Secure Backup, 838
SQL Developer, 73–74
installing Oracle Database software, 7–12
Linux platform for, 8–9, 10, 11–12
OUI inventory creation, 7–8
platform variations for, 10–12
prerequisite checks for, 6–7
response files for, 9
self test on, 12–14
silent install process, 9
two-minute drill on, 12
Windows platform for, 10–11
instances, 16
Automatic Storage Management, 40, 41–43
auxiliary database, 823–824
background processes for, 386–395
connecting to local, 443–444
container database, 911–912
failures related to, 664
memory structures of, 378–386
multitenant environment and, 941–943
recovery process for, 428, 666–668
shutdown process for, 428–429
startup process for, 426–428
INSTR function, 247
INTEGER data type, 82
International Organization for Standardization (ISO), 81, 352, 785
interprocess communication (IPC), 889
INTERSECT operator, 353, 358–359
INTERVAL DAY TO SECOND data type, 82
INTERVAL YEAR TO MONTH data type, 82
iSCSI devices, 44
ISO compliance, 81
isolation, principle of, 145–146
J
Java Database Connectivity (JDBC), 75
Java Runtime Environment (JRE), 73
JOB_QUEUE_PROCESSES parameter, 646
jobs
controlling by execution time, 633–634
creating in the Scheduler, 647–648
prioritizing in the Scheduler, 653–656
remote systems using, 653
self-contained, 650
JOIN ON clause, 307, 315–317, 319
exercise on using, 316–317
self-joins and, 321–322
JOIN USING clause, 307, 314–315, 319
joining, concept of, 188–189, 304
joins, 304–339
ambiguous column names and, 310–311
ANSI SQL syntax for, 309–310
JOIN ON clause, 307, 315–317, 319, 321–322
JOIN USING clause, 307, 314–315, 319
NATURAL JOIN clause, 306, 311–314, 319
nonequijoins as, 304, 305, 319–321
n-way or multiple, 318–319
Oracle syntax for, 308–309
self test on using, 335–339
two-minute drill on, 334–335
types of, 305–308
K
key columns, 101
L
large object data types, 82
LAST_DAY function, 253
LDAP connections, 75
LD_LIBRARY_PATH variable, 69
LEFT OUTER JOIN clause, 325–326
LENGTH function, 245–246
Level 0 incremental backups, 702
library cache, 382–383
Lightweight Directory Access Protocol (LDAP), 75, 873
LIKE operator, 217, 218, 220–222
limiting query results, 214–225
Boolean operators for, 222–224
comparison operators for, 217–222
precedence rules for, 224–225
two-minute drill on, 235
WHERE clause for, 214–225
links, database, 461–462
Linux OS
demonstration schema creation on, 203–204
installing Grid Infrastructure on, 48–50
installing Oracle Database on, 8–9, 10, 11–12
launching the DBCA on, 18
managing Oracle Restart on, 57
SQL*Plus on, 68–70
support for Oracle Database on, 4
LIST FAILURE command, 769, 770
LISTAGG function, 286–287
Listener Control utility, 452–454
listener.ora configuration file, 452
listeners. See database listeners
literal expressions, 211
little-endian platforms, 817
LOBs (large objects), 473
local area networks (LANs), 274
local database connections, 443–444
local naming name resolution, 455–456
local privileges, 932–933
local roles, 933
local users, 930–931
lock contention, 168–171
causes of, 168–169
deadlocks and, 171
detecting, 169–171
solving, 171
lockAccount.sql script, 28
locking mechanism, 164–173
automatic and manual locking, 166–167
conflict monitoring and resolution, 168–171
enqueue mechanism related to, 166
exercise on working with, 172–173
shared and exclusive locks, 165–166
two-minute drill on, 175
log buffer, 380–381, 422, 598, 599
log switch, 397
logical models, 190
logical operators. See Boolean operators
logical standby database, 666
logical storage structures, 400, 403–404, 470
logical unit numbers (LUNs), 44
logical volume manager (LVM), 41
logon triggers, 422
logs
Scheduler job, 654–655
LONG data type, 82
LONG RAW data type, 82
long-running queries, 162–163
LOWER function, 243–244
lowercase SQL statements, 206–207
LPAD function, 246
LREG (Listener Registration Process), 391, 446
lsnrctl utility, 413, 414, 452–454
M
machine names, 807
Manageability Monitor. See MMON
managing
AWR settings, 575–576
constraints, 107
database design templates, 30–31
extents, 483–484
memory, 596–602
optimizer statistics, 604–609
Oracle Restart, 56–57
passwords, 527–529
resources, 622–643
storage, 492–494
undo data, 156–157
mandatory auditing, 534
mandatory columns, 102
manual database upgrades, 874–875
manual locking, 167
manual software upgrades, 858
manual statistics gathering, 607–609
many-to-many relationships, 191, 194, 470
many-to-one relationships, 191, 194
master-detail relationship, 194, 325
MAXOPENFILES parameter, 845, 846
MAXPIECESIZE parameter, 846
MAX_STRING_SIZE parameter, 81
Mean Time to Recover (MTTR) Advisor, 579
media failure, 664
Media Management Layer (MML), 691
media server, 838
memory advisors, 578, 602–604, 615, 943–944
memory broker, 601
memory buffers, 602–604
memory management, 596–602
Automatic, 600–601
exercise on setting, 601–602
memory advisors for, 578, 602–604
multitenant, 941–942
PGA, 596–598
SGA, 598–600
Memory Manager (MMAN), 390–391, 601
memory structures, 378–386
database buffer cache, 379–380
exercise on investigating, 385–386
Java pool, 384
large pool, 384
log buffer, 380–381
shared pool, 382–384
streams pool, 384–385
two-minute drill on, 403
memory target advisor, 603
MEMORY_MAX_TARGET parameter, 600
MEMORY_TARGET parameter, 600
MERGE command, 142–143
metadata links, 934
metrics vs. statistics, 593
Microsoft SQL Server, 199
Microsoft Windows. See Windows OS
migration
row, 491
MIN function, 285
mirroring, RAID, 44
MMAN (Memory Manager), 390–391, 601
MMNL (Manageability Monitor Light), 390
MMON (Manageability Monitor), 390
alerts raised by, 581
AWR statistics and, 492, 574–575
memory broker and, 601
MOD function, 250–251
monitoring
condition, 580–581
conflict, 168–171
Flashback Database, 804–805
multitenant environment, 941–944
Resource Manager, 637–638
RMAN sessions/jobs, 841–843
tablespace, 492
undo data, 156–161
MONTHS_BETWEEN function, 252
moving data, 546, 552–554, 556–565
Data Pump for, 556–565
external tables for, 552–554
self-test questions/answers on, 567–571
two-minute drill on, 565–567
multiple-column grouping, 291–292
multiple-row functions, 243
See also group functions
multiple-row subqueries, 348–349
comparison operators for, 348, 349
examples of writing, 351–352
multiplexing, RMAN, 845
multisection backups, 708–710
multitenant environments, 884–890, 896–924, 926–959
ALTER SYSTEM command used in, 919
architecture overview for, 884–890
auditing in, 937–938
automating PDB startup in, 916–917
backup and recovery in, 938–940
changing PDB status in, 918
communications between containers in, 891–892
concepts pertaining to, 884–887
creating CDBs and PDBs in, 896–907
Data Pump used in, 561–565
Data Recovery Advisor used in, 938, 953
Database Replay used in, 949–951
establishing connections in, 908–911
migrating pre-12.1 databases in, 907–908
opening/closing PDBs in, 914–916
parameter value changes in, 918–919
performance monitoring/tuning in, 941–944
reasons for setting up, 887–888
resource management in, 944–949
self-test questions/answers on, 893–894, 920–921, 955–959
shutting down CDBs in, 916
space management in, 926–930
SQL*Loader used in, 565
starting up CDBs in, 912–913
two-minute drill on, 892–893, 920–921, 951–954
users, roles, and privileges in, 930–936
See also container databases; pluggable databases
multithreaded model, 386
N
name resolution, 444
Easy Connect, 455
local naming, 455–456
utility for testing, 457
namespaces, object, 79–80
naming/renaming
schema objects, 78–79
tables with SQL*Plus, 792
tablespaces, 479–480
national language support (NLS) parameters, 258
NATURAL JOIN clause, 306, 311–314, 319
natural joins, 306, 307, 315, 319, 334
NCLOB data type, 82
nested functions, 242
group functions, 292
single-row functions, 265–266
nested loop join technique, 93
nested subqueries, 342
nested tables, 473
Net Configuration Assistant, 452–455
Listener Control utility, 452–454
TNS_ADMIN environment variable, 454–455
network configuration, 873
network failure, 664
network mode export/import, 860, 861
network-attached storage (NAS) devices, 43, 44
networking. See Oracle Net
NEXT_DAY function, 253
NEXTVAL pseudocolumn, 117
NLS_SESSION_PARAMETERS view, 258
noarchivelog mode, 672, 688–690
NOAUDIT command, 530
NOLOG switch, 410
non-container databases (non-CDBs)
exporting to PDBs from, 561–563
importing from PDBs into, 564
noncritical datafiles, 725–726
nonequality predicate, 140
nonequijoins, 304, 305, 319–321, 334
nonunique indexes, 97
normal shutdown mode, 428
normal user logon, 411
normal users, 410
normalizing data, 189, 192–193
NOT EXISTS condition, 352
NOT NULL columns, 213
notifications, 581
not-null constraints, 102, 103
NULL values
bitmap indexes and, 97
B*Tree indexes and, 95
conditional functions for, 267–268
IS NULL operator for, 222
SQL statements and, 212–213
subquery results as, 352
unique constraints and, 101
nullable columns, 213
number-to-character conversions, 256, 258–260
numeric format masks, 259
numeric functions, 248–251
MOD, 250–251
ROUND, 249–250
TRUNC, 250
numeric-based conditions, 215
NVARCHAR2 data type, 81
O
object links, 934
object privileges, 520–521
object statistics, 604–605, 606, 608
objects
directory, 546–547
exercise on using, 80–81
namespaces for groups of, 79–80
naming of schema, 78–79
privileges related to, 520–521
Scheduler, 647
synonyms for, 112–115
types of, 76–77
obtool commands, 839–840
OE demonstration schema, 199, 203
offline tablespaces, 480–481
OFFSET clause, 227
OHASD process, 40
OMF naming convention, 6
one-legged rows, 304
one-to-many relationships, 191, 194
one-to-one relationships, 191, 194, 470
online redo log files, 396–397
archiver processes and, 672–673
configuration of, 673
protecting, 670–671
two-minute drill on, 678
online tablespaces, 480–481
online transaction processing (OLTP), 610
open backups, 687–688
OPEN mode, 427
operating system authentication, 513–514
operating system groups, 10
operators, 209
alternative quote, 212
arithmetic, 209–210
boolean, 222–224
concatenation, 211
Optimal Flexible Architecture (OFA), 5
autotask for gathering statistics, 584
how statistics are used by, 605–607
manual statistics gathering with, 607–609
object statistics and, 604–605, 606
optimizer hints, 99
Oracle Base directory, 5
Oracle Call Interface (OCI), 442
Oracle Certified Associate (OCA), 40
Oracle Certified Professional (OCP), 10
Oracle Data Guard, 666
Oracle Data Pump. See Data Pump
Oracle data storage model, 470–472
Oracle Data Vault, 868
Oracle Database
architectural components, 374–378
data dictionary, 400–401
logical structures, 400
migration of data, 859, 860–862
physical structures, 395–399
startup and shutdown, 425–430
upgrading, 868–882
See also databases
Oracle Database software
directory structure, 5–6
disk space requirement, 5
Linux installation, 8–9, 10, 11–12
options for downloading, 4
planning an installation of, 4–7, 12
platforms supporting, 4
upgrading, 856–860
user accounts, 5
Windows installation, 10–11
Oracle Enterprise Manager. See Enterprise Manager
Oracle Health Monitor, 762–765
Oracle Home directory, 5, 203, 856, 857, 870–871
Oracle Internet Directory (OID), 71
Oracle join syntax, 308–309
Oracle Label Security (OLS), 868
Oracle Local Registry (OLR), 41, 56
Oracle Managed Files (OMF), 21, 485–486
Oracle Multitenant option. See multitenant environments
Oracle Net, 442–467
client-server paradigm and, 442–443
client-side configuration, 455–457
connectivity testing, 457
database listener creation, 445–446
Database Resident Connection Pool, 460–461
dynamic server registration, 446
exercise on configuring, 458–460
links between databases, 461–462
Net Configuration Assistant, 452–455
self-test questions/answers on, 463–467
session establishment, 443–445
shared server, 446–452
two-minute drill, 462–463
Oracle Restart, 56–64
database configuration, 60–62
Grid Infrastructure and, 56
listener registration, 62
process administration, 56–57
resource administration, 58–59
restart capability, 59–60
self-test questions/answers on, 63–64
two-minute drill, 62
Oracle Scheduler. See Scheduler
Oracle Secure Backup (OSB), 666, 834, 838–840
installing and configuring, 838
miscellaneous obtool commands, 839–840
self-test questions/answers on, 848, 850
two-minute drill on, 847
using RMAN with, 838–839
Oracle Software Delivery Cloud, 4
Oracle Technology Network (OTN), 4
Oracle Universal Installer (OUI), 4, 7–12
GI installation, 45
interactive install, 8–9
inventory creation, 7–8
Linux installation, 8–9, 10, 11–12
platform variations, 10
response files, 9
upgrades using, 857–858
version issues, 8
Windows installation, 10–11
Oracle Warehouse Builder (OWB), 869
ORACLE_HOME variable, 69
ORACLE_SID parameter, 20, 888, 889
ORDER BY clause, 225–227
ascending sort with, 225–226
composite sorting with, 227
compound queries and, 355, 361
descending sort with, 226
exercise on using, 227
positional sorting with, 226
OSB. See Oracle Secure Backup
OUI. See Oracle Universal Installer
outer joins, 304, 307, 324–330
exercise on performing, 330
FULL OUTER JOIN clause, 329
inner joins distinguished from, 325
LEFT OUTER JOIN clause, 325–326
RIGHT OUTER JOIN clause, 326–328
two-minute drill on, 334
outer queries, 342
P
parallel upgrade script, 875
parallelism
backupset, 844–845
limiting the degree of, 633
selecting for upgrades, 873
PARALLEL_SERVER_LIMIT directive, 945–946
initialization, 28–29, 416–424
static and dynamic, 416–417
parameters
basic, 419–420
CDB and PDB, 918–919
changing instance, 420–422
initialization, 21, 28–29, 422–424
PGA memory management, 597–598
SGA memory management, 599
standard auditing, 534
static and dynamic, 417–419
parent-child relationship, 102–103, 325
parentheses, 354
partial checkpoints, 669
partitions
index, 473
password encryption, 837
password files
authentication using, 513–514
external, 399
authentication using, 514
characters used for, 20
profiles for managing, 527–529
SYS and SYSTEM schema, 20
PATH variable, 69
pattern comparison, 220–222
PCTFREE setting, 490
PDB$SEED container, 901
PDBs. See pluggable databases
pending area, 628
performance
indexes for improving, 92–93
lock contention related to, 168
monitoring and tuning, 592–620, 941–944
views for optimizing, 110
performance monitoring, 593–595, 614, 941
performance tuning, 592–620
Automatic Memory Management and, 600–601
managing optimizer statistics, 604–609
memory advisors for, 602–604
methodology for, 592–593
monitoring activity for, 593–595
multitenant environment, 941–944
PGA memory management and, 596–598
RMAN operations and, 843–847
self-test questions/answers on, 615–620
SGA memory management and, 598–600
SQL Access Advisor for, 612–614
SQL Tuning Advisor for, 609–612
two-minute drill on, 614–615
persistent RMAN settings, 692–695
default values, 692–693
format specifiers, 694–695
retention policy, 693–694
PGA. See Program Global Area
PGA_AGGREGATE_TARGET parameter, 597–598, 600
physical storage structures, 395–399
controlfile, 396
datafiles, 397–399
logical storage structures vs., 470
online redo log files, 396–397
two-minute drill on, 403
PL/SQL
execution call example, 582
function result cache, 383
statistics and, 604
See also specific procedures
plans
database installation, 4–7, 12
resource, 625–626, 628–636, 653
pluggable databases (PDBs), 561, 884–885, 889–892
automating the startup of, 916–917
backing up, 938–939
changing the status of, 918
characteristics of, 889–890
cloning existing PDBs to create, 902–904
communications between, 891–892
creation techniques for, 900–907
Database Replay and, 949–951
duplicating using RMAN, 940–941
establishing connections to, 908–911
exporting to non-CDBs from, 564
exporting/importing between, 563–564
importing from non-CDBs into, 561–563
multitenant architecture and, 884–890
opening and closing, 914–916
parameter changes for, 918–919
performance tuning in, 941–944
plugging into CDBs, 906–907
privileges granted in, 932–933
resource management for, 944–949
roles managed in, 933
seed container for creating, 901–902
selectively opening/closing, 915–916
self-test questions/answers on, 893–894, 921–924, 955–959
service names and, 908–910
services created for, 910
shares assigned to, 944–945
tablespace management in, 926–930
two-minute drill on, 892–893, 920–921, 951–954
unplugging and dropping, 907
users managed in, 930–931, 934–935
See also container databases; multitenant environments
PMON (Process Monitor), 386–387
policies
Fine Grained Auditing, 531–532
RMAN retention, 693–694
unified auditing, 533, 535–536, 537
positional notation, 133
positional sorting, 226
postDBCreation.sql script, 28
Post-Upgrade Status Tool, 875
postupgrade tasks, 876
precedence, operator, 224–225
predefined roles, 524
predicate, 214
prerequisite checks
for DBCA database creation, 22
for DBUA database upgrades, 872
for installing Oracle Database software, 6–7
Pre-Upgrade Information Tool, 869–870, 877
primary key constraints, 102
DELETE command using, 140
sequences for generating, 115
PRINT command, 701
private synonyms, 113
Privilege Analysis mechanism, 525–526
privileges, 517–522
analysis of, 525–526
common and local, 932–933
flashback transaction, 787
granting, 517, 518–519, 521–522
object, 520–521
startup or shutdown, 425–426
system, 518–520
two-minute drill on, 538
problem key, 761
problems, database, 761
Process Monitor (PMON), 386–387
profiles, 527–529
creating and assigning, 528–529
password limits using, 527–528
two-minute drill on, 539
Program Global Area (PGA), 21, 375, 596
memory management, 596–598
PGA Advisor, 578
programs, Scheduler, 649
projection
concept of, 188–189
subqueries used for, 345–346
provisioning PDBs, 891
pseudocolumns, 785–786
PUBLIC role, 524
public synonyms, 113
push/pull duplication methods, 822
Q
queries
ad hoc, 231
limiting rows retrieved by, 214–225
recycle bin, 797
repeated, 231
sorting rows retrieved by, 225–229
undo retention for, 162–163
See also subqueries
queues
alert, 581
common, 448–449
control, 554–555
status, 555
quotas, user account, 510–511
R
RAID striping/mirroring, 44
range comparison, 219
RATE parameter, 846
ratio CPU method, 631
RAW data type, 81
readability of SQL statements, 208
read-consistent image, 155
read-only tablespaces
backing up, 706
marking tablespaces as, 481–482
Real Application Clusters (RAC), 40, 160, 377, 583, 911
RECO (Recoverer Process), 392
RECOVER command, 725
recovery, 724–753
CDB and PDB, 940
controlfile, 737–738
image copy, 730–736
incremental backup, 730–731
password file, 743
redo log group, 738–742
restoration vs., 724
RMAN command for, 725
self test on, 747–753
spfile, 736–737
table, 798–799
tempfile, 743–744
two-minute drill on, 744–747
See also backup and recovery; RMAN
recovery catalog, 696–699
configuration, 697
creating, 698
owner creation, 697
reasons for using, 696
synchronizing, 698–699
Recovery Manager. See RMAN
recovery time objective (RTO), 665
recovery window, 693–694
Recovery Writer (RVWR), 802, 804
RECOVERY_PARALLELISM parameter, 667
recycle bin, 797–798
dropped tables in, 88, 795, 797
reclaiming space from, 798
RECYCLEBIN parameter, 797
redo generation, 147, 148, 150, 159, 161
See also undo data
redo log, 376
archive log files, 399, 672–673
exercise on configuring, 674–675
log buffer, 380–381
online log files, 396–397, 670–671, 672
two-minute drill on, 678
redo log group recovery, 738–742
from lost ACTIVE log group, 742
from lost CURRENT log group, 742
from lost INACTIVE log group, 740–742
from member failures, 739–740
redundancy retention policy, 694
referential integrity, 194–197
registering resources, 58–59, 62
relational algebra, 188
relational database management (RDBMS) standard, 375, 376
relational paradigm, 189–204
data modeling and, 190
demonstration schemas, 199–204
entities and relations in, 190–194
foreign keys used in, 194–197
hierarchical paradigm vs., 198–199
normalization of data in, 192–193
primary keys used in, 194
real-world scenarios for, 189–190
referential integrity in, 194–197
tables, rows, and columns in, 197–199
users and schemas in, 200
Relational Schema notation, 190
relational theory, 188
relationships, 190–194
RENAME command, 792–793
renaming. See naming/renaming
REPAIR FAILURE command, 769, 770
repeated queries, 231
REPLACE function, 248
repositories
ADR, 756–765
AWR, 574–577
RMAN, 710–711
reserved words, 205
resizing. See sizing/resizing
Resource Manager, 622–643
active session pool method, 632
architecture overview, 623–626
autotask restrictions, 585
configuration process, 626–628, 638–639
consumer groups, 623–624
CPU allocation method, 628–631
execution time controls, 633–634
exercises on using, 636, 637–638
idle session termination, 634
multitenant use of, 944–949
need for, 622–623
parallel processing limits, 633
ratio CPU allocation method, 631
resource plans, 625–626, 628–636, 639, 653
Scheduler associated with, 647, 651, 653–655
self-test questions/answers on, 639–643
two-minute drill on, 638–639
undo data restrictions, 635–636
resource plans
migrating non-CDB, 949
PDB, 948–949
viewing, 948
RESOURCE role, 524
RESOURCE_LIMITS parameter, 626
multitenant allocation of, 944–949
tool for managing, 622–643
response files, 9
Restart. See Oracle Restart
restoration
CDB and PDB, 940
controlfile, 737–738
password file, 743
recovery distinguished from, 724
RMAN command for, 725
spfile, 736–737
See also recovery
RESTORE command, 725
restore points, 727–728
RESTRICTED mode, 918
result cache, 383
resumable space allocation, 496–497, 499
retention
flashback, 803–804
RMAN, 693–694
undo, 162–163
reverse key indexes, 97
revoking privileges, 517, 519, 521
RIGHT OUTER JOIN clause, 326–328
RMAN (Recovery Manager), 665, 686–722, 840–847
active database duplication, 824–825
advanced capabilities, 696–711
architecture, 686–687
archival backups, 706–708
backup concepts for using, 686–688
backupset backups, 705–706
block media recovery, 766–769
CDB and PDB backups, 938–939
complete recovery, 724, 725–727
compression options, 710
controlfile restoration, 737
default settings, 692–693
duplexed backupsets, 705
duplication from backups, 826
encrypted backups, 834–837
exercise on using, 695–696
Fast Recovery Area, 665, 675–676
Flashback Database and, 807
format specifiers, 694–695
image copy recovery, 730–736
incomplete recovery, 724, 727–730
incremental backups, 701–704, 730–731
monitoring performance of, 841–843
multiplexing operations in, 845
multisection backups, 708–710
Oracle Secure Backup and, 838–839
parallelizing backupsets in, 844–845
PDB duplication using, 940–941
persistent settings, 692–695
phases in backup process, 844
read-only tablespace backups, 706
recovery catalog, 696–699
redo log group recovery, 738–742
repository verification, 710–711
RESTORE and RECOVER commands, 725
retention policy, 693–694
self-test questions/answers on, 714–722, 848–851
spfile restoration, 736–737
stored scripts, 699–701
syntax for using, 688–691
table recovery, 798–799
tempfile recovery, 743–744
tuning performance of, 843–847
two-minute drills, 711–713, 847–848
See also backup and recovery
RMAN RECOVER…BLOCK command, 767, 768–769
roles, 522–527
administration, 513
common and local, 933, 935–936
creating and granting, 522–523, 526–527
enabling, 524–525
predefined, 524
two-minute drill on, 538
roll forward, 666
rollback data, 154
rollback segments, 473
ROLLBACK statement, 151, 154–155
root scripts, 10
ROUND function
date, 253–254
numeric, 249–250
row cache, 383
row chaining, 491
ROW EXCLUSIVE lock mode, 167
row ID, 785
row migration, 491
ROW SHARE lock mode, 167
ROWID data type, 83
row-level results, 293
row-limiting clause, 227–229
rows
blocks affected by, 475
chaining of, 491
limiting based on queries, 214–225
migration of, 491
relational models and, 190, 197–199
sorting based on queries, 225–229
RPAD function, 246
run-time binding, 229
S
SAVEPOINT statement, 151
savepoints, transaction, 151, 152
scalar values, 138–139
Scheduler, 646–660
architecture, 646–647
job creation in, 647–648
logging options in, 654–655
objects associated with, 647
prioritizing jobs in, 653–656, 657
programs used in, 649
Resource Manager and, 647, 651, 653–655
schedules created in, 649–650
self-contained jobs in, 650
self-test questions/answers on, 658–660
simplifying tasks using, 646–651, 657
two-minute drill on, 657
Scheduler Agent, 653
SCHEDULER_ADMIN role, 524
schedules, 649–650
schema objects, 78–79
SCOPE keyword, 421
scripts
database creation, 25–30
database transport, 821
database upgrade, 875
demonstration schema, 203–204
parallel upgrade, 875
RMAN stored, 699–701
searched CASE expression, 271–273
second normal form (2NF), 192
security, 508–544
authentication for, 508, 512–515
authorization for, 508
encrypting backups for, 834–837
Oracle Secure Backup, 838–840
privileges for, 517–522
profiles for, 527–529
roles for, 522–527
self-test questions/answers on, 539–544
two-minute drill on, 538–539
user accounts for, 508–517
views for enforcing, 108
Segment Advisor, 493–494, 498, 580, 584
segments, 395, 398, 400, 471, 472–474
automatic management of, 490–491
deferred creation of, 488–489
exercise on managing, 489–490
Segment Advisor for managing, 493–494, 498
shrink functionality for, 494–496, 498
space management for, 484–485, 488–491
SELECT clause, 205–206
SELECT FOR UPDATE statement
transaction control using, 151–153
WAIT and NOWAIT clauses of, 166
SELECT statements, 132, 186–189
ampersand substitution for, 229–234
capabilities of, 188–189
conditional expressions in, 265–273, 274
executing, 204–214
exercise on using, 208–209
expressions and operators, 209–212
FROM clause of, 205
GROUP BY clause and, 289–290, 294
HAVING clause and, 294–296
importance of, 186
indexes assisting, 93
joining multiple tables using, 304–321
ORDER BY clause and, 225–227
readability good practices, 208
row-limiting clause of, 227–229
rules governing syntax of, 206–209
set operators and, 342
subqueries and, 342
syntax of primitive, 205–206
transactions using, 146–147
two-minute drill on, 234–235
WHERE clause of, 214–225
SELECT_CATALOG_ROLE, 524
selection, concept of, 188–189
self-contained jobs, 650
exercise on performing, 322–324
JOIN ON clause used for, 321–322
two-minute drill on, 334
self-referencing foreign key, 200
self-referencing foreign key constraints, 103
self-test questions/answers
on ASM and Grid Infrastructure, 52–54
on AWR and the Alert system, 587–590
on backing up with RMAN, 714–722
on backup and recovery processes, 679–684
on CDB and PDB creation/management, 921–924, 955–959
on Database Configuration Assistant, 33–37
on database security, 539–544
on DDL and schema objects, 122–130
on diagnosing errors, 773–775
on DML and concurrency, 175–184
on duplicating databases, 829–832
on flashback technologies, 809–814
on group functions, 297–302
on installing Oracle Database software, 12–14
on instance management, 435–439
on joining tables, 335–339
on migrating data, 863–865
on moving data, 567–571
on multitenant environments, 893–894, 920–924, 955–959
on Oracle Database architecture, 404–407
on Oracle Net configuration, 463–467
on Oracle Restart for managing components, 63–64
on performance tuning, 615–620
on pluggable databases, 893–894
on recovery and restoration, 747–753
on the Resource Manager, 639–643
on retrieving, restricting, and sorting data, 236–240
on RMAN operations, 714–722, 747–753, 848–851
on the Scheduler, 658–660
on single-row and conversion functions, 275–278
on storage space management, 499–505
on subqueries and set operators, 363–370
on transportable tablespaces, 829, 831
on upgrading database software, 863–865
on upgrading Oracle databases, 878–882
See also two-minute drills
sequences, 115–120
creating, 115–116
description of using, 117–119
exercise on working with, 119–120
explanation of, 115
modifying, 118–119
options for, 116–117
primary keys and, 115
two-minute drill on, 122
server process, 445
server-managed backups, 686
server-managed incomplete recovery, 728–729
servers
administrative, 838
dedicated, 446–447
shared, 446–452
stand-alone, 45–51
service level agreements (SLAs), 665, 840
service requests (SRs), 7
services
creating for CDBs or PDBs, 910
dynamic registration of, 446
sessions
active, 632
Oracle Net, 443–445
terminating idle, 634
SET AUTOCOMMIT ON command, 153
SET command, 233
set comparison, 219–220
SET DECRYPTION command, 837
SET ENCRYPTION command, 837
set operators, 352–361
complex examples of, 360–361
compound queries and, 352, 353–361
exercise on describing, 355–356
general principles of, 353–355
INTERSECT operator, 353, 358–359
self test on, 363–370
two-minute drill on, 362–363
UNION ALL operator, 352, 355, 356–357, 358, 361
SET_ATTRIBUTE procedure, 654
SET_THRESHOLD procedure, 582
sets and Venn diagrams, 353, 354
SGA. See System Global Area
SGA_TARGET parameter, 599, 600
shadow processes, 375
SHARE lock mode, 167
SHARE ROW EXCLUSIVE lock mode, 167
shared pool, 382–384
data dictionary cache, 383
library cache, 382–383
memory allocation to, 598
result cache mechanism, 383
sizing, 384
shared server, 446–452
architecture, 447–450
configuration process, 450
default configuration, 451–452
when to use, 450–451
shell script, 26
SHOW RECYCLEBIN command, 795, 797
SHOW SQL button, 477
SHRINK command, 494
SHRINK SPACE command, 495
SHUTDOWN command, 428–429, 430
SHUTDOWN IMMEDIATE statement, 916
silent install, 9
simple CASE expression, 271, 272
simple views, 110–111
single ampersand substitution, 229–230
single-instance databases
architecture of, 374–377
Grid Infrastructure in, 40
single-row functions, 243
single-row subqueries, 348–349
comparison operators for, 348, 349
examples of writing, 351–352
sizing/resizing
shared pools, 384
tablespaces, 482–483
undo tablespace, 157–158
SKIP LOCKED keyword, 166
SMON (System Monitor), 386, 667
snapshots, 390
sort merge join, 93
sorting query results, 225–229
ascending sort, 225–226
composite sort, 227
descending sort, 226
ORDER BY clause for, 225–227
positional sort, 226
row-limiting clause for, 227–229
two-minute drill on, 235
source tables, 305
space management, 470
Alert system and, 581
command failure and, 144
multitenant environment, 926–927
resumable space allocation and, 496–497
segment-related, 484–485, 488–491
See also storage
spfile restoration, 736–737
SQL (Structured Query Language), 17
SQL Access Advisor, 579, 612–614
capabilities of, 612–613
DBMS_ADVISOR package and, 613–614
two-minute drill on, 615
SQL advisors, 579
SQL Developer, 73–76
cloning PDBs using, 902–904
database connections, 75–76
installing and launching, 73–74
user interface for, 74–75
SQL files, 555
express mode, 550–551
multitenant environments and, 565
overview on using, 548–550
SQL*Plus, 68–73
container database creation, 898
database connections, 71–73
directory creation, 547
Flashback Database and, 806–807
Flashback Drop and, 795–796
Linux use of, 68–70
login syntax for, 412
renaming tables with, 792
SET AUTOCOMMIT ON command, 153
tablespace creation, 478
Windows use of, 70–71
working with, 410–411
SQL query result cache, 383
SQL Repair Advisor, 580
executing for transactions, 146–148
expressions and operators, 209–212
NULL values and, 212–213
readability good practices, 208
running the SQL Tuning Advisor for, 611–612
statement terminators for, 207
syntax rules governing, 206–209
uppercase vs. lowercase, 206–207
views for simplifying, 108–109
See also specific statements
SQL Tuning Advisor, 579, 609–612
capabilities of, 610
DBMS_SQLTUNE package and, 610–611
exercise on running, 611–612
two-minute drill on, 615
sqlnet.ora file, 452
stale statistics, 605
stand-alone servers, 45–51
standard auditing, 530–531, 534–535
standard deviation, 284–285
standby database, 666
star transformation, 344–345
STARTUP command, 667
STARTUP FORCE command, 428
STARTUP NOMOUNT command, 912–913
startup process
automating PDB, 916–917
CDB instance, 912–913
database instance, 426–428, 429–430
STARTUP UPGRADE command, 875
statement failure, 664
statement terminators, 207
states, constraint, 105–106
static parameter files, 416–417
static parameters, 417–419
statistics
manually gathering, 607–609
metrics vs., 593
PL/SQL and, 604
STATISTICS_LEVEL parameter, 585
status queue, 555
STDDEV function, 284–285
storage, 470–505
compression options for, 491–492
data dictionary and, 400–401
exercises about, 401–402, 475–476
extents used in, 472, 474–475, 483–484
location of database file, 21
logical structures for, 400, 470
Oracle model for, 470–472
physical structures for, 395–399, 470
resumable space allocation for, 496–497
Segment Advisor for managing, 493–494
segments used in, 471, 472–474, 484–485, 488–491
self-test questions/answers about, 499–505
shrink functionality for, 494–496
tablespaces used in, 470–471, 476–487, 492–493
two-minute drill on managing, 497–499
storage area network (SAN) devices, 43, 44
stored scripts, 699–701
Streams, Oracle, 377
striping, RAID, 44
structure-and-data templates, 30
structure-only templates, 30
subqueries, 342–352
comparison, 343–344
correlated, 349–350
creating tables from, 85–86
DELETE command using, 140
DML statements and, 346
exercises on exploring, 343, 347, 350
EXISTS condition used in, 352
null results in, 352
overview on using, 342–343
problems solved with, 343–347
projections using, 345–346
self test on, 363–370
single- and multiple-row, 348–349, 351–352
star transformation using, 344–345
tables generated from, 345
two-minute drill on, 362
See also queries
substitution variables, 229–232
See also ampersand substitution
SUBSTR function, 248
SUM function, 282–283
suspended processes, 496
SWITCH command
exercises on working with, 732–736
fast switch to image copies using, 732
synchronizing recovery catalog, 698–699
synonyms, 112–115
creating and dropping, 113–114
exercise on working with, 114–115
private vs. public, 113
two-minute drill on, 122
syntax
ANSI SQL join, 309–310
errors in DML statements, 144
Oracle join, 308–309
RMAN, 688–691
rules for SQL statements, 206–209
view-related, 111–112
SYS schema, 20, 27, 78, 188, 785
SYSAUX tablespace, 28, 400, 476, 575
SYSDATE function, 251
sysdba connections, 75
SYSDBA privileges, 411–412, 426
SYSOPER privileges, 426
system change number (SCN), 781
System Global Area (SGA), 21, 374, 596
ASM instance, 42
data structures, 378–379
memory management, 598–600
pending area, 628
SGA Advisor, 578
system identifier (SID), 20
System Monitor (SMON), 386, 667
system privileges, 518–520
system requirements, 6
SYSTEM tablespace, 17, 21, 28, 476, 510, 889
T
table aliases, 310
table flashback, 783–785
table partitions, 473
tables, 84–92
altering definitions for, 86–87
basic compression of, 491, 492
column specifications for, 84–85
constraints for, 100–107
exercise on creating, 88–90
external, 552–554
flashback for, 779, 780, 783–785, 791–798
generating with subqueries, 345
indexes for, 92–100
joining, 304–335
locks used on, 165–166
nested, 473
recovering from backups, 798–799
reinstating dropped, 791–798
relational models and, 190, 197–199
segments as heap-structured, 473
shrink operation for, 494–496
structural description of, 186–188
subquery-created, 85–86
truncating, 87
two-minute drill on, 120–121
views for, 107–115
altering, 479–483
architecture, 926–927
backing up, 706
CDB root container, 888
creating, 476–478
dropping, 483
exercises about, 478–479, 486–487
exporting/importing, 557–561
extent management, 483–484
Flashback Data Archive, 799–800
marking as read-only, 481–482
monitoring/managing space usage for, 492–493
multitenant environments and, 926–930
online and offline, 480–481
Oracle Managed Files for, 485–486
Oracle storage model, 470–471
pluggable database, 889
renaming, 479–480
resizing, 482–483
restoring and recovering, 726
segment space management, 484–485
temporary, 511
transporting, 557–560, 816–819
two-minute drill on, 497–498
undo, 635
target
backup/recovery, 686
database duplication, 822
table join, 305
automated, 584–586
postupgrade, 876
Scheduler for simplifying, 646–651
TCP protocol, 455
tempfile recovery, 743–744, 747
templates
database creation, 19
database design, 30–31
two types of, 30
temporary tablespaces, 511, 929
temporary undo segments, 159
TEMP_UNDO_ENABLED parameter, 156
terminators, statement, 207
test questions/answers. See self-test questions/answers
testing connectivity, 457
text substitution, 232
third normal form (3NF), 188, 192, 593
thresholds, alert, 581–583
time
execution, 633–634
idle, 634
time component format models, 262
TIMEOUT option, 496
time-sharing algorithm, 447
TIMESTAMP data type, 82
TIMESTAMP WITH LOCAL TIMEZONE data type, 82
TIMESTAMP WITH TIMEZONE data type, 82
timezone data upgrades, 873
TNS connections, 75
TNS_ADMIN environment variable, 454–455
tnsnames.ora file, 452
tnsping utility, 457
TO_CHAR function, 258–264
date-to-character conversions, 260–264
number-to-character conversions, 258–260
TO_DATE function, 264–265
TO_NUMBER function, 265
trace files, 399
transactional shutdown mode, 428
ACID test for, 145–146
autocommit for, 153
COMMIT statement for, 149–150
control statements for, 149–153
DELETE statement for, 148
distributed, 392
executing SQL statements for, 146–148
exercise on working with, 153–154
flashback for, 163–164, 779, 787–790
INSERT statement for, 148
ROLLBACK statement for, 151, 154–155
SELECT FOR UPDATE statement for, 151–153
SELECT statement for, 146–147
start and end of, 148–149
two-minute drill on, 174
undo retention and, 162
UPDATE statement for, 147–148
transparent encryption, 834–836
Transparent Network Substrate (TNS) layer, 72
transportable tablespaces, 816–819
compatibility requirements, 816–817
configuration process, 816–818
endian requirements, 817–818
process of transporting, 818–819
self-test questions/answers on, 829, 831
two-minute drill on, 827–828
transporting
TRIM function, 247
TRUNC function
numeric, 250
TRUNCATE command, 141–142
TRUNCATE TABLE command, 87
tuning performance. See performance tuning
two-minute drills
on ASM and Grid Infrastructure, 51
on backup and recovery, 678–679
on CDB and PDB creation/management, 920–921
on conversion and conditional functions, 274
on Database Configuration Assistant, 33
on database security, 538–539
on DDL and schema objects, 120–122
on DML and concurrency, 173–175
on flashback technologies, 808–809
on installing Oracle Database software, 12
on instance management, 434–435
on joining tables, 334–335
on moving data, 565–567
on multitenant environments, 892–893, 920–921, 951–954
on Oracle Database architecture, 403–404
on Oracle Net configuration, 462–463
on Oracle Restart for managing components, 62
on performance tuning, 614–615
on recovery and restoration, 744–747
on the Resource Manager, 638–639
on retrieving, restricting, and sorting data, 234–236
on RMAN backups, 711–713
on the Scheduler, 657
on storage space management, 497–499
on subqueries and set operators, 362–363
on transporting tablespaces and databases, 827–828
See also self-test questions/answers
two-phase commits, 392
U
udev facility, 45
UNDEFINE command, 232–233
undo data, 154–164
configuring retention of, 162–163
DML and generation of, 154–155
error conditions related to, 156
explanation on generating, 154–155
Flashback Query and, 163, 790–791
monitoring and administering, 155, 156–161
parameters for managing, 156–157
redo data distinguished from, 161
restricting generation of, 635–636
temporary undo segments and, 159
two-minute drill on, 174
undo tablespaces and, 157–158, 159–161
undo pool, 635–636
undo retention
configuration of, 162–163
Flashback Query and, 163
undo segments, 145, 146, 147, 155, 159, 473
undo tablespaces
creating and managing, 159–160
error conditions related to, 156
exercise on working with, 160–161
resource management for, 635
sizing and monitoring, 157–158
UNDO_MANAGEMENT parameter, 155, 156, 157
UNDO_RETENTION parameter, 156, 157, 162, 163
UNDO_TABLESPACE parameter, 156, 157, 160
UNDOTBS1 tablespace, 28
unified auditing, 533–534
enabling, 535–536
exercise on using, 536–538
migrating to, 875–876
multitenant environment, 937–938
policies for, 533, 535–536, 537
uniform size extents, 483–484
UNION ALL operator, 352, 355, 356–357, 358, 361
unique constraints, 101
unique indexes, 97
unique keys, 190
Universal Markup Language (UML), 190
UPDATE command, 136–139
exercise on using, 139
row migration and, 491
transactions using, 147–148
WHERE clause and, 136–137, 138, 139
upgrading database software, 856–862
data migration and, 859
DBUA vs. manual methods for, 858
determining release number for, 856–857
Oracle Universal Installer for, 857–858
self-test questions/answers on, 863–865
steps in process of, 859–860
two-minute drill on, 862
upgrading Oracle databases, 868–882
Database Upgrade Assistant for, 871–874
manually using scripts and tools, 874–875
migration to unified auditing, 875–876
Oracle Data Vault and, 868
Oracle Label Security and, 868
Oracle Warehouse Builder and, 869
postupgrade tasks for, 876
preparing Oracle Home for, 870–871
Pre-Upgrade Information Tool for, 869–870
self-test questions/answers on, 878–882
simulation exercise on, 876–877
two-minute drill on, 877–878
UPPER function, 244
uppercase SQL statements, 206–207
account status for, 512
attributes of, 508–509
authentication methods for, 512–515
creation of, 515–516
default tablespaces for, 510
exercise on creating, 516–517
quotas for, 510–511
temporary tablespaces for, 511
two-minute drill on, 538
usernames for, 509
user errors, 664
User Global Areas (UGAs), 449, 634
user IDs, 10
user interface
Database Express, 415–416
SQL Developer, 74–75
user process failure, 664
user process tools, 377
USER views, 886
user-managed backups, 686
USER_OBJECTS view, 77
USER_RECYCLEBIN view, 797
users
local, 930–931
normal, 410
USERS tablespace
exercise on restoring and recovering, 726
incomplete recovery to restore, 729–730
SWITCH command for recovering, 734–736
UTILIZATION_LIMIT directive, 945–946
V
V$CONTAINERS view, 887, 889, 890
V$DATABASE_BLOCK_CORRUPTION view, 767, 768
V$DIAG_INFO view, 758
V$INSTANCE view, 432
V$LOG view, 739
V$SESSION_LONGOPS view, 843
V$SYSSTAT view, 432
V$TABLESPACE view, 433
V$UNDOSTAT view, 158
V$VERSION view, 856
value-based auditing, 532
VALUES keyword, 134
variables
environment, 454–455
substitution, 229–232
variance calculation, 284–285
VARIANCE function, 284–285
vee dollar (V$) views, 432–433
VERIFY command, 233–234
versions flashback, 785–787
views, 107–115
alert, 581
Data Recovery Advisor, 770
dynamic performance, 432–433, 841
exercise on creating, 112
Flashback Data Archive, 800
memory allocation, 601
performance monitoring, 594
reasons for using, 108–110
resource plan directive, 948
SELECT statements and, 77, 107, 110
simple and complex, 110–111
SQL Tuning Advisor, 611
syntax for working with, 111–112
virtual memory, 596
W
wait events, 594
WHERE clause, 214–225
boolean operators and, 222–224
character-based conditions and, 215–216
comparison operators and, 217–222
date-based conditions and, 216–217
DELETE command and, 139
HAVING clause and, 293–294
numeric-based conditions and, 215
precedence rules for, 224–225
restricting group results with, 293
SELECT statement and, 214–217
subqueries in, 342
UPDATE command and, 136–137, 138, 139
wildcard characters, 220
window groups, 653
windows, Scheduler, 653, 655–656
Windows OS
demonstration schema creation on, 204
installing Grid Infrastructure on, 47–48
installing Oracle Database on, 10–11
launching the DBCA on, 18
managing Oracle Restart on, 56–57
SQL*Plus on, 70–71
support for Oracle Database on, 4
workload replays, 949–951