oracle数据库迷你课堂技巧、诀窍和方法

1、Frank Fu教育顾问Oracle 大学 甲骨文公司Oracle 数据库的提示、技巧和技术概述本课程重点介绍适用于数据库管理的 Oracle9i Release 2 的新特性要完全理解这里的许多新的特性，需要具备 Oracle 数据库（特别是 Oracle8 和 Oracle8i）使用经验显示执行计划SQL*Plus 自动跟踪创建 plan_table 表。创建并授予 plustrace 角色。 Autotrace 语法：SQL $ORACLE_HOME/sqlplus/admin/plustrce.sqlSQL GRANT plustrace TO scott;SET AUTOTRACE

2、Off | On | Traceonly Explain | Statistics 生成执行计划可以在没有跟踪的情况下使用需要 plan_table 表 utlxplan.sql创建说明计划：SQLEXPLAIN PLAN FOR 2 SELECT last_name FROM hr.employees;使用 dbms_xplan.display 查询 plan_table 表查询 plan_table，以显示执行计划：直接查询 plan_table。使用脚本 utlxpls.sql（隐藏并行查询信息）。使用脚本 utlxplp.sql（显示并行查询信息）。使用 dbms_xplan 程序包。

3、SQL SELECT * 2 FROM TABLE(dbms_xplan.display);Oracle9i 中的元数据 API 引进了一个新的程序包 DBMS_METADATA 利用这个程序包，您可以在以下模式下提取元数据：浏览模式，或编程模式，其中程序可以指定：要检索的对象的类型各种选择准则默认的输出转换是 XML，但也可以是任意格式（使用 XSL）。Oracle9i 中的元数据 API 浏览示例查询可以包含 WHERE 子句。输出是 SQL 格式的，但也可以是 XML 格式。结合 SPOOL 语句用于 XML 文件可以根据 SELECT 语句的结果提取任意组合的对象。在提取时运行一个假脱

4、机程序从而提供一个可以立即编辑的文件。SQL SELECT dbms_metadata.get_ddl (TABLE,SALES) 2 FROM dual;列表分区的默认分区为其它列表分区不包含的所有值创建一个 DEFAULT 分区：CREATE TABLE customer.PARTITION BY LIST (state) (PARTITION p1 VALUES (CA,CO), PARTITION p2 VALUES (FL,TX), PARTITION p3 VALUES ( DEFAULT ) );范围列表组合分区列表（状态）范围（月）CREATE TABLE customer .

5、PARTITION BY RANGE (month)SUBPARTITION BY LIST (state) .;p1_s1p1_s2p1_s3p2_s1p2_s2p2_s3p3_s1p3_s2p3_s3p4_s1p4_s2p4_s3 3 6 9 ALTER TABLE employees 2 RENAME COLUMN salary TO sal;SQL ALTER TABLE employees 2 RENAME CONSTRAINT sys_c002691 3 TO pk_employees_id;RMAN：控制存档日志空间的使用避免恢复期间由于用于存储存档日志的磁盘空间不足引起的错误定

6、义存储空间大小，用于从备份集拷贝到磁盘中的存档日志结合使用 MAXSIZE 选项与 DELETE 选项RMAN RECOVER DATABASE 2 DELETE 3 ARCHIVELOG MAXSIZE 100K;备份服务器参数文件当 CONFIGURE CONTROLFILE AUTOBACK = ON 时自动进行备份利用 BACKUP SPFILE进行显式备份RMAN BACKAUP COPIES 2 DEVICE TYPE sbt SPFILE;RMAN BACKUP SPFILE;持久性配置参数可自定义的配置参数简化了 RMAN 操作。一次性设定默认的设置，并用于随后的作业。数据库管

7、理员可以调用 RMAN 并且用命令： BACKUP DATABASE 来备份数据库。Oracle9i 提供了新的 CONFIGURE 命令，以持久替换默认的设置。配置值存储在控制文件中，并根据需要重新与恢复目录同步。自动通道分配这个特性适用于 BACKUP、COPY 和 RESTORE 命令。如果在 RMAN 命令中没有显式指定一个通道，那么将自动分配一个通道。通过 CONFIGURE 命令指定默认值。这个特性的好处是简化了后续命令，从而改善了易用性。CONFIGURE CHANNEL 命令使用该命令来配置用于所有或个别通道的设置：CONFIGURE CHANNEL n ;channel_op

8、tion_list := TYPE, NAME, PARMS, CONNECT STRING, DEBUG, FORMAT, TRACE, MAXPIECESIZE, RATE, MAXOPENFILES, SEND企业管理器备份向导备份向导：删除存档日志备份向导：重载 RMAN 配置 DBNEWID 实用工具数据库的 DBID 数据库的 DBNAME 数据库的 DBNAME 和 DBIDd:nidDBNEWID: Release 9.2.0.1.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights res

9、erved.Keyword Description (Default)-TARGET Username/Password (NONE)DBNAME New database name (NONE)LOGFILE Output Log (NONE)REVERT Revert failed change NOSETNAME Set a new database name only NOAPPEND Append to output log NOHELP Displays these messages NO共享池顾问SQL SELECT shared_pool_size_for_estimate A

10、S pool_size, 2 estd_lc_size, estd_lc_time_saved 3 FROM v$shared_pool_advice;POOL_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED- - - 32 8 7868 40 15 7868 48 17 7868 56 17 7868 64 17 7868 72 17 7868 80 17 7868 88 17 7868 96 17 7868企业管理器共享池大小顾问工具平均恢复时间 (MTTR) 顾问通过设置初始化参数收集统计信息FAST_START_MTTR_TARGET 设为非零值STATIST

11、ICS_LEVEL 设为 TYPICAL 或 ALL查看不同的 FAST_START_MTTR_TARGET 设置的预计开销在视图 v$mttr_target_advice 的行中通过到这个视图的企业管理器图形化界面利用企业管理器查看 MTTR 顾问V$PGA_TARGET_ADVICE TARGET_MB CACHE_HIT_PERCENT ESTD_OVERALLOC_COUNT- - - 63 23 367 125 24 30 250 30 3 375 39 1 500 58 0 600 59 0 700 59 0 800 60 0 900 60 0 1000 61 0 76 0 300

12、0 83 0 4000 85 0PGA 大小调整建议结论LOW_KB HIGH_KB OPTIMAL ONEPASS MPASS- - - - - 8 16 156107 0 0 16 32 0 0 64 128 13 0 0 128 256 58 0 0 256 512 10 0 0 512 1024 653 0 0 1024 2048 530 0 0 2048 4096 509 0 0 4096 8192 227 0 0 8192 16384 176 0 0 16384 32768 65536 66 103 0 65536 131072 262144 0 48 0262144 524288

13、 0 23 0企业管理器中的 PGA 大小顾问工具输出数据压缩CREATE TABLE employees( employee_id INTEGER, first_name VACHAR2(20), ) COMPRESS; CREATE TABLESPACE sample DATAFILE sample01.dbf SIZE 20M DEFAULT COMPRESS; CREATE TABLE countries( country_id CHAR(2), country_name VARCHAR2(40), region_id NUMBER ) PRESS TABLESPACEsample;

14、数据压缩优化： 支持行转移CREATE TABLE employees (employee_id NUMBER, first_name VARCHAR2(20), last_name VARCHAR2(30), .) ENABLE ROW MOVEMENT; （分区和未分区的)本地管理的 SYSTEM 表空间利用一个本地管理的 SYSTEM 表空间创建数据库：CREATE DATABASE mydb .DATAFILE system01.dbf SIZE 100M EXTENT MANAGEMENT LOCAL.;闪回查询- Query based on past SCN number:SEL

15、ECT employee_id, manager_idFROM employeesAS OF SCN 81591;- Find changes made between 1-2 hours ago:SELECT a.last_name, b.last_nameFROM employees AS OF TIMESTAMP SYSDATE-1/24 a FULL OUTER JOIN employees AS OF TIMESTAMP SYSDATE-2/24 b ON (a.employee_id = b.employee_id)- Create new table based on old data:CREATE TABLE new_employees ASSELECT * FROM employees AS OF SCN 90932;提醒 请完成 OracleWorld 会议调查谢谢！接下来，您可以.访问甲骨文产品演示区，了解 Oracle 软件和服务是如何工作的作客甲骨文产品演示区中的 “专家见面会”，结识顶尖的 Or