Oracle创建分区表
参考网站：https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm
今天在网上看了一下range分区表的创建，遇到以下几个问题：
ORA-00959: 表空间 'TSA' 不存在，
ORA-01843: 无效的月份
一下是具体的例子：
SQL> CREATE TABLE sales 2 ( prod_id NUMBER(6) 3 , cust_id NUMBER 4 , time_id DATE 5 , channel_id CHAR(1) 6 , promo_id NUMBER(6) 7 , quantity_sold NUMBER(3) 8 , amount_sold NUMBER(10,2) 9 ) 10 PARTITION BY RANGE (time_id) 11 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-4月-2006','DD-MON-YYYY')) 12 TABLESPACE tsa 13 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-6月-2006','DD-MON-YYYY')) 14 TABLESPACE tsb 15 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-10月-2006','DD-MON-YYYY')) 16 TABLESPACE tsc 17 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-1月-2007','Ddd-MON-YYYY')) 18 TABLESPACE tsd 19 ); TABLESPACE tsa * 第 12 行出现错误: ORA-00959: 表空间 'TSA' 不存在 SQL> SELECT tablespace_name FROM dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS BMWMMS_TS_DATA BMWMMS_TS_INDEX 已选择7行。 SQL> CREATE tablespace "tsd" DATAFILE 'D:/temp/TEST01.DBF' SIZE 10M; 表空间已创建。 SQL> CREATE TABLE sales 2 ( prod_id NUMBER(6) 3 , cust_id NUMBER 4 , time_id DATE 5 , channel_id CHAR(1) 6 , promo_id NUMBER(6) 7 , quantity_sold NUMBER(3) 8 , amount_sold NUMBER(10,2) 9 ) 10 PARTITION BY RANGE (time_id) 11 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) 12 TABLESPACE tsa 13 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) 14 TABLESPACE tsb 15 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) 16 TABLESPACE tsc 17 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) 18 TABLESPACE tsd 19 ); ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) * 第 11 行出现错误: ORA-01843: 无效的月份 SQL> create tablespace "tsa" DATAFILE 'D:/temp/TEST.DBF' SIZE 10M ; 表空间已创建。 SQL> select sysdate from dual; SYSDATE -------------- 02-6月 -16 SQL> CREATE TABLE sales 2 ( prod_id NUMBER(6) 3 , cust_id NUMBER 4 , time_id DATE 5 , channel_id CHAR(1) 6 , promo_id NUMBER(6) 7 , quantity_sold NUMBER(3) 8 , amount_sold NUMBER(10,2) 9 ) 10 PARTITION BY RANGE (time_id) 11 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-4月-2006','DD-MON-YYYY')) 12 TABLESPACE tsa 13 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-6月-2006','DD-MON-YYYY')) 14 TABLESPACE tsb 15 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-10月-2006','DD-MON-YYYY')) 16 TABLESPACE tsc 17 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-1月-2007','Ddd-MON-YYYY')) 18 TABLESPACE tsd 19 ); TABLESPACE tsa * 第 12 行出现错误: ORA-00959: 表空间 'TSA' 不存在 SQL> SELECT tablespace_name FROM dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS BMWMMS_TS_DATA BMWMMS_TS_INDEX tsd tsa 已选择9行。 SQL> CREATE TABLE sales 2 ( prod_id NUMBER(6) 3 , cust_id NUMBER 4 , time_id DATE 5 , channel_id CHAR(1) 6 , promo_id NUMBER(6) 7 , quantity_sold NUMBER(3) 8 , amount_sold NUMBER(10,2) 9 ) 10 PARTITION BY RANGE (time_id) 11 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-4月-2006','DD-MON-YYYY')) 12 TABLESPACE tsa 13 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-6月-2006','DD-MON-YYYY')) 14 TABLESPACE tsb 15 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-10月-2006','DD-MON-YYYY')) 16 TABLESPACE tsc 17 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-1月-2007','Ddd-MON-YYYY')) 18 TABLESPACE tsd 19 ); TABLESPACE tsa * 第 12 行出现错误: ORA-00959: 表空间 'TSA' 不存在 SQL> CREATE tablespace tsa DATAFILE 'D:/temp/TEST_A.DBF' SIZE 10M; 表空间已创建。 SQL> CREATE tablespace tsb DATAFILE 'D:/temp/TEST_B.DBF' SIZE 10M; 表空间已创建。 SQL> CREATE tablespace tsc DATAFILE 'D:/temp/TEST_C.DBF' SIZE 10M; 表空间已创建。 SQL> CREATE tablespace tsd DATAFILE 'D:/temp/TEST_D.DBF' SIZE 10M; 表空间已创建。 SQL> select sysdate from dual; SYSDATE -------------- 02-6月 -16 SQL> CREATE TABLE sales 2 ( prod_id NUMBER(6) 3 , cust_id NUMBER 4 , time_id DATE 5 , channel_id CHAR(1) 6 , promo_id NUMBER(6) 7 , quantity_sold NUMBER(3) 8 , amount_sold NUMBER(10,2) 9 ) 10 PARTITION BY RANGE (time_id) 11 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-4月-2006','DD-MON-YYYY')) 12 TABLESPACE tsa 13 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-6月-2006','DD-MON-YYYY')) 14 TABLESPACE tsb 15 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-10月-2006','DD-MON-YYYY')) 16 TABLESPACE tsc 17 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-1月-2007','Ddd-MON-YYYY')) 18 TABLESPACE tsd 19 ); 表已创建。 SQL>
