课程第37次（2018-09-03星期一）

botang

Skillset 3：
Section 8：
numyminterval()和numtodsinterval()这两个函数就是用来产生时间间隔：

1. create table sh.sales_history_2006_2(
   
2. PROD_ID          NUMBER NOT NULL,
   
3. CUST_ID         NUMBER NOT NULL,
   
4. TIME_ID         DATE NOT NULL,
   
5. CHANNEL_ID         NUMBER NOT NULL,
   
6. PROMO_ID         NUMBER NOT NULL,
   
7. QUANTITY_SOLD         NUMBER(10,2) NOT NULL,
   
8. AMOUNT_SOLD         NUMBER(10,2) NOT NULL
   
9. )
   
10. partition by range (time_id)
    
11. interval(numtodsinterval(1,'MINUTE'))
    
12. (
    
13.          partition sal1 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace SALES_TBS1,
    
14.          partition sal2 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace SALES_TBS2,
    
15.          partition sal3 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace SALES_TBS3,
    
16.          partition sal4 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace SALES_TBS4
    
17. );

复制代码

1. select  * from dba_tables t
   
2. where t.owner='SH' and t.table_name='SALES_HISTORY_2006_2';
   
3. 
   
4. select  * from dba_tab_partitions tp
   
5. where tp.table_owner='SH'  and tp.table_name='SALES_HISTORY_2006_2';

复制代码

	TABLE_OWNER	TABLE_NAME	COMPOSITE	PARTITION_NAME	SUBPARTITION_COUNT	HIGH_VALUE	HIGH_VALUE_LENGTH	PARTITION_POSITION	TABLESPACE_NAME	PCT_FREE	PCT_USED	INI_TRANS	MAX_TRANS	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENT	MAX_EXTENT	MAX_SIZE	PCT_INCREASE	FREELISTS	FREELIST_GROUPS	LOGGING	COMPRESSION	COMPRESS_FOR	NUM_ROWS	BLOCKS	EMPTY_BLOCKS	AVG_SPACE	CHAIN_CNT	AVG_ROW_LEN	SAMPLE_SIZE	LAST_ANALYZED	BUFFER_POOL	GLOBAL_STATS	USER_STATS
1	SH	SALES_HISTORY_2006_2	NO	SAL1	0	<Long>	83	1	SALES_TBS1	10		1	255	65536	1048576	1	2147483645	2147483645				YES	DISABLED										DEFAULT	NO	NO
2	SH	SALES_HISTORY_2006_2	NO	SAL2	0	<Long>	83	2	SALES_TBS2	10		1	255	65536	1048576	1	2147483645	2147483645				YES	DISABLED										DEFAULT	NO	NO
3	SH	SALES_HISTORY_2006_2	NO	SAL3	0	<Long>	83	3	SALES_TBS3	10		1	255	65536	1048576	1	2147483645	2147483645				YES	DISABLED										DEFAULT	NO	NO
4	SH	SALES_HISTORY_2006_2	NO	SAL4	0	<Long>	83	4	SALES_TBS4	10		1	255	65536	1048576	1	2147483645	2147483645				YES	DISABLED										DEFAULT	NO	NO
5	SH	SALES_HISTORY_2006_2	NO	SYS_P41	0	<Long>	83	5	USERS	10		1	255	65536	1048576	1	2147483645	2147483645				YES	DISABLED										DEFAULT	NO	NO
6	SH	SALES_HISTORY_2006_2	NO	SYS_P42	0	<Long>	83	6	USERS	10		1	255	65536	1048576	1	2147483645	2147483645				YES	DISABLED										DEFAULT	NO	NO

1. create table sh.sales_history_2006(
   
2. PROD_ID          NUMBER NOT NULL,
   
3. CUST_ID         NUMBER NOT NULL,
   
4. TIME_ID         DATE NOT NULL,
   
5. CHANNEL_ID         NUMBER NOT NULL,
   
6. PROMO_ID         NUMBER NOT NULL,
   
7. QUANTITY_SOLD         NUMBER(10,2) NOT NULL,
   
8. AMOUNT_SOLD         NUMBER(10,2) NOT NULL
   
9. )
   
10. partition by range (time_id)
    
11. interval(numtoyminterval(1,'YEAR'))
    
12. (
    
13.          partition sal1 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace SALES_TBS1,
    
14.          partition sal2 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace SALES_TBS2,
    
15.          partition sal3 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace SALES_TBS3,
    
16.          partition sal4 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace SALES_TBS4
    
17. )

复制代码

1. select  to_char(hire_date,'YYYY-MM-DD:HH24:MI:SS') ,
   
2.             hire_date + interval '365  13:00:25.312' day(3) to second(3)      from  hr.employees
   
3.   where employee_id=100;

复制代码

	TO_CHAR(HIRE_DATE,'YYYY-MM-DD:	HIRE_DATE+INTERVAL'36513:00:25
1	1987-06-17:00:00:00	6/16/1988 1:00:25 PM

1.    create table sh.sales_history_2006_3(
   
2.     PROD_ID          NUMBER NOT NULL,
   
3.     CUST_ID         NUMBER NOT NULL,
   
4.     TIME_ID         DATE NOT NULL,
   
5.     CHANNEL_ID         NUMBER NOT NULL,
   
6.     PROMO_ID         NUMBER NOT NULL,
   
7.     QUANTITY_SOLD         NUMBER(10,2) NOT NULL,
   
8.     AMOUNT_SOLD         NUMBER(10,2) NOT NULL
   
9.     )
   
10.     partition by range (time_id)
    
11.     interval(  interval '365  13:00:25.312' day(3) to second(3)      )
    
12.     (
    
13.              partition sal1 values less than (to_date('2003-01-01','YYYY-MM-DD')) tablespace SALES_TBS1,
    
14.              partition sal2 values less than (to_date('2004-01-01','YYYY-MM-DD')) tablespace SALES_TBS2,
    
15.              partition sal3 values less than (to_date('2005-01-01','YYYY-MM-DD')) tablespace SALES_TBS3,
    
16.              partition sal4 values less than (to_date('2006-01-01','YYYY-MM-DD')) tablespace SALES_TBS4
    
17.     );

复制代码

1. select  * from system_privilege_map  m where m.name  like 'FLASHBACK%';

复制代码

	PRIVILEGE	NAME	PROPERTY
1	-243	FLASHBACK ANY TABLE	0
2	-350	FLASHBACK ARCHIVE ADMINISTER	0

---------------------

1. Section 10: Capturing and Propagating Streams
   
2. ---on emrep/prod1
   
3. ---archive log list;
   
4. ---check hr user on target
   
5. 
   
6. alter system set global_names=true;
   
7. create directory src_dir as '/home/oracle/files';
   
8. create directory dst_dir as '/home/oracle/files';
   
9. 
   
10. create tablespace streams_tbs datafile '/u01/app/oracle/oradata/PROD1/streams_tbs.dbf' size 100m
    
11. /
    
12. create user strmadmin identified by streams_123 default tablespace streams_tbs
    
13. /
    
14. grant dba,select_catalog_role,select any dictionary to strmadmin
    
15. /
    
16. begin
    
17.         dbms_streams_auth.grant_admin_privilege('STRMADMIN',true);
    
18. end;
    
19. /
    
20. 
    
21. conn strmadmin/streams_123@prod1
    
22. create database link emrep
    
23. connect to strmadmin identified by streams_123
    
24. using 'emrep'
    
25. /
    
26. 
    
27. conn strmadmin/streams_123@emrep
    
28. create database link prod1
    
29. connect to strmadmin identified by streams_123
    
30. using 'prod1'
    
31. /
    
32. 
    
33. conn strmadmin/streams_123@prod1
    
34. 
    
35. begin
    
36.         dbms_streams_adm.maintain_tables(
    
37.                 table_names=>'hr.employees,hr.departments',
    
38.                 source_directory_object=>'SRC_DIR',
    
39.                 destination_directory_object=>'DST_DIR',
    
40.                 source_database=>'PROD1',
    
41.                 destination_database=>'EMREP',
    
42.                 capture_name => 'PROD1_CAPTURE',
    
43.                 propagation_name => 'PROD1_PROPAGATION',
    
44.                 perform_actions=>true,
    
45.                 bi_directional=>false,
    
46.                 include_ddl=>true,
    
47.                 instantiation=>dbms_streams_adm.instantiation_table_network
    
48.         );
    
49. end;
    
50. /
    
51. 
    
52. 
    
53. 
    
54. select STREAMS_TYPE,rule_name,RULE_TYPE from DBA_STREAMS_TABLE_RULES
    
55. 
    
56. conn hr/hr
    
57. @3_10_4.sql
    
58. 
    
59. BEGIN
    
60. DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
    
61. rule_name => 'departments4',
    
62. transform_function => 'hr.zero_sal');
    
63. END;
    
64. /
    
65. 
    
66. ---if error  see
    
67. EXECUTE DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
    
68. execute dbms_streams_adm.RECOVER_OPERATION(operation_mode = 'FORWARD'/operation_mode = 'ROLLBACK'/operation_mode = 'PURGE');
    
69. DBA_RECOVERABLE_SCRIPT_ERRORS
    
70. DBA_RECOVERABLE_SCRIPT
    
71. DBA_RECOVERABLE_SCRIPT_PARAMS
    
72. DBA_RECOVERABLE_SCRIPT_BLOCKS
    

复制代码

--------------------

1. BEGIN
   
2. dbms_resource_manager.clear_pending_area();
   
3. dbms_resource_manager.create_pending_area();
   
4. dbms_resource_manager.update_plan_directive(
   
5.     plan => 'DAYTIME',
   
6.     group_or_subplan => 'OLTP',
   
7.     new_switch_for_call => true
   
8. );
   
9. dbms_resource_manager.submit_pending_area();
   
10. END;

复制代码