Bo's Oracle Station

查看: 2079|回复: 0

课程第11-13次(2017-05-12星期五,2017-05-13星期六晚上和2017-05-14星期日上午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-5-14 21:29:37 | 显示全部楼层 |阅读模式
第一阶段OCM Exam Preparation(11-13):
s5.sql:
  1. create table hr.MAGAZINE_ARTICLES(
  2.   AUTHOR VARCHAR2(30),
  3.   ARTICLE_NAME VARCHAR2(50),
  4.   ARTICLE_DATE DATE,
  5.   ARTICLE_DATA CLOB )
  6. tablespace users lob(ARTICLE_DATA) store as (
  7. tablespace lob_data  chunk 16k storage (initial 2M  next 2M)
  8. nocache disable storage in row);
复制代码
s52.sql:
  1. create table hr.ORACLE9I_REFERENCES(
  2. ORACLE9I_ARTICLE ROWID,
  3. INSERT_TIME TIMESTAMP WITH LOCAL TIME ZONE);
复制代码
s53.sql:
  1. insert into  hr.ORACLE9I_REFERENCES
  2. select  rowid  ,  scn_to_timestamp(ora_rowscn) from hr.MAGAZINE_ARTICLES  where  instr(AUTHOR||ARTICLE_NAME||ARTICLE_DATE||ARTICLE_DATA,'Oracle9i',1,3)>0;

  3. commit;
复制代码
s54.sql:
  1. create tablespace DATA01  datafile '/u01/app/oracle/oradata/PROD/Disk1/DATA01.dbf'  size 250M uniform size 4M blocksize 16K;
  2. create tablespace DATA02  datafile '/u01/app/oracle/oradata/PROD/Disk2/DATA02.dbf'  size 250M uniform size 4M blocksize 16K;
  3. create tablespace DATA03  datafile '/u01/app/oracle/oradata/PROD/Disk3/DATA03.dbf'  size 250M uniform size 4M blocksize 16K;
  4. create tablespace DATA04  datafile '/u01/app/oracle/oradata/PROD/Disk4/DATA04.dbf'  size 250M uniform size 4M blocksize 16K;
  5. create tablespace DATA05  datafile '/u01/app/oracle/oradata/PROD/Disk5/DATA05.dbf'  size 250M uniform size 4M blocksize 16K;
复制代码
s55.sql:
  1. create table sh.sales_history
  2. partition by range(sdate)
  3. (partition  p1   values less than (to_date('1999-01-01','YYYY-MM-DD'))   tablespace data01 ,
  4.   partition  p2   values less than (to_date('2000-01-01','YYYY-MM-DD'))   tablespace data02,
  5.   partition  p3   values less than (to_date('2001-01-01','YYYY-MM-DD'))  tablespace data03,
  6.   partition  p4   values less than (to_date('2002-01-01','YYYY-MM-DD'))  tablespace data04,
  7.   partition  p5   values less than (to_date('2003-01-01','YYYY-MM-DD'))  tablespace data05)
  8. as select  * from oltp_user.sales where 1=2;
复制代码
s56.sql:
  1. create unique index sh.SALES_HISTORY_PK
  2. on sh.SALES_HISTORY(SALES_ID)
  3. global partition by hash(SALES_ID)
  4. (
  5. partition p1 tablespace INDX,
  6. partition p2 tablespace INDX,
  7. partition p3 tablespace INDX,
  8. partition p4 tablespace INDX
  9. )
  10. parallel 4;
复制代码
s57.sql:
  1. create index sh.SALES_HISTORY_DATE_IDX on
  2. sh.SALES_HISTORY(sdate) local;
复制代码
s58.sql:
  1. select  text from dba_source where name='DBMS_FGA';

  2. begin
  3. dbms_fga.drop_policy(
  4. OBJECT_SCHEMA =>'HR',
  5. OBJECT_NAME=>'EMPLOYEES',
  6.   POLICY_NAME=>'POLICY1'
  7. );
  8. dbms_fga.add_policy(
  9.   OBJECT_SCHEMA =>'HR',
  10. OBJECT_NAME=>'EMPLOYEES',
  11. POLICY_NAME=>'POLICY1',
  12. AUDIT_CONDITION=>'SALARY is not null or COMMISSION_PCT is not null',
  13. AUDIT_COLUMN=>'SALARY,COMMISSION_PCT',
  14. STATEMENT_TYPES=>'SELECT'
  15. );
  16. end;
  17. /
复制代码
s59.sql:
  1. create table hr.ORIGINAL_SALARY  as
  2. select  employee_id, commission_pct, salary
  3. from hr.employees ;
复制代码
s6.sql:
  1. BEGIN
  2. dbms_resource_manager.clear_pending_area();
  3. dbms_resource_manager.create_pending_area();
  4. dbms_resource_manager.create_plan( 'WEEKDAYS', 'WEEKDAYS',CPU_MTH=>'RATIO');
  5. dbms_resource_manager.create_plan_directive(
  6.     plan => 'WEEKDAYS',
  7.     group_or_subplan => 'DSS',
  8.     comment => '',
  9.     cpu_p1 => 30, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
  10.     cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
  11.     parallel_degree_limit_p1 => NULL,
  12.     active_sess_pool_p1 => NULL,
  13.     queueing_p1 => NULL,
  14.     switch_group => '',
  15.     switch_time => NULL,
  16.     switch_estimate => false,
  17.     max_est_exec_time => NULL,
  18.     undo_pool => NULL,
  19.     max_idle_time => NULL,
  20.     max_idle_blocker_time => NULL,
  21.     switch_time_in_call => NULL
  22. );
  23. dbms_resource_manager.create_plan_directive(
  24.     plan => 'WEEKDAYS',
  25.     group_or_subplan => 'OLTP',
  26.     comment => '',
  27.     cpu_p1 => 50, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
  28.     cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
  29.     parallel_degree_limit_p1 => NULL,
  30.     active_sess_pool_p1 => NULL,
  31.     queueing_p1 => NULL,
  32.     switch_group => '',
  33.     switch_time => NULL,
  34.     switch_estimate => false,
  35.     max_est_exec_time => NULL,
  36.     undo_pool => NULL,
  37.     max_idle_time => NULL,
  38.     max_idle_blocker_time => NULL,
  39.     switch_time_in_call => NULL
  40. );
  41. dbms_resource_manager.create_plan_directive(
  42.     plan => 'WEEKDAYS',
  43.     group_or_subplan => 'OTHER_GROUPS',
  44.     comment => '',
  45.     cpu_p1 => 20, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
  46.     cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
  47.     parallel_degree_limit_p1 => NULL,
  48.     active_sess_pool_p1 => NULL,
  49.     queueing_p1 => NULL,
  50.     switch_group => '',
  51.     switch_time => NULL,
  52.     switch_estimate => false,
  53.     max_est_exec_time => NULL,
  54.     undo_pool => NULL,
  55.     max_idle_time => NULL,
  56.     max_idle_blocker_time => NULL,
  57.     switch_time_in_call => NULL
  58. );
  59. dbms_resource_manager.submit_pending_area();
  60. END;
复制代码
s62.sql:
  1. create or replace view LARGE_PROC as
  2. select * from  v$db_object_cache
  3. where TYPE in ('PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','FUNCTION')
  4. and SHARABLE_MEM > 51200;

  5. create public synonym LARGE_PROC for
  6. LARGE_PROC;
复制代码
s63.sql:
  1. begin
  2. for rec in ( select  index_name from dba_indexes where table_owner='OLTP_USER' and table_name='SALES')
  3. loop
  4.    execute immediate 'alter index oltp_user.'||rec.index_name||' monitoring usage';
  5. end loop;
  6. end;
  7. /
复制代码
s64.sql:
  1. create table OLTP_USER.STUDENTS(
  2. STUD_ID number   constraint pk_STUDENTS  primary key,
  3. FNAME varchar2(20),
  4. LNAME varchar2(20));

  5. create table OLTP_USER.ATTENDEES(
  6. STUD_ID number constraint fk_STUD_ID references OLTP_USER.STUDENTS,
  7. CLASS_ID number constraint fk_CLASS_ID references OLTP_USER.class,
  8. constraint pk_ATTENDEES primary key (STUD_ID,CLASS_ID))
  9. organization index;
复制代码
s65.sql:
  1. begin
  2. dbms_stats.gather_table_stats('HR','EMPLOYEES',estimate_percent=>100,method_opt=>'for all columns size auto for columns department_id size 254');
  3. end;
  4. /
复制代码
s66.sql:
  1. create tablespace CUST_TBS datafile '/u01/app/oracle/oradata/PROD/Disk1/cust_tbs_01.dbf' size 20971520 segment space management auto;
复制代码
s67.sql:
  1. create outline outline1 on   
  2. SELECT c.cust_id, SUM(amount_sold)
  3.   FROM
  4.   sh.sales s, sh.customers c
  5.   WHERE s.cust_id = c.cust_id
  6.   GROUP BY c.cust_id;
复制代码
s68.sql:
  1. create  index  sh.i_transaction on sh.transaction(USER_ID)
  2. global partition by hash(USER_ID ) partitions 4;
复制代码
s69.sql:
  1. begin
  2. perfstat.statspack.snap(I_SNAP_LEVEL=>7, I_UCOMMENT=>'MANUAL');
  3. end;
  4. /
复制代码
s691.sql:
  1. Rem
  2. Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
  3. Rem
  4. Rem spauto.sql
  5. Rem
  6. Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
  7. Rem
  8. Rem    NAME
  9. Rem      spauto.sql
  10. Rem
  11. Rem    DESCRIPTION
  12. Rem      SQL*PLUS command file to automate the collection of STATPACK
  13. Rem      statistics.
  14. Rem
  15. Rem    NOTES
  16. Rem      Should be run as the STATSPACK owner, PERFSTAT.
  17. Rem      Requires job_queue_processes init.ora parameter to be
  18. Rem      set to a number >0 before automatic statistics gathering
  19. Rem      will run.
  20. Rem
  21. Rem    MODIFIED   (MM/DD/YY)
  22. Rem    cdialeri    02/16/00 - 1191805
  23. Rem    cdialeri    12/06/99 - 1059172, 1103031
  24. Rem    cdialeri    08/13/99 - Created
  25. Rem


  26. spool spauto.lis

  27. --
  28. --  Schedule a snapshot to be run on this instance every hour, on the hour

  29. variable jobno number;
  30. variable instno number;
  31. begin
  32.   select instance_number into :instno from v$instance;
  33.   dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+5/1440,'MI'), 'trunc(SYSDATE+5/1440,''MI'')', TRUE, :instno);
  34.   commit;
  35. end;
  36. /


  37. prompt
  38. prompt  Job number for automated statistics collection for this instance
  39. prompt  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  40. prompt  Note that this job number is needed when modifying or removing
  41. prompt  the job:
  42. print jobno

  43. prompt
  44. prompt  Job queue process
  45. prompt  ~~~~~~~~~~~~~~~~~
  46. prompt  Below is the current setting of the job_queue_processes init.ora
  47. prompt  parameter - the value for this parameter must be greater
  48. prompt  than 0 to use automatic statistics gathering:
  49. show parameter job_queue_processes
  50. prompt

  51. prompt
  52. prompt  Next scheduled run
  53. prompt  ~~~~~~~~~~~~~~~~~~
  54. prompt  The next scheduled run for this job is:
  55. select job, next_date, next_sec
  56.   from user_jobs
  57. where job = :jobno;

  58. spool off;

  59. ! sleep 905
  60. select  to_char(SNAP_TIME,'YYYY-MM-DD:HH24:MI:SS')  from STATS$SNAPSHOT ;
  61. begin
  62. dbms_job.remove(:jobno);
  63. end;
  64. /

复制代码



回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 19:52 , Processed in 0.035091 second(s), 24 queries .

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