Pro Oracle Database 11g Administration (107 page)
TABLE statement, 377
CONTROL_FILES parameter, 92, 94–97, 452,
CREATE
527–528, 537–538
CONTROLFILE_TYPE column, V$DATABASE
CREATE OR REPLACE method, 198
view, 528
CREATE PROFILE statement, 127
COPY command, 468, 487, 491–492
CREATE ROLE privilege, 130
COPY_FILE procedure, 85
CREATE ROLE statement, 130
copying
CREATE script, 76
existing installation, 12–15
CREATE SESSION privilege, 115–116, 483
attaching Oracle home, 14–15
CREATE SPFILE FROM PFILE statement, 30
copying binaries, 13–14
CREATE SYNONYM command, 201
Oracle Scheduler jobs, 573
CREATE TABLE AS SELECT statement, 146
COPY_JOB procedure, 573
CREATE TABLE
coraenv utility, 27
FROM
CORE_DUMP_DEST parameter, 537
CREATE TABLE privilege, 115–116, 365, 390
corruption, checking for in RMAN backups,
CREATE TABLE statement, 120, 134, 249, 257,
499–501
272, 279–280, 282, 295, 351
using BACKUP...VALIDATE, 501
CREATE TABLE...ORGANIZATION EXTERNAL
using RESTORE...VALIDATE, 501
script, 346
using VALIDATE command, 500
CREATE TABLE...ORGANIZATION EXTERNAL
cost-based optimizer (CBO), 138
statement, 345, 347–348, 355, 357–358
cp command, 95, 416, 419
CREATE TABLE...ORGANIZATION
cpio file, 8
EXTERNAL...AS SELECT statement,
355
CPU_PER_CALL setting, 127
CREATE TABLESPACE script, 74–75
CPU_PER_SESSION setting, 127
CREATE TABLESPACE statement, 73, 80
CPU_TIME column, 612
644
■ INDEX
CREATE USER privilege, 115
Ctrl+P, rerunning commands with, 58
CREATE USER SQL statement, 115
CUMULATIVE keyword, 497
CREATE USER statement, 34, 127
CURRENT_USER parameter, 221
CREATE VIEW privilege, 194
CURRVAL pseudo-column, 206
CREATE VIEW statement, 194
CUSTOMERS table, 332
CREATED column, 224
CYCLE option, 205
CREATE_DTT column, 138, 161
CREATE_JOB procedure, 570–571
■ D
credb.sql file, 33
D command, 606
cretbsp.sql script, 75
-d option, 561, 564, 567, 607
creuser.sql script, 117
\d variable, bash shell, 53
crit_var variable, 403
daemons, terminology of OSB, 548
cron daemon, 577, 582
DAILY_SALES table, 362
cron file, 577
data
cron jobs, 575–581
exporting, 310–311
adding by editing cron table directly, 579
creating database directories, 310–311
adding by loading cron table from file,
granting access to directories, 311
580–581
percentage of, 325
enabling access to, 577
taking exports, 311
vs. Oracle Scheduler, 574–575
importing, 311–312
overview of cron, 575–576
transferring, 318–321
redirecting output for, 581
copying datafiles, 320–321
table entries for, 577–578
directly across networks, 319–320
troubleshooting, 581
unloading and loading using external
cron table, 574, 576–581, 583, 589
tables, 354–357
cron utility, 64–66, 402, 482, 507, 574–575, 582,
compressing dump files, 356
590
encrypting dump files, 356–357
cron.allow file, 577
using parallelism to reduce elapsed
cron.deny file, 577
time, 356
crontab command, 579
Data Definition Language.
See
DDL
crontab file, 577
data dictionary, 213–241
crontab utility, 577, 580
architecture of, 213–218
CROSSCHECK command, 473, 482, 493, 538
documentation for, 216–218
cs column, 605
dynamic performance views, 215–216
CSV files, loading into databases, 347–350
static views, 213–214
creating directory objects and granting
constraint information, 229–231
access, 348
displaying, 229–230
creating external tables, 348–349
showing primary-key and foreign-key
loading regular tables from external tables,
relationships, 230–231
349–350
creating, 36–37
viewing external-table metadata, 349
differences in schemas, displaying, 238–241
CTAS statement, 350
index information, 227–229
CTAS table, 146
displaying for table, 227–228
CTIME column, 113
foreign-key columns not indexed,
Ctrl+N, rerunning commands with, 58
228–229
645
■ INDEX
logical and physical structures for, 218–220
filtering data and objects, 324–328
object dependencies, displaying, 236–238
excluding objects from export files,
security information, 232–236
325–326
displaying granted roles, 232
excluding objects from imports, 328
displaying object privileges, 235–236
excluding statistics, 327
displaying system privileges, 233–235
exporting percentage of data, 325
table information, 223–226
exporting table, index, constraint, and
displaying object disk-space usage,
trigger DDL, 327–328
224–225
including objects in imports, 328
displaying table row counts, 225–226
including only specific objects in export
viewing accessible tables, 224
files, 327
user information, 220–223
specifying Query, 324–325
currently connected user, 220–221
importing data, 311–312
currently executing SQL, 222
interactive command mode, 313–316
user accounts in database, 223
attaching to running jobs, 314–315
users currently logged in, 221–222
entering, 313–314
views, 338–339
stopping and restarting jobs, 315
Data Manipulation Language (DML), 123, 133,
terminating jobs, 316
174, 194, 214, 387, 451
legacy mode, 341–344
Data Pump, 307–344
mapping to exp utility, 341–342
architecture of, 308–310
mapping to imp utility, 343–344
common tasks of, 328–337
listing contents of dump files, 317
cloning users, 333
monitoring jobs, 338–340
compressing output, 336
data-dictionary views, 338–339
creating consistent exports, 329–330
Data Pump log file, 338
creating daily DDL file, 336
database alert log, 339
encrypting data, 337
interactive command-mode status, 340
importing when objects already exist,
operating-system utilities, 340
330–331
status table, 339
remapping data, 332–333
parameter file, 316
renaming tables, 331–332
transferring data, 318–321
reusing output file names, 335
copying datafiles, 320–321
specifying additional dump files, 335
directly across networks, 319–320
suppressing log file, 333
Data Recovery Advisor, 512–515
using parallelism, 334
listing failures, 512–513
estimating size of export jobs, 317
repairing failures, 514–515
exporting and importing tablespaces and
suggesting corrective action, 513–514
datafiles, 321–323
data transfer element (DTE), 567
changing segment and storage
Database Configuration Assistant (dbca), 25,
attributes, 322–323
45–46
changing size of datafiles, 323
DATABASE DEFAULT TABLESPACE statement,
exporting tablespace metadata, 322
35
specifying different datafile paths and
database point-in-time recovery (DBPITR), 449,
names, 322
529
exporting data, 310–311
databases
646
■ INDEX
alert log, 339
DBA/ALL/USER_IND_EXPRESSIONS view, 178,
directories, creating, 310–311
228
flashing back, 454
DBA/ALL/USER_IND_PARTITIONS view, 219,
loading CSV files into, 347–350
287
creating directory objects and granting
DBA/ALL/USER_IND_SUBPARTITIONS view,
access, 348
287
creating external tables, 348–349
DBA/ALL/USER_INTERNAL_TRIGGERS view,
390
loading regular tables from external
tables, 349–350
DBA/ALL/USER_LOBS view, 248, 262
viewing external-table metadata, 349
DBA/ALL/USER_MVIEW_AGGREGATES view,
364
noarchivelog-mode.
See
noarchivelog-mode
databases
DBA/ALL/USER_MVIEW_ANALYSIS view, 364
starting and stopping, 583–586
DBA/ALL/USER_MVIEW_COMMENTS view,
364
datafiles.
See also
tablespaces
DBA/ALL/USER_MVIEW_DETAIL_PARTITION
copying, 320–321
view, 364
creating required directories for, 537
DBA/ALL/USER_MVIEW_DETAIL_RELATIONS
exporting and importing, 321–323
view, 364
changing segment and storage
DBA/ALL/USER_MVIEW_DETAIL_SUBPARTIT
attributes, 322–323
ION view, 365
changing size of datafiles, 323
DBA/ALL/USER_MVIEW_JOINS view, 364
exporting tablespace metadata, 322
DBA/ALL/USER_MVIEW_KEYS view, 364
specifying different datafile paths and
DBA/ALL/USER_MVIEW_LOGS view, 364
names, 322
DBA/ALL/USER_MVIEW_REFRESH_TIMES
renaming, 85–90
view, 364
renaming to reflect new directory locations,
DBA/ALL/USER_MVIEWS view, 364–365, 383,
539–540
400–401
restoring, 521–522
DBA/ALL/USER_PART_COL_STATISTICS view,
restoring from backups, 442–443
287
RMAN backups of, 491–494
DBA/ALL/USER_PART_HISTOGRAMS view,
toggling offline and online, 83–85
287
updated, 440
DBA/ALL/USER_PART_INDEXES view, 219, 287
DATAFILES parameter, 343
DBA/ALL/USER_PART_KEY_COLUMNS view,
DATA_LARGE tablespace, 136
287
DATA_ONLY option, 331, 337
DBA/ALL/USER_PART_TABLES view, 219, 287
DBA/ALL/USER_REFRESH view, 365, 408
DATA_SMALL tablespace, 136
DBA/ALL/USER_REGISTERED_MVIEWS view,
DATE data type, 137, 149, 275, 283
364
_DATE variable, SQL*Plus, 55
DBA/ALL/USER_SEGMENTS view, 263, 265
DATE_ID column, 375
DBA/ALL/USER_SUBPART_COL_STATISTICS
DB setting, AUDIT_TRAIL parameter, 627
view, 290
db_1 directory, 13
DBA/ALL/USER_SUBPART_HISTOGRAMS
DBA/ALL/USER_BASE_TABLE_MVIEWS view,
view, 287
364
DBA/ALL/USER_SUBPARTITION_TEMPLATES
DBA/ALL/USER_CONSTRAINTS view, 179, 229
view, 287
DBA/ALL/USER_INDEXES view, 179, 219
DBA/ALL/USER_SUBPART_KEY_COLUMNS
view, 287
647
■ INDEX
DBA/ALL/USER_SYNONYMS view, 202–203
dba_setup script, 60–61
DBA/ALL/USER_TABLES view, 219, 226
DBA_SYNONYMS view, 203
DBA/ALL/USER_TAB_PARTITIONS view, 219,
DBA_SYS_PRIVS view, 128, 233–234
287
DBA_TABLES view, 224
DBA/ALL/USER_TAB_SUBPARTITIONS view,
DBA_TABLESPACES view, 247
287
DBA_TAB_PARTITIONS view, 288
DBA/ALL/USER_TUNE_MVIEW view, 365
DBA_TAB_PRIVS view, 234
DBA/ALL/USER_USERS view, 219
DBA_USERS view, 116, 223
DBA/ALL_LOBS view, 262
DBA_USERS_WITH_DEFPWD view, 113
DBA/ALL_USER_PART_LOBS view, 252
dbca (Database Configuration Assistant), 25,
dba group, 4–5, 32
45–46
DBA privilege, 485
dbca.rsp file, 45
DBA role, 114, 118, 232, 348
DB_CREATE_FILE_DEST parameter, 80,
DBA static view, 214
499–500
DBA/USER/ALL_SEGMENTS view, 249
DB_EXTENDED setting, AUDIT_TRAIL
DBA/USER_EXTENTS view, 219
parameter, 627
DBA/USER_FREE_SPACE view, 219
DB_FLASHBACK_RETENTION_TARGET
DBA/USER_SEGMENTS view, 219
parameter, 455
DBA/USER_TABLESPACES view, 219
--dbid option, 564
DBA_AUDIT_OBJECT view, 628
db_install.rsp file, 10
DBA_AUDIT_POLICIES view, 633
DBMS_ADVISOR.TUNE_MVIEW procedure,
365
DBA_COL_PRIVS view, 234
DBMS_AUDIT_MGMT package, 632
DBA_CONSTRAINTS view, 229–231
DBMS_BACKUP_RESTORE package, 459
DBA_DATA_FILES view, 219
DBMS_DATAPUMP package, 308
DBA_DATAPUMP_JOBS view, 338
DBMS_FGA package, 633
DBA_DATAPUMP_SESSIONS view, 344
DBMS_FILE_TRANSFER built-in PL/SQL
DBA_DEPENDENCIES view, 236–237
package, 85
DBA_ENCRYPTED_COLUMNS view, 259
DBMS_JOB package, 398–399
DBA_EXTENTS view, 214
DBMS_LOB package, 246, 264
DBA_EXTERNAL_LOCATIONS table, 349
DBMS_METADATA package, 140, 150, 188, 199,
DBA_EXTERNAL_TABLES view, 349
203, 308, 317, 384
dba_fcns file, 56, 69
DBMS_METADATA view, 209
dba_fcns script, 61–62
DBMS_METADATA.GET_DDL function, 199
DBA_FGA_AUDIT_TRAIL view, 634
DBMS_MVIEW package, 367–368, 371, 373,
DBA_PART_TABLES view, 288
380–382, 396, 407
DBA_RCHILD view, 365, 410