课程第36次（2018-08-31星期五）

botang

Skillset 3：
Section 5：
5.2                  Ensure that all the statements execute in parallel.                    If a statement cannot execute in parallel, it should not execute at all.

1. create or replace trigger trg1
   
2. after logon  on database
   
3. declare
   
4.   pragma AUTONOMOUS_TRANSACTION;
   
5.   begin
   
6.     execute immediate 'alter session force parallel dml  parallel 8';
   
7. end;
   

复制代码

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> startup force
   
4. ORACLE instance started.
   
5. 
   
6. Total System Global Area  535662592 bytes
   
7. Fixed Size                    1314580 bytes
   
8. Variable Size                  264241388 bytes
   
9. Database Buffers          264241152 bytes
   
10. Redo Buffers                    5865472 bytes
    
11. Database mounted.
    
12. Database opened.
    
13. SQL> conn hr/hr
    
14. Connected.
    
15. SQL> explain plan for update employees set salary=salary+1;
    
16. 
    
17. Explained.
    
18. 
    
19. SQL>  @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql
    
20. 
    
21. PLAN_TABLE_OUTPUT
    
22. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
23. Plan hash value: 545592796
    
24. 
    
25. ----------------------------------------------------------------------------------------------------------------
    
26. | Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time          |    TQ  |IN-OUT| PQ Distrib |
    
27. ----------------------------------------------------------------------------------------------------------------
    
28. |   0 | UPDATE STATEMENT      |           |   107 |   428 |        2   (0)| 00:00:01 |           |          |               |
    
29. |   1 |  UPDATE               | EMPLOYEES |          |          |               |          |           |          |               |
    
30. |   2 |   PX COORDINATOR      |           |          |          |               |          |           |          |               |
    
31. |   3 |    PX SEND QC (RANDOM)| :TQ10000  |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
    
32. |   4 |     PX BLOCK ITERATOR |           |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | PCWC |               |
    
33. |   5 |      TABLE ACCESS FULL| EMPLOYEES |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | PCWP |               |
    
34. 
    
35. PLAN_TABLE_OUTPUT
    
36. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
37. ----------------------------------------------------------------------------------------------------------------
    
38. 
    
39. 12 rows selected.
    
40. 
    
41. SQL> alter table employees noparallel ;
    
42. 
    
43. Table altered.
    
44. 
    
45. SQL> explain plan for update employees set salary=salary+1;
    
46. 
    
47. Explained.
    
48. 
    
49. SQL>  @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql
    
50. 
    
51. PLAN_TABLE_OUTPUT
    
52. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
53. Plan hash value: 964452392
    
54. 
    
55. --------------------------------------------------------------------------------
    
56. | Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    
57. --------------------------------------------------------------------------------
    
58. |   0 | UPDATE STATEMENT   |               |   107 |   428 |     3         (0)| 00:00:01 |
    
59. |   1 |  UPDATE            | EMPLOYEES |       |       |            |               |
    
60. |   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   428 |     3         (0)| 00:00:01 |
    
61. --------------------------------------------------------------------------------
    
62. 
    
63. 9 rows selected.
    
64. 
    
65. SQL> conn / as sysdba
    
66. Connected.
    
67. SQL> create or replace trigger trg1
    
68. after logon  on database
    
69. declare
    
70.   pragma AUTONOMOUS_TRANSACTION;
    
71.   begin
    
72.     execute immediate 'alter session force parallel dml  parallel 8';
    
73. end;  2    3    4    5    6    7  
    
74.   8  /
    
75. 
    
76. Trigger created.
    
77. 
    
78. SQL> conn hr/hr
    
79. Connected.
    
80. SQL>  explain plan for update employees set salary=salary+1;
    
81. 
    
82. Explained.
    
83. 
    
84. SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlxplp.sql
    
85. 
    
86. PLAN_TABLE_OUTPUT
    
87. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
88. Plan hash value: 545592796
    
89. 
    
90. ----------------------------------------------------------------------------------------------------------------
    
91. | Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time          |    TQ  |IN-OUT| PQ Distrib |
    
92. ----------------------------------------------------------------------------------------------------------------
    
93. |   0 | UPDATE STATEMENT      |           |   107 |   428 |        2   (0)| 00:00:01 |           |          |               |
    
94. |   1 |  UPDATE               | EMPLOYEES |          |          |               |          |           |          |               |
    
95. |   2 |   PX COORDINATOR      |           |          |          |               |          |           |          |               |
    
96. |   3 |    PX SEND QC (RANDOM)| :TQ10000  |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
    
97. |   4 |     PX BLOCK ITERATOR |           |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | PCWC |               |
    
98. |   5 |      TABLE ACCESS FULL| EMPLOYEES |   107 |   428 |        2   (0)| 00:00:01 |  Q1,00 | PCWP |               |
    
99. 
    
100. PLAN_TABLE_OUTPUT
     
101. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     
102. ----------------------------------------------------------------------------------------------------------------
     
103. 
     
104. 12 rows selected.
     
105. 
     
106. SQL>
     

复制代码

1. SQL> alter system set db_securefile=PERMITTED;
   
2. 
   
3. System altered.
   
4. 
   
5. SQL> create table oe.CUSTOMER_PROFILES(CUST_ID  NUMBER,
   
6.   2   FIRST_NAME  VARCHAR2(20),
   
7.   3    LAST_NAME  VARCHAR2(30),
   
8.   4  PROFILE_INFO  BLOB)
   
9.   5  lob (profile_info) store as securefile
   
10.   6  (
    
11.   7   disable storage in row
    
12.   8   deduplicate
    
13.   9   compress high
    
14. 10   tablespace sf_data)
    
15. 11  tablespace sf_data;
    
16. 
    
17. Table created.
    
18. 
    
19. SQL>
    

复制代码

1. select table_name, partition_name, high_value
   
2. from user_tab_partitions
   
3. where table_name in ('NEW_ORDERS','NEW_ORDER_ITEMS')
   
4. order by partition_position, table_name;
   

复制代码

先用以下语句把NEW_ORDERS表建在SH下面：

1.    select      dbms_metadata.get_ddl('TABLE','ORDERS','OE')  from dual;

复制代码

1. grant select  on hr.employees to sh;
   
2.    
   
3.    grant references on hr.employees to sh;

复制代码

1. CREATE TABLE SH.NEW_ORDERS
   
2.    (        "ORDER_ID" NUMBER(12,0),
   
3.         "ORDER_DATE" date CONSTRAINT "ORDER_DATE_NN" NOT NULL ENABLE,
   
4.         "ORDER_MODE" VARCHAR2(8),
   
5.         "CUSTOMER_ID" NUMBER(6,0) CONSTRAINT "ORDER_CUSTOMER_ID_NN" NOT NULL ENABLE,
   
6.         "ORDER_STATUS" NUMBER(2,0),
   
7.         "ORDER_TOTAL" NUMBER(8,2),
   
8.         "SALES_REP_ID" NUMBER(6,0),
   
9.         "PROMOTION_ID" NUMBER(6,0),
   
10.          CONSTRAINT "ORDER_MODE_LOV" CHECK (order_mode in ('direct','online')) ENABLE,
    
11.          CONSTRAINT "ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE,
    
12.          CONSTRAINT "ORDER_PK" PRIMARY KEY ("ORDER_ID")
    
13.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
    
14.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    
15.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    
16.   TABLESPACE "EXAMPLE"  ENABLE,
    
17.          CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
    
18.           REFERENCES "SH"."CUSTOMERS" ("CUST_ID") ON DELETE SET NULL ENABLE,
    
19.          CONSTRAINT "ORDERS_SALES_REP_FK" FOREIGN KEY ("SALES_REP_ID")
    
20.           REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL ENABLE
    
21.    )
    
22.    partition by range (ORDER_DATE)
    
23.   ( partition  part1  values less than  (to_date('01-JAN-1999','dd-MON-yyyy')) tablespace  part_tbs1,
    
24.     partition  part2  values less than (maxvalue)  tablespace part_tbs2) ;

复制代码

1. create table sh.NEW_ORDER_ITEMS (
   
2. ORDER_ID        NUMBER(12) NOT NULL,
   
3. PRODUCT_ID      NUMBER NOT NULL,
   
4. QUANTITY        NUMBER NOT NULL,
   
5. SALES_AMOUNT    NUMBER NOT NULL,
   
6. constraint fk_NEW_ORDER_ITEMS  foreign key(ORDER_ID)   references sh.NEW_ORDERs)
   
7. partition by  reference  (fk_NEW_ORDER_ITEMS ) ;
   

复制代码

验证：

1. select table_name, partition_name, high_value
   
2.     from dba_tab_partitions tp
   
3.     where  tp.table_owner  ='SH' and  table_name in ('NEW_ORDERS','NEW_ORDER_ITEMS')
   
4.     order by partition_position, table_name;

复制代码

	TABLE_NAME	PARTITION_NAME	HIGH_VALUE
1	NEW_ORDERS	PART1	<Long>
2	NEW_ORDER_ITEMS	PART1	<Long>
3	NEW_ORDERS	PART2	<Long>
4	NEW_ORDER_ITEMS	PART2	<Long>

1.   insert into sh.new_orders  select  * from oe.orders;

复制代码

1. insert into sh.new_order_items  select  ORDER_ID, PRODUCT_ID,QUANTITY,unit_price*QUANTITY
   
2.     from oe.order_items;

复制代码