课程第8-10次（2017-05-05星期五，2017-05-06星期六晚上和2017-05-07星期日上午）

botang

第一阶段OCM Exam Preparation（8-10）：
s0.sql:

1. CREATE DATABASE PROD
   
2.    USER SYS IDENTIFIED BY oracle
   
3.    USER SYSTEM IDENTIFIED BY oracle
   
4.    LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo01.log') SIZE 100M,
   
5.            GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk2/redo02.log') SIZE 100M,
   
6.            GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk3/redo03.log') SIZE 100M
   
7.    MAXLOGFILES 50
   
8.    MAXLOGMEMBERS 5
   
9.    MAXLOGHISTORY 1
   
10.    MAXDATAFILES 100
    
11.    MAXINSTANCES 1
    
12.    CHARACTER SET AL32UTF8
    
13.    NATIONAL CHARACTER SET AL16UTF16
    
14.    DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 700M REUSE autoextend on
    
15.    EXTENT MANAGEMENT LOCAL
    
16.    SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/Disk2/sysaux01.dbf' SIZE 500M REUSE autoextend on
    
17.    DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/PROD/Disk4/users01.dbf' size 30M autoextend on
    
18.    DEFAULT TEMPORARY TABLESPACE temp
    
19.       TEMPFILE '/u01/app/oracle/oradata/PROD/Disk5/temp01.dbf'
    
20.       SIZE 50M REUSE autoextend on
    
21.    UNDO TABLESPACE undotbs1
    
22.       DATAFILE '/u01/app/oracle/oradata/PROD/Disk3/undotbs1.dbf'
    
23.       SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

复制代码

s2.sql:

1. create or replace procedure proc_s2
   
2. is
   
3. begin
   
4. for rec  in ( select   index_name from dba_indexes where owner='HR'  and index_type <> 'IOT - TOP')
   
5. loop
   
6.   execute immediate 'alter index hr.'||rec.index_name||' rebuild online';
   
7. end loop;
   
8. end;
   
9. /

复制代码

s4.sql:

1. set serveroutput on
   
2. declare
   
3. v_1 varchar2(100);
   
4. begin
   
5. dbms_advisor.TUNE_MVIEW( TASK_NAME => v_1 ,
   
6.                    MV_CREATE_STMT=>'create materialized view sh.prod_mv refresh fast enable query rewrite as SELECT time_id,prod_subcategory,SUM(unit_cost) ucost FROM sh.costs c,sh.products p where c.prod_id=p.prod_id GROUP BY time_id,prod_subcategory');
   
7. dbms_output.put_line(dbms_advisor.GET_TASK_SCRIPT(v_1)) ;
   
8. end;
   
9. /

复制代码

s41.sql:

1. CREATE
   
2. MATERIALIZED VIEW LOG ON
   
3.     "SH"."COSTS"
   
4.     WITH ROWID,
   
5. SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
   
6.     INCLUDING NEW VALUES;
   
7. 
   
8. CREATE
   
9. MATERIALIZED VIEW LOG ON
   
10.     "SH"."PRODUCTS"
    
11.     WITH ROWID,
    
12. SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    
13.     INCLUDING NEW VALUES;
    
14. 
    
15. CREATE
    
16. MATERIALIZED VIEW SH.PROD_MV
    
17.     REFRESH FAST WITH ROWID
    
18.     ENABLE QUERY
    
19. REWRITE
    
20.     AS SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2,
    
21. SUM("SH"."COSTS"."UNIT_COST")
    
22.        M1, COUNT("SH"."COSTS"."UNIT_COST") M2,
    
23. COUNT(*) M3 FROM SH.PRODUCTS,
    
24.        SH.COSTS WHERE SH.COSTS.PROD_ID =
    
25. SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY,
    
26. SH.COSTS.TIME_ID;
    

复制代码

s42.sql:

1. select employee_id, first_name, last_name, phone_number, salary
   
2. from employees@dblink1;

复制代码

s43.sql:

1. create table  sh.prod_master
   
2. (
   
3.   PROD_ID number,
   
4.   CUST_ID number,
   
5.   TIME_ID date,
   
6.   CHANNEL_ID varchar2(10),
   
7.   PROMO_ID number,
   
8.   QUANTITY_SOLD number,
   
9.   AMOUNT_SOLD number(8,2)
   
10. );
    

复制代码

s431.txt:

1. sqlldr  sh/sh  control=myprod_master.ctl  data=prod_master.dat  log=/home/oracle/s44.sql  external_table=GENERATE_ONLY

复制代码

s44.sql:

1. drop table sh.PROD_MASTER;
   
2. 
   
3. CREATE TABLE sh.PROD_MASTER
   
4. (
   
5.   "PROD_ID" NUMBER,
   
6.   "CUST_ID" NUMBER,
   
7.   "TIME_ID" DATE,
   
8.   "CHANNEL_ID" VARCHAR2(10),
   
9.   "PROMO_ID" NUMBER,
   
10.   "QUANTITY_SOLD" NUMBER,
    
11.   "AMOUNT_SOLD" NUMBER(8,2)
    
12. )
    
13. ORGANIZATION external
    
14. (
    
15.   TYPE oracle_loader
    
16.   DEFAULT DIRECTORY DIR1
    
17.   ACCESS PARAMETERS
    
18.   (
    
19.     RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    
20.     BADFILE 'DIR1':'prod_master.bad'
    
21.     LOGFILE 'prod_master.log'
    
22.     READSIZE 1048576
    
23.     FIELDS TERMINATED BY "|" LDRTRIM
    
24.     MISSING FIELD VALUES ARE NULL
    
25.     REJECT ROWS WITH ALL NULL FIELDS
    
26.     (
    
27.       "PROD_ID" CHAR(255)
    
28.         TERMINATED BY "|",
    
29.       "CUST_ID" CHAR(255)
    
30.         TERMINATED BY "|",
    
31.       "TIME_ID" CHAR(255)
    
32.         TERMINATED BY "|"
    
33.         DATE_FORMAT DATE MASK "DD-MON-YYYY",
    
34.       "CHANNEL_ID" CHAR(255)
    
35.         TERMINATED BY "|",
    
36.       "PROMO_ID" CHAR(255)
    
37.         TERMINATED BY "|",
    
38.       "QUANTITY_SOLD" CHAR(255)
    
39.         TERMINATED BY "|",
    
40.       "AMOUNT_SOLD" CHAR(255)
    
41.         TERMINATED BY "|"
    
42.     )
    
43.   )
    
44.   location
    
45.   (
    
46.     'prod_master.dat'
    
47.   )
    
48. )REJECT LIMIT UNLIMITED;

复制代码

s45.sql:

1. create table sh.countries_ext
   
2. organization external
   
3. (
   
4. type oracle_datapump
   
5. default directory dir1
   
6. location ( 'countries_ext.dmp' )
   
7. )
   
8. reject limit unlimited
   
9. as
   
10. select country_id, country_name,country_region
    
11. from sh.countries;
    

复制代码

s46.sql:

1. create table system.countries_ext
   
2. ( country_id NUMBER  , country_name  VARCHAR2(40) ,country_region  VARCHAR2(20)       )
   
3. organization external
   
4. (
   
5. type oracle_datapump
   
6. default directory dir1
   
7. location ( 'countries_ext.dmp' )
   
8. )
   
9. reject limit unlimited;
   

复制代码