oracle10G關於file group

就我的理解, 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;

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

ＡＳＰ＿ＲＥＷＲＩＴＥ ＳＡＭＰＬＥ

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]

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> /

已建立表格.

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

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;

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 ;

已更改表格空間.