ORACLE10G--使用dbms_advisor 設定ADDM的功能

ADDM 提供效能改善建議
手動產生ADDM report
SQL> @?/rdbms/admin/addmrpt.sql
AWR 預設每60分鐘提供快照. AWR預設是自動執行, 但也能使用DBMS_WORKLOAD_REPOSITORY手動執行
MMON 會啓動ADDM 去分析二筆快照. 然後提出效能改善的建議

dbms_advisor 六個主要功能
1.create_task 開新TASK
2.set_default_task 修改工作
3.delete_task 刪除
4.execute_task 執行
5.get_task_report 取得報告
exec dbms_advisor.get_task_report( task_name IN VARCHAR2,
type IN VARCHAR2 :='TEXT',
level IN VARCHAR2 :='TYPICAL',
section IN VARCHAR2 :='ALL',
owner_name IN VARCHAR2 :=NULL
) Return clob;
type 有TEXT, XML,HTML三種值可以設定
6.set_default_task_parameter 設定工作值

相關view
DBA_ADVISOR_TASKS
DBA_ADVISOR_PARAMETERS
DBA_ADVISOR_DEF_LOG
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_RATIONALE
DBA_ADVISOR_ACTIONS
DBA_ADVISOR_DEF_PARAMETERS
SQL> exec dbms_advisor.set_default_task_parameter('ADDM','DBIO_EXPECTED',9000);<--DBIO_EXPECTED並不是預設參數

關閉ADDM 功能
STATISTICS_LEVEL 預設參數有三種設定值 BASIC, TYPICAL, ALL, 若要關閉ADDM功能, 請設值為BASIC

oracle -- 關於v$lock table

第一次看到v$lock 這個table,
真是一頭霧水, 原來它並不指單純的table lock 或record lock.
ID1、ID2會根據TYPE不同而儲存不同的資訊.
例如TM表示對表的更新LOCK,
TX表示對更新的ROLLBACK資訊.通常update的時候， TM和TX是一組.
TYPE
BL - Buffer hash table instance lock
CF - Control file schema global enqueue lock
CI - Cross-instance function invocation instance lock
CS - Control file schema global enqueue lock
CU - Cursor bind lock
DF - Data file instance lock
DL - Direct loader parallel index create
DM - Mount/startup db primary/secondary instance lock
DR - Distributed recovery process lock
DX - Distributed transaction entry lock
FI - SGA open-file information lock
FS - File set lock
HW - Space management operations on a specific segment lock
IN - Instance number lock
IR - Instance recovery serialization global enqueue lock
IS - Instance state lock
IV - Library cache invalidation instance lock
JQ - Job queue lock
KK - Thread kick lock
MB - Master buffer hash table instance lock
MM - Mount definition gloabal enqueue lock
MR - Media recovery lock
PF - Password file lock
PI - Parallel operation lock
PR - Process startup lock
PS - Parallel operation lock
RE - USE_ROW_ENQUEUE enforcement lock
RT - Redo thread global enqueue lock
RW - Row wait enqueue lock
SC - System commit number instance lock
SH - System commit number high water mark enqueue lock
SM - SMON lock
SN - Sequence number instance lock
SQ - Sequence number enqueue lock
SS - Sort segment lock
ST - Space transaction enqueue lock
SV - Sequence number value lock
TA - Generic enqueue lock
TD - DDL enqueue lock
TE - Extend-segment enqueue lock
TM - DML enqueue lock
TT - Temporary table enqueue lock
TX - Transaction enqueue lock
UL - User supplied lock
UN - User name lock
US - Undo segment DDL lock
WL - Being-written redo log instance lock
WS - Write-atomic-log-switch global enqueue lock

LMODE
1 - No Lock
2 - Row Share
3 - Row Exclusive
4 - Share
5 - Share Row Exclusive
6 - Exclusive

oracle10G -- create external tables 將文字檔存成table 供資料庫存取

1.文字檔emp.dat
10000001,nina,FINANCE
10000002,nina2,FINANCE2
10000003,nina3,FINANCE3
10000004,nina4,FINANCE4

2.sqlplus 操作過程
Microsoft Windows [版本 5.2.3790]
(C) 版權所有 1985-2003 Microsoft Corp。

E:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 9月 27 22:46:32 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
已連線.
建立存放文字檔的目錄. 文字檔要放在這個目錄.
SQL> create or replace directory employee_data as 'C:\employee_data'
2 ;

已建立目錄.

SQL> create table employee_ext
2 (
3 empid number(8),
4 emp_name varchar2(30),
5 dept_name varchar2(20)
6 )
7 organization external
8 (type oracle_loader
9 default directory employee_data
10 access parameters
11 (
12 records delimited by newline
13 fields terminated by ','
14 missing field values are null
15 )
16 location('emp.dat')
17 )
18 ;

已建立表格.


SQL> select * from employee_ext
2 ;

EMPID EMP_NAME DEPT_NAME
---------- ------------------------------ --------------------
10000001 nina FINANCE
10000002 nina2 FINANCE2
10000003 nina3 FINANCE3
10000004 nina4 FINANCE4


3.相關view dba_external_tables

oracle10G -- create external tables 將文字檔存成table 供資料庫存取

1.文字檔emp.dat
10000001,nina,FINANCE
10000002,nina2,FINANCE2
10000003,nina3,FINANCE3
10000004,nina4,FINANCE4

2.sqlplus 操作過程
Microsoft Windows [版本 5.2.3790]
(C) 版權所有 1985-2003 Microsoft Corp。

E:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 9月 27 22:46:32 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
已連線.
建立存放文字檔的目錄. 文字檔要放在這個目錄.
SQL> create or replace directory employee_data as 'C:\employee_data'
2 ;

已建立目錄.

SQL> create table employee_ext
2 (
3 empid number(8),
4 emp_name varchar2(30),
5 dept_name varchar2(20)
6 )
7 organization external
8 (type oracle_loader
9 default directory employee_data
10 access parameters
11 (
12 records delimited by newline
13 fields terminated by ','
14 missing field values are null
15 )
16 location('emp.dat')
17 )
18 ;

已建立表格.


SQL> select * from employee_ext
2 ;

EMPID EMP_NAME DEPT_NAME
---------- ------------------------------ --------------------
10000001 nina FINANCE
10000002 nina2 FINANCE2
10000003 nina3 FINANCE3
10000004 nina4 FINANCE4


3.相關view dba_external_tables

oracle 10G --關於EXPORT DUMP / IMPORT DUMP

MT table 和job同名
export dump /import dump 完成, mt table 會刪除
控制job是mcp process (the master control process)
每一個job會有一個mcp, 格式是_DMnn_
使用select * from V$process 查詢


1. expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS
The data pump job will gather the estimates without actually performing the export .
2. Which view would you query to determine the user sessions attached to a data pump job
DBA_DATAPUMP_SESSIONS
3.使用expdp 只可以傳輸資料到磁碟, 不可以傳輸資料到磁帶
4.expdp help=y or impdp help=y
5.data pump 操作需要三種檔案, dump files, log files, sql files
6.data pump 是server 端操作, 所以dump file 會存在server 端, 不會存在client
7.操作範例
(1)先使用os建立該目錄E:\oracle\product\10.2.0\oradata\data_dump_file
使用sys 連線在server 端建立data dump file 環境
(1)SQL> create directory pdump_dir1 as 'E:\oracle\product\10.2.0\oradata\data_dump_file'
2 ;

已建立目錄.

(3)SQL> grant read,write on directory pdump_dir1 to scott
2 ;

順利授權.
exit

(4)在dos視窗下列舉三種指令
直接匯出不做log
expdp scott/scott directory=pdump_dir1 dumpfile=pdump_dir1.testexp01.dmp
直接匯出做log, 指定匯出dept table
expdp scott/scott directory=pdump_dir1 tables=dept dumpfile=pdump_dir1.testexp03.dmp logfile=pdump_dir1:test.log
直接匯出做log, 將該user 資料全部匯出
expdp scott/scott directory=pdump_dir1 dumpfile=pdump_dir1.testexp02.dmp logfile=pdump_dir1:test.log


Export: Release 10.2.0.1.0 - Production on 星期二, 25 9月, 2007 23:26:51

Copyright (c) 2003, 2005, Oracle. All rights reserved.

連線至: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
啟動 "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=pdump_dir1 dumpfi
le=pdump_dir1.testexp01.dmp
使用 BLOCKS 方法進行預估中...
處理物件類型 SCHEMA_EXPORT/TABLE/TABLE_DATA
總共預估使用 BLOCKS 方法: 128 KB
處理物件類型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
處理物件類型 SCHEMA_EXPORT/TABLE/TABLE
處理物件類型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
處理物件類型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
處理物件類型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
處理物件類型 SCHEMA_EXPORT/TABLE/COMMENT
處理物件類型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
處理物件類型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 已匯出 "SCOTT"."DEPT" 5.656 KB 4 個資料列

. . 已匯出 "SCOTT"."SALGRADE" 5.585 KB 5 個資料列

. . 已匯出 "SCOTT"."BONUS" 0 KB 0 個資料列

. . 已匯出 "SCOTT"."EMP" 0 KB 0 個資料列

已順利載入/取消載入主要表格 "SCOTT"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
SCOTT.SYS_EXPORT_SCHEMA_01 的傾印檔集:
E:\ORACLE\PRODUCT\10.2.0\ORADATA\DATA_DUMP_FILE\PDUMP_DIR1.TESTEXP01.DMP
工作 "SCOTT"."SYS_EXPORT_SCHEMA_01" 已於 23:27:14 順利完成


E:\Documents and Settings\Administrator>

(5) datapump 相關的views select * from dba_datapump_jobs
(6)sqlfile parameter enables 你去執行 DDL 指令
(7)可以不用指定dump file 名稱, 預設名稱是expdat.dmp
(8)使用參數檔, 就是將指令寫成檔案
expdp parfile=myfile.txt
(9)job 使用ctrl + c暫停
(10)參數 包含include不包含exclude
exclude=index
include=schema:"='USER'"
include=table:"IN('USER','USER2')"
條件可下 =, like , in ,where
QUERY=scott.dept:"WHERE ID_DEPT='IT'"
(11)viewing data pump sessions
select sid,serial#
from v$session s, dba_datapump_sessions d
where s.saddr=d.saddr

我的年度目標

2000年通過mcse認證
2002年學習java
2003年通過乙級技術士認證
2004年通過oracle 9i oca
2005年通過oracle 9i ocp
2006年拿到二專學歷
2007年目標通過oracle 10G OCP <--進行中

ORACLE--spool 將sqlplus 操作過程記錄下來

conn / as sysdba
spool 20070925.log --開始記錄

select * from v$database

spool off -- 結束記錄