课程第11-13次（2017-05-12星期五，2017-05-13星期六晚上和2017-05-14星期日上午）

botang

第一阶段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. 
   
4. 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. 
   
3. begin
   
4. dbms_fga.drop_policy(
   
5. OBJECT_SCHEMA =>'HR',
   
6. OBJECT_NAME=>'EMPLOYEES',
   
7.   POLICY_NAME=>'POLICY1'
   
8. );
   
9. dbms_fga.add_policy(
   
10.   OBJECT_SCHEMA =>'HR',
    
11. OBJECT_NAME=>'EMPLOYEES',
    
12. POLICY_NAME=>'POLICY1',
    
13. AUDIT_CONDITION=>'SALARY is not null or COMMISSION_PCT is not null',
    
14. AUDIT_COLUMN=>'SALARY,COMMISSION_PCT',
    
15. STATEMENT_TYPES=>'SELECT'
    
16. );
    
17. end;
    
18. /

复制代码

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. 
   
6. create public synonym LARGE_PROC for
   
7. 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. 
   
6. create table OLTP_USER.ATTENDEES(
   
7. STUD_ID number constraint fk_STUD_ID references OLTP_USER.STUDENTS,
   
8. CLASS_ID number constraint fk_CLASS_ID references OLTP_USER.class,
   
9. constraint pk_ATTENDEES primary key (STUD_ID,CLASS_ID))
   
10. 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. 
    
27. 
    
28. spool spauto.lis
    
29. 
    
30. --
    
31. --  Schedule a snapshot to be run on this instance every hour, on the hour
    
32. 
    
33. variable jobno number;
    
34. variable instno number;
    
35. begin
    
36.   select instance_number into :instno from v$instance;
    
37.   dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+5/1440,'MI'), 'trunc(SYSDATE+5/1440,''MI'')', TRUE, :instno);
    
38.   commit;
    
39. end;
    
40. /
    
41. 
    
42. 
    
43. prompt
    
44. prompt  Job number for automated statistics collection for this instance
    
45. prompt  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
46. prompt  Note that this job number is needed when modifying or removing
    
47. prompt  the job:
    
48. print jobno
    
49. 
    
50. prompt
    
51. prompt  Job queue process
    
52. prompt  ~~~~~~~~~~~~~~~~~
    
53. prompt  Below is the current setting of the job_queue_processes init.ora
    
54. prompt  parameter - the value for this parameter must be greater
    
55. prompt  than 0 to use automatic statistics gathering:
    
56. show parameter job_queue_processes
    
57. prompt
    
58. 
    
59. prompt
    
60. prompt  Next scheduled run
    
61. prompt  ~~~~~~~~~~~~~~~~~~
    
62. prompt  The next scheduled run for this job is:
    
63. select job, next_date, next_sec
    
64.   from user_jobs
    
65. where job = :jobno;
    
66. 
    
67. spool off;
    
68. 
    
69. ! sleep 905
    
70. select  to_char(SNAP_TIME,'YYYY-MM-DD:HH24:MI:SS')  from STATS$SNAPSHOT ;
    
71. begin
    
72. dbms_job.remove(:jobno);
    
73. end;
    
74. /
    
75. 
    

复制代码