drop table T_PM_ACCT_DTL_AF_TEST;
create table T_PM_ACCT_DTL_AF_TEST
(
DATA_DATE date,
ACCT_NO VARCHAR2(100),
ACCT_ORD VARCHAR2(30),
ACCT_NO_PK VARCHAR2(100),
ACCT_BAL NUMBER(18,4),
D_CMP_BAL NUMBER(24,4),
M_CMP_BAL NUMBER(24,4),
Y_CMP_BAL NUMBER(24,4),
FLAG VARCHAR2(10),
ACCT_FLAG VARCHAR2(10),
TERM NUMBER(8),
TERM_FLAG VARCHAR2(10),
CUR_CODE VARCHAR2(8),
CUR_NAME VARCHAR2(20),
SUB_CODE VARCHAR2(50),
CUST_NO VARCHAR2(30),
CUST_TYPE VARCHAR2(10),
CUST_NAME VARCHAR2(60),
BANK_CORP_CODE VARCHAR2(10),
BRAN_NAME VARCHAR2(50),
MGR_CODE VARCHAR2(30),
MGR_NAME VARCHAR2(50),
OPEN_DATE VARCHAR2(10),
FIX_BAL NUMBER(16,2),
DIV_FIX_FLAG NUMBER(1),
ADJUST_AMT NUMBER(16,2),
ADJUST_AMT_AF NUMBER(24,4),
Y_AVG_AF NUMBER(24,4),
Y_ADD_AF NUMBER(24,4),
ACCT_INTR NUMBER(16,2),
SIM_PROFIT NUMBER(16,2),
SEPA_POR NUMBER(6,2),
PRI NUMBER(5),
BRAN_CODE VARCHAR2(50),
UNIT1_CODE VARCHAR2(10),
UNIT2_CODE VARCHAR2(10),
UNIT3_CODE VARCHAR2(10),
DEPT1_CODE VARCHAR2(10),
INTR_RATE NUMBER(11,6),
DUE_DATE NUMBER(8)
)
partition by range (DATA_DATE)
(
partition ACCT_DTL_AF_20110101 values less than (to_date('2011-01-01','yyyy-mm-dd'))
)
SQL> insert into T_PM_ACCT_DTL_AF_TEST(DATA_DATE) values(date'2010-12-31');
1 row created.
SQL> select DATA_DATE from T_PM_ACCT_DTL_AF_test partition(ACCT_DTL_AF_20110101);
DATA_DATE
---------
31-DEC-10
2.添加分区:
alter table T_PM_ACCT_DTL_AF_test add partition ACCT_DTL_AF_20110201 values less than (to_date('2011-02-01','yyyy-mm-dd'))
3.插入数据,查看分布
SQL> insert into T_PM_ACCT_DTL_AF_TEST(DATA_DATE) values(date'2011-01-01');
1 row created.
SQL> select DATA_DATE from T_PM_ACCT_DTL_AF_test partition(ACCT_DTL_AF_20110101);
DATA_DATE
---------
31-DEC-10
SQL> select DATA_DATE from T_PM_ACCT_DTL_AF_test partition(ACCT_DTL_AF_20110201);
DATA_DATE
---------
01-JAN-11
---------------------------------------------------------------------------------------------------------------------------------
SQL> insert into T_PM_ACCT_DTL_AF_TEST(DATA_DATE) values(date'2000-01-01');
1 row created.
SQL> select DATA_DATE from T_PM_ACCT_DTL_AF_test partition(ACCT_DTL_AF_20110101);
DATA_DATE
---------
31-DEC-10
01-JAN-00
SQL> select DATA_DATE from T_PM_ACCT_DTL_AF_test partition(ACCT_DTL_AF_20110201);
DATA_DATE
---------
01-JAN-11