就我的理解, dbms_file_group這個元件好像是用來
管理data dump file 用的. 它依層次分為group , version (版本), file 三種層次
一.建立file group
begin
dbms_file_group.create_file_group(
file_group_name => 'scott.test_fg',
min_versions =>6,max_versions => 14,
retention_days =>31,
default_directory=>'SH_FILES_DIR',
comments=>'For storing monthly sales data'
);
end;
相關view :select * from dba_file_groups
二.建立file group version
begin
dbms_file_group.create_version(
file_group_name =>'scott.test_fg',
version_name =>'SH_MAY2007',
comments =>'For test'
);
end;
相關view : select * from dba_file_group_versions
三. 將dump file 加入到file group version中
begin
dbms_file_group.add_file(
file_group_name=>'scott.test_fg',
file_name=>'PDUMP_DIR1.TESTEXP01.DMP',
file_type=>dbms_file_group.export_dump_file,
version_name =>'SH_MAY2007'
);
end;
相關view : select * from dba_file_group_files
四. 將dump file 從file group version中移除
begin
dbms_file_group.remove_file(
file_group_name=>'scott.test_fg',
file_name=>'PDUMP_DIR1.TESTEXP01.DMP',
version_name =>'SH_MAY2007'
);
end;
2007年10月30日 星期二
2007年10月28日 星期日
ORACLE10G.about MATERIALIZED VIEW
1.一般view 是一堆sql的別名, 但MATERIALIZED VIEW可以創造實體化的view
2.sample:
CREATE MATERIALIZED VIEW test_dept
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
三種refresh 方式
1.強制refresh
REFRESH FORCE ON DEMAND
2.完整refresh
REFRESH COMPLETE
3.快速refresh
REFRESH FAST ON COMMIT
WITH ROWID AS
SELECT * FROM dept;
限制:
不可以有sysdate,rownum,raw,long, group by 等語法
3.create materialized view log
一.sample:
create materialized view log on dept
with sequence,rowid
including new values
二.建立create materialized view log, 資料修改記錄會另存table
可用下列sql 查詢table名稱
select owner, table_name from dba_tables where table_name like 'MLOG%'
以這個例子為例.
資料修改記錄會另存table MLOG$_DEPT
select * from MLOG$_DEPT
2.sample:
CREATE MATERIALIZED VIEW test_dept
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
三種refresh 方式
1.強制refresh
REFRESH FORCE ON DEMAND
2.完整refresh
REFRESH COMPLETE
3.快速refresh
REFRESH FAST ON COMMIT
WITH ROWID AS
SELECT * FROM dept;
限制:
不可以有sysdate,rownum,raw,long, group by 等語法
3.create materialized view log
一.sample:
create materialized view log on dept
with sequence,rowid
including new values
二.建立create materialized view log, 資料修改記錄會另存table
可用下列sql 查詢table名稱
select owner, table_name from dba_tables where table_name like 'MLOG%'
以這個例子為例.
資料修改記錄會另存table MLOG$_DEPT
select * from MLOG$_DEPT
2007年10月24日 星期三
ＡＳＰ＿ＲＥＷＲＩＴＥ ＳＡＭＰＬＥ
A網址轉到B網址 301導向
RewriteCond Host: ABC\.netfun\.com\.tw
RewriteRule / http\://DEF\.netfun\.com\.tw [I,RP,L]
RewriteCond Host: ABC\.netfun\.com\.tw
RewriteRule /board.asp http\://DEF\.netfun\.com\.tw/board.asp [I,RP,L]
RewriteCond Host: ([^w][^w][^w][A-Z0-9_().-]+)\.netfun\.com\.tw
RewriteRule / /$1/ [I]
#http://www.netfun.com.tw/username = http://www.netfun.com.tw/AA/BB/CC.asp?pageid=username
RewriteRule /([A-Z0-9_()]+) /AA/BB/CC.asp\?pageid=$1 [L,I,U]
RewriteCond Host: ABC\.netfun\.com\.tw
RewriteRule / http\://DEF\.netfun\.com\.tw [I,RP,L]
RewriteCond Host: ABC\.netfun\.com\.tw
RewriteRule /board.asp http\://DEF\.netfun\.com\.tw/board.asp [I,RP,L]
RewriteCond Host: ([^w][^w][^w][A-Z0-9_().-]+)\.netfun\.com\.tw
RewriteRule / /$1/ [I]
#http://www.netfun.com.tw/username = http://www.netfun.com.tw/AA/BB/CC.asp?pageid=username
RewriteRule /([A-Z0-9_()]+) /AA/BB/CC.asp\?pageid=$1 [L,I,U]
2007年10月18日 星期四
oracle10G Using Sorted Hash Clusters sample
SQL> ;
1 create cluster call_cluster(
2 call_number number,
3 call_timestamp number sort,
4 call_duration number sort)
5* hashkeys 10000 single table hash is call_number size 50
SQL> /
1 create table calls (
2 call_number number, call_timestamp number, call_duration number,call_info varchar2(50)) cluster call_cluster
3* (call_number,call_timestamp,call_duration)
SQL> /
已建立表格.
1 create cluster call_cluster(
2 call_number number,
3 call_timestamp number sort,
4 call_duration number sort)
5* hashkeys 10000 single table hash is call_number size 50
SQL> /
1 create table calls (
2 call_number number, call_timestamp number, call_duration number,call_info varchar2(50)) cluster call_cluster
3* (call_number,call_timestamp,call_duration)
SQL> /
已建立表格.
2007年10月16日 星期二
oracle10G使用dbms_sqltune
三大功能
ACCESS PATH ANALYSIS
SQL Structure Analysis
SQL Tuning Advisor Recommendations
sample
------
declare
tname varchar2(30);
begin
tname :=
dbms_sqltune.create_tuning_task(
sql_text=>'select * from scott.dept',
task_name => 'test_tune2'
);
dbms_sqltune.execute_tuning_task('test_tune2');
end;
/
select task_name from user_advisor_log
/
select dbms_sqltune.report_tuning_task('TASK_NAME') from dual
select dbms_sqltune.report_tuning_task('test_tune2') from dual
ACCESS PATH ANALYSIS
SQL Structure Analysis
SQL Tuning Advisor Recommendations
sample
------
declare
tname varchar2(30);
begin
tname :=
dbms_sqltune.create_tuning_task(
sql_text=>'select * from scott.dept',
task_name => 'test_tune2'
);
dbms_sqltune.execute_tuning_task('test_tune2');
end;
/
select task_name from user_advisor_log
/
select dbms_sqltune.report_tuning_task('TASK_NAME') from dual
select dbms_sqltune.report_tuning_task('test_tune2') from dual
2007年10月15日 星期一
ORACLE10G 基本job scheduler使用dbms_scheduler 管理 基本job scheduler
example
begin
dbms_scheduler.create_job
(
job_name => 'DAILY_BACKUP_JOB_01',
job_type => 'STORED_PROCEDURE',
program_name => 'DAILY_BACKUP_PROGRAM',
schedule_name => 'DAILY_BACKUP_WINDOW'
);
end;
dbms_jobs
1.the three main components of the Scheduler -- job , program ,schedules.
2.advanced scheduler components : job classes , windows , resource groups.
(1)JOB 範例
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."P70_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'declare
first_day date;
last_day date;
begin
first_day :=sysdate;
last_day := add_months(first_day,6);
dbms_output.put_line(to_char(last_day,''YYYYMMDD''));
end;',
start_date => systimestamp at time zone '+8:00',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
(2)JOB + programe 範例
BEGIN
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'job_type');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'job_action');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'number_of_arguments');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'program_name', value => 'SYS.P70');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'start_date', value => to_timestamp_tz('2006-02-22 22:59:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
END;
(3) programe 範例
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'SYS.P70',
force=>TRUE);
END;
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'SYS.P70',
attribute=>'PROGRAM_ACTION',
value=>'declare
first_day2 date;
last_day date;
begin
first_day2 :=sysdate;
last_day := add_months(first_day2,6);
dbms_output.put_line(to_char(last_day,''YYYYMMDD''));
end;');
END;
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'SYS.P70');
END;
(4)SCHEDULER 範例
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=MINUTELY',
start_date => systimestamp at time zone '+8:00',
end_date => to_timestamp_tz('2006-02-22 23:00:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
schedule_name => '"SYS"."P70_SCHEDULE"');
END;
修改排程時間
BEGIN
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_SCHEDULE"', attribute => 'end_date', value => to_timestamp_tz('2006-02-25 23:20:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
END;
(5)SCHEDULER + JOB + programe 範例
BEGIN
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'start_date');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'repeat_interval');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'end_date');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'schedule_name', value => 'SYS.P70_SCHEDULE');
END;
(6)disable 或 enable job,scheduler,programe
exec dbms_scheduler.enable(SYS.P70_SCHEDULE);
exec dbms_scheduler.disable(SYS.P70_SCHEDULE);
(7)drop job,scheduler,programe
可以用desc dbms_scheduler 查function內容
exec dbms_scheduler.drop_job(SYS.P70_SCHEDULE);
exec dbms_scheduler.drop_window(SYS.P70_SCHEDULE);
使用dbms_scheduler 管理 進階job scheduler
(8)create job class
BEGIN
sys.dbms_scheduler.create_job_class(
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
resource_consumer_group => 'OTHER_GROUPS',
job_class_name => 'MY_JOB_CLASS');
END;
(9)更改job 的 job class
BEGIN
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'job_class', value => 'DEFAULT_JOB_CLASS');
END;
(10)建立window
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'MY_WINDOW',
resource_plan=>'INTERNAL_PLAN',
schedule_name=>'SYS.P70_SCHEDULE',
duration=>numtodsinterval(60, 'minute'),
window_priority=>'LOW',
comments=>'');
END;
(11)查看job 優先權
select job_name,job_priority from dba_scheduler_jobs
(12)create mapping to assign priorities to resource groups
把user scott mapping 到default 群組.
可設定這幾個V$SESSION參數
ORACLE_USER
CLIENT_OS_USER
CLIENT_MACHINE
CLIENT_PROGRAM
SERVICE_NAME
MODULE_NAME
MODULE_NAME_ACTION
SERVICE_MODULE
SERVICE_MODULE_ACTION
EXPLICIT
dbms_resource_manager.oracle_user
dbms_resource_manager.client_os_user
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.oracle_user,
'SCOTT',
'DEFAULT_CONSUMER_GROUP'
);
dbms_resource_manager.submit_pending_area();
END;
begin
dbms_scheduler.create_job
(
job_name => 'DAILY_BACKUP_JOB_01',
job_type => 'STORED_PROCEDURE',
program_name => 'DAILY_BACKUP_PROGRAM',
schedule_name => 'DAILY_BACKUP_WINDOW'
);
end;
dbms_jobs
1.the three main components of the Scheduler -- job , program ,schedules.
2.advanced scheduler components : job classes , windows , resource groups.
(1)JOB 範例
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."P70_JOB"',
job_type => 'PLSQL_BLOCK',
job_action => 'declare
first_day date;
last_day date;
begin
first_day :=sysdate;
last_day := add_months(first_day,6);
dbms_output.put_line(to_char(last_day,''YYYYMMDD''));
end;',
start_date => systimestamp at time zone '+8:00',
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
(2)JOB + programe 範例
BEGIN
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'job_type');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'job_action');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'number_of_arguments');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'program_name', value => 'SYS.P70');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'start_date', value => to_timestamp_tz('2006-02-22 22:59:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
END;
(3) programe 範例
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'SYS.P70',
force=>TRUE);
END;
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'SYS.P70',
attribute=>'PROGRAM_ACTION',
value=>'declare
first_day2 date;
last_day date;
begin
first_day2 :=sysdate;
last_day := add_months(first_day2,6);
dbms_output.put_line(to_char(last_day,''YYYYMMDD''));
end;');
END;
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'SYS.P70');
END;
(4)SCHEDULER 範例
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=MINUTELY',
start_date => systimestamp at time zone '+8:00',
end_date => to_timestamp_tz('2006-02-22 23:00:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'),
schedule_name => '"SYS"."P70_SCHEDULE"');
END;
修改排程時間
BEGIN
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_SCHEDULE"', attribute => 'end_date', value => to_timestamp_tz('2006-02-25 23:20:00 +8:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
END;
(5)SCHEDULER + JOB + programe 範例
BEGIN
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'start_date');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'repeat_interval');
sys.dbms_scheduler.set_attribute_null( name => '"SYS"."P70_JOB"', attribute => 'end_date');
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'schedule_name', value => 'SYS.P70_SCHEDULE');
END;
(6)disable 或 enable job,scheduler,programe
exec dbms_scheduler.enable(SYS.P70_SCHEDULE);
exec dbms_scheduler.disable(SYS.P70_SCHEDULE);
(7)drop job,scheduler,programe
可以用desc dbms_scheduler 查function內容
exec dbms_scheduler.drop_job(SYS.P70_SCHEDULE);
exec dbms_scheduler.drop_window(SYS.P70_SCHEDULE);
使用dbms_scheduler 管理 進階job scheduler
(8)create job class
BEGIN
sys.dbms_scheduler.create_job_class(
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
resource_consumer_group => 'OTHER_GROUPS',
job_class_name => 'MY_JOB_CLASS');
END;
(9)更改job 的 job class
BEGIN
sys.dbms_scheduler.set_attribute( name => '"SYS"."P70_JOB"', attribute => 'job_class', value => 'DEFAULT_JOB_CLASS');
END;
(10)建立window
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'MY_WINDOW',
resource_plan=>'INTERNAL_PLAN',
schedule_name=>'SYS.P70_SCHEDULE',
duration=>numtodsinterval(60, 'minute'),
window_priority=>'LOW',
comments=>'');
END;
(11)查看job 優先權
select job_name,job_priority from dba_scheduler_jobs
(12)create mapping to assign priorities to resource groups
把user scott mapping 到default 群組.
可設定這幾個V$SESSION參數
ORACLE_USER
CLIENT_OS_USER
CLIENT_MACHINE
CLIENT_PROGRAM
SERVICE_NAME
MODULE_NAME
MODULE_NAME_ACTION
SERVICE_MODULE
SERVICE_MODULE_ACTION
EXPLICIT
dbms_resource_manager.oracle_user
dbms_resource_manager.client_os_user
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
dbms_resource_manager.oracle_user,
'SCOTT',
'DEFAULT_CONSUMER_GROUP'
);
dbms_resource_manager.submit_pending_area();
END;
ORACLE10g設定資料庫臨界值警告
E:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 10月 15 23:09:08 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
已連線.
SQL> create tablespace test datafile 'test01.dbf' size 1m extent management loca
l uniform size 200k
2 ;
已建立表格空間.
(warning alert at 75% full, critical at 90% full)
SQL> exec dbms_server_alert.set_threshold(dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge,'75',dbms_server_alert.operator_ge,'90',1,1,null,d
bms_server_alert.object_type_tablespace,'TEST');
已順利完成 PL/SQL 程序.
SQL> create table test_table (name varchar2(30)) tablespace test storage(minexte
nts 4);
已建立表格.
SQL> select reason from dba_outstanding_alerts
2 ;
REASON
--------------------------------------------------------------------------------
db_recovery_file_dest_size (2147483648 個位元組) 已經使用 %s%%, 還有97.64%可用的
SQL> ALTER TABLESPACE "TEST" ADD DATAFILE 'user06.dbf' SIZE 100M
2 ;
已更改表格空間.
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 10月 15 23:09:08 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
已連線.
SQL> create tablespace test datafile 'test01.dbf' size 1m extent management loca
l uniform size 200k
2 ;
已建立表格空間.
(warning alert at 75% full, critical at 90% full)
SQL> exec dbms_server_alert.set_threshold(dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge,'75',dbms_server_alert.operator_ge,'90',1,1,null,d
bms_server_alert.object_type_tablespace,'TEST');
已順利完成 PL/SQL 程序.
SQL> create table test_table (name varchar2(30)) tablespace test storage(minexte
nts 4);
已建立表格.
SQL> select reason from dba_outstanding_alerts
2 ;
REASON
--------------------------------------------------------------------------------
db_recovery_file_dest_size (2147483648 個位元組) 已經使用 %s%%, 還有97.64%可用的
SQL> ALTER TABLESPACE "TEST" ADD DATAFILE 'user06.dbf' SIZE 100M
2 ;
已更改表格空間.
2007年10月11日 星期四
oracle --管理 ASH (The Active Session History)
管理 ASH (The Active Session History)
每五到十分鐘執行一次
在v$active_session_history有一個欄位session_state,
session_state 有兩個值ON CPU或WAITING, 但值是WAITING又要看event 欄位是否有值, 有值才是真正IDLE TIME
每五到十分鐘執行一次
在v$active_session_history有一個欄位session_state,
session_state 有兩個值ON CPU或WAITING, 但值是WAITING又要看event 欄位是否有值, 有值才是真正IDLE TIME
oracle --小技巧, 查物件用法
desc 加物件名稱
E:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 10月 11 23:41:46 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
已連線.
SQL> desc dbms_server_alert
FUNCTION EXPAND_MESSAGE RETURNS VARCHAR2
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
USER_LANGUAGE VARCHAR2 IN
MESSAGE_ID NUMBER IN
ARGUMENT_1 VARCHAR2 IN
ARGUMENT_2 VARCHAR2 IN
ARGUMENT_3 VARCHAR2 IN
ARGUMENT_4 VARCHAR2 IN
ARGUMENT_5 VARCHAR2 IN
PROCEDURE GET_THRESHOLD
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
METRICS_ID BINARY_INTEGER IN
WARNING_OPERATOR BINARY_INTEGER OUT
WARNING_VALUE VARCHAR2 OUT
CRITICAL_OPERATOR BINARY_INTEGER OUT
CRITICAL_VALUE VARCHAR2 OUT
OBSERVATION_PERIOD BINARY_INTEGER OUT
CONSECUTIVE_OCCURRENCES BINARY_INTEGER OUT
INSTANCE_NAME VARCHAR2 IN
OBJECT_TYPE BINARY_INTEGER IN
OBJECT_NAME VARCHAR2 IN
PROCEDURE SET_THRESHOLD
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
METRICS_ID BINARY_INTEGER IN
WARNING_OPERATOR BINARY_INTEGER IN
WARNING_VALUE VARCHAR2 IN
CRITICAL_OPERATOR BINARY_INTEGER IN
CRITICAL_VALUE VARCHAR2 IN
OBSERVATION_PERIOD BINARY_INTEGER IN
CONSECUTIVE_OCCURRENCES BINARY_INTEGER IN
INSTANCE_NAME VARCHAR2 IN
OBJECT_TYPE BINARY_INTEGER IN
OBJECT_NAME VARCHAR2 IN
FUNCTION VIEW_THRESHOLDS RETURNS THRESHOLD_TYPE_SET
SQL>
E:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 10月 11 23:41:46 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
已連線.
SQL> desc dbms_server_alert
FUNCTION EXPAND_MESSAGE RETURNS VARCHAR2
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
USER_LANGUAGE VARCHAR2 IN
MESSAGE_ID NUMBER IN
ARGUMENT_1 VARCHAR2 IN
ARGUMENT_2 VARCHAR2 IN
ARGUMENT_3 VARCHAR2 IN
ARGUMENT_4 VARCHAR2 IN
ARGUMENT_5 VARCHAR2 IN
PROCEDURE GET_THRESHOLD
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
METRICS_ID BINARY_INTEGER IN
WARNING_OPERATOR BINARY_INTEGER OUT
WARNING_VALUE VARCHAR2 OUT
CRITICAL_OPERATOR BINARY_INTEGER OUT
CRITICAL_VALUE VARCHAR2 OUT
OBSERVATION_PERIOD BINARY_INTEGER OUT
CONSECUTIVE_OCCURRENCES BINARY_INTEGER OUT
INSTANCE_NAME VARCHAR2 IN
OBJECT_TYPE BINARY_INTEGER IN
OBJECT_NAME VARCHAR2 IN
PROCEDURE SET_THRESHOLD
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
METRICS_ID BINARY_INTEGER IN
WARNING_OPERATOR BINARY_INTEGER IN
WARNING_VALUE VARCHAR2 IN
CRITICAL_OPERATOR BINARY_INTEGER IN
CRITICAL_VALUE VARCHAR2 IN
OBSERVATION_PERIOD BINARY_INTEGER IN
CONSECUTIVE_OCCURRENCES BINARY_INTEGER IN
INSTANCE_NAME VARCHAR2 IN
OBJECT_TYPE BINARY_INTEGER IN
OBJECT_NAME VARCHAR2 IN
FUNCTION VIEW_THRESHOLDS RETURNS THRESHOLD_TYPE_SET
SQL>
2007年10月4日 星期四
oracle 10G -- UNDO manager
UNDO manager 可分手動及自動兩種
Automatic Undo Management 下列三個參數控制
UNDO_MANAGEMENT 可設 MANUAL 或 AUTO
UNDO_TABLESPACE
UNDO_RETENTION 預設值是900秒, 如果tablespace 空間不夠, 新資料會覆蓋舊資料.
如果tablespace 空間夠, 資料最少保持900秒
(1)create undo tablespace UNDOTABS1 datafile 'c:\oracle\product\10.1.0\oradata\undotbs_01.dbf'
size 100m autoextend on retention guarantee;
Using the Retention Guarantee Option期限內UNDO資料保證不會消失
(2)alter tablespace UNDOTABS1 retention guarantee;
由scn 查time 或由 time 查 scn
select current_scn,scn_to_timestamp(current_scn) ,timestamp_to_scn(sysdate)
from v$database
select tablespace_name ,retention from dba_tablespaces
OEＭ介面可以使用The Undo Advisor 提供 undo tablespace size 建議值, undo retention time 建議值
Automatic Undo Management 下列三個參數控制
UNDO_MANAGEMENT 可設 MANUAL 或 AUTO
UNDO_TABLESPACE
UNDO_RETENTION 預設值是900秒, 如果tablespace 空間不夠, 新資料會覆蓋舊資料.
如果tablespace 空間夠, 資料最少保持900秒
(1)create undo tablespace UNDOTABS1 datafile 'c:\oracle\product\10.1.0\oradata\undotbs_01.dbf'
size 100m autoextend on retention guarantee;
Using the Retention Guarantee Option期限內UNDO資料保證不會消失
(2)alter tablespace UNDOTABS1 retention guarantee;
由scn 查time 或由 time 查 scn
select current_scn,scn_to_timestamp(current_scn) ,timestamp_to_scn(sysdate)
from v$database
select tablespace_name ,retention from dba_tablespaces
OEＭ介面可以使用The Undo Advisor 提供 undo tablespace size 建議值, undo retention time 建議值
2007年10月2日 星期二
oarcle 10G -- analyze data (手動收集)
gather_table_stats 收集所有table statistics
gather_index_stats 收集所有index statistics
gather_schema_stats 收集所有schema statistics
gather_database_stats 收集所有database statistics
gather_dictionary_stats 收集所有dictionary statistics
gather_system_stats 收集所有system statistics
dbms_stats另外的指令
dbms_stats.delete_*_stats
dbms_stats.update_*_stats
dbms_stats.import_*_stats
dbms_stats.restore_*_stats
dbms_stats.set_*_stats
dbms_stats.lock_*_stats
dbms_stats.unlock_*_stats
sample:
exec dbms_stats.gather_schema_stats('scott',dbms_stats.auto_sample_size);
exec dbms_stats.delete_table_stats('scott','dept');
相關view dba_optstat_operations,dba_tab_stats_history
gather_index_stats 收集所有index statistics
gather_schema_stats 收集所有schema statistics
gather_database_stats 收集所有database statistics
gather_dictionary_stats 收集所有dictionary statistics
gather_system_stats 收集所有system statistics
dbms_stats另外的指令
dbms_stats.delete_*_stats
dbms_stats.update_*_stats
dbms_stats.import_*_stats
dbms_stats.restore_*_stats
dbms_stats.set_*_stats
dbms_stats.lock_*_stats
dbms_stats.unlock_*_stats
sample:
exec dbms_stats.gather_schema_stats('scott',dbms_stats.auto_sample_size);
exec dbms_stats.delete_table_stats('scott','dept');
相關view dba_optstat_operations,dba_tab_stats_history
2007年10月1日 星期一
oracle 10G -- 讓oracle automatic Optimizer Statistics Collection Worksble
1.Taanalyze data
2.Column statistics
3.Index statistics
4.System statistics
在oracle 8i 以前版本使用analyze table 收集, 在oracle 8i後使用 dbms_stats 收集
在oracle 10G oracle server 自行收集(defaule cost base)
5.讓參數statistics_level 為typical 或 ALL
oracle 會使用dbms_stats 運作
6.Oracle defined automatic optimizer statistics collection job
select * from dba_scheduler_jobs
GATHER_STATS_JOB
enable job
SQL > begin
dbms_scheduler.enable('gather_stats_job');
end;
disable job
SQL > begin
dbms_scheduler.disable('gather_stats_job');
end;
7.session level statistics
wait statistics
segment usage statistics
2.Column statistics
3.Index statistics
4.System statistics
在oracle 8i 以前版本使用analyze table 收集, 在oracle 8i後使用 dbms_stats 收集
在oracle 10G oracle server 自行收集(defaule cost base)
5.讓參數statistics_level 為typical 或 ALL
oracle 會使用dbms_stats 運作
6.Oracle defined automatic optimizer statistics collection job
select * from dba_scheduler_jobs
GATHER_STATS_JOB
enable job
SQL > begin
dbms_scheduler.enable('gather_stats_job');
end;
disable job
SQL > begin
dbms_scheduler.disable('gather_stats_job');
end;
7.session level statistics
wait statistics
segment usage statistics
oracle10G -- 使用automatic memory management 功能
<自動管理SGA>(ASMM)
Background process : MMAN
Enable Automatic SGA:
設statistics_level to typical 或 all (同時也啟動 The Automatic Workload Repository AWR功能)
設sga_target 大於 0
1.oracle 建議不要設sga_max_size參數
2.SGA包含下列兩種記憶體 : oracle 管理記憶體, user自定記憶體
2.1 oracle 建議不要更改 下列四個auto-tuned 參數, oracle server 會自行管理
Automatically Managed SGA Components
Buffer cache(DB_CACHE_SIZE)
Shared pool(SHARED_POOL_SIZE)
Large pool(LARGE_POOL_SIZE)
Java pool(JAVA_POOL_SIZE)
2.2 Manually Sized SGA Components
You are using automatic shared memory management configuration.
Which five initialization parameters will take their memory from the SGA_TARGET paramete
(1)LOG_BUFFER
(2)DB_nk_CACHE_SIZE
(3)STREAMS_POOL_SIZE
(4)DB_KEEP_CACHE_SIZE
(5)DB_RECYCLE_CACHE_SIZE
限定使用總sga記憶體 SGA_MAX_SIZE
3.動態修改sga_target 的值
alter system set sga_target = 600M
相關VIEW
v$vgastat, v$sga
<自動管理PGA>
Enable Automatic PGA:
workarea_size_policy 設auto
PGA_AGGREGATE_TARGET > 0
Background process : MMAN
Enable Automatic SGA:
設statistics_level to typical 或 all (同時也啟動 The Automatic Workload Repository AWR功能)
設sga_target 大於 0
1.oracle 建議不要設sga_max_size參數
2.SGA包含下列兩種記憶體 : oracle 管理記憶體, user自定記憶體
2.1 oracle 建議不要更改 下列四個auto-tuned 參數, oracle server 會自行管理
Automatically Managed SGA Components
Buffer cache(DB_CACHE_SIZE)
Shared pool(SHARED_POOL_SIZE)
Large pool(LARGE_POOL_SIZE)
Java pool(JAVA_POOL_SIZE)
2.2 Manually Sized SGA Components
You are using automatic shared memory management configuration.
Which five initialization parameters will take their memory from the SGA_TARGET paramete
(1)LOG_BUFFER
(2)DB_nk_CACHE_SIZE
(3)STREAMS_POOL_SIZE
(4)DB_KEEP_CACHE_SIZE
(5)DB_RECYCLE_CACHE_SIZE
限定使用總sga記憶體 SGA_MAX_SIZE
3.動態修改sga_target 的值
alter system set sga_target = 600M
相關VIEW
v$vgastat, v$sga
<自動管理PGA>
Enable Automatic PGA:
workarea_size_policy 設auto
PGA_AGGREGATE_TARGET > 0
ASP -- WebService連接語法
WebService 第一版語法
Set WS = Server.CreateObject("cpec.ws1")
Set RS = WS.URL2RS("http://www.yam.com.tw")
Set WS = Nothing
WebService 第二版語法
Set WS = Server.CreateObject("cpec.ws2")
WS.SetURL("http://www.yam.com.tw")
Set RS1 = WS.GO
Set WS = Nothing
Set WS = Server.CreateObject("cpec.ws1")
Set RS = WS.URL2RS("http://www.yam.com.tw")
Set WS = Nothing
WebService 第二版語法
Set WS = Server.CreateObject("cpec.ws2")
WS.SetURL("http://www.yam.com.tw")
Set RS1 = WS.GO
Set WS = Nothing
訂閱：
文章 (Atom)
