Index
Special Characters and Numbers
&&master_user variable, 202
$ADR_HOME/trace directory, 93
%a variable, 423
%CPU column, 607
%d variable, 425
%MEM column, 607
%r variable, 423
%s variable, 423
%t variable, 423
? variable, 35
@ variable, bash shell, 53
$ variable, bash shell, 53
! variable, bash shell, 53
+cmdline attribute, 553
+rman attribute, 553
< command, 606
> command, 606
1/app/oracle directory, 598
A
a variable, bash shell, 53
ABORT parameter, SHUTDOWN command, 44
Access control list (ACL) rules, 13
ACCESS PARAMETERS clause, 353, 356–357
ACL (Access control list) rules, 13
ACSLS (automated cartridge system library software), 546
--active option, 564
Active Session History (ASH), 612, 615
ADD LOGFILE GROUP statement, 105
ADD PARTITION clause, 292
ADD procedure, DBMS_REFRESH package, 410
addbw command, 560
ADDM (Automatic Database Diagnostic Monitor), 614–615
Administrators group, 567
ADR_HOME directory, 4
ADR_HOME/trace directory, 339
ADVISE command, 514
ADVISE FAILURE command, 513–514
alert log, troubleshooting, 600–602
viewing with ADRCI utility, 601–602
alert.log file, 97, 103, 418, 442, 512, 598–602
alert_O11R2.log file, 352
alias command, 55
aliases, shortcuts for commands using, 55–56
ALIGNMENT column, 302
--all option, 564
ALL static view, 214
ALL_COL_PRIVS view, 234
ALL_COL_PRIVS_MADE view, 234
ALL_COL_PRIVS_RECD view, 234
ALLOCATE CHANNEL command, 478
ALL_SYNONYMS view, 203
ALL_TAB_PRIVS view, 234
ALL_TAB_PRIVS_MADE view, 234
ALL_TAB_PRIVS_RECD view, 234
ALTER DATABASE ADD LOGFILE MEMBER statement, 106
ALTER DATABASE BACKUP CONTROLFILE statement, 433, 447
ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement, 87
ALTER DATABASE BACKUP statement, 89
ALTER DATABASE BEGIN BACKUP statement, 436
ALTER DATABASE CLEAR LOGFILE command, 101
ALTER DATABASE CLOSE statement, 45
ALTER DATABASE command, 499, 515
ALTER DATABASE DATAFILE … OFFLINE FOR DROP statement, 85
ALTER DATABASE DATAFILE … OFFLINE statement, 85
ALTER DATABASE DATAFILE … RESIZE command, 82
ALTER DATABASE DATAFILE statement, 83–85
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement, 34
ALTER DATABASE DROP LOGFILE GROUP statement, 106
ALTER DATABASE DROP LOGFILE MEMBER statement, 107
alter database open resetlogs command, 530–531
ALTER DATABASE OPEN statement, 442
ALTER DATABASE RENAME FILE statement, 86–87, 522
ALTER DATABASE RENAME FILE … TO statement, 85
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE statement, 81
ALTER DATABASE statement, 83, 88
ALTER DATABASE TEMPFILE…RESIZE statement, 625
ALTER INDEX … REBUILD command, 158
ALTER INDEX … RENAME TO statement, 188
ALTER INDEX statement, 295
ALTER INDEX…MONITORING USAGE statement, 190
ALTER INDEX…REBUILD PARTITION statement, 297
ALTER MATERIALIZED VIEW LOG ON <table_name> MOVE statement, 394
ALTER MATERIALIZED VIEW LOG ON…SHRINK statement, 393
ALTER MATERIALIZED VIEW statement, 379
ALTER MATERIALIZED VIEW…MOVE TABLESPACE statement, 389
ALTER PROFILE statement, 126
ALTER SEQUENCE statement, 210
ALTER SESSION statement, 93, 111, 247
ALTER SYSTEM CHECKPOINT command, 106
ALTER SYSTEM command, 321
ALTER SYSTEM KILL SESSION statement, 617
ALTER SYSTEM SET statement, 147
ALTER SYSTEM statement, 30, 97, 247, 422
ALTER SYSTEM SWITCH LOGFILE statement, 106
ALTER TABLE … ADD statement, 147
ALTER TABLE … DROP statement, 149
ALTER TABLE … MODIFY PARTITION statement, 252
ALTER TABLE … MODIFY statement, 148
ALTER TABLE … MOVE statement, 157–158
ALTER TABLE … RENAME statement, 149
ALTER TABLE … SHRINK SPACE statement, 157
ALTER TABLE statement, 134, 141, 162–163, 167, 170, 257, 293, 295, 297
ALTER TABLE…ADD PARTITION statement, 292
ALTER TABLE…ADD statement, 253
ALTER TABLE…DROP PARTITION statement, 297
ALTER TABLE…DROP statement, 254
ALTER TABLE…MERGE PARTITIONS statement, 296–297
ALTER TABLE…MOVE PARTITION statement, 288
ALTER TABLE…MOVE statement, 260
ALTER TABLE…MOVE…STORE AS statement, 253
ALTER TABLESPACE … ADD DATAFILE statement, 82
ALTER TABLESPACE … OFFLINE IMMEDIATE statement, 85
ALTER TABLESPACE … OFFLINE NORMAL statement, 83, 85
ALTER TABLESPACE … OFFLINE TEMPORARY statement, 85
ALTER TABLESPACE … RENAME DATAFILE … TO statement, 85
ALTER TABLESPACE statement, 76–77, 80, 82–83, 85–86
ALTER TABLESPACE…RENAME DATAFILE statement, 86
ALTER TABLE…SPLIT PARTITION statement, 295
ALTER TABLE…TRUNCATE PARTITION statement, 299
ALTER USER privilege, 118
ALTER USER statement, 118, 122
ALTER VIEW command, 198
alt_prof_dyn.sql script, 126
-altr command, 59
APP_DATA tablespace, 72
APP_DATA_LARGE tablespace, 72
APP_DATA_SMALL tablespace, 72
APPEND hint, 350
APPEND keyword, 505
APP_INDEX tablespace, 72
appinvprd.log file, 588
APPUSR user, 121
archive-redo log destination, 465–466
archive redo logs
backing up, 469
ARCHIVE_LAG_TARGET parameter, 100, 103
ARCHIVELOG ALL clause, 540
archive.log file, 338
backing up archive-redo log files, 428
disabling, 427
enabling, 426
ensuring that database is in, 431
making architectural decisions, 421
making cold backups of, 428–430
setting archive-redo file location, 421–426
user-defined disk locations, 422–424
using FRA for archive log files, 424–425
archivelog-mode databases, 441–448
incomplete recovery of, 449–450
mount mode, 442
restoring datafile from backups, 442–443
restoring control files, 445–448
arp utility, 21
arrow keys, scrolling with, 58
AS BACKUPSET command, 490
AS COPY command, 490
ASH (Active Session History), 612, 615
ASM (Automatic Storage Management), 46
ASSM (automated segment space management), 247
ATOMIC_REFRESH parameter, 399, 409–410
attachHome option, 15
attaching Oracle home, copying existing installation, 14–15
attributes, segment and storage, 322–323
AUDIT ALL statement, 631
AUDIT_FILE_DEST variable, 627
auditing, troubleshooting, 625–635
and fine-grained auditing, 633–635
viewing enabled actions, 629–630
AUDIT_SYS_OPERATIONS parameter, 627
AUDIT_TRAIL parameter, 626–627, 630–631
AUDIT_TRAIL_TYPE parameter, 632
AUD_TBSP tablespace, 632
AUTHENTICATED_IDENTITY parameter, 221
authentication
and connecting to databases, 41
AUTHENTICATION_METHOD parameter, 221
AUTOALLOCATE clause, 74
AUTOALLOCATE feature, 75
autobackup directory, 527
autobackups
of control files, 468
restoring control file using, 528
AUTO_DROP parameter, 571
AUTOEXTEND feature, 74
autoincrementing, columns, 206–207
automated cartridge system library software (ACSLS), 546
automated segment space management (ASSM), 247
Automatic Database Diagnostic Monitor (ADDM), 614–615
Automatic Storage Management (ASM), 46
Automatic Workload Repository (AWR), 612, 614
adding by editing cron table directly, 579
adding by loading cron table from file, 580–581
enabling access to, 577
redirecting output for, 581
troubleshooting, 581
checking for archive redo destination fullness, 586–588
checking for files over certain age, 590–591
checking for locked production accounts, 589–590
checking for too many processes, 591–592
starting and stopping database and listener, 583–586
truncating large log files, 588–589
verifying integrity of RMAN backups, 592–593
Oracle Scheduler jobs, 570–574
copying, 573
deleting, 574
disabling, 573
enabling, 573
logging history for, 572
modifying, 572
autotrace tool, 155
availability, of database, 596–597
AWR (Automatic Workload Repository), 612, 614
awrsqrpt.sql file, 614
B
b column, vmstat utility, 604–605
-B option, 567
-b option, top command, 607
B-tree cluster index type, 173
B-tree index type, 173
B-tree indexes, creating, 175–176
B&R (backup and recovery), 413–456
backing up archive-redo log files, 428
disabling, 427
enabling, 426
making architectural decisions, 421
making cold backups of, 428–430
setting archive-redo file location, 421–426
archivelog-mode databases, 441–448
incomplete recovery of, 449–450
restoring control files, 445–448
flashing back databases, 454
FLASHBACK TABLE TO BEFORE DROP, 452
to SCN, 453
to timestamps, 453
hot backups, scripting, 434–436
altering databaseinto backup mode, 432
altering databaseout of backup mode, 432
backing up archive-redo logs generated during backup, 433
backing up control files, 433
copying datafiles with OS utilities, 432
determining where to copy backup files, 431
determining which files need to be backed up, 431
ensuring that database is in archivelog mode, 431
maximum sequence number of online-redo logs, 431–433
noarchivelog-mode databases, 414–420
copies of files, 416
determine locations and names of files, 415
with online-redo logs, 416–417
restarting databases, 416
shutting down instances, 416
space required, 415
without online-redo logs, 417–418
redo generated during backups, 439–440
updated datafiles, 440
BACKGROUND_DUMP_DEST parameter, 537, 598
BACKGROUND_DUMP_DEST varaible, 598
backing up. See also OSB
backup and recovery. See B&R
BACKUP AS COPY command, 511, 524, 530
BACKUP command, 458–459, 466, 468, 476, 487, 489, 491–492, 523
BACKUP INCREMENTAL command, 498
BACKUP INCREMENTAL LEVEL=0 DATABASE PLUS ARCHIVELOG command, 490
Backup-Retention policy, 471–472
backup sets, vs. image copies, 490–491
backup users, 465
BACKUP VALIDATE command, 523
backups
copying to destination server, 536
creating on originating database, 535–536
making control file aware of location of, 538
online or offline, 465
restoring control file from, 538
restoring entire database using backup control file, 519
used for recovery, previewing, 516–517
validating files before restoring, 517
BACKUP…VALIDATE command, 500–501
BADFILE parameter, 353
base directory, for OFA, 3
.bash_profile file, 26
.bashrc file, 26, 29, 52, 54, 56, 69
BASIC compression algorithm, 474, 477
BasicFile LOBs (large objects)
migrating to SecureFile LOB, 260–262
overview, 246
batch mode, top utility, 607
bck.log file, 581
bdump function, 61
bi column, 605
BIGFILE clause, 81
bigfile feature, for tablespaces, 81
bigfiletablespace, 81
binary large object (BLOB), 243–244, 265
Bitmap index type, 173
bitmap indexes, creating, 179–180
Bitmap join index type, 173, 180–181
BITMAP keyword, 179
BLKG_OS_PID column, 618
bllnx1_home-oracle.ds file, 559–561
BLOB (binary large object), 243–244, 265
BLOB_DATA tablespace, 249
blob.sql file, 265
block-change tracking, 476
BLOCKRECOVER command, 524
bo column, 605
BOOKS table, 201
bottlenecks, troubleshooting, 602–609
mapping operating system process to SQL statement, 608–609
using vmstat, 605
buff column, 605
BUFFER_GETS column, 612
BUILD DEFERRED clause, 378
BY ACCESS clause, AUDIT statement, 628
BYHOUR option, 572
BYMINUTE option, 572
BYTES column, DBA/ALL/USER_SEGMENTS view, 265
C
C constraint code, 230
C:Program FilesOracleBackup directory, 549
cache column, 605
CACHE column, DBA/ALL_LOBS view, 262
CACHE option, 205
CACHE READS setting, 254
CACHE setting, 254
caching for LOBs, 254
CANCEL clause, RECOVER DATABASE statement, 449
CASCADE CONSTRAINTS clause, DROP TABLESPACE statement, 79
CASCADE CONSTRAINTS option, 151
CASCADE option, 168
CASCADE parameter, 226
cat command, 600
catalog-based restore, file system restore with OSB, 562
CATALOG command, 471, 496–497, 538, 558
CATALOG DEVICE TYPE SBT_TAPE BACKUPPIECE command, 558
CATALOG START WITH <directory> command, 558
catds command, 559
catproc.sql script, 215
cbdir variable, 419
CBO (cost-based optimizer), 138
CB_RAD_COUNTS view, 237
cd command, 562
cdds command, 561
CHANGE function, DBMS_REFRESH package, 409
change/SCN-based recovery, 533
character large object (CLOB), 243–244, 263–264
CHARSET parameter, 343
CHAYA user, 323
CHECK LOGICAL clause, 500, 593
checking for constraints, 165–166
Checkpoint not complete issue, 105
Checkpoint not complete message, 104
chkbw command, 562
chkds command, 559
chmf command, 555
chmod command, 60
CHSCHED command, 561
chssel command, 556
CHUNK column, DBA/ALL_LOBS view, 262
chuser command, 554
CIA_SEL user, 617
classes, terminology of OSB, 548
CLOB (character large object), 243–244, 263–264
CLOB_DATA tablespace, 249
clob.sql file, 264
cloning users, 333
CLUSTER_BUCKETS table, 231
clustered table type, 134
COLUMN_EXPRESSION column, 178, 227
COLUMN_NAME column, DBA/ALL_LOBS view, 262
COLUMN_POSITION column, 227
columns
adding, 147
encrypting, 376
renaming, 149
COMMAND column, 607
command editor, setting, 59–60
command history, listing, 58–59
command prompt, customizing, 52–53
COMMISSION column, 286
COMMIT parameter, 343
COMMIT statement, 153, 617, 620
COMPACT clause, 157
COMPATIBLE parameter, 141
COMPILE parameter, 343
--complete option, 564
complete recovery, RMAN, 516–527
performing block level recovery, 523–524
restoring archive redo log files, 524–526
restoring entire database, 518–519
restoring tablespaces, 519–520
testing restore and recovery, 516–518
complete-refreshable MV, 365–369
composite partition type, 272
composite partitioning, of tables, 281–282
COMPOSITE_LIMIT setting, 127
COMPRESS clause, 256
COMPRESS FOR OLTP clause, 143
COMPRESS N clause, 182
COMPRESS option, ACCESS PARAMETERS clause, 356
compressing
dump files, 356
MVs, 376
output, 336
COMPRESSION column, DBA/ALL_LOBS view, 263
COMPRESSION parameter, 336, 357
Computer Name tab, System window, 21
COMPUTER_SYSTEMS table, 154
concatenated indexes, creating, 176–177
CONFIGURE ARCHIVELOG DELETION command, 473
CONFIGURE CHANNEL command, 467, 478
CONFIGURE CHANNEL…FORMAT command, 467
CONFIGURE command, 463, 466, 476–477, 480–482, 489, 556–557
CONNECT option, AUDIT statement, 629
_CONNECT_IDENTIFIER variable, 55
connecting to databases, and authentication, 41
CONNECT_TIME setting, 127
CONSISTENT parameter, 342
consistent=y parameter, 341
enabling, 168
showing primary-key and foreign-key relationships, 230–231
CONSTRAINTS parameter, 342–343
CONSTRAINT_TYPE column, 229, 231
CONTENT parameter, 324, 330–331, 333, 343
CONTENT=ALL parameter, 342
CONTENT=DATA_ONLY option, 330
--contentmanaged option, 554
CONTENT=METADATA_ONLY option, 336
--contents option, 563
contents.sql file, 318
CONTINUE_CLIENT command, 313–315
autobackups of, 468
creating required directories for, 537
file names and locations of, 93–94
restoring from backups, 538
CONTROL_FILE_RECORD_KEEP_TIME initialization parameter, 471
CONTROL_FILE_RECORD_KEEP_TIME parameter, 463, 465, 471, 474
CONTROL_FILES parameter, 92, 94–97, 452, 527–528, 537–538
CONTROLFILE_TYPE column, V$DATABASE view, 528
COPY command, 468, 487, 491–492
COPY_FILE procedure, 85
copying
Oracle Scheduler jobs, 573
COPY_JOB procedure, 573
coraenv utility, 27
CORE_DUMP_DEST parameter, 537
corruption, checking for in RMAN backups, 499–501
using BACKUP…VALIDATE, 501
using RESTORE…VALIDATE, 501
using VALIDATE command, 500
cost-based optimizer (CBO), 138
cpio file, 8
CPU_PER_CALL setting, 127
CPU_PER_SESSION setting, 127
CPU_TIME column, 612
CREATE ANY VIEW privilege, 194
CREATE CONTROLFILE statement, 87–89, 482, 543
CREATE DATABASE command, 214
CREATE DATABASE LINK script, 319
CREATE DATABASE LINK statement, 239, 322, 404
CREATE DATABASE statement, 25, 29, 32–36
CREATE DIRECTORY command, 310
CREATE EXTERNALLY statement, 116
CREATE GLOBAL TEMPORARY TABLE statement, 159
CREATE INDEX statement, 171, 175, 300
CREATE MATERIALIZED VIEW LOG command, 391
CREATE MATERIALIZED VIEW privilege, 365
CREATE MATERIALIZED VIEW statement, 379
CREATE MATERIALIZED VIEW…AS SELECT statement, 366
CREATE MATERIALIZED VIEW…ON PREBUILT TABLE statement, 377
CREATE <new_part_tab> AS SELECT * FROM <old_tab> method, 290
CREATE OR REPLACE method, 198
CREATE PROFILE statement, 127
CREATE ROLE privilege, 130
CREATE ROLE statement, 130
CREATE script, 76
CREATE SESSION privilege, 115–116, 483
CREATE SPFILE FROM PFILE statement, 30
CREATE SYNONYM command, 201
CREATE TABLE AS SELECT statement, 146
CREATE TABLE <new table>…AS SELECT * FROM <old table> statement, 291
CREATE TABLE privilege, 115–116, 365, 390
CREATE TABLE statement, 120, 134, 249, 257, 272, 279–280, 282, 295, 351
CREATE TABLE…ORGANIZATION EXTERNAL script, 346
CREATE TABLE…ORGANIZATION EXTERNAL statement, 345, 347–348, 355, 357–358
CREATE TABLE…ORGANIZATION EXTERNAL…AS SELECT statement, 355
CREATE TABLESPACE script, 74–75
CREATE TABLESPACE statement, 73, 80
CREATE USER privilege, 115
CREATE USER SQL statement, 115
CREATE USER statement, 34, 127
CREATE VIEW privilege, 194
CREATE VIEW statement, 194
CREATED column, 224
credb.sql file, 33
cretbsp.sql script, 75
creuser.sql script, 117
crit_var variable, 403
cron file, 577
adding by editing cron table directly, 579
adding by loading cron table from file, 580–581
enabling access to, 577
redirecting output for, 581
troubleshooting, 581
cron table, 574, 576–581, 583, 589
cron utility, 64–66, 402, 482, 507, 574–575, 582, 590
cron.allow file, 577
cron.deny file, 577
crontab command, 579
crontab file, 577
CROSSCHECK command, 473, 482, 493, 538
cs column, 605
CSV files, loading into databases, 347–350
creating directory objects and granting access, 348
creating external tables, 348–349
loading regular tables from external tables, 349–350
viewing external-table metadata, 349
CTAS statement, 350
CTAS table, 146
CTIME column, 113
Ctrl+N, rerunning commands with, 58
Ctrl+P, rerunning commands with, 58
CUMULATIVE keyword, 497
CURRENT_USER parameter, 221
CURRVAL pseudo-column, 206
CUSTOMERS table, 332
CYCLE option, 205
D
D command, 606
d variable, bash shell, 53
daemons, terminology of OSB, 548
DAILY_SALES table, 362
data
creating database directories, 310–311
granting access to directories, 311
percentage of, 325
taking exports, 311
directly across networks, 319–320
unloading and loading using external tables, 354–357
compressing dump files, 356
encrypting dump files, 356–357
using parallelism to reduce elapsed time, 356
Data Definition Language. See DDL
dynamic performance views, 215–216
constraint information, 229–231
showing primary-key and foreign-key relationships, 230–231
differences in schemas, displaying, 238–241
foreign-key columns not indexed, 228–229
logical and physical structures for, 218–220
object dependencies, displaying, 236–238
displaying granted roles, 232
displaying object privileges, 235–236
displaying system privileges, 233–235
displaying object disk-space usage, 224–225
displaying table row counts, 225–226
viewing accessible tables, 224
currently connected user, 220–221
currently executing SQL, 222
user accounts in database, 223
users currently logged in, 221–222
Data Manipulation Language (DML), 123, 133, 174, 194, 214, 387, 451
cloning users, 333
compressing output, 336
creating consistent exports, 329–330
creating daily DDL file, 336
encrypting data, 337
importing when objects already exist, 330–331
reusing output file names, 335
specifying additional dump files, 335
suppressing log file, 333
using parallelism, 334
estimating size of export jobs, 317
exporting and importing tablespaces and datafiles, 321–323
changing segment and storage attributes, 322–323
changing size of datafiles, 323
exporting tablespace metadata, 322
specifying different datafile paths and names, 322
filtering data and objects, 324–328
excluding objects from export files, 325–326
excluding objects from imports, 328
excluding statistics, 327
exporting percentage of data, 325
exporting table, index, constraint, and trigger DDL, 327–328
including objects in imports, 328
including only specific objects in export files, 327
interactive command mode, 313–316
attaching to running jobs, 314–315
stopping and restarting jobs, 315
terminating jobs, 316
mapping to exp utility, 341–342
mapping to imp utility, 343–344
listing contents of dump files, 317
data-dictionary views, 338–339
Data Pump log file, 338
database alert log, 339
interactive command-mode status, 340
operating-system utilities, 340
status table, 339
parameter file, 316
directly across networks, 319–320
Data Recovery Advisor, 512–515
suggesting corrective action, 513–514
data transfer element (DTE), 567
Database Configuration Assistant (dbca), 25, 45–46
DATABASE DEFAULT TABLESPACE statement, 35
database point-in-time recovery (DBPITR), 449, 529
databases
alert log, 339
directories, creating, 310–311
flashing back, 454
loading CSV files into, 347–350
creating directory objects and granting access, 348
creating external tables, 348–349
loading regular tables from external tables, 349–350
viewing external-table metadata, 349
noarchivelog-mode. Seenoarchivelog-mode databases
starting and stopping, 583–586
datafiles. See alsotablespaces
creating required directories for, 537
exporting and importing, 321–323
changing segment and storage attributes, 322–323
changing size of datafiles, 323
exporting tablespace metadata, 322
specifying different datafile paths and names, 322
renaming to reflect new directory locations, 539–540
restoring from backups, 442–443
toggling offline and online, 83–85
updated, 440
DATAFILES parameter, 343
DATA_LARGE tablespace, 136
DATA_SMALL tablespace, 136
DATE data type, 137, 149, 275, 283
_DATE variable, SQL*Plus, 55
DATE_ID column, 375
DB setting, AUDIT_TRAIL parameter, 627
db_1 directory, 13
DBA/ALL/USER_BASE_TABLE_MVIEWS view, 364
DBA/ALL/USER_CONSTRAINTS view, 179, 229
DBA/ALL/USER_INDEXES view, 179, 219
DBA/ALL/USER_IND_EXPRESSIONS view, 178, 228
DBA/ALL/USER_IND_PARTITIONS view, 219, 287
DBA/ALL/USER_IND_SUBPARTITIONS view, 287
DBA/ALL/USER_INTERNAL_TRIGGERS view, 390
DBA/ALL/USER_LOBS view, 248, 262
DBA/ALL/USER_MVIEW_AGGREGATES view, 364
DBA/ALL/USER_MVIEW_ANALYSIS view, 364
DBA/ALL/USER_MVIEW_COMMENTS view, 364
DBA/ALL/USER_MVIEW_DETAIL_PARTITION view, 364
DBA/ALL/USER_MVIEW_DETAIL_RELATIONS view, 364
DBA/ALL/USER_MVIEW_DETAIL_SUBPARTITION view, 365
DBA/ALL/USER_MVIEW_JOINS view, 364
DBA/ALL/USER_MVIEW_KEYS view, 364
DBA/ALL/USER_MVIEW_LOGS view, 364
DBA/ALL/USER_MVIEW_REFRESH_TIMES view, 364
DBA/ALL/USER_MVIEWS view, 364–365, 383, 400–401
DBA/ALL/USER_PART_COL_STATISTICS view, 287
DBA/ALL/USER_PART_HISTOGRAMS view, 287
DBA/ALL/USER_PART_INDEXES view, 219, 287
DBA/ALL/USER_PART_KEY_COLUMNS view, 287
DBA/ALL/USER_PART_TABLES view, 219, 287
DBA/ALL/USER_REFRESH view, 365, 408
DBA/ALL/USER_REGISTERED_MVIEWS view, 364
DBA/ALL/USER_SEGMENTS view, 263, 265
DBA/ALL/USER_SUBPART_COL_STATISTICS view, 290
DBA/ALL/USER_SUBPART_HISTOGRAMS view, 287
DBA/ALL/USER_SUBPARTITION_TEMPLATES view, 287
DBA/ALL/USER_SUBPART_KEY_COLUMNS view, 287
DBA/ALL/USER_SYNONYMS view, 202–203
DBA/ALL/USER_TABLES view, 219, 226
DBA/ALL/USER_TAB_PARTITIONS view, 219, 287
DBA/ALL/USER_TAB_SUBPARTITIONS view, 287
DBA/ALL/USER_TUNE_MVIEW view, 365
DBA/ALL/USER_USERS view, 219
DBA/ALL_LOBS view, 262
DBA/ALL_USER_PART_LOBS view, 252
DBA privilege, 485
DBA static view, 214
DBA/USER/ALL_SEGMENTS view, 249
DBA/USER_EXTENTS view, 219
DBA/USER_FREE_SPACE view, 219
DBA/USER_SEGMENTS view, 219
DBA/USER_TABLESPACES view, 219
DBA_AUDIT_OBJECT view, 628
DBA_AUDIT_POLICIES view, 633
DBA_COL_PRIVS view, 234
DBA_DATA_FILES view, 219
DBA_DATAPUMP_JOBS view, 338
DBA_DATAPUMP_SESSIONS view, 344
DBA_DEPENDENCIES view, 236–237
DBA_ENCRYPTED_COLUMNS view, 259
DBA_EXTENTS view, 214
DBA_EXTERNAL_LOCATIONS table, 349
DBA_EXTERNAL_TABLES view, 349
DBA_FGA_AUDIT_TRAIL view, 634
DBA_PART_TABLES view, 288
DBA_REGISTERED_MVIEWS view, 407
DBA_ROLE_PRIVS view, 130, 232, 234
DBA_SCHEDULER_JOBS view, 571, 574
DBA_SEGMENTS view, 214
DBA_SEQUENCES view, 208
dba_setup file, 69
DBA_SYNONYMS view, 203
DBA_SYS_PRIVS view, 128, 233–234
DBA_TABLES view, 224
DBA_TABLESPACES view, 247
DBA_TAB_PARTITIONS view, 288
DBA_TAB_PRIVS view, 234
DBA_USERS_WITH_DEFPWD view, 113
dbca (Database Configuration Assistant), 25, 45–46
dbca.rsp file, 45
DB_CREATE_FILE_DEST parameter, 80, 499–500
DB_EXTENDED setting, AUDIT_TRAIL parameter, 627
DB_FLASHBACK_RETENTION_TARGET parameter, 455
--dbid option, 564
db_install.rsp file, 10
DBMS_ADVISOR.TUNE_MVIEW procedure, 365
DBMS_AUDIT_MGMT package, 632
DBMS_BACKUP_RESTORE package, 459
DBMS_DATAPUMP package, 308
DBMS_FGA package, 633
DBMS_FILE_TRANSFER built-in PL/SQL package, 85
DBMS_METADATA package, 140, 150, 188, 199, 203, 308, 317, 384
DBMS_METADATA view, 209
DBMS_METADATA.GET_DDL function, 199
DBMS_MVIEW package, 367–368, 371, 373, 380–382, 396, 407
DBMS_MVIEW, vs. DBMS_REFRESH, 409–410
DBMS_REDEFINITION package, 261, 290
DBMS_REFRESH, vs. DBMS_MVIEW, 409–410
DBMS_SCHEDULER package, 570, 573–574
DBMS_SPACE package, 155
DBMS_SPACE statement, testing for space below high-water mark with, 156–159
DBMS_SPACE.SPACE_USAGE package, 266–267
DBMS_UNDO_ADV package, 621
--dbname option, 564
DB_NAME parameter, 221
dbname parameter, 564
DB_NAME variable, 543
dbora service, 584
DBPITR (database point-in-time recovery), 449, 529
DB_RECOVERY_FILE_DEST parameter, 424–425, 459, 525
DB_RECOVERY_FILE_DEST_SIZE parameter, 424, 459
DB_SECUREFILE setting, 247
dbshut script, 585
dbstart script, 585
dbstart utility, 27
dbstop utility, 27
DB_UNIQUE_NAME parameter, 221
DB_WRITER_PROCESSES parameter, 105
D_COMPANIES table, 237
D_CUSTOMERS table, 181
D_DATE_DTT column, 275
D_DATE_ID column, 274–275, 289, 293, 300, 302
DDL (Data Definition Language)
creating file, 336
displaying for tables, 150–151
DDL_LOCK_TIMEOUT parameter, 147, 189
decision support system (DSS), 278
DEDUPLICATE clause, 252
deduplicatingSecureFile LOBs, 257–258
DEDUPLICATION column, DBA/ALL_LOBS view, 263
DEFAULT DIRECTORY clause, 348
DEFAULT list, 279
DEFAULT partition, 292
DEFAULT profile, 119, 124–126, 128, 589
DEFAULT_PWD$ view, 113
deferred segment creation, of tables, 141–142
DEFERRED_SEGMENT_CREATION parameter, 142
DEGREE parameter, 226
del command, 47
--del option, 584
DELETE NOPROMPT OBSOLETE command, 472, 482
DELETE OBSOLETE command, 472
delete statement, 72, 140, 153–154, 169, 299, 399
DELETE statement, removing data from tables with, 153
deleting Oracle Scheduler jobs, 574
DELIMITED BY parameter, 353
dep_dyn_<owner>.sql script, 237
dep_dyn_<owner>.txt file, 238
DEPT_ID column, 162, 164–165, 196
DEPTREE_FILL procedure, 238
DESCRIBE command, 111
DESCRIBE statement, 391
DESTROY parameter, 343
DESTROY procedure, DBMS_REFRESH package, 411
DETAIL clause, 513
DETERMINISTIC keyword, 177
/dev/null file, 600
df command, 13
diagnostic repository, and OFA, 4
DIAGNOSTIC_DEST parameter, 537, 598
DICT_COLUMNS view, 217
DICTIONARY view, 217
differences mode, watch utility, 605
DIRECT parameter, 342
directories, granting access to, 311
directory objects, creating and granting access, 348
DIRECTORY option, 317
DIRECTORY parameter, 311
DISABLE procedure, 573
DISABLE ROW MOVEMENT clause, 290
DISABLE STORAGE IN ROW clause, 255
DISABLE_POLICY procedure, 634
disabling
Oracle Scheduler jobs, 573
DISCARDFILE parameter, 353
dis_dyn.sql file, 168
disk fullness, troubleshooting, 597–600
locating alert log and trace files, 597–599
DISPLAY not set error, 12
DISPLAY variable, 12, 19, 22, 25
DISPLAY variable, remotely installing Oracle, 21–22
displaying size of tablespaces, 81–82
DML (Data Manipulation Language), 123, 133, 174, 194, 214, 387, 451
DML usage, troubleshooting auditing, 627–628
documentation, for data dictionary, 216–218
Domain index type, 173
DOMAIN_NAMES table, 238
DOSKEY utility, 58
DOWNGRADE parameter, 43
DP_DIR directory, 313
DPUMP_DIR2 directory, 317
DROP ANY TABLE privilege, 151
DROP CATALOG command, 485
DROP DATABASE command, 47
DROP DATABASE statement, 46
DROP INDEX statement, 191
DROP MATERIALIZED VIEW command, 384
DROP MATERIALIZED VIEW LOG ON statement, 395
DROP MATERIALIZED VIEW statement, 378, 407, 411
DROP operation, 149
DROP SEQUENCE statement, 209
DROP SUBPARTITION clause, 297
DROP SYNONYM statement, 204
DROP TABLE statement, 151–153, 160, 385
DROP TABLESPACE … INCLUDING CONTENTS AND DATAFILES statement, 80
DROP TABLESPACE statement, 79
DROP UNUSED clause, 150
DROP VIEW statement, 200
drop_dyn.sql script, 395
DROP_JOB procedure, 574
dropping
indexes, 191
sequences, 209
synonyms, 204
tables, 151
views, 200
DROP_POLICY procedure, 634
D_SOURCE_ID column, 175
D_SOURCES table, 175
DSS (decision support system), 278
DTE (data transfer element), 567
du utility, 603
dump files
compressing, 356
listing contents of, 317
specifying, 335
dump/trace files, creating required directories for, 537
DUPLICATE DATABASE command, 534
dynamic performance views, for data dictionary, 215–216
E
e variable, bash shell, 53
ECHO parameter, 481
edds command, 561
EDIT command, 55
emacs utility, 59
EMP table, 164–165, 196, 628, 633
EMP_DEPT_V view, 197
EMP_ID column, 196
EMP_NAME column, 177
ENABLE procedure, DBMS_SCHEDULER package, 573
ENABLE QUERY REWRITE clause, 380
ENABLE ROW MOVEMENT clause, 290
ENABLE STORAGE IN ROW clause, 255
ENABLED column, 573
enabling
constraints, 168
Oracle Scheduler jobs, 573
ENCRYPT clause, 258
ENCRYPT column, DBA/ALL_LOBS view, 263
ENCRYPT keyword, 259
ENCRYPTED_COLUMNS_ONLY option, 337
encrypting
columns, 376
data, 337
encryption, configuring, 477
ENCRYPTION option, ACCESS PARAMETERS clause, 356
ENCRYPTION parameter, 337, 359
ENGDEV database, 403
environment configuration, 51–70
customizing command prompt, 52–53
customizing SQL prompt, 54
listing command history, 58–59
pressing Ctrl+P and Ctrl+N, 58
scrolling with arrow keys, 58
searching in reverse, 59
scripts for common tasks
configuring startup file, 69
creating directories for, 68–69
lock.sql script, 67
login.sql script, 66
shortcuts for frequently used commands
setting manually, 26
setting with oraset script, 27–29
ESTIMATE_ONLY parameter, 317
ESTIMATE_PERCENT parameter, 226
/etc/cron.d directory, 576–577
/etc/cron.daily directory, 576
/etc/cron.hourly directory, 576
/etc/cron.monthly directory, 576
/etc/cron.weekly directory, 576
/etc directory, 12, 14, 26, 28
/etc/group file, 5
/etc/init.d/crond script, 575–576
/etc/init.d directory, 583
/etc/init.d/observiced start command, 568
/etc/init.d/observiced stop command, 568
/etc/init.d/OracleBackup start command, 568
/etc/init.d/OracleBackup stop command, 568
/etc/passwd file, 6
/etc/rc.d directory, 584
/etc/rc.d/init.d/OracleBackup start command, 568
/etc/rc.d/init.d/OracleBackup stop command, 568
/etc/rc.d/init.d/sshd script, 586
/etc/rc.d/rc5.d/S55sshd script, 586
/etc/rc.d/rc<N>.d directory, 586
exa.csv.gz file, 358
EXA_INFO table, 350
EXCEPTIONS INTO clause, 169
EXCEPTIONS table, 169
EXCLUDE clause, 326
EXCLUDE command, 493
EXCLUDE parameter, 324, 326–328
EXCLUDE=CONSTRAINT parameter, 343
EXCLUDE=CONSTRAINTS parameter, 342
EXCLUDE=GRANT parameter, 342
EXCLUDE=INDEXES parameter, 343
EXCLUDE=OBJECT_GRANT parameter, 343
EXCLUDE=TRIGGER parameter, 342
EXECUTE statement, 298
EXEC[UTE] statement, 396
EXECUTING state, 340
EXECUTING status, 338
EXLUDE parameter, 325
exp process, 312
exp utility, mapping to, 341–342
exp01.dmp file, 334
exp02.dmp file, 334
expd method, 291
expdp job, 311
EXPDP method, 290
expfull.sql file, 317
EXPLAIN_MVIEW procedure, DBMS_MVIEW package, 381–383, 385
EXPLAIN_REWRITE procedure, DBMS_MVIEW package, 380
exp.log file, 309
export files
excluding objects from, 325–326
including only specific objects in, 327
export jobs, estimating size of, 317
exporting
creating database directories, 310–311
granting access to directories, 311
percentage of, 325
taking exports, 311
table, index, constraint, and trigger DDL, 327–328
tablespaces and datafiles, 321–323
changing segment and storage attributes, 322–323
changing size of datafiles, 323
exporting tablespace metadata, 322
specifying different datafile paths and names, 322
EXTENT MANAGEMENT LOCAL clause, 73
EXTERNAL job type, 571
external table type, 134
loading CSV files into databases, 347–350
creating directory objects and granting access, 348
loading regular tables from, 349–350
viewing external-table metadata, 349
performing advanced transformations, 350–352
preprocessing, 357
unloading and loading data using, 354–357
compressing dump files, 356
encrypting dump files, 356–357
using parallelism to reduce elapsed time, 356
viewing text files from SQL, 352–353
F
F command, 606
F constraint code, 230
-f switch, 600
FAILED_LOGIN_ATTEMPTS setting, 125
fast-recovery area. See FRA
fast recovery area, RMAN backups of, 493
F_DOWN_DOM_FK9 index, 190
FGA (fine-grained auditing), 633
FGA_LOG$ table, 634
FILE# column, V$DATAFILE view, 521
files, determining which need to be backed up, 431
FILESIZE command, 313
FILESIZE parameter, 343
filtering data and objects, 324–328
excluding objects from export files, 325–326
excluding objects from imports, 328
excluding statistics, 327
exporting percentage of data, 325
exporting table, index, constraint, and trigger DDL, 327–328
including objects in imports, 328
including only specific objects in export files, 327
fine-grained auditing, 633–635
fine-grained auditing (FGA), 633
FIRST_CHANGE# column
V$ARCHIVED_LOG view, 533
V$LOG view, 533
V$LOG_HISTORY view, 533
FIRST_NAME column, 182
FIXED parameter, 353
FLASHBACK statement, 453
FLASHBACK TABLE statement, 152–153, 451
FLASHBACK TABLE TO BEFORE DROP statement, 152, 451–452
FLASHBACK_SCN parameter, 329–330, 335, 342
FLASHBACK_TIME parameter, 329–330, 342
flashing back databases, 454
FLASHBACK TABLE TO BEFORE DROP, 452
to SCN, 453
to timestamps, 453
--folow option, 565
FOR DROP clause, 84
FORCE command, 519
FORCE LOGGING mode, 144
FORCE parameter, 43
foreign-key constraints, creating, 164–165
FORMAT column, DBA/ALL_LOBS view, 263
FRA (fast-recovery area), 425–426
using for archive log files, 424
using FRA for archive log files, 425
free column, 605
free utility, 603
FREEPOOLS column, DBA/ALL_LOBS view, 262
FREE_SPACE (MB_FREE) column, 624
FROM SEQUENCE clause, 525
FROM_LOCATION variable, 12
FROMUSER parameter, 343
FS1 parameter, 157
FS2 parameter, 157
FS3 parameter, 157
FS4 parameter, 157
F_SALES table, 169, 276, 297–300
F_SALES_IDX2 index, 302
F_SHIPMENTS table, 181
full backup, vs. incremental level=0, 490
FULL mode, 326
FULL parameter, 322
function-based indexes, 177–178
Function index type, 173
function, shortcuts for commands using, 56–57
FUZZY column, V$DATAFILE_HEADER view, 530
-g option, 5
G
GET_DDL function, 150, 188, 199, 203
GET_DDL procedure, 151
GLOBAL keyword, 303
Global partitioned index type, 173
--go option, 561
GO option, 561
GRANT ALL statement, 129
GRANT ANY PRIVILEGE privilege, 128
GRANT statement, 128, 224, 235, 311
GRANT SYSDBA statement, 40
grep command, 94
groupadd command, 5
groupdel command, 6
grouping of privileges, for users, 130
GROUPING_APP user, 129
groupmod command, 6
groups
creating system, before installation, 5–6
altering, 409
creating, 408
DBMS_MVIEW vs. DBMS_REFRESH, 409–410
determining, 410
guidelines, for indexes, 173–175
gunzip utility, 358
H
H command, 606
H constraint code, 230
-h option, 564
h variable, bash shell, 53
Hash cluster index type, 173
hash partition type, 272
hash, partitioning tables by, 280
HEADER clause, 517
heap-organized tables, creating, 135–138
HEERA tablespace, 323
shrinking table, 157
testing for space below
HIGH_VALUE column, 275, 280, 283
history command, 58
\HKEY_LOCAL_MACHINE\SoftwareOracle key, 12
HOME/.bashrc file, 56
HOME/.bashrc script, 582
HOME/bin directory, 60–62, 64–65, 68–69
HOME/bin/log directory, 68
HOME directory, 3
/home directory, 14
/home/oracle directory, 3, 14, 559
/home/oracle/oraInventory directory, 3
/home/oracle/scripts directory, 66, 263
/home/oracle/scripts/rmanbkup.sh file, 562–563
/home/oracle/temp directory, 559
HOME/scripts directory, 54, 66–69
HOME variable, 6
HOMEPATH varaible, 602
--host option, 564
HOST parameter, 221
altering databaseinto backup mode, 432
altering databaseout of backup mode, 432
backing up any archive-redo logs generated during backup, 433
backing up control file, 433
copying datafiles with OS utilities, 432
determining where to copy backup files, 431
determining which files need to be backed up, 431
ensuring that database is in archivelog mode, 431
maximum sequence number of online-redo logs, 433
note maximum sequence number of online-redo logs, 431
hotback.sql script, 434
h.par file, 328
I
id column, 605
IDENTIFIED BY clause, 122
IDLE_TIME setting, 127
IGNORE parameter, 343
ignore=y option, 331
image copies
vs. backup sets, RMAN backups, 490–491
IMMEDIATE clause, 414, 416, 429, 465
IMMEDIATE option, 315
imp utility, mapping to, 343–344
impdp command, 312
impdp process, 317
importing
tablespaces and datafiles, 321–323
changing segment and storage attributes, 322–323
changing size of datafiles, 323
exporting tablespace metadata, 322
specifying different datafile paths and names, 322
import.log file, 338
imp.par file, 316
in column, 605
INCLUDE parameter, 324–325, 327–328
INCLUDE=INDEXES parameter, 342
INCLUDING clause, 161
determining type of, 531
performing change/SCN-based recovery, 533
performing log sequenced-based recovery, 532
performing time-based recovery, 532
restoring to restore point, 533–534
INCREMENT BY setting, 205, 210
incremental backups, 475, 496–499
incrementally updating backups, 498–499
overview, 497
using block change tracking, 499
incremental level=0, vs. full backup, 490
incrementally updated backups, 475
IND$ table, 214
index-organized table (IOTs), 161
avoiding redo generation for, 183
function-based indexes, 177–178
key-compressed indexes, 182
naming standards for, 185
parallelizing index creation, 183
reverse-key indexes, 181
foreign-key columns not indexed, 228–229
displaying code to re-create, 188
dropping, 191
making unusable, 190
renaming, 188
differently than tables, 303–304
specifying tablespaces for, 186–187
INDEXFILE command, 318
indexing log columns, 392
INDEX_NAME column, DBA/ALL_LOBS view, 262
INDEX_TYPE column, 161
initialization file, configuring, 30–31
init.ora file
adding control files when using, 95–96
init<ORACLE_SID>.ora file, 29–30
--inputrequest option, 564
IN_ROW column
DBA/ALL_LOBS view, 263
USER_LOBS view, 255
INSERT /*+ APPEND */ INTO <new_part_tab> SELECT * FROM <old_tab> method, 290
INSERT AS SELECT statement, 350
insert statement, 72, 141, 194, 206, 286, 351, 399
insertvol command, 567
checking operating system configuration, 6–7
with copy of existing installation, 12–15
downloading, 7
remotely with graphical installer, 18–23
copying installation media to remote server, 20
ensuring DISPLAY variable is set, 21–22
executing runInstaller utility, 22
installing required software on local PC, 19
logging in to remote computer, 21
running xhost command, 21
troubleshooting, 22
Oracle Database 10g scenario, 8–10
Oracle Database 11g scenario, 10–11
INSTANCE_NAME parameter, 221
instances, shutting down, 416–417
INSTEAD OF clause, for views, 197–198
INST_ID column, 216
inst.loc file, \HKEY_LOCAL_MACHINE\SoftwareOracle key, 12
interactive command mode, 313–316
attaching to running jobs, 314–315
status, 340
stopping and restarting jobs, 315
terminating jobs, 316
interfaces, terminology of OSB, 547
INTERVAL calculation, 409
INTERVAL clause, 282
interval partition type, 272
INTERVAL(NUMTOYMINTERVAL(1, ‘YEAR’)) clause, 283
INV synonym, 201
INV table, 155, 157, 201, 325, 354–355, 387, 395, 617, 633
INV user, 333
INV view, 200
INV1 tablespace, 80
inv2.par file, 325
INV_CLOB tablespace, 253
INV_DW table, 355
INV_DW user, 333
INVEN table, 331
inventory directory, for OFA, 3
inventory.xml file, 12, 14–15, 17
INV_ET table, 355
INV_IDX1 index, 181
INV_IDX_MED tablespace, 187
INV_IDX_SMALL tablespace, 187
INV_IMAGE column, 253
INVISIBLE clause, 184
invisible indexes, creating, 183–185
from existing index, 184
for third-party applications, 184–185
INV_MGMT schema, 201
INV_MGMT_APP user, 129
INV_MGMT.EMP table, 628
INV_MGMT.INV table, 201
INV_OLD view, 200
inv.par file, 324
INVPRDRMAN.log file, 482
INV_SEQ sequence, 205
INV_UIDX1 constraint, 178
iostat utility, 603
IOTs (index-organized table), 161
IP_ADDRESS parameter, 221
ISDATE function, 149
ISDBA parameter, 221
ISNUM function, 148
-j option, 565
J
j variable, bash shell, 53
JOB_CLASS parameter, 571
jobs
export, estimating size of, 317
data-dictionary views, 338–339
Data Pump log file, 338
database alert log, 339
interactive command-mode status, 340
operating-system utilities, 340
status table, 339
stopping and restarting, 315
terminating, 316
JOB_TYPE parameter, 571
JR_DBA role, 130
-k option, 566
K
KEEP_DUPLICATES clause, 258
key-compressed indexes, 182
KILL_JOB command, 308, 313–314, 316
L
l variable, bash shell, 53
large files, RMAN backups of, 495
large objects. See LOBs
LAST_DDL_TIME column, 224
LAST_NAME column, CUSTOMERS table, 182, 332
LAST_REFRESH_DATE column, 400–401, 406, 409
LD_LIBRARY_PATH variable, 30
LEFT OUTER JOIN clause, 229
mapping to exp utility, 341–342
mapping to imp utility, 343–344
LELLISON user, 130
--level option, 565
LEVEL pseudo-column, 166
LIST BACKUP command, 504, 525, 538, 557
LIST BACKUP SUMARY command, 504
LIST FAILURE command, 512, 514
list partition type, 272
list, partitioning tables by, 279–280
listener
configuring and implementing, 37–39
starting and stopping, 583–586
listener.log file, 588–589, 599–600
loading
BLOB, 265
loadvol command, 563
LOB column, 253
LOB_DATA tablespace, 253
adding column, 253
BasicFile, 246
caching, 254
SecureFile LOB column, 250–251
in specific tablespace, 249–250
loading BLOB, 265
moving column, 253
migrating BasicFiles to, 260–262
and storage of in row, 255–256
types of, 244
LOB…STORE AS clause, 249
LOCAL clause, 300
Local partitioned index type, 173
LOCAL type, 300
LOCATION clause, 348
LOCATIONS table, 179
lock_chk.bsh script, 591
locking, troubleshooting, 616–618
locks, obtaining before modifying table, 147
lock.sql script, 67
log files
Data Pump, 338
suppressing, 333
.log files, oraInventory/logs directory, 12
--log option, 565
log sequenced-based recovery, 532
LOG_ARCHIVE_DEST_1 parameter, 426, 525, 537, 588
LOG_ARCHIVE_DEST_N parameter, 422–425, 463, 465–466, 480
LOG_ARCHIVE_FORMAT parameter, 422, 425, 480
LOG_ARCHIVE_MIN_SUCCEED_DEST parameter, 423
LOGFILE parameter, 342–343, 353
LOGFILE=[directory_object:]logfile_name parameter, 357
LOGGING clause, 77
LOGGING column, DBA/ALL_LOBS view, 262
logging history, for Oracle Scheduler jobs, 572
logging on, as different user, 120–122
logging output, from RMAN backups, 501–503
capturing with script command, 502–503
capturing with tee, 502
SPOOL LOG command, 503
viewing most recent RMAN output, 503
logical structure, vs. physical structure of database, 218–220
LOGICAL_READS_PER_CALL setting, 127
LOGICAL_READS_PER_SESSION setting, 127
login.sql file, 54
logon/logoff events, troubleshooting auditing, 628–629
LONG column, 383
LONG data type, 199, 244, 274, 280
LONG RAW data type, 244
LONG variable, 140, 199, 209, 280, 383–384
lsbackup command, 561
lsclass command, 552
lsds command, 559
lsmf command, 555
lsnrctl utility, 38
lspiece command, 558
lssched command, 561
lsssel command, 556
-m option, 565
M
MAILTO variable, 581
MAKE procedure, DBMS_REFRESH package, 408
man tar command, 14
man top command, 608
mapping
materialized views. See MVs
MAX function, 274
Maximum Protection Mode, 100
MAXOPENFILES parameter, 478
MAXPIECESIZE parameter, 478
MAXSETSIZE parameter, 477
MAXVALUE option, 205
MAXVALUE parameter, 274
MAXVALUE partition, 272–273, 275
MB_FREE (FREE_SPACE) column, 624
media recovery, testing, 517–518
MEMBER column, V$LOGFILE view, 98
memory_max_target parameter, 31
memory_target parameter, 31
MESSAGE_TEXT LIKE command, 602
metadata
external-table, 349
tablespace, 322
METADATA_ONLY option, 331, 333, 337
MHURD user, 130
MINEXTENTS parameter, 154
MINVALUE option, 205
MISSING FIELD VALUES ARE NULL parameter, 353
mkdev command, 567
mkds command, 559
MKSCHED command, 562
mkssel command, 556
mntlist variable, 65
modifying
Oracle Scheduler jobs, 572
adding columns, 147
obtaining lock, 147
renaming columns, 149
renaming tables, 147
users, 122
data-dictionary views, 338–339
Data Pump log file, 338
database alert log, 339
interactive command-mode status, 340
operating-system utilities, 340
status table, 339
MOS (My Oracle Support), 10
mount mode, 442
restoring tablespaces while in, 520
starting up database in, 538
starting up databases in, 418
MOUNT parameter, 43
moving
for LOBs, 253
redo log files, 107
mpstat utility, 603
M_ROW$$ column, 375
--msgno option, 565
MTS tablespace, 158
mvcount_dyn.sql script, 394
mvdata01.dbf file, 521
MVs (materialized views), 361–411
altering parallelism, 389
building on prebuilt tables, 377–378
compressing, 376
complete-refreshable MV, 365–369
creating for query-rewrite, 380
creating remote refreshes, 403–407
determining references to log, 406–407
viewing base-table information, 405–406
encrypting columns, 376
altering, 409
creating, 408
DBMS_MVIEW vs. DBMS_REFRESH, 409–410
determining, 410
refresh, adding to, 410
refresh, dropping, 411
refresh, removing from, 411
refreshing, 409
modifying base-table DDL and propagating to, 385–388
determining progress of, 401
monitoring real-time refresh progress, 401–402
viewing last refresh times, 400–401
partitioning, 375
referencing useful views, 364–365
automating refreshes using shell script and scheduling utilities, 397–398
manually, from SQL*Plus, 396
performing, 399
specifying tablespace for, 374
toggling redo logging on, 388–389
unpopulated, creating, 378
checking row count of, 393–394
indexing log columns, 392
materialized, dropping, 395
materialized, moving, 394
shrinking space in, 393
viewing space used by, 392–393
My Oracle Support (MOS), 10
mycron.txt file, 580
mydb.rsp file, 46
-n option, 607
N
variable, bash shell, 53
NAME column, V$DATAFILE view, 521
NAME_CLAUSE option, EXCLUDE parameter, 326
names, datafile, 322
naming standards, for indexes, 185
national character large object (NCLOB), 243–244
NCLOB (national character large object), 243–244
NDMP (Network data management protocol), 548
nested table type, 134
net start observiced command, 568
net stop observiced command, 568
netstat utility, 603
Network data management protocol (NDMP), 548
network files directory, for OFA, 4
NETWORK_LINK parameter, 320, 329, 333
NEVER REFRESH clause, 379
newname.sql script, 539
<new_part_tab> method, 290
NEXT parameter, 398
NEXTVAL pseudo-column, 206
NI column, 607
NID utility, 543
nline-redo logs, 433
NLS_DATE_FORMAT parameter, 221
NLS_DATE_FORMAT, setting, 488
NLS_DATE_FORMAT variable, 481, 487, 532
no rows selected message, 179
noarchivelog-mode databases, 414–420
copies of files, 416
determine locations and names of files, 415
with online-redo logs, 416–417
restarting databases, 416
shutting down instances, 416
space required, 415
without online-redo logs, 417–418
NOBADFILE parameter, 353
NOCACHE option, 205
NOCACHE setting, 254
NOCOMPRESS clause, 257
NOCYCLE option, 205
NODISCARDFILE parameter, 353
NOLOGFILE parameter, 333, 353, 357
NOLOGGING clause, 76–77, 144, 146, 183, 278
NOLOGGING feature, 144
NOLOGGING operation, 505
NOLOGGING option, 145, 290, 388
NOMAXVALUE option, 205
NOMINVALUE option, 205
NOMOUNT parameter, 43
NOMOUNT state, 526
NONE setting, AUDIT_TRAIL parameter, 627
NOORDER option, 205
NORESETLOGS clause, 88
NORMAL parameter, 44
NO_SPACE_CNT column, 620
NOT NULL constraint, 138, 166–167
NOVALIDATE clause, 169
NUMBER data type, partitioning tables by range using, 273–275
NUMBER field, 275
NUM_ROWS column, 226
O
O command, 606
-o command, 59
O constraint code, 230
OB_DEVICE parameter, 557
OB_ENCRYPTION parameter, 557
object dependencies, displaying, 236–238
object privileges, for users, 129
object table type, 134
OBJECT_CONSISTENT parameter, 342
OBJECT_PATH column, 325
objects
excluding from export files, 325–326
excluding from imports, 328
including in export files, 327
including in imports, 328
OB_MEDIA_FAMILY parameter, 555, 557
OB_MEDIA_FAMILY variable, 556
OB_RESOURCE_WAIT_TIME parameter, 557
OB_RESTORE_DEVICE parameter, 557
obtar restore, file system restore with OSB, 563
obtool utility, 547–548, 551–552, 558–559, 562–564, 566
OEM (Oracle Enterprise Manager), 564
OFA (Optimal Flexible Architecture), 1–4
base directory, 3
and diagnostic repository, 4
inventory directory, 3
network files directory, 4
offline files, RMAN backups of, 494–495
OFFLINE FOR DROP clause, 84
oinstall group, 5
<old_tab> method, 290
OLTP (online transaction processing), 31, 143, 173, 218, 270, 378
OMF (Oracle Managed File), 80
ON COMMIT clause, 378–379, 399
ON COMMIT table, 379
on-demand backups, file system backup with OSB, 561
ON DEMAND clause, 379
on demand, partitioning of tables, 282–284
ON keyword, 393
ON PREBUILT TABLE clause, 384–387
online and offline
toggling of tablespaces, 83–85
copying files back from backups, 417
maximum sequence number of, 431
opening database with OPEN RESETLOGS clause, 418
setting new location for, 541–542
shutting down instances, 416–417
starting up databases, 417
starting up databases in mount mode, 418
online transaction processing (OLTP), 31, 143, 173, 218, 270, 378
opatch utility, 18
open cursor, troubleshooting, 618–619
OPEN parameter, 43
OPEN READ ONLY parameter, 43
OPEN RECOVER parameter, 43
OPEN RESETLOGS clause, 98, 417–418
OPEN RESETLOGS command, 418, 519, 528, 542
OPEN_CURSORS parameter, 618
oper group, 5
operating-system utilities, 340
Optimal Flexible Architecture. See OFA
OPTIMAL_LOGFILE_SIZE column, V$INSTANCE_RECOVERY view, 103
OPTIMIZER_USE_INVISIBLE_INDEXES parameter, 184
ORA-00312 error, 418
ORA-00313 error, 418
ORA-00376: file can't be read at this time error, 78
ORA-01555 error, 620
ORA-01567 error, 106
ORA-01578 error, 77
ORA-01623 error, 106
ORA-01652 error, 624
ORA-02449: unique/primary keys in table referenced by foreign keys error, 78
ORA-19511 error, 553
ORA-27037: unable to obtain file status error, 87
ORA-43853: SECUREFILE lobs can't be used in non-ASSM tablespace error, 250
/ora01/app/oracle directory, 3, 598
/ora01/archtemp directory, 526
/ora01/backups directory, 599
/ora01/dbfile/O11R2 directory, 32, 416–417
/ora01/orainst directory, 7
ORA_01555_CNT column, 620
/ora02/dbfile/O11DEV directory, 537
/ora02/dbfile/O11R2 directory, 32
/ora02/fra directory, 424
/ora02/oraarch/O11DEV directory, 537
/ora02/oraarch/O11R2 directory, 422
/ora02/rman/O11DEV directory, 538
oracle account, 6
Oracle Advanced Compression option, 143, 257
Oracle Advanced Security Option, 258
/oracle/app/oracle directory, 537
/oracle/app/oracle/product/11.2.0.1 directory, 13
ORACLE column, 351
Oracle Enterprise Manager (OEM), 564
Oracle Managed File (OMF), 80
Oracle Recovery Manager. See RMAN
Oracle Scheduler jobs, 570–574
copying, 573
deleting, 574
disabling, 573
enabling, 573
logging history for, 572
modifying, 572
Oracle Secure Backup. See OSB
Oracle Technology Network (OTN), 5, 100
Oracle Universal Installer (OUI), 1
oracle user, 4, 6, 32, 68, 607
ORACLE_BASE/diag/rdbms/dbname/instname directory, 4
ORACLE_BASE directory, 4, 35, 599
ORACLE_BASE variable, 3, 597–598
ORACLE_DATAPUMP driver, 355, 357
ORACLE_HOME/bin/dbshut script, 583, 585
ORACLE_HOME/bin/dbstart script, 583, 585
ORACLE_HOME/bin directory, 27, 29, 458, 460
ORACLE_HOMEdatabase directory, 29–30, 39, 42
ORACLE_HOME/dbs/arch directory, 465
ORACLE_HOME/dbs directory, 29–30, 32, 39, 42, 461, 466, 468, 492
ORACLE_HOME/dbs/initE64202.ora file, 537
ORACLE_HOME/dbs/[email protected] file, 469
ORACLE_HOME directory, 4, 17, 583, 585, 597
ORACLE_HOME/lib directory, 30
ORACLE_HOME/network/admin directory, 4, 37, 41
ORACLE_HOME/oui/bin directory, 14
ORACLE_HOME/rdbms/admin directory, 214
ORACLE_HOME/rdbms/admin/spdoc.txt file, 616
ORACLE_HOME variable, 16, 18, 35, 63–64, 460, 481, 536, 582, 598
ORACLE_HOME_NAME variable, 16
ORACLE_LOADER driver, 353
ORACLE_SID variable, 29, 55, 63–64, 460, 481, 536, 543, 582, 597
ora_dba group, 41
/oradump/cbackup/O11R2 directory, 416–417
/oradump directory, 309, 311, 355
/oradump/hbackup/O11R2 directory, 431, 434, 453
oraenv utility, setting environment variables with, 27
/oraet directory, 347
/orahome/oracle/bin directory, 570
/orahome/oracle/bin/log directory, 570
/orahome/orainst/10.2.0.4 directory, 15
ORA_HOME variable, 583
orahome.tar file, 13
oraInventory/ContentsXML directory, 12, 17
oraInventory/ContentsXML/inventory.xml file, 12, 15, 17
oraInventory directory, 12, 14, 17
oraInventory/logs directory, 11–12
ORA_OWNER variable, 583
oraset script, setting environment variables with, 27–29
oratab utility, setting environment variables with, 26–27
ORDER BY clause, 612
ORDER option, 205
ORDER_ITEMS table, 284
ORDERS table, 284
_O_RELEASE variable, SQL*Plus, 55
ORGANIZATION INDEX clause, 161
OS setting, AUDIT_TRAIL parameter, 627
OS utilities, copying datafiles with, 432
OS variables, sourcing, 536
OS Watcher utility, 610
OS_AUTHENT_PREFIX variable, 116
osb-10.3.0.3.0_linux32.zip file, 549
OSB-CATALOG-DS file, 562
OSB (Oracle Secure Backup), 545–568
command-line access to, 551–552
database backup storage selector, 555
database restore with, 557–558
file system backup with, 558–561
configuring backup schedules and triggers, 560–561
configuring backup windows, 560
creating dataset files, 559
on-demand backups, 561
file system restore with, 562–563
showing job transcripts, 565–566
administrative domain and servers, 546–547
daemons, 548
interfaces, 547
users and classes, 548
virtual test devices for, 566–567
OSB_HOME/etc directory, 548
OSB_HOME/install directory, 552
OSB_HOME variable, 551
/osb_vdevices/vdrive1 directory, 567
/osb_vdevices/vdrive2 directory, 567
/osb_vdevices/vlib1 directory, 567
OS_USER parameter, 221
OTN (Oracle Technology Network), 5, 100
OUI (Oracle Universal Installer), 1
output
compressing, 336
for cron jobs, redirecting, 581
output file names, reusing, 335
OVERFLOW clause, 161
_O_VERSION variable, SQL*Plus, 55
OWNER column, DBA/ALL_LOBS view, 262
OWNER parameter, 342
P
P column, 382
P constraint code, 230
p switch, mkdir command, 32
P1_TBSP tablespace, 276
P_2008 partition, 299
p6810189_10204_Solaris-64.zip file, 15
p7695070_10204_Solaris-64.zip file, 17
PARALLEL clause, 142, 183, 356
PARALLEL feature, 335
PARALLEL parameter, 334
parallel tables, creating, 142
parallelism
altering, 389
overview, 334
using to reduce elapsed time, 356
parallelizing index creation, 183
PARALLEL_THREADS_PER_CPU parameter, 142–143, 183
parameter file, 316
PARFILE option, 316
PARMS clause, 557
PARSING_USER_ID column, V$SQLAREA view, 612
PARTIES table, 158
PARTITION BY HASH clause, 280
PARTITION BY LIST clause, 279
PARTITION BY RANGE clause, 272
PARTITION BY REFERENCE clause, for partitioning tables, 284–286
PARTITION BY SYSTEM clause, for partitioning tables, 286–287
PARTITION clause, 375
PARTITIONED column, DBA/ALL_LOBS view, 263
partitioned table type, 134
differently than tables, 303–304
maintaining partitions, 287–299
exchanging with existing table, 293–295
manipulating data within, 299
moving updated rows automatically, 289–290
renaming, 295
statistics for, 298
MVs, 375
pruning of, 304
composite partitioning, 281–282
by hash, 280
PARTITION BY REFERENCE clause, 284–286
PARTITION BY SYSTEM clause, 286–287
placing partitions into tablespaces, 276–278
on virtual column, 286
PASSWORD command, 118
password-verification function, 119
PASSWORD_GRACE_TIME setting, 125
PASSWORD_LIFE_TIME setting, 125
PASSWORD_LOCK_TIME setting, 125
PASSWORD_REUSE_MAX setting, 125
PASSWORD_REUSE_TIME setting, 125
passwords, for users
changing, 118
PASSWORD_VERIFY_FUNCTION setting, 125
PATCH_DESC column, 264
PATCH_DESC LOB column, 258
PATCH_FILE BLOB column, 265
PATCHMAIN table, 264
PATCHMAIN_NEW table, 260
PATCH_SEQ sequence, 264
patch.txt file, 264
PATH directory, 18
paths, datafile, 322
PCTFREE clause, 278
PCTSPACE option, 323
PCTUSED clause, 278
PCTVERSION column, DBA/ALL_LOBS view, 262
--pending option, 564
PERFSTAT user, 615
PFILE parameter, 43
physical structure, vs. logical structure of database, 218–220
PID column, 607
ping command, 596
ping utility, 21
PLSQL BLOCK job type, 571
PLUS ARCHIVELOG clause, 480
PR column, 607
--preauth option, 553
preprocessing external tables, 357
PREPROCESSOR clause, 358
PREPROCESSOR parameter, 353
PREVIEW clause, 514
previewing backups used for recovery, 516–517
PRIMARY KEY clause, 369
primary-key constraints, creating, 162–163
PRIVATE_SGA setting, 127
_PRIVILEGE variable, SQL*Plus, 55
privileges, for users, 128–129
grouping of, 130
object, 129
proc_count.bsh script, 592
proc.dmp file, 327
process status (ps), 340
processes, checking for too many, 591–592
PROD_SKU_ID column, 161
production accounts, checking for locked, 589–590
products.xml file, 12
--progress option, 565
pruning partitions, 304
ps command, 575
ps (process status), 340
pseudo-columns, for sequences, 205–206
PTIME column, 113
PUBLIC role, 129
pupbld.sql script, 37
PURGE RECYCLEBIN statement, 152
PURGE_LOG procedure, 572
PURGE_MVIEW_FROM_LOG procedure, DBMS_MVIEW package, 407
pwd command, 59
pwdds command, 559
Q
queries, creating tables from, 146
QUERY column, DBA/ALL/USER_MVIEWS view, 383
QUERY_REWRITE_ENABLED parameter, 380
QUIET parameter, 43
quit command, 552
R
r column, 605
R command, 606
R constraint code, 230
r option, 60
variable, bash shell, 53
RAC (Real Application Clusters), 45, 545
range partition type, 272
range, partitioning tables by, 272–278
RATE parameter, 478
raw restore, file system restore with OSB, 562–563
RCAT user, 483
READ ONLY clause, 196
read-only tables, creating, 140–141
read-only tablespaces, restoring, 520
Real Application Clusters (RAC), 45, 545
REBUILD clause, 189
RECORDLENGTH parameter, 342–343
RECOVER command, 441, 458, 511–512, 519, 525, 532
RECOVER COPY command, 498
RECOVER DATABASE command, 518, 540
RECOVER DATABASE PARALLEL command, 534
RECOVER DATABASE statement, 445, 449
RECOVER DATABASE UNTIL command, 530
RECOVER DATABASE USING BACKUP CONTROLFILE clause, 446
RECOVER DATAFILE command, 521
RECOVER DATAFILE statement, 449
RECOVER statement, 443
RECOVER TABLESPACE command, 521
RECOVER TABLESPACE UNTIL command, 530
RECOVER…TEST command, 517
backing up, 484
registering target database, 484
restoring control file using, 527–528
synchronizing, 485
versions of, 485
RECOVERY_CATALOG_OWNER role, 483
RECYCLEBIN parameter, 152
RECYCLEBIN view, 452
redo
avoiding generation of for indexes, 183
adding files to group, 106–107
adding groups, 105
controlling generation of, 76–77
determining optimal number of groups, 103–105
determining optimal size of groups, 102–103
displaying information for, 100–102
moving or renaming files, 107
online, setting new location for, 541–542
removing files from group, 107
redoNA.rdo file, 35
reference partition type, 272
REFRESH function, DBMS_REFRESH package, 409
refresh group
adding to, 410
dropping, 411
removing from, 411
REFRESH procedure, DBMS_MVIEW package, 367, 371, 396
refreshes
determining progress of, 401
monitoring real-time refresh progress, 401–402
viewing last refresh times, 400–401
MV groups, 409
determining references to log, 406–407
viewing base-table information, 405–406
automating refreshes using shell script and scheduling utilities, 397–398
manually, from SQL*Plus, 396
performing, 399
REG_IDX1 bitmap index, 180
REGISTER DATABASE command, 484
REG_MID partition, 296
REG_MID_A partition, 300
REG_MID_B partition, 296
REG_P_1 partition, 296
REG_P_2 partition, 296
REG_SALES column, 288
REG_TBSP_3 tablespace, 339
REG_WEST partition, 288
REMAP_SCHEMA parameter, 333, 343–344
REMAP_TABLE parameter, 331, 336
REMAP_TABLESPACE feature, 323
REMAP_TABLESPACE parameter, 333
REMOTE_LOGIN_PASSWORDFILE parameter, 39–40, 461
copying installation media to remote server, 20
ensuring DISPLAY variable is set, 21–22
executing runInstaller utility, 22
installing required software on local PC, 19
logging in to remote computer, 21
running xhost command, 21
troubleshooting, 22
REMOVE procedure, DBMS_JOB package, 399
removing
redo log files from groups, 107
RENAME statement, 200, 203, 209
renaming
columns, 149
database, 543
indexes, 188
partitions, 295
sequences, 209
synonyms, 203
tables, 147
tablespaces, 76
views, 200
rends command, 559
renmf command, 555
renssel command, 556
renuser command, 554
REPAIR command, 514
REPAIR FAILURE command, 514
REPEAT_INTERVAL column, 572
REPEAT_INTERVAL parameter, 571
REPLACE option, 331
REP_MV user, 403
REPORT SCHEMA command, 491, 521, 540
--requires option, 565
listing command history, 58–59
pressing Ctrl+P and Ctrl+N, 58
scrolling with arrow keys, 58
searching in reverse, 59
RES column, 607
RESETLOGS clause, 446, 448, 450
RESETLOGS_ID column, V$ARCHIVED_LOG view, 433
back to lower value, 211
setting current value of, 210
resizing redo log groups, 105–106
resource intensive SQL statements, troubleshooting, 610–615
monitoring real-time statistics, 610–611
using ASH, 615
using AWR, 614
using Statspack, 615
resource limits, for users, 126–128
RESOURCE_LIMIT parameter, 126
response files
creating databases using, 45–46
Oracle Database 10g scenario, 8–10
Oracle Database 11g scenario, 10–11
restarting databases, 416
RESTORE command, 458, 511–512, 519, 530, 532, 539, 562–563
RESTORE CONTROLFILE command, 527
RESTORE CONTROLFILE FROM AUTOBACKUP command, 528
RESTORE DATABASE CHECK READONLY command, 520
RESTORE DATABASE command, 518, 529
RESTORE DATABASE UNTIL command, 529
RESTORE DATAFILE command, 521
RESTORE DATDABASE command, 520
restore points, flashing back tables to, 453–454
RESTORE TABLESPACE command, 519
RESTORE…PREVIEW command, 516
RESTORE…VALIDATE command, 500–501, 517, 592–593
RESTORE…VALIDATE HEADER command, 517
restoring. See also OSB
RESTRICT parameter, 43
RESUMABLE_NAME parameter, 342–343
RESUMABLE_TIMEOUT parameter, 342–343
RESYNC command, 485
RETENTION column, DBA/ALL_LOBS view, 262
REUSE STORAGE parameter, 154
REUSE_DATAFILES=y parameter, 343
REUSE_DUMPFILES parameter, 335
REVERSE clause, 181
reverse-i-search utility, 59
Reverse key index type, 173
reverse-key indexes, creating, 181
reverse, searching in, 59
REVOKE statement, 128–130, 235
REWRITE_ENABLED column, 380
rm utility, 428
RMAN backup command, 570
adding backup information to repository, 495–496
checking for corruption in, 499–501
using BACKUP…VALIDATE, 501
using RESTORE…VALIDATE, 501
using VALIDATE command, 500
backup sets vs. image copies, 490–491
full backup vs. incremental level=0, 490
excluding tablespaces from, 493–494
of fast recovery area, 493
incrementally updating backups, 498–499
overview, 497
using block change tracking, 499
of large files in parallel, 495
capturing with script command, 502–503
capturing with tee, 502
SPOOL LOG command, 503
viewing most recent RMAN output, 503
offline or inaccessible files, skipping, 494–495
using LIST command, 504
using SQL, 505
setting NLS_DATE_FORMAT, 488
SHOW ALL command, 489
of spfile, 492
verifying integrity of, 592–593
RMAN (Oracle Recovery Manager)
architectural decisions, 462–478
backing up archive-redo logs, 469
configuring archive-redo logs' deletion policy, 472–473
configuring backup location and file format, 466–467
configuring Backup-Retention policy, 471–472
configuring binary compression, 476–477
configuring encryption, 477
configuring miscellaneous settings, 477–478
determining location for snapshot control file, 469–470
running client remotely or locally, 464
setting archive-redo log destination and file format, 465–466
setting autobackups of control files, 468
setting CONTROL_FILE_RECORD_KEEP_TIME initialization parameter, 471
setting degree of parallelism, 473–474
specifying backup users, 465
specifying location of autobackup of control file, 468
using backup sets or image copies, 474–475
using block-change tracking, 476
using incremental backups, 475
using incrementally updated backups, 475
using online or offline backups, 465
using recovery catalog, 470
performing block level recovery, 523–524
restoring archived redo log files, 524–526
restoring datafiles to non-default locations, 522–523
restoring entire database, 518–519
restoring read-only tablespaces, 520
restoring tablespaces, 519–520
restoring temporary tablespaces, 521
testing restore and recovery, 516–518
determining media recovery required, 510–511
determining what to restore, 511–515
using Data Recovery Advisor, 512–515
determining type of, 531
performing change/SCN-based recovery, 533
performing log sequenced-based recovery, 532
performing time-based recovery, 532
restoring to restore point, 533–534
backing up, 484
registering target database, 484
synchronizing, 485
versions of, 485
restoring and recovering to different server
copying RMAN backup to destination server, 536
creating init.ora file for database to be restored, 536–537
creating required directories for datafiles, control files, and dump/trace files, 537
creating RMAN backup on originating database, 535–536
ensuring that Oracle is installed, 536
making control file aware of location of RMAN backups, 538
opening database, 542
renaming and restoring datafiles to reflect new directory locations, 539–540
renaming database, 543
restoring control file from RMAN backup, 538
setting new location for online redo logs, 541–542
sourcing required OS variables, 536
starting up database in mount mode, 538
starting up database in NOMOUNT mode, 537
restoring control file, 527–528
segueing from decisions to action, 478–482
stopping and starting Oracle, 515–516
rman utility, 46, 458, 547, 551
rmanback.log file, 502
rman_chk.bsh script, 508
RMAN_NEW_BACK job, 573
rmbw command, 560
rmds command, 559
rmmf command, 555
rmsched command, 561
rmssel command, 556
rmuser command, 554
ROLE column, 232
ROLE_ROLE_PRIVS view, 234
root account, 5
root user, 11, 32, 575, 577, 581
ROWID clause, 369
ROW_ID column, 169
ROW_MOVEMENT column, USER_TABLES view, 290
ROWNUM pseudo-column, 166, 611–612, 619
rows
removing from partitions, 298–299
ROWS parameter, 342
ROWS=N parameter, 343
rsync utility, 13
run{ } block, 524, 526, 532, 539, 556–557
run-parts utility, 576
runInstaller command, 9
runInstaller utility, 9, 15–17, 19, 22
RUN_JOB procedure, 574
running manually, Oracle Scheduler jobs, 573–574
RUPD$ table, 371
RUPD$_<master_table_name> table, 391
S
S column, 607
S constraint code, 230
-S option, 567
s variable, bash shell, 53
SALARY column, EMP table, 633
SALES table, 194, 362, 365, 367
SALES_AMT column, 288
SALES_MV table, 367
sar utility, 603
SAs (system administrators), 5, 586
/sbin/init program, 585
/sbin/init.d/OracleBackup start command, 568
/sbin/init.d/OracleBackup stop command, 568
SCHEMA parameter, 151, 209, 324
schemas
displaying differences in, 238–241
and users, 111
SCHEMAS parameter, 342
SCN (system change number), 98, 102, 329, 390, 439, 453
SCRIPT option, 150
scripts, for common tasks
configuring startup file, 69
creating directories for, 68–69
lock.sql script, 67
login.sql script, 66
SEC_CASE_SENSITIVE_LOGON parameter, 120
SECTION SIZE parameter, 495
secure shell (ssh), 19
SecureFile LOBs (large objects), 246–262
migrating BasicFiles to, 260–262
granted roles, 232
of passwords for users, 119–120, 124–126
segment, changing attributes, 322–323
SEGMENT CREATION DEFERRED clause, 366
SEGMENT CREATION IMMEDIATE clause, 366
SEGMENT SPACE MANAGEMENT AUTO clause, 74, 247
SEGMENT_CREATED column, DBA/ALL_LOBS view, 263
SEGMENT_NAME column, DBA/ALL_LOBS view, 262
SEGMENT_SPACE_MANAGEMENT column, 247
segueing decisions to action, 478–482
SELECT statement, 35, 138, 144, 174, 185, 199, 202, 370, 383, 621
SELECT_CATALOG_ROLE role, 214, 221, 232
SEQUENCE BETWEEN clause, 525
autoincrementing columns, 206–207
creating, 204
dropping, 209
renaming, 209
back to lower value, 211
setting current value of, 210
using one vs. many, 208
SERVER_HOST parameter, 221
servers, number of databases on, 47
service command, 575
SERVICE_NAME parameter, 221
SES_ACTIONS column, 632
SESSIONS_PER_USER setting, 127
SET ARCHIVELOG DESTINATION clause, 528
SET clause, 140
SET command, 526
SET EDITOR command, 601
SET HOMEPATH command, 602
SET LONG command, 178
SET SQLPROMPT command, 54
SET UNUSED clause, 150
SET_ATTRIBUTE procedure, 572
setbw command, 560
SET_SCHEDULER_ATTRIBUTE procedure, 572
setup script, 549
setup.exe command, 9
SGA (system global area), 42, 103
sga_target parameter, 31
sga_target_max parameter, 31
SHELL variable, 22
SHOW ALERT command, 601
SHOW ALL command, 489
SHOW HOMES command, 602
SHOW option, 150
SHOW RECYCLEBIN statement, 452
SHR column, 607
shrinking tables, 157
SHUTDOWN ABORT statement, 44, 98, 515
SHUTDOWN IMMEDIATE statement, 43–44, 98, 515
shutdown.log file, 585
si column, 605
<SID>/backupset_<YYYY_MM_DD> directory, 493
SID parameter, 221
silent mode, dbca utility, 25, 45–46
size, of tablespaces
SKIP INACCESSIBLE command, 495
SKIP OFFLINE command, 495
SKIP parameter, 353
SKIP READONLY command, 494
SLOG$ table, 407
snapshot control file, determining location for, 469–470
so column, vmstat utility, 604–605
Source Code link, 60
space consumed, by LOBs, 265–268
Spacebar command, 606
SPACE_USAGE procedure, 266
spfile
adding control files when using, 94–95
RMAN backups of, 492
spfile<ORACLE_SID>.ora file, 30
SPOOL LOG command, 503
SQL
*Loader, vs. external tables, 345–346
viewing text files from, 352–353
SQL prompt, customizing, 54
SQL TEXT column, 609
sql.bsq file, 214
SQLFILE option, 317
SQLFILE parameter, 318, 322, 343
SQL_ID column, 222
sqlldr utility, 346
SQL*Plus, refreshing MVs from, 396
_SQLPLUS_RELEASE variable, SQL*Plus, 55
ssh (secure shell), 19
ssh utility, 19
/stage/osb directory, 549
START WITH clause, 379, 398–399
START WITH option, 205
START WITH parameter, 398
STARTUP command, 515
startup file, configuring, 69
STARTUP NOMOUNT command, 215, 527–528
STARTUP NOMOUNT statement, 32, 45
STARTUP statement, 30, 41–42, 44
startup.log file, 585
startx command, 19
STATE column, 195
STATE_CODE column, 288
static views, for data dictionary, 213–214
statistics
excluding, 327
partitions, 298
STATISTICS parameter, 342, 344
Statspack, troubleshooting temporary tablespace, 615
STATUS command, 313–315, 334, 340
status tables, 339
ST_FLG column, 166
STOP_JOB command, 308
STOP_JOB [=IMMEDIATE] command, 313–314
stopping
Oracle Scheduler jobs, 572–573
storage, changing attributes, 322–323
STORE AS SECUREFILE clause, 250
STORED PROCEDURE job type, 571
STREAMS_CONFIGURATION parameter, 344
STREAMS_INSTANTIATION parameter, 344
strings command, 337
--subjobs option, 565
SUBTRACT function, DBMS_REFRESH package, 411
suppressing log file, 333
SWITCH command, 522
SWITCH_DIR variable, 586
swpd column, 605
sy column, 605
creating, 201
dropping, 204
dynamically generating, 201–202
renaming, 203
SYS account, 112
SYS schema, 36, 114, 117, 119, 214–215, 634
sys user, 33–34, 72, 417–418, 426–427, 459, 465, 480
SYS.AUD$ table, 628
SYSAUX tablespace, 72, 76, 491
SYS_CONTEXT function, 220–221, 235
SYSDBA privilege, 416–418, 426–427, 460–461, 465
SYSDBA role, 114
SYS_EXPORT_SCHEMA_NN table, 308
SYS.GV$ view, 215
SYSOPER privilege, 461
SYSOPER role, 114
system administrators (SAs), 5, 586
system change number (SCN), 98, 102, 329, 390, 439, 453
SYSTEM datafile, 34
system global area (SGA), 42, 103
system partition type, 272
system privileges, for users, 128–129
SYSTEM rollback segment, 78
SYSTEM tablespace, 34–35, 72, 78, 86, 152, 214, 468, 521, 531
SYS.V_$ view, 215
SYS.V$ view, 215
T
variable, bash shell, 53
TAB$ table, 214
tabcount_<user>.sql file, 225
tabind.sql file, 318
TABLE_EXISTS_ACTION option, 330
TABLE_EXISTS_ACTION parameter, 330, 343
TABLE_EXISTS_ACTION=APPEND option, 330
TABLE_OWNER column, 203
avoiding redo creation, 144–145
compressing table data, 143–144
deferred segment creation for, 141–142
IOTs, 161
parallel table, 142
from query, 146
displaying object disk-space usage, 224–225
displaying table row counts, 225–226
dropping, 151
FLASHBACK TABLE TO BEFORE DROP, 452
to SCN, 453
to timestamps, 453
shrinking table, 157
testing for space below, 155–156
loading from external tables, 349–350
adding columns, 147
obtaining lock, 147
renaming columns, 149
renaming tables, 147
composite partitioning, 281–282
by hash, 280
PARTITION BY REFERENCE clause, 284–286
PARTITION BY SYSTEM clause, 286–287
placing partitions into tablespaces, 276–278
on virtual column, 286
shrinking, 157
viewing accessible tables, 224
tablespace point-in-time recovery (TSPITR), 533
TABLESPACE_NAME column, DBA/ALL_LOBS view, 262
tablespaces. See alsodatafiles
bigfile feature for, 81
exporting and importing, 321–323
changing segment and storage attributes, 322–323
changing size of datafiles, 323
exporting tablespace metadata, 322
specifying different datafile paths and names, 322
metadata, exporting, 322
and OMF feature, 80
placing partitions into, 276–278
and redo logging, controlling generation of, 76–77
renaming, 76
RMAN backups of
overview, 491
skipping read-only, 494
size of
SYSAUX, 72
SYSTEM, 72
TEMP, 72
toggling offline and online, 83–85
UNDO, 72
USERS, 72
and users, settings for, 116–118
TABLESPACES parameter, 342, 344
TAIL option, 601
tar command, 13
tar -tvf<tarfile_name> command, 13
tar utility, 13
target databases, registering, 484
TARGET parameter, 458
cloning users, 333
compressing output, 336
creating consistent exports, 329–330
creating daily DDL file, 336
encrypting data, 337
importing when objects already exist, 330–331
reusing output file names, 335
specifying additional dump files, 335
suppressing log file, 333
using parallelism, 334
TBSP1 tablespace, 323
tbsp_chk.log file, 64
tbsp.sql file, 322
TDE (Transparent Data Encryption), 258
telnet command, 596
TEMP tablespace, 34, 72, 115, 117, 429, 543
TEMPFILE option, 83
TEMPORARY column, 160
temporary table type, 134
temporary tables, creating, 159–160
temporary tablespace
restoring, 521
determining if sized correctly, 623–624
viewing SQL that is consuming temporary space, 624–625
TEMP_OUTPUT table, 159
TERMINAL parameter, 221
TERMINATED BY parameter, 353
terminating jobs, 316
administrative domain and servers, 546–547
daemons, 548
interfaces, 547
users and classes, 548
testing
text files, viewing from SQL, 352–353
TEXT_LENGTH column, 199
THRESH_GET_WORRIED variable, 586
THRESH_SPACE_CRIT variable, 586
time-based recovery, 532
TIME+ column, 607
TIME column, 607
--times option, 565
TIMESTAMP data type, 137, 149, 275–276
timestamps, flashing back tables to, 453
/tmp/rman.log file, 502
TNS_ADMIN/tnsnames.ora file, 38, 484
TNS_ADMIN variable, 37, 39, 588
tnsping command, 597
TO DESTINATION command, 493
TO NONE command, 472
TO_DATE function, 275–276, 532
<TO_DESTINATION> directory, 493
toggling redo logging, 388–389
TOID_NOVALIDATE parameter, 344
top --help command, 608
top, troubleshooting bottlenecks using, 606–607
TOUSER parameter, 344
TRANSACTIONAL clause, 414, 416, 429, 465
TRANSACTIONAL LOCAL parameter, 44
TRANSACTIONAL parameter, 44
TRANSFORM parameter, 322–323, 342
Transparent Data Encryption (TDE), 258
TRANSPORT_DATAFILES parameter, 343
TRANSPORT_FULL_CHECK parameter, 342
TRANSPORT_TABLESPACE parameter, 342, 344
TRANSPORT_TABLESPACES parameter, 342, 344
TRIGGERS parameter, 342
viewing with ADRCI utility, 601–602
and fine-grained auditing, 633–635
viewing enabled actions, 629–630
mapping operating system process to SQL statement, 608–609
using vmstat, 605
cron jobs, 581
database availability, 596–597
locating alert log and trace files, 597–599
remote installation, 22
resource intensive SQL statements, 610–615
monitoring real-time statistics, 610–611
using ASH, 615
using AWR, 614
using Statspack, 615
temporary tablespace issues, 623–625
determining if sized correctly, 623–624
viewing SQL that is consuming temporary space, 624–625
undo tablespace issues, 620–623
determining if sized correctly, 620–621
viewing SQL that is consuming undo space, 622–623
TRUNCATE command, 631
TRUNCATE parameter, 331
TRUNCATE statement, 153–154, 157, 331, 399, 410
TSPITR (tablespace point-in-time recovery), 533
TTS_FULL_CHECK parameter, 342
TTS_OWNERS parameter, 344
type command, 57
U
U constraint code, 230
u variable, bash shell, 53
unalias command, 56
uncompress utility, 549
UNDO feature, 31
UNDO tablespace, 31
overview, 72
determining if sized correctly, 620–621
viewing SQL that is consuming undo space, 622–623
undo_management parameter, 31
UNDO_RETENTION parameter, 620
undo_tablespace parameter, 31
UNIFORM SIZE clause, 73
uninstallob script, 551
UNION clause, 299
UNIQUE clause, 178
unique indexes, creating, 178–179
UNIQUE keyword, 164
UNOBF function, 333
UNREGISTER_MVIEW procedure, DBMS_MVIEW package, 407
UNTIL CHANGE clause, 449
UNTIL SEQUENCE clause, 525
UNTIL TIME clause, 532
UNUSABLE status, 190
unzip command, 15
UPDATE INDEXES clause, 292–293, 297, 304
UPDATE privilege, 129
update statement, 72, 140, 195–196
UPDATE_DTT column, 138
UPGRADE parameter, 43
upgrading
UPPER function, 177
UPPER(EMP_NAME) function, 177
us column, 605
USABLE state, 190
usage, monitoring of for indexes, 190–191
USE_CURRENT_SESSION parameter, 574
USER/ALL/DBA view, 213
USER column, 607
user-defined disk locations, 422–424
USER SYS clause, 35
USER SYSTEM clause, 35
USER$ table, 214
_USER variable, SQL*Plus, 55
USER view, 214
USER1 tablespace, 247
useradd command, 6
USER_COL_PRIVS view, 235
USER_COL_PRIVS_MADE view, 235
USER_COL_PRIVS_RECD view, 235
USER_CONSTRAINTS view, 229
userdel command, 6
USER_DUMP_DEST parameter, 93, 537
USERENV namespace, 220
USER_IND_COLUMNS view, 227
USER_INDEXES view, 227, 289, 376
USER_IND_PARTITIONS table, 300–301
USER_JOBS view, 398
usermod command, 6
USER_MVIEW_LOGS view, 392
USER_MVIEWS view, 366, 372, 380, 409
USERNAME column, 233
USER_OBJECTS view, 366
USER_PART_INDEXES table, 302
USER_PART_TABLES table, 375
USER_REFRESH view, 398
USER_ROLE_PRIVS view, 130, 232, 235
accounts in database, 223
cloning, 333
authentication method for, 115–116
system before installation, 5–6
tablespace settings for, 116–118
currently connected user, 220–221
currently executing SQL, 222
logging on as different, 120–122
modifying, 122
passwords for
changing, 118
grouping of, 130
object, 129
and schemas, 111
terminology of OSB, 548
users currently logged in, 221–222
USERS tablespace, 34–35, 72, 115, 122, 394, 443, 445, 517, 520
USER_SEGMENTS view, 250, 366–367
USER_SYS_PRIVS view, 235
USER_TABLES view, 214, 224, 226, 290, 388–389
USER_TAB_MODIFICATIONS view, 628
USER_TAB_PARTITIONS table, 284, 375
USER_TAB_PRIVS_MADE view, 235
USER_TAB_PRIVS_RECD view, 235
USING BACKUP CONTROLFILE clause, 447
/usr/local/oracle/backup directory, 549, 551
UTLDTREE script, 238
utlpwdmg.sql script, 119
V
V constraint code, 230
v variable, bash shell, 53
VALID status, 292
VALIDATE command, 499–501, 523
VALIDATE HEADER clause, 593
validating backup files, before restoring, 517
VALUE column, 95
VALUES LESS THAN clause, 272, 274
/var/opt/oracle directory, 12, 14, 26, 28, 583
/var/spool/cron directory, 576
/var/spool/cron/<username> file, 576
VARCHAR data type, 137
VARCHAR2 data type, 137
V$ARCHIVED_LOG view, 219, 428, 433, 533, 588
V$BACKUP view, 505
V$BACKUP_ARCHIVELOG_DETAILS view, 505
V$BACKUP_CONTROLFILE_DETAILS view, 505
V$BACKUP_COPY_DETAILS view, 505
V$BACKUP_DATAFILE view, 505
V$BACKUP_DATAFILE_DETAILS view, 505
V$BACKUP_FILES view, 505
V$BACKUP_PIECE view, 505
V$BACKUP_PIECE_DETAILS view, 505
V$BACKUP_SET view, 505
V$BACKUP_SET_DETAILS view, 505
V_DATA tablespace, 323
V$DATABASE_BLOCK_CORRUPTION view, 500–501, 523–524
V$DATAFILE view, 219, 442–443, 521
V$DATAFILE_HEADER view, 219, 443, 530
VERSION parameter, 357
V$FIXED_VIEW_DEFINITION view, 199, 216
vi utility, 16, 59, 95, 559, 579
view command, 600
checking row count of, 393–394
indexing log columns, 392
materialized
dropping, 395
moving, 394
shrinking space in, 393
viewing space used by, 392–393
VIEW_DEFINITION column, 216
WITH CHECK OPTION clause, 194–195
displaying SQL used to create, 198–199
dropping, 200
materialized. See MVs
modifying view definition, 198
WITH READ ONLY clause, 195
renaming, 200
V$INSTANCE_RECOVERY view, 103
VIRT column, 607
virtual columns
partitioning of, 286
virtual memory statistics (vmstat), 603–605
virtual partition type, 272
virtual test devices, for OSB, 566–567
VISIBLE clause, 184
VISUAL variable, 579
vlib1 library, 567
V$LOG view, 100–102, 219, 531, 533
V$LOGFILE view, 98, 100–102, 108, 542
V$LOG_HISTORY view, 102, 219, 525, 531, 533
vmstat (virtual memory statistics), 603–605
V$MVREFRESH view, 365
V$OPEN_CURSOR view, 619
V$PARAMETER view, 215
V$PWFILE_USERS view, 40
V$RESTORE_POINT view, 533
V$RMAN_BACKUP_JOB_DETAILS view, 505, 507
V$RMAN_COMPRESSION_ALGORITHM view, 477
V$RMAN_ENCRYPTION_ALGORITHMS view, 477
V$ROLLNAME view, 622
V$ROLLSTAT view, 622
V$SESSTAT view, 613
V$SQL_MONITOR view, 611–612, 614
V$SQLSTATATS view, 611
V$SQLTEXT view, 222
V$SQLTEXT_WITH_NEWLINES view, 222
V$SYSSTAT view, 613
V$TEMPFILE view, 83
V$UNDOSTAT view, 620
W
wa column, vmstat utility, 604–605
WAIT_OS_PID column, 618
watch command, 604
watch -d command, 605
WHENEVER NOT SUCCESSFUL setting, 630
WHENEVER SUCCESSFUL setting, 630
WHERE clause, 139–140, 146, 174, 176, 178, 180, 364, 390, 392
WITH ADMIN OPTION clause, 128
WITH CHECK OPTION clause, for views, 194–195
WITH COMMIT SCN clause, 391
WITH GRANT OPTION clause, 129
WITH OBJECT ID clause, 391
WITH PRIMARY KEY clause, 375, 391
WITH READ ONLY clause, 195
WITHOUT VALIDATION clause, 294–295
WORKPART table, 294
:wq command, vi utility, 579
WRAPUP state, 402
write mode, for tablespaces, 77–78
-x option, 563
X,Y
X$ table, 215
xargs command, 599
XENGDB server, 403
xeyes utility, 20
XML setting, AUDIT_TRAIL parameter, 627
Z
Z command, 606