课程第37次（2018-09-03星期一）
Skillset 3：Section 8：
numyminterval()和numtodsinterval()这两个函数就是用来产生时间间隔：
create table sh.sales_history_2006_2(
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL
)
partition by range (time_id)
interval(numtodsinterval(1,'MINUTE'))
(
partition sal1 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace SALES_TBS1,
partition sal2 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace SALES_TBS2,
partition sal3 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace SALES_TBS3,
partition sal4 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace SALES_TBS4
);
select* from dba_tables t
where t.owner='SH' and t.table_name='SALES_HISTORY_2006_2';
select* from dba_tab_partitions tp
where tp.table_owner='SH'and tp.table_name='SALES_HISTORY_2006_2';
TABLE_OWNERTABLE_NAMECOMPOSITEPARTITION_NAMESUBPARTITION_COUNTHIGH_VALUEHIGH_VALUE_LENGTHPARTITION_POSITIONTABLESPACE_NAMEPCT_FREEPCT_USEDINI_TRANSMAX_TRANSINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTMAX_EXTENTMAX_SIZEPCT_INCREASEFREELISTSFREELIST_GROUPSLOGGINGCOMPRESSIONCOMPRESS_FORNUM_ROWSBLOCKSEMPTY_BLOCKSAVG_SPACECHAIN_CNTAVG_ROW_LENSAMPLE_SIZELAST_ANALYZEDBUFFER_POOLGLOBAL_STATSUSER_STATS
1SHSALES_HISTORY_2006_2NOSAL10<Long>831SALES_TBS110 1255655361048576121474836452147483645 YESDISABLED DEFAULTNONO
2SHSALES_HISTORY_2006_2NOSAL20<Long>832SALES_TBS210 1255655361048576121474836452147483645 YESDISABLED DEFAULTNONO
3SHSALES_HISTORY_2006_2NOSAL30<Long>833SALES_TBS310 1255655361048576121474836452147483645 YESDISABLED DEFAULTNONO
4SHSALES_HISTORY_2006_2NOSAL40<Long>834SALES_TBS410 1255655361048576121474836452147483645 YESDISABLED DEFAULTNONO
5SHSALES_HISTORY_2006_2NOSYS_P410<Long>835USERS10 1255655361048576121474836452147483645 YESDISABLED DEFAULTNONO
6SHSALES_HISTORY_2006_2NOSYS_P420<Long>836USERS10 1255655361048576121474836452147483645 YESDISABLED DEFAULTNONO
create table sh.sales_history_2006(
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL
)
partition by range (time_id)
interval(numtoyminterval(1,'YEAR'))
(
partition sal1 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace SALES_TBS1,
partition sal2 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace SALES_TBS2,
partition sal3 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace SALES_TBS3,
partition sal4 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace SALES_TBS4
)
selectto_char(hire_date,'YYYY-MM-DD:HH24:MI:SS') ,
hire_date + interval '36513:00:25.312' day(3) to second(3) fromhr.employees
where employee_id=100;
TO_CHAR(HIRE_DATE,'YYYY-MM-DD:HIRE_DATE+INTERVAL'36513:00:25
11987-06-17:00:00:006/16/1988 1:00:25 PM
create table sh.sales_history_2006_3(
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL
)
partition by range (time_id)
interval(interval '36513:00:25.312' day(3) to second(3) )
(
partition sal1 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace SALES_TBS1,
partition sal2 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace SALES_TBS2,
partition sal3 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace SALES_TBS3,
partition sal4 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace SALES_TBS4
);
select* from system_privilege_mapm where m.namelike 'FLASHBACK%';
PRIVILEGENAMEPROPERTY
1-243FLASHBACK ANY TABLE0
2-350FLASHBACK ARCHIVE ADMINISTER0
---------------------
Section 10: Capturing and Propagating Streams
---on emrep/prod1
---archive log list;
---check hr user on target
alter system set global_names=true;
create directory src_dir as '/home/oracle/files';
create directory dst_dir as '/home/oracle/files';
create tablespace streams_tbs datafile '/u01/app/oracle/oradata/PROD1/streams_tbs.dbf' size 100m
/
create user strmadmin identified by streams_123 default tablespace streams_tbs
/
grant dba,select_catalog_role,select any dictionary to strmadmin
/
begin
dbms_streams_auth.grant_admin_privilege('STRMADMIN',true);
end;
/
conn strmadmin/streams_123@prod1
create database link emrep
connect to strmadmin identified by streams_123
using 'emrep'
/
conn strmadmin/streams_123@emrep
create database link prod1
connect to strmadmin identified by streams_123
using 'prod1'
/
conn strmadmin/streams_123@prod1
begin
dbms_streams_adm.maintain_tables(
table_names=>'hr.employees,hr.departments',
source_directory_object=>'SRC_DIR',
destination_directory_object=>'DST_DIR',
source_database=>'PROD1',
destination_database=>'EMREP',
capture_name => 'PROD1_CAPTURE',
propagation_name => 'PROD1_PROPAGATION',
perform_actions=>true,
bi_directional=>false,
include_ddl=>true,
instantiation=>dbms_streams_adm.instantiation_table_network
);
end;
/
select STREAMS_TYPE,rule_name,RULE_TYPE from DBA_STREAMS_TABLE_RULES
conn hr/hr
@3_10_4.sql
BEGIN
DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
rule_name => 'departments4',
transform_function => 'hr.zero_sal');
END;
/
---if errorsee
EXECUTE DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
execute dbms_streams_adm.RECOVER_OPERATION(operation_mode = 'FORWARD'/operation_mode = 'ROLLBACK'/operation_mode = 'PURGE');
DBA_RECOVERABLE_SCRIPT_ERRORS
DBA_RECOVERABLE_SCRIPT
DBA_RECOVERABLE_SCRIPT_PARAMS
DBA_RECOVERABLE_SCRIPT_BLOCKS
--------------------
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAYTIME',
group_or_subplan => 'OLTP',
new_switch_for_call => true
);
dbms_resource_manager.submit_pending_area();
END;
页:
[1]