课程第37/38次（2017-08-06星期天上下午）

botang

第三阶段New Feature/OCM Exam Preparation（31-38）

1.   select   tp.table_name, tp.partition_name  ,
   
2.      tp.high_value , tp.tablespace_name
   
3.   from dba_tab_partitions tp
   
4. where tp.table_owner='SH' and tp.table_name in ('ORDERS', 'ORDER_ITEMS')
   
5. order by   tp.partition_position, tp.table_name;
   
6.    
   
7.   select  i.index_name , ip.partition_name , decode(ip.status  , null,i.status, ip.status)  
   
8.   from dba_indexes i , dba_ind_partitions   ip
   
9.   where i.index_name=ip.index_name(+)      and i.table_name='ORDERS'   and i.owner='SH';

复制代码

分区表与DML操作限制：

1. SQL>
   
2. SQL> create table tc ( a  number )
   
3.   2  partition by (a)
   
4.   3  (partition p1 values less than (a) ,
   
5.   4   partition p2 values less than (100) ) ;
   
6. partition by (a)
   
7.              *
   
8. ERROR at line 2:
   
9. ORA-14151: invalid table partitioning method
   
10. 
    
11. 
    
12. SQL> create table tc ( a  number )
    
13.   2   partition  by range (a)
    
14.   3  (partition p1 values less than (a) ,
    
15.   4   partition p2 values less than (100) )
    
16.   5  ;
    
17. (partition p1 values less than (a) ,
    
18.                                  *
    
19. ERROR at line 3:
    
20. ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
    
21. 
    
22. 
    
23. SQL> create table tc ( a  number )
    
24.   2    partition  by range (a)
    
25.   3  (partition p1 values less than (10),
    
26. 
    
27. SQL>  
    
28. SQL>
    
29. SQL>  create table tc ( a  number )
    
30.   2   partition  by range (a)
    
31.   3    (partition p1 values less than (10),
    
32.   4   partition p2 values less than (100) );
    
33. 
    
34. Table created.
    
35. 
    
36. SQL> insert into tc values (100) ;
    
37. insert into tc values (100)
    
38.             *
    
39. ERROR at line 1:
    
40. ORA-14400: inserted partition key does not map to any partition
    
41. 
    
42. 
    
43. SQL> insert into tc values (99) ;
    
44. 
    
45. 1 row created.
    
46. 
    
47. SQL> commit;
    
48. 
    
49. Commit complete.
    
50. 
    
51. SQL> update tc set a=9 where a=99;
    
52. update tc set a=9 where a=99
    
53.        *
    
54. ERROR at line 1:
    
55. ORA-14402: updating partition key column would cause a partition change
    
56. 
    
57. 
    
58. SQL> update tc set a=98 where a=99;
    
59. 
    
60. 1 row updated.
    
61. 
    
62. SQL> commit;
    
63. 
    
64. Commit complete.
    
65. 
    
66. SQL> create table td ( a  number )
    
67.   2  partition  by hash (a)
    
68.   3  (partition p1  , partition p2 , partition p3 , partition p4 ) ;
    
69. 
    
70. Table created.
    
71. 
    
72. SQL> insert into td values (1) ;
    
73. 
    
74. 1 row created.
    
75. 
    
76. SQL> commit;
    
77. 
    
78. Commit complete.
    
79. 
    
80. SQL> update td values (9) ;
    
81. update td values (9)
    
82.           *
    
83. ERROR at line 1:
    
84. ORA-00971: missing SET keyword
    
85. 
    
86. 
    
87. SQL> update td set a=9 ;   
    
88. update td set a=9
    
89.        *
    
90. ERROR at line 1:
    
91. ORA-14402: updating partition key column would cause a partition change
    
92. 
    
93. 
    
94. SQL> update td values (2) ;
    
95. update td values (2)
    
96.           *
    
97. ERROR at line 1:
    
98. ORA-00971: missing SET keyword
    
99. 
    
100. 
     
101. SQL> update td  set  a=2 ;
     
102. update td  set        a=2
     
103.        *
     
104. ERROR at line 1:
     
105. ORA-14402: updating partition key column would cause a partition change
     
106. 
     
107. 
     
108. SQL> create table  te (a number )
     
109.   2  partition  by system ;
     
110. 
     
111. Table created.
     
112. 
     
113. SQL> insert into te values (1) ;
     
114. insert into te values (1)
     
115.             *
     
116. ERROR at line 1:
     
117. ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
     
118. 
     
119. 
     
120. SQL>  create table  tf ( a number )
     
121.   2  partition by system
     
122.   3  partitions 4 ;
     
123. 
     
124. Table created.
     
125. 
     
126. SQL> insert into tf values (1) ;
     
127. insert into tf values (1)
     
128.             *
     
129. ERROR at line 1:
     
130. ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
     
131. 
     
132. 
     
133. SQL> insert into tf partition sys_p32 values (1) ;
     
134. insert into tf partition sys_p32 values (1)
     
135.                          *
     
136. ERROR at line 1:
     
137. ORA-00926: missing VALUES keyword
     
138. 
     
139. 
     
140. SQL> insert into tf partition (sys_p32) values (1) ;
     
141. 
     
142. 1 row created.
     
143. 
     
144. SQL> commit;
     
145. 
     
146. Commit complete.
     
147. 
     
148. SQL> update tf set a=1999 where a=1;
     
149. 
     
150. 1 row updated.
     
151. 
     
152. SQL> commit;
     
153. 
     
154. Commit complete.
     
155. 
     
156. SQL>
     

复制代码

1. 
   
2.   select  * from dba_tab_partitions tp
   
3.    where tp.table_owner='SH' and tp.table_name='HISTORICAL_NEWSALES';
   
4.   
   
5.   alter table sh.newsales merge
   
6.     partitions sys_p25,  sys_p26
   
7.     into partition p_before_3_jan_2005;
   
8.   
   
9.    ----------------------------
   
10.    
    
11.      select  * from dba_tab_partitions tp
    
12.    where tp.table_owner='SH' and tp.table_name='SHIPMENTS';
    
13.    
    
14.       select  * from dba_tab_subpartitions tp
    
15.    where tp.table_owner='SH' and tp.table_name='SHIPMENTS';
    
16.    
    
17.    --------
    
18.    
    
19.    CREATE TABLE sh.sales3
    
20.    (    prod_id NUMBER(6) NOT NULL,
    
21.        cust_id NUMBER NOT NULL,
    
22.         time_id DATE NOT NULL,
    
23.         channel_id char(1) NOT NULL,
    
24.        promo_id NUMBER (6) NOT NULL,
    
25.         quantity_sold NUMBER(3) NOT NULL,
    
26.          amount_sold NUMBER(10,2) NOT NULL )
    
27. PARTITION BY RANGE (time_id)
    
28. SUBPARTITION BY RANGE (cust_id)
    
29. SUBPARTITION TEMPLATE
    
30.    (  SUBPARTITION sp1 VALUES LESS THAN (50000),
    
31.        SUBPARTITION sp2 VALUES LESS THAN (100000),
    
32.            SUBPARTITION sp3 VALUES LESS THAN (150000),
    
33.                SUBPARTITION sp4 VALUES LESS THAN (MAXVALUE) )
    
34. (
    
35.   PARTITION   p1  VALUES LESS THAN (TO_DATE('1-4-2007','DD-MM-YYYY')),
    
36.   PARTITION   p2  VALUES LESS THAN (TO_DATE('1-7-2007','DD-MM-YYYY')),
    
37.   PARTITION   p3  VALUES LESS THAN (TO_DATE('1-8-2007','DD-MM-YYYY')),
    
38.   PARTITION    p4 VALUES LESS THAN (TO_DATE('1-1-2008','DD-MM-YYYY'))
    
39. );
    
40. 
    
41.    ----
    
42.         select  * from dba_tab_partitions tp
    
43.    where tp.table_owner='SH' and tp.table_name='SALES3';
    
44.    
    
45.       select  * from dba_tab_subpartitions tp
    
46.    where tp.table_owner='SH' and tp.table_name='SALES3';
    
47.    
    
48.    --------------------

复制代码

删除SQL Access Advisor的模板：

1. 
   
2.   declare
   
3.     name varchar2(30);
   
4.     cursor name_cur1 is
   
5.       select task_name from user_advisor_templates
   
6.         where task_name like 'SQLACCESS%';
   
7.   begin
   
8.     ---------------------------------------------------------------------------
   
9.     --  Get rid of templates, tasks and workloads.  
   
10.     ---------------------------------------------------------------------------
    
11. 
    
12.     open name_cur1;
    
13.   
    
14.     loop
    
15.       fetch name_cur1 into name;
    
16.       exit when name_cur1%NOTFOUND;
    
17.   
    
18.       dbms_advisor.update_task_attributes(name,null,null,'FALSE','FALSE');
    
19.       dbms_advisor.delete_task(name);
    
20.     end loop;
    
21.   
    
22.     close name_cur1;
    
23.   
    
24.   end;

复制代码

添加SQL Access Advisor的模板：

1. declare
   
2. l_task_id number;
   
3. l_name varchar2(30);
   
4. begin
   
5. l_name := 'SQLACCESS_PARTITION_SMALL';
   
6. dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,l_task_id,l_name,'For demo purposes only');
   
7. dbms_advisor.set_task_parameter(l_name,'_PA_MIN_TABLE_SIZE_PART',100);
   
8. dbms_advisor.set_task_parameter(l_name,'EXECUTION_TYPE','FULL');
   
9. dbms_advisor.set_task_parameter(l_name,'ANALYSIS_SCOPE','ALL');
   
10. dbms_advisor.set_task_parameter(l_name,'MODE','COMPREHENSIVE');
    
11. dbms_advisor.update_task_attributes(l_name,null,null,'TRUE','TRUE');
    
12. end;
    
13. /
    

复制代码