OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P23

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

97
lượt xem 18
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P23: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P23

ALTER SYSTEM ARCHIVE LOG GROUP statement – ANALYZE ANY privilege 1031 shared servers, 649 – 650 ALTER TABLE MOVE statement, 566 tablespaces, 528 ALTER TABLE READ ONLY ALTER SYSTEM ARCHIVE LOG statement, 317 GROUP statement, 917 – 918 ALTER TABLE RENAME COLUMN ALTER SYSTEM CHECKPOINT statement, 314 statement, 848 ALTER TABLE RENAME TO ALTER SYSTEM KILL SESSION statement, 317 statement, 741 ALTER TABLE SET UNUSED ALTER SYSTEM SET statement COLUMNS clause, 315 auditing, 696 ALTER TABLESPACE statement, 754 resources, 688, 691 ALTER TABLESPACE ADD DATAFILE ALTER SYSTEM SWITCH LOGFILE statement, 538, 551 statement, 848 ALTER TABLESPACE APPL_DATA ALTER TABLE statement statement, 538 auditing option, 697 ALTER TABLESPACE BEGIN BACKUP constraints, 319 statement, 540, 868 – 869 triggers, 733 ALTER TABLESPACE END BACKUP ALTER TABLE ADD statement, 310 – 312 statement, 540, 868 ALTER TABLE ADD CONSTRAINT ALTER TABLESPACE READ ONLY statement, 321 – 324 statement, 540 ALTER TABLE DISABLE ALTER TABLESPACE READ WRITE CONSTRAINT statement, 325 statement, 540 ALTER TABLE DISABLE PRIMARY ALTER TABLESPACE USER_DATA KEY CASCADE statement, 325 statement, 539 ALTER TABLE DROP statement, 314 – 315 ALTER TRIGGER statement, 733 ALTER TABLE DROP CONSTRAINT ALTER USER DEFAULT ROLE statement, 325 statement, 684 ALTER TABLE DROP PRIMARY KEY ALTER USER PASSWORD CASCADE statement, 325 statement, 669 ALTER TABLE DROP UNIQUE ALTER USER privilege, 679 statement, 325 ALTER USER PROFILE statement, 666 ALTER TABLE ENABLE statement, ALTER USER QUOTA statement, 666 325 – 326 ALTER VIEW statement, 347 – 349 ALTER TABLE ENABLE ROW AMM (Automatic Memory Management), MOVEMENT statement, 905 822 – 824 ALTER TABLE MODIFY statement, 313, ampersands (&) 320, 325 positional notation for variables, 50 ALTER TABLE MODIFY substitute variables, 45, 47 – 49 CONSTRAINT statement, 572 ANALYZE ANY privilege, 679
1032 ANALYZE_DB procedure – auditing ANALYZE_DB procedure, 797 arithmetic ANALYZE event triggers, 731 dates, 299 – 301 AND DATAFILES clause, 537 operators, 20 AND operators, 33 – 34, 83 AS OF TIMESTAMP clause, 899 – 900 ANSI syntax for joins AS SELECT clause, 308 – 310 Cartesian, 209 – 210 ASBM process, 409 inner, 202 ASC keyword, 38 outer, 212 – 214 ASCII function, 70 ANY operator, 32, 224 – 225 ASCIISTR function, 104 ANY privilege, 687 – 688 ASH (Active Session History), 784, APPEND keyword 788 – 789, 788 – 789 query results files, 15 ashrpt.sql script, 788 SQL buffer, 10 ASIN function, 82 – 83 SQL*Loader, 971 ASM (Automated Storage Apply Patch Wizard, 993, 993 Management), 460 applying patches, 993 – 994, 993 – 994 ASM Balance process, 409 architecture, 398 – 399, 400 ASM Disk process, 409 instances, 402, 402 ASMM (Automatic Shared Memory memory structures, 402 – 408, 405 Management), 470, 820 – 822 processes structures, 408 – 415 ASMM screen, 825 – 826, 826 storage structures, 415 – 423, 417, 423 ASSOCIATE event triggers, 731 user processes, 400 – 401, 401 asterisks (*) archive log mode, 421 column selection, 26 archive logs, 421 comments, 15 archived log files, 859 dates, 300 archived redo log files, 854 multiplication, 20 architecture, 854 – 855 ATAN function, 83 contents, 415 ATAN2 function, 83 destinations, 856 – 858, 856 – 857 AUD$ table, 704 space issues, 855 AUDIT event triggers, 731 ARCHIVELOG clause, 858 Audit Settings screen, 704, 705 ARCHIVELOG mode, 854 – 855 AUDIT statement, 697 non-system-critical data file recovery, AUDIT_SYS_OPERATIONS 920 – 925, 921 – 922 parameter, 700 system-critical data file recovery, 926 auditing, 695 – 696 Archiver process, 409 fine-grained, 705 – 708 archiving process, 854 objects, 702 – 704 ARCn process, 409, 421 – 422, 421 privileges, 701 – 702 purging audit trails, 704, 705 statements, 696 – 701
authentication – baselines in AWR 1033 authentication, 663 – 664 averages, 156 – 157 AUTHID CURRENT_USER clause, 727 AVG function, 156 – 157 AUTOALLOCATE option, 529 – 530 AWR (Automatic Workload Repository) AUTOEXTEND clause, 546 – 547, baselines, 789 – 792, 791 – 792 556 – 557, 751 collection, 784 – 787, 785 – 786 Automated Database Diagnostic snapshots, 787 – 789 Monitoring (ADDM) feature, 792 AWR Baselines screen, 791, 791 EM Database Control, 792 – 798, 794 – 797 views, 798 – 800 B Automated Maintenance Tasks, 768, B code in number conversions, 116 782, 783 B-tree indexes, 372, 572 – 573 Automated Maintenance Tasks screen, BACKUP CURRENT CONTROLFILE 782, 783 statement, 867 Automated Storage Management BACKUP RECOVERY AREA (ASM), 460 statement, 862 Automatic Diagnostic Repository (ADR), backup sets, 873 766, 805 – 809 backup to trace backups, 864 Automatic Maintenance Tasks screen, backups, 862 474, 475 catalog maintenance, 878, 878 Automatic Memory Management (AMM), control files, 864 – 868 822 – 824 database, 868 – 869 AUTOMATIC segment space exam essentials, 880 – 881 management, 531 full and incremental, 873 – 876, Automatic Shared Memory Management 875 – 877 (ASMM), 470, 820 – 822 image copies and backup sets, 873 Automatic SQL Execution Memory managing, 876 – 879, 878 – 879 Management, 822 reports, 878 – 879, 879 Automatic Undo Management screen, review questions, 882 – 887 751, 751 RMAN, 869 – 876, 870 – 872, 875 – 877 Automatic Workload Repository (AWR) settings, 869 – 873, 870 – 872 baselines, 789 – 792, 791 – 792 summary, 879 – 880 collection, 784 – 787, 785 – 786 tablespace backup mode for, 540 – 541 snapshots, 787 – 789 terminology, 862 – 863 Automatic Workload Repository screen, Bad file type with SQL*Loader, 967 785, 785, 787 base tables, 342 AUTOSTATS_TARGET parameter, 775 baselines in AWR, 789 – 792, 791 – 792 Availability screen, 898, 926
1034 BEFORE LOGOFF events – Change Tracking Writer process BEFORE LOGOFF events, 732 BY ACCESS clause, 697 BEFORE SHUTDOWN events, 733 BY SESSION clause, 697 BEGIN BACKUP clause, 540, 868 – 869 BYTE keyword, 292 BEGINDATA keyword, 971 bequeath connections, 603 BETWEEN operator, 36 C BFILE datatype, 302 C code in number conversions, 115 BFILENAME function, 124 CACHE parameter bigfile tablespaces, 527 – 528 sequences, 361 BIN_TO_NUM function, 104 tables, 564 binary backups, 867 caches binary datatypes, 301 – 302 database, 403, 405 – 406, 405 BINARY_DOUBLE datatype, 295 patches, 990, 990 BINARY_FLOAT datatype, 295 sequences, 361 binary operators, 20 tables, 564 binary parameter files (sfiles), 489 CALL command, 727 bit field conversion functions, 104 carets (^) for inequality operator, 30 BITAND function, 83 Cartesian joins, 208 – 210 bitmap indexes, 373, 572 – 573 CASCADE clause BITMAP keyword, 373 constraints, 325 BLOB datatype statistics preferences, 775 columns, 562 users, 668 conversions, 120 CASCADE CONSTRAINTS clause, functions, 126 315 – 316 syntax, 302 CASE expression block sizes, 423 scalar subqueries in, 229 nonstandard, 533 – 534 syntax, 44 – 45 setting, 471 CAST function, 105 blocks, 422 – 423 Catalog Additional Files button, 878 BLOCKSIZE clause, 533 catalog maintenance, 878, 878 bodies, package, 727 – 728 catalog.sql script, 488 BOTH trimming value, 79 catproc.sql script, 488 buckets CBO (cost-based optimizer), 767 hash functions, 129 CD installation, 431 histograms, 89 – 90 CEIL function, 84 BUFFER_POOL parameter, 561 CHANGE command, 11 buffers CHANGE FAILURE command, 925 database, 403, 405 – 406, 405, 561 change_password command, 621 redo log, 403, 406, 414 Change Tracking Writer process, 410 SQL, 8, 10 – 15
change vectors – committed undo information 1035 change vectors, 406 clusters CHAR datatype, 16, 292 description, 290, 395 character datatypes, 16 – 17, 291 – 294 RAC, 399, 642 conversion functions, 104, 111 – 117, COALESCE function, 67 – 68 122 – 123 coefficient of correlation function, single-row functions, 68 – 80 163 – 164 character sets cold backups, 868 configuring, 472 – 473, 472 collecting statistics, 768 – 769 template definitions, 455 AWR, 784 – 787, 785 – 786 Character Sets tab, 472 – 473, 472 EM Grid Control, 770 – 773, 770 – 773 CHARTOROWID function, 106 SQL*PLUS, 769 – 770 CHECK clause, 320 – 321, 569 columns check constraints, 320 – 321, 569 adding, 310 – 312 Checkpoint process, 408, 413 alias names, 26 checkpoints, 413, 846 CREATE TABLE, 559 child tables, 394 datatypes, 557 – 558 CHR function, 70 default values, 306 – 308 CHUNK parameter, 563 defined names, 344 – 345 CIRCUITS parameter, 649 definition changes, 313 CJQn process, 409 derived, 308 CKPT process, 408, 413, 846 dropping, 314 – 315 CLEAR BUFFER command, 12 names, 303 – 305, 558 clearing online redo log files, 854 renaming, 314 client configuration, 626 virtual, 288 Easy Connect method, 628 – 629 COLUMNS CONTINUE clause, 315 local naming method, 629 – 635, commas (,) in number conversions, 115 630 – 632, 634 COMMENT event triggers, 731 name resolution, 626 – 628 COMMENT statement, 308 tnsnames.ora file, 633 COMMENT ON COLUMN statement, troubleshooting, 635 – 637 308, 697 client/server architecture, 593 – 594 COMMENT ON TABLE statement, clients, Data Pump, 938, 940 308, 697 CLOB datatype comments columns, 562 script files, 15 conversions, 120 tables, 308 functions, 126 COMMIT statement syntax, 293 deadlocks, 742 closing incidents, 985 – 986, 986 transactions, 268 – 271 CLUSTER_DATABASE parameter, 489 committed undo information, 750
1036 Common Options section – CONTINUE_CLIENT parameter Common Options section, 455 CONNECT_TIMEOUT parameter, 612 comparison operators, 29 – 32 CONNECT TO clause, 370 COMPATIBLE parameter, 489 Connection Manager, 598 Compile Invalid View, 818, 818 Connection Mode tab, 473, 473 compiling connections files in installation, 437 – 438, 437 listener requests, 602 – 605, 603 – 605 invalid and unusable objects, 815 – 818, Oracle Shared Server, 650 – 651 817 – 819 processes, 400 complex inner joins, 200 settings, 473, 473 COMPOSE function, 106 CONNECTIONS attribute, 645 composite indexes, 371 – 372 connectivity, network, 596, 596 composite keys, 321 consistency in transaction control, COMPOSITE_LIMIT resource, 690 – 691 272 – 273 compound queries, 216 – 221 consistent backups, 863, 868 COMPRESS clause, 574 constraints, 319, 526, 568 COMPUTE STATISTICS clause, 574 check, 320 – 321, 569 CONCAT function, 70 – 71 creating, 319 – 320, 328 – 331 concatenated indexes, 373 deferred checking, 327 – 328, 571 – 572 concatenating strings, 20 – 21, 70 – 71 description, 395 concatenation operator, 20 – 21 disabled, 324 concurrency, locks for, 736 – 737 dropping, 324 – 325 Configure Automated Maintenance Tasks enabling and disabling, 325 – 326 screen, 782, 783 exam essentials, 332 Configure Automatic Storage Management example, 578 (ASM) option, 453 foreign key, 323 – 324, 571 – 572 Configure Backup Settings: Backup Set NOT NULL, 320 screen, 870 – 871, 871 parent-child relationships, 394 Configure Backup Settings: Device screen, primary key, 322 – 323, 570 869 – 870, 870 review questions, 333 – 339 Configure Backup Settings: Policy screen, summary, 331 871 – 872, 872 unique, 321 – 322, 569 – 570 Configure Database Options option, 452 validated, 326 – 327 Confirmation screen, 479, 479 on views, 347 conflicts, locks, 739 – 742, 740 – 741 CONTENT parameter, 953, 956 CONNECT command, 7 CONTINUE_CLIENT command CONNECT_DATA parameter, 633 expdp, 947 CONNECT role, 685 impdp, 952 CONNECT_TIME resource, 689 CONTINUE_CLIENT parameter, 958
CONTINUE LOAD DATA command – CREATE PUBLIC DATABASE LINK statement 1037 CONTINUE LOAD DATA command, Create a Database option, 452 970 – 971 CREATE_BASELINE procedure, 789 CONTROL_FILE_RECORD_KEEP_ Create Baseline: Repeating Baseline TIME parameter, 842 screen, 792, 792 control files, 415 – 417, 417, 841 Create Baseline: Single Baseline screen, architecture, 841 – 843, 843 791, 791 backing up, 864 – 868 CREATE BIGFILE statement, 527 failure recovery, 913 – 915 CREATE BITMAP INDEX statement, 373 in flash recovery area, 859 Create Database option, 477 multiplexing, 844 – 845 CREATE DATABASE statement SQL*Loader, 967, 970 – 972 Oracle Managed Files, 551 CONTROL_FILES parameter, 489, redo log files, 847, 849 844 – 845, 914 tablespaces, 527 Control Files screen, 843, 843 temporary tablespaces, 534 – 535 Control Files section for template CREATE DATABASE LINK definitions, 455 statement, 370 CONTROLFILE_RECORD_KEEP_ CREATE DIRECTORY statement, 941 TIME parameter, 416 CREATE_EXTENDED_STATS conversions statement, 779 date formats, 91 CREATE FUNCTION statement, 726 functions summary, 101 – 123 CREATE GLOBAL TEMPORARY numbers to words, 119 – 120 TABLE statement, 565 CONVERT function, 106 – 107 CREATE INDEX statement, 372 Coordinated Universal Time (UTC), 296 Create Listener screen, 614, 615 copying files in installation, 437 – 438, 437 Create Net Service Name page, 634 core dump files, 806 Create New Service Name page, 634 corporation passwords, 694 – 695 CREATE OR REPLACE FUNCTION CORR function, 163 – 164 statement, 694, 726 correlated subqueries, 221, 227 – 228 CREATE OR REPLACE PACKAGE Correlation Time Proximity setting, 985 statement, 727 – 728 COS function, 84 CREATE OR REPLACE PROCEDURE COSH function, 84 statement, 726 cost-based optimizer (CBO), 767 CREATE OR REPLACE TRIGGER COUNT function, 148, 157 – 158 statement, 729 – 730, 732 COVAR_POP function, 164 – 165 CREATE OR REPLACE VIEW statement, COVAR_SAMP function, 165 347 – 348 CPU_PER_CALL resource, 689 CREATE PROFILE statement, 688 – 691 CPU_PER_SESSION resource, 689 CREATE PUBLIC DATABASE LINK CPUs (critical patch updates), 987 statement, 370 crash recovery, 897
1038 CREATE PUBLIC SYNONYM statement – CYCLE keyword CREATE PUBLIC SYNONYM statement, CREATE VIEW statement, 342 – 345 367 – 368 Creation Options screen, 477 – 481, 478 CREATE ROLE statement, 682, 684 credentials CREATE SEQUENCE statement, database, 457 – 458, 458 360 – 361 patches, 993 CREATE SMALLFILE statement, 528 Credentials and Schedule screen, 993 CREATE_SNAPSHOT statement, 787 Critical alert level, 804 CREATE SYNONYM statement, 367 critical patch updates (CPUs), 987 CREATE TABLE statement, 303, 559 – 562 cross joins, 209 – 210 constraints, 319 – 322 cross-protocol connectivity, 598 external tables, 975 – 977 Crosscheck All button, 878 CREATE TABLE AS SELECT statement, CTAS syntax, 309 308 – 310 CTWR process, 410 CREATE TABLE IOT statement, 576 CUBE modifier, 177 – 178, 180 – 184 CREATE TABLE ORGANIZATION CUME_DIST function, 165 – 166 EXTERNAL statement, 559 cumulative distributions, 165 – 166 CREATE TABLESPACE statement, CURRENT_DATE function, 95 527 – 531 current_listener command, 622 CREATE TABLESPACE APPL_DATA current log files, 847 statement, 529, 532, 546 current session, retrieving, 45 – 47 CREATE TABLESPACE DATAFILE Current tab, 494 statement, 553 CURRENT_TIMESTAMP function, CREATE TEMPORARY TABLESPACE 95 – 96 statement, 534 – 535 CURRVAL keyword, 361 – 362 CREATE UNDO TABLESPACE custom packages, 983 statement, 536 – 537 custom scripts, 467 – 468, 468 CREATE UNIQUE INDEX Customize Package screen, 984, 984 statement, 372 Customized backup schedule: Options CREATE USER IDENTIFIED BY screen, 874, 875 statement, 663 Customized backup schedule: Review CREATE USER IDENTIFIED screen, 876, 877 EXTERNALLY statement, 664 Customized backup schedule: Schedule CREATE USER IDENTIFIED screen, 876, 877 GLOBALLY statement, 664 Customized backup schedule: Settings Create User screen, 668, 668 screen, 876, 876 CREATE USER statement, 662 – 663 Cutoff Age for Incident Inclusion profiles, 666 setting, 985 quotas, 666 CYCLE keyword, 361 tablespaces, 665
D code in number conversions – Database Configuration Assistant (DBCA) 1039 Data Pump facility, 936 – 937 D access methods, 940 D code in number conversions, 115 architecture, 937, 938 data blocks, 524 clients, 940 data concurrency, 736 – 737 components, 937 data consistency, 272 – 273 data and metadata filters, 956 – 958 Data Control Language (DCL) EM Database Control, 962 – 967, statements, 718 962 – 963, 965 – 967 Data Definition Language (DDL) exam essentials, 995 – 996 statements, 718 export and import modes, 943 – 944 description, 6 exports, 944 – 949, 962 – 964, 962 – 963 event triggers, 730 – 731 fine-grained object selection, 961 lock waits, 738 – 739 imports, 949 – 956, 964 – 967, 965 – 967 tablespaces, 543 job management, 958 – 961 data dictionary loading external tables, 975 – 977 caches, 407 location setup, 941 – 942, 942 protecting, 686 processes, 938 – 940 unusable objects in, 816 – 817 review questions, 997 – 1002 views, 485 – 486, 486, 488, 815 summary, 995 data files, 415, 417 – 418, 419, 546 Data Pump Import: Re-mapping screen, exam essentials, 583 964, 965 in flash recovery area, 859 Data Pump Import: Review screen, information about, 555 – 557 966, 966 moving, 550 Data Recovery Advisor (DRA), 802, 890, online and offline, 547 915 – 917, 916 Oracle Managed Files, 550 – 554 non-system-critical data file recovery, overview, 524 – 526, 525 921 – 925, 921 – 922 renaming, 548 – 549 system-critical data file recovery, 926 review questions, 584 – 589 data visibility, 271 – 272 size, 545 – 546 Data Warehouse template, 453 SQL*Loader, 967 database-authenticated accounts, 663 summary, 582 Database Configuration Assistant Data Manipulation Language statements. (DBCA), 450 – 451 See DML (Data Manipulation configuration settings, 481 – 482 Language) statements content settings, 464 – 466, 464 – 466 Data Pump Export: Options screen, creation options, 477 – 481, 478 – 481 962, 963 credential settings, 457 – 458, 458 Data Pump Export: Review screen, database deletions, 482 962, 963 exam essentials, 515
1040 Database Content screen – databases file location settings, 460 – 462, 461 exam essentials, 831 identification settings, 455 – 456, 456 optimizer statistics. See optimizer initialization parameters, 468 – 473, statistics 469 – 474 performance statistics. See performance invoking, 451 – 453, 451 – 452 statistics management options, 456 – 457, 457 review questions, 832 – 837 password management, 480, 480 server-generated alerts, 802 – 805, 803 recovery configuration, 462 – 464, summary, 830 462, 464 database management systems review questions, 516 – 521 (DBMSs), 392 sample schemas and custom scripts, Database mode for Data Pump, 943 467 – 468, 468 DATABASE_PROPERTIES view, 486, security and maintenance settings, 528, 665 474, 475 Database Schema Live database role, 950 storage options, 459 – 460, 460, Database Schema Table role, 949 475 – 477, 476 – 477 Database Schema Table Tablespace Live summary, 514 role, 949 template definitions Database Services screen, 609, 609 creating, 483 – 484, 483 Database Storage screen, 475 – 477, deleting, 484 476 – 477 predefined, 453 – 455, 453 – 454 Database Templates screen, 453, 453 Database Content screen, 464 – 467, Database Writer process, 408, 413 464 – 465 databases Database Control. See Enterprise Manager alert logs, 506 – 511, 511 Database Control tool auditing. See auditing Database Control screen, 501, 501, backups. See backups 505, 505 buffer caches, 403, 405 – 406, 405 Database Credentials screen, 457 – 458 458 configuring, 481 – 482 DATABASE_EXPORT_OBJECTS creating, 512 – 513 view, 957 defined, 399 Database File Locations screen, deleting, 482 460 – 462, 461 event triggers, 732 – 733 Database Identification screen, file locations, 460 – 462, 461 455 – 456, 456 links, 290, 370, 395 DATABASE LINK statement, 697 maintaining. See database maintenance database maintenance, 766 – 767 objects, 394 – 395 ADR, 805 – 809 Oracle Managed Files for, 551 – 553 advisory framework, 800 – 802 overview, 392 – 393 automated, 782, 783 privileges, 674
DATAFILE clause – DBA_FREE_SPACE view 1041 recovering. See recovering databases; DB_CREATE_ONLINE_LOG_DEST recovery components parameter, 490, 551 – 552 relational, 393 – 394, 393 DB_DOMAIN parameter, 490 roles, 685 DB_K_CACHE_SIZE parameter, 534 shutting down, 503 – 506, 505 DB_KEEP_CACHE_SIZE parameter, 406 starting up, 498 – 503 DB_NAME parameter, 490 startup states, 503 DB_RECOVERY_FILE_DEST parameter, storage, 475 – 477, 476 – 477 490, 860 – 861 templates, 453 – 455, 453 – 454 DB_RECOVERY_FILE_DEST_SIZE DATAFILE clause, 553 parameter, 490, 860 – 862 Datafiles section for template DB_RECYCLE_CACHE_SIZE definitions, 455 parameter, 406 datatypes, 15 – 16, 290 – 291, 291 DB_UNIQUE_NAME parameter, 490 binary, 301 – 302 DB_WRITER_PROCESSES character, 16 – 17, 291 – 294 parameter, 413 columns, 557 – 558 DBA_ADDM_FINDINGS view, 798, 800 conversion function, 105 DBA_ADDM_TASKS view, 798 date and time, 19, 295 – 301 DBA_ADVISOR_ACTIONS view, numeric, 17 – 19, 294 – 295 798 – 800 row ID, 302 DBA_ADVISOR_FINDINGS view, 798 DATE datatype, 19, 296 DBA_ADVISOR_OBJECTS view, date datatypes, 16, 19, 295 – 301 798 – 800 DATE keyword, 23 DBA_ADVISOR_RATIONALE view, dates 798, 800 arithmetic, 299 – 301 DBA_ADVISOR_ conversions, 111 – 115, 117 – 118, 122 RECOMMENDATIONS view, 798 default format, 473 DBA_ALERT_HISTORY view, 804 format conversions, 91 DBA_AUDIT_POLICIES view, 707 retrieving, 95 DBA_AUDIT_TRAIL view, 700 rounding, 100 DBA_DATA_FILES view, 231, 418, 542, single-row functions, 90 – 101 547, 555 – 556, 815 truncating, 101 DBA_DATAPUMP_JOBS view, 958 – 959 datetime datatypes DBA_DATAPUMP_SESSIONS view, 958 literals, 23 DBA_EXTERNAL_LOCATIONS single-row functions, 90 – 101 view, 977 DB_BLOCK_SIZE parameter, 489, 534 DBA_EXTERNAL_TABLES view, 977 DB_CACHE_SIZE parameter, 406, 534 DBA_FGA_AUDIT_TRAIL view, 708 DB_CREATE_FILE_DEST parameter, DBA_FREE_SPACE view, 231, 815, 902 490, 528, 551 – 552
1042 DBA_HIST_ACTIVE_SESS_HISTORY view – DBWn process DBA_HIST_ACTIVE_SESS_HISTORY DBMS_DATAPUMP.OPEN view, 788 procedure, 939 DBA_HIST_SNAPSHOT view, 787 DBMS_DATAPUMP.START_JOB DBA_HISTOGRAMS view, 815 procedure, 939 DBA_INDEX_PARTITIONS view, 816 DBMS_FGA.ADD_POLICY procedure, DBA_INDEXES view, 815 – 816 705 – 706 DBA_LOCK view, 741 DBMS_FGA.DISABLE_POLICY DBA_OBJ_AUDIT_OPTS view, 703 procedure, 707 DBA_OBJECTS view, 815 – 816 DBMS_FGA.DROP_POLICY DBA_OUTSTANDING_ALERTS view, procedure, 707 804, 862 DBMS_FGA.ENABLE_POLICY DBA_PRIV_AUDIT_OPTS view, 702 procedure, 707 DBA privilege, 687 DBMS_FLASHBACK.TRANSACTION_ DBA role, 685 BACKOUT procedure, 909 DBA_ROLLBACK_SEGS view, 744 DBMS_METADATA package, 733, DBA_SEGMENTS view, 161, 815 937, 939 DBA_SOURCE view, 725 DBMS_MONITOR package, 733, 811 DBA_STMT_AUDIT_OPTS view, 699 DBMS_OBFUSCATION_TOOLKIT DBA_SYS_PRIVS view, 681 – 682 package, 686 DBA_TAB_COLUMNS view, 486 DBMS_SERVER_ALERT package, 804 DBA_TAB_STAT_PREFS view, 776 DBMS_SPACE_ADMIN package, 530 DBA_TABLES view, 485 – 486, 486, 815 DBMS_STATS package, 733, DBA_TABLESPACES view, 231, 541, 815 767 – 770, 773 DBA_TEMP_FILES view, 231, 542, DBMS_STATS.CREATE_EXTENDED_ 556 – 557 STATS procedure, 779 DBA_THRESHOLDS dictionary view, 804 DBMS_STATS.GATHER_DATABASE_ DBA_TRIGGERS view, 725, 733 STATS_JOB_PROC procedure, 768 DBA_UPDATABLE_COLUMNS DBMS_STATS.SET_GLOBAL_PREFS view, 356 procedure, 774 – 775 DBA_WAITERS view, 741, 815 DBMS_STATS.SET_SCHEMA_PREFS DBCA. See Database Configuration procedure, 776 Assistant (DBCA) DBMS_TRACE package, 45 dbca command, 451 DBMS_WORKLOAD_REPOSITORY DBCA Database Templates screen, 455 package, 785 DBMS_ADD package, 797 DBMSs (database management DBMS_AUTO_TASK package, 768 systems), 392 DBMS_CRYPTO package, 686 DBSNMP account, 458, 669 DBMS_DATAPUMP package, DBTIMEZONE function, 96 936 – 937, 939 DBWn process, 406, 408, 413
DCL (Data Control Language) statements – DESC keyword 1043 DCL (Data Control Language) initialization-parameter files, 493 statements, 718 locales, 482 DDL (Data Definition Language) roles, 684 – 685 statements, 718 statistics staleness threshold, 777 description, 6 tablespaces, 526 – 527 event triggers, 730 – 731 user accounts, 669 lock waits, 738 – 739 DEFERRABLE clause, 327 – 328, 571 – 572 tablespaces, 543 DEFERRED keyword, 496 DDL_LOCK_TIMEOUT parameter, 739 deferring constraint checks, 327 – 328, deadlocks, 742 571 – 572 DEBUG privilege, 671 – 672 DEFINE command, 47 – 48 debugging-oriented system privileges, defined column names, 344 – 345 674 – 675 definitions declarative constraints, 347 templates, 455, 483 – 484, 483 DECODE function, 124 – 125, 182, 184 views, 348 DECOMPOSE function, 107 DEGREE parameter, 775 dedicated connections DEL command, 11 – 12 listeners, 603 – 604, 603 – 604 Delete a Database option, 452 Oracle Shared Server, 650 – 651 Delete All Expired button, 878 Dedicated Server Mode, 473 Delete All Obsolete button, 878 dedicated servers DELETE_CATALOG_ROLE role, 685 connection settings, 473, 473 DELETE event triggers, 730 listeners, 602 DELETE privilege, 671 vs. shared servers, 638 – 640, 639 DELETE procedure in ADDM DEFAULT cache, 406 analysis, 797 DEFAULT clause, 307 DELETE statement Default Date setting, 473 audit trails, 704 Default Language setting, 472 rows, 263 – 265, 723 – 724 DEFAULT pool, 406 SQL*Loader, 971 DEFAULT profile, 691 in subqueries, 236 – 237 DEFAULT STORAGE clause, DELETE TABLE statement, 697 532 – 533, 538 deleting DEFAULT TABLESPACE clause, 665 databases, 482 DEFAULT TEMPORARY TABLESPACE rows, 263 – 265, 723 – 724 clause, 534 – 535 template definitions, 484 defaults through views, 351 – 353 column values, 306 – 308 DENSE_RANK function, 166 – 167 data file size, 553 derived columns, 308 date format, 473 DESC keyword, 38 – 39
1044 DESCRIBE command – Dnnn process DESCRIBE command, 9, 42 DISABLE VALIDATE constraint, columns, 304 326 – 327 stored programs, 734 disabled constraints, 324 DESCRIPTION parameter disabling DISPATCHERS, 645 constraints, 325 – 326 listener.ora, 610 FGA policies, 707 destinations for archived redo log files, object auditing, 704 856 – 858, 856 – 857 privilege auditing, 702 DIA0 process, 409 roles, 683 – 684 DIAG process, 409 statement auditing, 700 Diagnosability process, 409 triggers, 733 diagnostic data DISASSOCIATE STATISTICS events, 731 EM Support Workbench, 981 discard files, 968 packaging, 983 – 985, 983 – 985 Disk Backup Location parameter, 870 DIAGNOSTIC_DEST parameter, Disk Backup Type parameter, 870 510, 805 disk space requirements, 425 dictionary-managed tablespaces, 529 Dispatcher process, 409 DICTIONARY view, 488 dispatcher processes, 638 dictionary views, 231 – 235, 485 – 486, DISPATCHERS parameter, 645 – 647 486, 488 Displaymode command, 622 dimensions, 290 DISTINCT keyword direct handoff connections, 603 – 604, 604 aggregate functions, 148 Direct Path API (DPAPI), 937 SELECT, 26 – 27, 40 direct path loading, 972 – 973 subqueries, 225 directories division, 20 Data Pump, 941 – 942, 942 DML (Data Manipulation Language) naming, 597 statements, 252, 718 OFA paths, 427 – 430 DELETE, 263 – 265 Directory Objects screen, 942, 942 description, 5 dirty buffers, 405 event triggers, 729 – 730 DISABLE clause, 733 exam essentials, 274 – 275 DISABLE CONSTRAINT clause, 325 INSERT, 253 – 259 DISABLE NOVALIDATE clause, 326, 347 MERGE, 265 – 267 DISABLE_POLICY procedure, 707 review questions, 276 – 286 DISABLE PRIMARY KEY CASCADE subqueries in, 236 – 237 clause, 325 summary, 274 DISABLE procedure, 768 UPDATE, 259 – 263 DISABLE STORAGE IN ROW clause, 563 views, 356 Dnnn process, 409
documentation in Oracle installation – EEEE code in number conversions 1045 documentation in Oracle installation, 424 dropping dollar signs ($) baselines, 790 identifier names, 304 columns, 314 – 315 number conversions, 115 constraints, 324 – 325 dots (.) FGA policies, 707 number conversions, 115 indexes, 373 substitution variables, 48 redo log groups, 853 double quotation marks (“) redo log members, 853 column names, 558 sequences, 361, 366 identifier names, 304 synonyms, 367 DPAPI (Direct Path API), 937 tables, 316 DRA (Data Recovery Advisor), 802, 890, tablespaces, 537 915 – 917, 916 user accounts, 668 non-system-critical data file recovery, views, 349 – 350 921 – 925, 921 – 922 DUAL table, 28 system-critical data file recovery, 926 _DUMP_DEST parameters, 806 DROP_BASELINE procedure, 790 _DUMP_DIRECTORY parameters, 805 DROP clause, 314 – 315 dump file sets, 944 DROP CONSTRAINT clause, 325 dump files DROP event triggers, 731 Data Pump, 940, 944 DROP_EXTENDED_STATISTICS file locations, 806 procedure, 782 DUMP function, 125 – 126 DROP INDEX statement, 373 DUMPFILE parameter, 948 DROP LOGFILE MEMBER clause, 853 dynamic performance tables, 487 DROP_POLICY procedure, 707 dynamic performance views, 404, DROP PRIMARY KEY CASCADE 487 – 488, 784, 813 – 814 clause, 325 dynamic service registration, 611, 623 – 624 DROP PUBLIC SYNONYM statement, 367 DROP SEQUENCE statement, 361, 366 E DROP_SNAPSHOT_RANGE Easy Connect method, 628 – 629 procedure, 787 Edit Address screen, 616 DROP SYNONYM statement, 367 Edit Archive Mode Parameters dialog box, DROP TABLE statement, 316 463, 464 DROP TABLESPACE statement, 537 EDIT command for SQL buffer, 10, 12 DROP UNIQUE clause, 325 Edit Listener screen, 616, 616 DROP UNUSED COLUMNS clause, 315 editing SQL buffer, 10 – 15 DROP USER statement, 668 EEEE code in number conversions, 116 DROP VIEW statement, 349 – 350
1046 ELSE clause – Enterprise Manager Grid Control ELSE clause, 44 data files, 419, 419 EMNC process, 409 Data Pump, 942, 942, 962 – 967, EMPTY_BLOB function, 126 962 – 963, 965 – 967 EMPTY_CLOB function, 126 DRA, 915 – 917, 916 Enable Archive option, 463 flash recovery area, 860 – 862, 861 ENABLE clause home page, 481, 481 constraints, 325 incidents, 979, 979 triggers, 733 initialization-parameter files, ENABLE NOVALIDATE constraint, 326 493 – 494, 494 ENABLE_POLICY procedure, 707 instance failures, 898 – 899, 898 ENABLE procedure, 768 listeners, 613 – 616, 613 – 616 ENABLE STORAGE IN ROW clause, 563 lock conflicts, 740 – 741, 740 – 741 ENABLE TRIGGERS clause, 905 memory management, 824 – 827, ENABLE VALIDATE constraint, 326 825 – 826 enabling non-system-critical data file recovery, constraints, 325 – 326 921 – 922, 921 – 922 FGA policies, 707 patches. See patches object auditing, 703 performance metrics, 827 – 829, Oracle Managed Files, 551 828 – 829 privilege auditing, 701 – 702 redo logs, 420, 420, 850 – 853, 852, roles, 682 – 683 856 – 857, 856 – 857 statement auditing, 696 – 699 SGA components, 405, 405 triggers, 733 shutting down Oracle, 505 – 506, 505 END BACKUP clause, 868 SQL*Loader, 973 – 974, 973 – 974 END keyword in CASE, 44 starting up Oracle, 500 – 502, 500 – 502 End of Installation screen, 439, 439 system-critical data file recovery, 926 Enterprise Manager tablespace information, 543 – 546, installing, 456 – 457 544 – 545, 613 – 616, 613 – 616 local naming method, 634 – 635, 634 trace files, 812, 813 Enterprise Manager Database Control undo tablespace, 751 – 754, 751, 753 tool, 395, 397 – 398, 398 unusable objects, 817 – 818, 817 – 819 ADDM analysis, 792 – 798, 794 – 797 user error recovery, 905 – 908, 906 – 908 alert logs, 511, 511, 809, 810, 919, 919 Enterprise Manager Gather Statistics alerts, 803 – 804, 803, 979, 979 Wizard, 770 ASH statistics, 788, 788 – 789 Enterprise Manager Grid Control AWR baselines, 790 – 792, 791 – 792 auditing, 704, 705 AWR statistics, 785 – 787, 785 – 786 automated maintenance tasks, 782, 783 backups, 878, 878 optimizer statistics, 770 – 774, 770 – 774 control files, 417, 417, 843, 843 user accounts, 667 – 668, 667 – 668
Enterprise Manager Repository component – external tables 1047 Enterprise Manager Repository EXECUTE_CATALOG_ROLE role, 685 component, 466 EXECUTE privilege, 672, 687 Enterprise Manager Support Workbench, EXECUTE PROCEDURE statement, 697 916, 916, 978 – 979 EXISTS operator, 36 – 37, 224, 228 diagnostic data packaging, 983 – 985, EXIT command 983 – 985 lsnrctl, 621 diagnostic information, 981 SQL*PLUS, 8 exam essentials, 995 – 996 EXIT_CLIENT command incident tracking and closing, expdp, 948 985 – 986, 986 impdp, 952 problem identification, 979 – 980, EXIT_CLIENT parameter, 958 979 – 980 EXP_FULL_DATABASE role, 950 review questions, 997 – 1002 EXP function, 84 service requests, 981 – 982, 982 expdp utility, 940, 944 – 949 summary, 995 expired undo information, 750 environment variables, 427 – 430 explicit locks, 273 equal signs (=) exponent function, 84 equality operator, 29 – 30 exports, Data Pump inequality operator, 30 EM Database Control, 962 – 964, joins, 199 962 – 963 single-row subqueries, 222 expdp, 940, 944 – 949 equality joins, 199 export modes, 943 – 944 equality operator, 29 – 30 expressions, 43 – 44 error codes, client-side, 637 CASE, 44 – 45 errors, views with, 345 – 346 PL/SQL, 725 ESCAPE clause, 37 – 38 extended statistics, 779 – 782 ESTIMATE parameter, 955 EXTENT MANAGEMENT LOCAL ESTIMATE_PERCENT parameter, 775 clause event alerts, 802 temporary tablespaces, 535 Event Monitor process, 409 undo tablespaces, 537 event triggers EXTENT MANAGEMENT LOCAL databases, 732 – 733 UNIFORM SIZE clause, 529 DDL, 730 – 731 extents, 524 DML, 729 – 730 defined, 423, 423 exclamation points (!) for inequality, 30 managing, 528 – 530 EXCLUDE parameter, 953, 956 – 957 external procedures, 601 EXCLUSIVE lock mode, 737 external tables exclusive locks, 274, 737 description, 559 EXEC command, 727 populating, 974 – 978
1048 externally authenticated users – FREELISTS parameter externally authenticated users, 663 – 664 flash recovery area, 463, 859 extproc process, 601 EM Database Control, 860 – 862, 861 EXTRACT function, 96 – 97 managing, 862 occupants, 859 SQL commands, 860 F FLASHBACK ANY TABLE privilege, 680 Flashback Data Archive process, 409 FAILED_LOGIN_ATTEMPTS FLASHBACK DATABASE command, 861 parameter, 691 – 692 flashback features failures, 890 – 891 drop, 902 – 904 instance, 893 – 894 logs, 859 media, 894 queries, 899 – 901 network, 892 table, 904 – 905 statement, 891 – 892 transactions, 909 – 910, 909 – 910 user error, 892 – 893 FLASHBACK ON/OFF clause, 533 user process, 892 FLASHBACK_SCN parameter, 955 FALSE value, 33 FLASHBACK TABLE statement, 902 – 905 FAST_START_IO_TARGET FLASHBACK_TIME parameter, 955 parameter, 898 FLOAT datatype, 295 FAST_START_MTTR_TARGET floating-point literals, 22 parameter, 802, 849, 898 – 899 FLOOR function, 85 FDBA process, 409 FM code in number conversions, 115 FET$ table, 529 FOLLOWS clause, 730 FGA (fine-grained auditing), 705 – 708 FOR DROP clause, 547 File Location Variables dialog box, FOR EACH ROW clause, 729 476, 476 FOR RECOVER option, 539 file locations for databases, 460 – 462, 461 FOR UPDATE clause, 273, 346 file system storage, 459 FORCE LOGGING clause, 533 filenames, parsing, 77 – 78 FORCE mode, 499 – 500 FILESIZE command, 948 FORCE option for views, 345 – 346 FILESIZE parameter, 947 foreign key constraints, 323 – 324, 571 – 572 filling strings, 73 – 75 foreign keys, 394 filters, data and metadata, 956 – 958 formats for dates, 91, 473 fine-grained auditing (FGA), 705 – 708 free buffers, 405 fine-grained object selection, 961 free space fired triggers, 729 dictionary views for, 231 – 235 firewalls, 599 – 600 requirements, 425 fixed views, 487 FREELIST GROUPS parameter, 561 FREELISTS parameter, 561
From an Existing Database (Structure As Well As Data) option – GUID generation 1049 From an Existing Database (Structure As global temporary tables (GTTs), 288, 305 Well As Data) option, 484 globally authenticated users, 664 From an Existing Database (Structure GRANT ANY OBJECT PRIVILEGE Only) option, 484 privilege, 679 From an Existing Template option, 484 GRANT ANY PRIVILEGE privilege, 679 FROM clause GRANT ANY ROLE privilege, 676, 680 DELETE, 263 GRANT event triggers, 731 multiple-table queries, 198 GRANT statement, 672 – 674, 680 – 682 SELECT, 24 – 25, 29 GRANT PROCEDURE statement, 697 FROM_TZ function, 97 GRANT SELECT ON statement, 369 full backups, 863, 873 – 876, 875 – 877 GRANT SEQUENCE statement, 697 full outer joins, 214 GRANT TABLE statement, 697 function-based indexes, 573, 575 granting privileges, 670, 672 – 674, 673, functions, 290 680 – 683, 681 group. See aggregate functions GRANULARITY parameter, 775 nesting, 184 – 186 granules, 403 PL/SQL, 725 – 726 greater than operators, 31 – 32 single-row. See single-row functions greater than signs (>) stored, 395 inequality operator, 30 more than operators, 31 – 32 GREATEST function, 126 – 127 G green-screen applications, 593 Greenwich mean time, 296 G code in number conversions, 115 GROUP BY clause GATHER_ procedures, 769 aggregate functions, 150 – 154 GATHER_DATABASE_STATS_JOB_ superaggregates, 177 – 184 PROC procedure, 768 views, 351 Gather Optimizer Statistics screen, GROUP_ID function, 168, 182 771 – 772, 772 – 773 GROUPING function, 148, 168, 182 GATHER_TABLE_STATS procedure, 779 GROUPING_ID function, 168, 182 – 184 General Purpose or Transaction groups Processing template, 453 – 454 functions. See aggregate functions Generate Database Creation Scripts redo log, 850, 851, 920 option, 477 GTTs (global temporary tables), 288, 305 GET_THRESHOLD procedure, 804 guaranteed undo retention, 753 – 754 GLOBAL_DBNAME parameter, 611, 628 GUID generation, 134 GLOBAL_NAME view, 486 Global Statistics Gathering Options screen, 774, 774
1050 hash functions – INDEX_STATS view IMMEDIATE option H parameter changes, 496 – 497 hash functions, 129 – 130 tablespaces, 539 HAVING clause, 176 – 177 IMP_FULL_DATABASE role, 950, 953 Health Check framework, 979 impdp utility, 940, 949 – 954 Health Monitor (HM) tool, 917 implicit data conversions, 91 heap tables, 558 implicit locks, 273 HELP command imports, Data Pump expdp, 948 EM Database Control, 964 – 967, impdp, 952 965 – 967 Heterogeneous Services component, 601 impdp, 949 – 954 HEXTORAW function, 107 import modes, 943 – 944 high-cardinality columns, 372, 573 network-mode, 955 – 956 high-think applications, 637 IN operator, 35, 224, 227 histograms, 89 – 90 inactive log files, 847 HM (Health Monitor) tool, 917 Incident File Retention Period setting, 985 host naming methods, 627 – 628, 631, 631 Incident Metadata Retention Period HOST parameter setting, 985 listener.ora, 611 incidents tnsnames.ora, 633 dump file locations, 806 hosts, listeners, 608 EM Database Control, 979, 979 hot backups, 868 – 869 packaging, 984 – 985, 985 hyphens (-) tracking and closing, 985 – 986, 986 dates, 299 – 300 INCLUDE parameter, 953, 956 – 957 statement continuation, 8 INCLUDING CONTENTS clause, 537 subtraction, 20 INCLUDING CONTENTS AND DATAFILES clause, 537 inconsistent backups, 863, 868 I INCREMENT BY clause, 360, 365 IDENTIFIED BY keywords, 663, incremental backups, 863, 873 – 876, 682 – 683 875 – 877 IDENTIFIED EXTERNALLY INCREMENTAL parameter, 775 keywords, 664 INDEX ANY TABLE privilege, 680, 682 IDENTIFIED GLOBALLY keywords, 664 INDEX auditing option, 698 identifiers for tables and columns, index-organized tables (IOTs) 303 – 304 description, 395, 558 – 559 IDLE_TIME resource, 689 working with, 575 – 576 image copies, 873 INDEX privilege, 671 INDEX_STATS view, 815