053-224

224.You need to create a partitioned table to store historical data and you issued the following command:
CREATE TABLE purchase_interval PARTITION BY RANGE (time_id) INTERVAL
(NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3) ( PARTITION p1 VALUES LESS
THAN(TO_DATE('1-1-2005', 'dd-mm-yyyy')), PARTITION p2 VALUES LESS THAN(TO_DATE('1-1- 2007',
'dd-mm-yyyy'))) AS SELECT * FROM purchases WHERE time_id < TO_DATE('1-1-2007','dd-mm-yyyy');
What is the outcome of the above command?
A. It returns an error because the range partitions P1 and P2 should be of the same range.
B. It creates two range partitions (P1, P2). Within each range partition, it creates monthwise subpartitions.
C. It creates two range partitions of varying range. For data beyond '1-1-2007,' it creates partitions with a
width of one month each.
D. It returns an error because the number of tablespaces (TBS1,TBS2,TBS3)specified does not match
the number of range partitions (P1,P2) specified.
Answer: C

解析:
NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. The argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype. The value for interval_unit specifies the unit of n and must resolve to one of the following string values:
'YEAR'
'MONTH'

create tablespace tbs1 datafile '/oradata/tbs1.dbf' size 50M;
create tablespace tbs2 datafile '/oradata/tbs2.dbf' size 50M;
create tablespace tbs3 datafile '/oradata/tbs3.dbf' size 50M;

create table employee  (id int,name varchar2(10),hire_date date);
insert into employee values (1,'a',to_date('20120103','yyyymmdd'));
insert into employee values (2,'b',to_date('20120204','yyyymmdd'));
insert into employee values (3,'c',to_date('20120303','yyyymmdd'));
insert into employee values (4,'d',to_date('20120403','yyyymmdd'));
insert into employee values (5,'e',to_date('20120503','yyyymmdd'));
insert into employee values (6,'f',to_date('20120603','yyyymmdd'));
insert into employee values (7,'g',to_date('20120703','yyyymmdd'));
insert into employee values (10,'g',to_date('20121003','yyyymmdd'));
insert into employee values (10,'g',to_date('20121103','yyyymmdd'));
insert into employee values (11,'g',to_date('20121203','yyyymmdd'));
commit;
CREATE TABLE emp PARTITION BY range(hire_date) INTERVAL
(NUMTOYMINTERVAL(1,'month')) store in  (tbs1,tbs2,tbs3)  ( PARTITION p1 VALUES LESS
THAN(TO_DATE('1-3-2012', 'dd-mm-yyyy')), PARTITION p2 VALUES LESS THAN(TO_DATE('1-5- 2012',
'dd-mm-yyyy'))) AS SELECT * FROM employee WHERE hire_date < TO_DATE('1-1-2014','dd-mm-yyyy');

select * from dba_segments a where owner='LBX' and a.segment_name='EMP';

 

图1:

图2:

经过实验,答案选C是没问题.

参数 store in  (tbs1,tbs2,tbs3) 怎么起作用的,看的还是有点迷糊!P1和P2分区竟然放在默认分区TBS_TPSS_DATA.别的系统自动生成的分区随机分布在tbs1-3里面.

事实上,oracle网站是这么描述的:

hash_partitions_by_quantity:

Alternatively, you can specify the number of partitions. In this case, the database assigns partition names of the formSYS_Pn. The STORE IN clause specifies one or more tablespaces where the hash partitions are to be stored. The number of tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

 

参考:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm