设为首页收藏本站

Botang唐波's Oracle Station

查看: 59|回复: 0

课程第37次(2018-09-03星期一)

[复制链接]

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
发表于 2018-9-3 19:40:59 | 显示全部楼层 |阅读模式
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. select  * from dba_tab_partitions tp
  4. 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


  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
11987-06-17:00:00:006/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%';
复制代码
   PRIVILEGENAMEPROPERTY
1-243FLASHBACK ANY TABLE0
2-350FLASHBACK ARCHIVE ADMINISTER0


---------------------
  1. Section 10: Capturing and Propagating Streams
  2. ---on emrep/prod1
  3. ---archive log list;
  4. ---check hr user on target

  5. alter system set global_names=true;
  6. create directory src_dir as '/home/oracle/files';
  7. create directory dst_dir as '/home/oracle/files';

  8. create tablespace streams_tbs datafile '/u01/app/oracle/oradata/PROD1/streams_tbs.dbf' size 100m
  9. /
  10. create user strmadmin identified by streams_123 default tablespace streams_tbs
  11. /
  12. grant dba,select_catalog_role,select any dictionary to strmadmin
  13. /
  14. begin
  15.         dbms_streams_auth.grant_admin_privilege('STRMADMIN',true);
  16. end;
  17. /

  18. conn strmadmin/streams_123@prod1
  19. create database link emrep
  20. connect to strmadmin identified by streams_123
  21. using 'emrep'
  22. /

  23. conn strmadmin/streams_123@emrep
  24. create database link prod1
  25. connect to strmadmin identified by streams_123
  26. using 'prod1'
  27. /

  28. conn strmadmin/streams_123@prod1

  29. begin
  30.         dbms_streams_adm.maintain_tables(
  31.                 table_names=>'hr.employees,hr.departments',
  32.                 source_directory_object=>'SRC_DIR',
  33.                 destination_directory_object=>'DST_DIR',
  34.                 source_database=>'PROD1',
  35.                 destination_database=>'EMREP',
  36.                 capture_name => 'PROD1_CAPTURE',
  37.                 propagation_name => 'PROD1_PROPAGATION',
  38.                 perform_actions=>true,
  39.                 bi_directional=>false,
  40.                 include_ddl=>true,
  41.                 instantiation=>dbms_streams_adm.instantiation_table_network
  42.         );
  43. end;
  44. /



  45. select STREAMS_TYPE,rule_name,RULE_TYPE from DBA_STREAMS_TABLE_RULES

  46. conn hr/hr
  47. @3_10_4.sql

  48. BEGIN
  49. DBMS_STREAMS_ADM.SET_RULE_TRANSFORM_FUNCTION(
  50. rule_name => 'departments4',
  51. transform_function => 'hr.zero_sal');
  52. END;
  53. /

  54. ---if error  see
  55. EXECUTE DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
  56. execute dbms_streams_adm.RECOVER_OPERATION(operation_mode = 'FORWARD'/operation_mode = 'ROLLBACK'/operation_mode = 'PURGE');
  57. DBA_RECOVERABLE_SCRIPT_ERRORS
  58. DBA_RECOVERABLE_SCRIPT
  59. DBA_RECOVERABLE_SCRIPT_PARAMS
  60. 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;
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-9-20 13:43 , Processed in 0.184437 second(s), 24 queries .

快速回复 返回顶部 返回列表