Pro Oracle Database 11g Administration (113 page)

users.sql script, 67–68

setting current value of, 210

SEC_CASE_SENSITIVE_LOGON parameter, 120

using multiple, 207–208

SECTION SIZE parameter, 495

using one vs. many, 208

secure shell (ssh), 19

SERVER_HOST parameter, 221

SECUREFILE clause, 246, 250

servers, number of databases on, 47

SECUREFILE column, 261, 263

service command, 575

SecureFile LOBs (large objects), 246–262

SERVICE_NAME parameter, 221

compressing, 256–257

SES_ACTIONS column, 632

creating columns of, 250–251

SESSIONS_PER_USER setting, 127

deduplicating, 257–258

SET ARCHIVELOG DESTINATION clause, 528

encrypting, 258–260

SET clause, 140

migrating BasicFiles to, 260–262

SET command, 526

prerequisites for, 246–247

SET ECHO ON command, 488–489

space consumed by, 267–268

SET EDITOR command, 601

security, 232–236

SET HOMEPATH command, 602

granted roles, 232

SET LONG command, 178

object privileges, 235–236

SET NEWNAME command, 522, 539

of passwords for users, 119–120, 124–126

SET SQLPROMPT command, 54

671

■ INDEX

SET UNUSED clause, 150

space consumed, by LOBs, 265–268

SET_ATTRIBUTE procedure, 572

for BasicFile, 266–267

setbw command, 560

for SecureFile, 267–268

setenv command, 21, 26

Spacebar command, 606

SET_SCHEDULER_ATTRIBUTE procedure, 572

SPACE_USAGE procedure, 266

setup script, 549

spfile

setup.exe command, 9

adding control files when using, 94–95

SGA (system global area), 42, 103

restoring, 526–527

sga_target parameter, 31

RMAN backups of, 492

sga_target_max parameter, 31

spfile.ora file, 30

SHELL variable, 22

split-block issue, 436–439

SHOW ALERT command, 601

splitting partitions, 295–296

SHOW ALL command, 489

SPOOL LOG command, 503

SHOW command, 468–469, 476

SQL

SHOW HOMES command, 602

*Loader, vs. external tables, 345–346

SHOW option, 150

viewing text files from, 352–353

SHOW parameter, 318, 343

SQL prompt, customizing, 54

SHOW RECYCLEBIN statement, 452

SQL TEXT column, 609

SHR column, 607

sql.bsq file, 214

shrinking tables, 157

SQLFILE option, 317

SHUTDOWN ABORT statement, 44, 98, 515

SQLFILE parameter, 318, 322, 343

SHUTDOWN command, 43–44, 515

SQL_ID column, 222

SHUTDOWN IMMEDIATE statement, 43–44,

sqlldr utility, 346

98, 515

sqlnet.ora file, 41, 259

shutdown statement, 41, 43–44

SQLPATH variable, 54, 66

shutdown.log file, 585

SQL*Plus, refreshing MVs from, 396

si column, 605

_SQLPLUS_RELEASE variable, SQL*Plus, 55

/backupset_ directory,

ssh (secure shell), 19

493

ssh utility, 19

SID parameter, 221

/stage/osb directory, 549

silent mode, dbca utility, 25, 45–46

STAR2 user, 617–618

size, of tablespaces

START WITH clause, 379, 398–399

altering, 82–83

START WITH option, 205

displaying, 81–82

START WITH parameter, 398

SKIP INACCESSIBLE command, 495

starting databases, 41–42

SKIP OFFLINE command, 495

START_JOB command, 313–315

SKIP option, 331, 336

STARTUP command, 515

SKIP parameter, 353

startup file, configuring, 69

SKIP READONLY command, 494

STARTUP NOMOUNT command, 215, 527–528

SLOG$ table, 407

STARTUP NOMOUNT statement, 32, 45

snapshot control file, determining location for,

STARTUP statement, 30, 41–42, 44

469–470

startup.log file, 585

SNAPTIME$$ column, 391–392

startx command, 19

so column, vmstat utility, 604–605

STATE column, 195

Source Code link, 60

672

■ INDEX

STATE_CODE column, 288

SYS.AUD$ table, 628

static views, for data dictionary, 213–214

SYSAUX tablespace, 72, 76, 491

statistics

SYS_CONTEXT function, 220–221, 235

excluding, 327

SYSDBA privilege, 416–418, 426–427, 460–461,

partitions, 298

465

STATISTICS parameter, 342, 344

SYSDBA role, 114

Statspack, troubleshooting temporary

SYS_EXPORT_SCHEMA_NN table, 308

tablespace, 615

SYS.GV$ view, 215

STATUS column, 101–102, 573

SYSOPER privilege, 461

STATUS command, 313–315, 334, 340

SYSOPER role, 114

STATUS parameter, 342–343

system administrators (SAs), 5, 586

status tables, 339

system change number (SCN), 98, 102, 329, 390,

ST_FLG column, 166

439, 453

STOP_JOB command, 308

SYSTEM datafile, 34

STOP_JOB [=IMMEDIATE] command, 313–314

system global area (SGA), 42, 103

STOP_JOB procedure, 572–573

system partition type, 272

stopping

system privileges, for users, 128–129

databases, 43–45

SYSTEM rollback segment, 78

Oracle Scheduler jobs, 572–573

SYSTEM schema, 37, 114

storage, changing attributes, 322–323

SYSTEM tablespace, 34–35, 72, 78, 86, 152, 214,

STORE AS SECUREFILE clause, 250

468, 521, 531

STORE IN clause, 280, 283

SYS.V_$ view, 215

STORED PROCEDURE job type, 571

SYS.V$ view, 215

STREAMS_CONFIGURATION parameter, 344

-t option, 561, 563, 567

STREAMS_INSTANTIATION parameter, 344

strings command, 337

■ T

--subjobs option, 565

\t variable, bash shell, 53

SUBTRACT function, DBMS_REFRESH

TAB$ table, 214

package, 411

tabcount_.sql file, 225

suppressing log file, 333

tabind.sql file, 318

SWITCH command, 522

TABLE_EXISTS_ACTION option, 330

SWITCH_DIR variable, 586

TABLE_EXISTS_ACTION parameter, 330, 343

swpd column, 605

TABLE_EXISTS_ACTION=APPEND option, 330

sy column, 605

TABLE_NAME column, 203, 262

synonyms, 200–204

TABLE_OWNER column, 203

creating, 201

tables, 223–226

creating public, 201–202

building on, 377–378

dropping, 204

creating, 135–146

dynamically generating, 201–202

avoiding redo creation, 144–145

metadata for, 202–203

compressing table data, 143–144

renaming, 203

deferred segment creation for, 141–142

SYS account, 112

heap-organized table, 135–138

SYS schema, 36, 114, 117, 119, 214–215, 634

IOTs, 161

sys user, 33–34, 72, 417–418, 426–427, 459, 465,

parallel table, 142

480

673

■ INDEX

from query, 146

viewing accessible tables, 224

read-only table, 140–141

tablespace point-in-time recovery (TSPITR),

temporary table, 159–160

533

virtual columns for, 138–140

TABLESPACE_NAME column, DBA/ALL_LOBS

displaying DDL, 150–151

view, 262

displaying object disk-space usage, 224–225

tablespaces.
See also
datafiles

displaying table row counts, 225–226

bigfile feature for, 81

dropping, 151

creating, 72–76

flashing back, 451–454

dropping, 78–80

FLASHBACK TABLE TO BEFORE DROP,

exporting and importing, 321–323

452

changing segment and storage

to restore points, 453–454

attributes, 322–323

to SCN, 453

changing size of datafiles, 323

to timestamps, 453

exporting tablespace metadata, 322

high-water mark for, 154–159

specifying different datafile paths and

names, 322

moving table, 158–159

metadata, exporting, 322

shrinking table, 157

and OMF feature, 80

testing for space below, 155–156

placing partitions into, 276–278

loading from external tables, 349–350

and redo logging, controlling generation of,

modifying, 146–150

76–77

adding columns, 147

renaming, 76

altering columns, 148–149

restoring, 519–520

dropping columns, 149–150

RMAN backups of

obtaining lock, 147

excluding from, 493–494

renaming columns, 149

overview, 491

renaming tables, 147

skipping read-only, 494

moving, 158–159

size of

partitioning of, 271–287

altering, 82–83

composite partitioning, 281–282

displaying, 81–82

on demand, 282–284

specifying, 186–187, 374

existing table, 290–291

SYSAUX, 72

by hash, 280

SYSTEM, 72

by list, 279–280

TEMP, 72

PARTITION BY REFERENCE clause,

toggling offline and online, 83–85

284–286

UNDO, 72

PARTITION BY SYSTEM clause, 286–287

USERS, 72

placing partitions into tablespaces, 276–

278

and users, settings for, 116–118

by range, 272–278

write mode for, 77–78

on virtual column, 286

TABLESPACES parameter, 342, 344

removing data from, 153–154

TAIL option, 601

renaming, 331–332

tar command, 13

shrinking, 157

tar -tvf command, 13

types of, 133–134

tar utility, 13

undropping, 152–153

target databases, registering, 484

674

■ INDEX

TARGET parameter, 458

restore and recovery, 516–518

tasks, 328–337

TEXT column, 199, 214

cloning users, 333

text files, viewing from SQL, 352–353

compressing output, 336

TEXT_LENGTH column, 199

creating consistent exports, 329–330

THRESH_GET_WORRIED variable, 586

creating daily DDL file, 336

THRESH_SPACE_CRIT variable, 586

encrypting data, 337

time-based recovery, 532

importing when objects already exist,

TIME+ column, 607

330–331

TIME column, 607

remapping data, 332–333

--times option, 565

renaming tables, 331–332

TIMESTAMP data type, 137, 149, 275–276

reusing output file names, 335

timestamps, flashing back tables to, 453

specifying additional dump files, 335

/tmp directory, 7, 591

suppressing log file, 333

/tmp/rman.log file, 502

using parallelism, 334

TNS_ADMIN/tnsnames.ora file, 38, 484

TBSP1 tablespace, 323

TNS_ADMIN variable, 37, 39, 588

tbsp_chk.bsh script, 62–64

tnsnames.ora file, 4, 38–39

tbsp_chk.log file, 64

tnsping command, 597

tbsp.sql file, 322

TO DESTINATION command, 493

TDE (Transparent Data Encryption), 258

TO NONE command, 472

telnet command, 596

TO_DATE function, 275–276, 532

TEMP tablespace, 34, 72, 115, 117, 429, 543

directory, 493

tempfile, adding, 542–543

toggling redo logging, 388–389

TEMPFILE option, 83

TOID_NOVALIDATE parameter, 344

TEMPORARY column, 160

top command, 606–607

temporary table type, 134

top --help command, 608

temporary tables, creating, 159–160

top, troubleshooting bottlenecks using,

temporary tablespace

606–607

restoring, 521

top utility, 603–604

troubleshooting, 623–625

top.sql script, 66–67

determining if sized correctly, 623–624

TOUSER parameter, 344

viewing SQL that is consuming

TRANSACTIONAL clause, 414, 416, 429, 465

temporary space, 624–625

TRANSACTIONAL LOCAL parameter, 44

TEMP_OUTPUT table, 159

TRANSACTIONAL parameter, 44

TERMINAL parameter, 221

TRANSFORM parameter, 322–323, 342

TERMINATED BY parameter, 353

transformations, 350–352

terminating jobs, 316

Transparent Data Encryption (TDE), 258

terminology, of OSB, 546–548

TRANSPORT_DATAFILES parameter, 343

administrative domain and servers, 546–547

TRANSPORT_FULL_CHECK parameter, 342

daemons, 548

TRANSPORT_TABLESPACE parameter, 342,

interfaces, 547

344

users and classes, 548

TRANSPORT_TABLESPACES parameter, 342,

testing

344

media recovery, 517–518

TRIGGERS parameter, 342

troubleshooting, 595–635

675

■ INDEX

alert log, 600–602

TSPITR (tablespace point-in-time recovery),

viewing via OS tools, 600–601

533

viewing with ADRCI utility, 601–602

TTS_FULL_CHECK parameter, 342

auditing, 625–635

TTS_OWNERS parameter, 344

disabling, 630–631

type command, 57

DML usage, 627–628

enabling, 626–627

■ U

and fine-grained auditing, 633–635

U constraint code, 230

logon/logoff events, 628–629

-u option, 6, 552, 607

moving audit table, 632–633

\u variable, bash shell, 53

purging audit table, 631–632