1.创建测试表select * from beacn_cust_nds_port_hold;select count(*) from beacn_cust_nds_port_hold;--8042234create table beacn_cust_nds_port_hold_test as select * from beacn_cust_nds_port_hold;--62.969sselect * from beacn_cust_nds_port_hold_test;select count(*) from beacn_cust_nds_port_hold_test;--8042234
2.备份数据create table cust_nds_port_hold_test_bak as select * from beacn_cust_nds_port_hold_test;--46.859select * from cust_nds_port_hold_test_bak;select count(*) from cust_nds_port_hold_test_bak;--8042234
3.删除业务表 drop table beacn_cust_nds_port_hold_test purge; select * from beacn_cust_nds_port_hold_test;
4.创建新的业务表：分区表--确定分区方式：列表分区select distinct instr_ccy from cust_nds_port_hold_test_bak;--15select count(*) from cust_nds_port_hold_test_bak where instr_ccy='EUR';--707422select count(*) from cust_nds_port_hold_test_bak where instr_ccy='NZD';--549408select count(*) from cust_nds_port_hold_test_bak where instr_ccy='SEK';--295722select count(*) from cust_nds_port_hold_test_bak where instr_ccy='CNY';--1260860select count(*) from cust_nds_port_hold_test_bak where instr_ccy='GBP';--706386select count(*) from cust_nds_port_hold_test_bak where instr_ccy='JPY';--706102select count(*) from cust_nds_port_hold_test_bak where instr_ccy='DEM';--8select count(*) from cust_nds_port_hold_test_bak where instr_ccy='SGD';--443410select count(*) from cust_nds_port_hold_test_bak where instr_ccy='FRF';--3932select count(*) from cust_nds_port_hold_test_bak where instr_ccy='NOK';--120select count(*) from cust_nds_port_hold_test_bak where instr_ccy='HKD';--711226select count(*) from cust_nds_port_hold_test_bak where instr_ccy='CHF';--528834select count(*) from cust_nds_port_hold_test_bak where instr_ccy='CAD';--702708select count(*) from cust_nds_port_hold_test_bak where instr_ccy='AUD';--705626select count(*) from cust_nds_port_hold_test_bak where instr_ccy='USD';--720470 --创建表空间CREATE TABLESPACE SP_TEST1DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST1.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST2DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST2.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST3DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST3.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST4DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST4.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST5DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST5.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST6DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST6.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST7DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST7.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST8DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST8.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST9DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST9.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST10DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST10.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST11DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST11.DBF ' SIZE 100M REUSE; CREATE TABLESPACE SP_TEST12DATAFILE 'F:\ORACLE10G\SIDB108\DATAFILE\SP_TEST12.DBF ' SIZE 100M REUSE; --2.25s --查询创建的表空间select * from user_tablespaces; -- 创建分区表：列表分区-- Create tablecreate table beacn_cust_nds_port_hold_test(CUST_ID NUMBER(9) not null,AS_OF_DATE DATE not null,LOCALE VARCHAR2(5) not null,ACCT_NUM VARCHAR2(30) not null,CERT_NUM VARCHAR2(8) not null,INSTR_ID VARCHAR2(20) not null,INSTR_NAME VARCHAR2(100),INSTR_CCY VARCHAR2(3),INV_ASSET VARCHAR2(10),DISPLAY_GROUP VARCHAR2(10),DISPLAY_SUB_GROUP VARCHAR2(10),AMT NUMBER(28,6),AVAIL_AMT NUMBER(28,6),HOLD_AMT NUMBER(28,6),PCT NUMBER(5,2),REF_CCY VARCHAR2(3),AMT_IN_REF_CCY NUMBER(28,6),HIDE_AMT NUMBER(28,6),HIDE_AMT_IN_REF_CCY NUMBER(28,6))partition by list(instr_ccy)(partition cust_nds_port_hold_test_p1 values('EUR') tablespace SP_TEST1,partition cust_nds_port_hold_test_p2 values('NZD') tablespace SP_TEST2,partition cust_nds_port_hold_test_p3 values('SEK','DEM','FRF','NOK') tablespace SP_TEST3,--DEM FRF NOKpartition cust_nds_port_hold_test_p4 values('CNY') tablespace SP_TEST4,partition cust_nds_port_hold_test_p5 values('GBP') tablespace SP_TEST5,partition cust_nds_port_hold_test_p6 values('JPY') tablespace SP_TEST6,partition cust_nds_port_hold_test_p7 values('SGD') tablespace SP_TEST7,partition cust_nds_port_hold_test_p8 values('HKD') tablespace SP_TEST8,partition cust_nds_port_hold_test_p9 values('CHF') tablespace SP_TEST9,partition cust_nds_port_hold_test_p10 values('CAD') tablespace SP_TEST10,partition cust_nds_port_hold_test_p11 values('AUD') tablespace SP_TEST11,partition cust_nds_port_hold_test_p12 values('USD') tablespace SP_TEST12);-- Create/Recreate primary, unique and foreign key constraintsalter table beacn_cust_nds_port_hold_testadd constraint cust_nds_port_hold_test_PK primary key (CUST_ID, AS_OF_DATE, LOCALE, ACCT_NUM, CERT_NUM, INSTR_ID);-- Create/Recreate indexescreate index UNIQUE_IDX_NDS_PORT_TEST on beacn_cust_nds_port_hold_test (CUST_ID);
5.转移数据到新的业务表insert into beacn_cust_nds_port_hold_test select * from cust_nds_port_hold_test_bak ;--大于30分钟select * from beacn_cust_nds_port_hold_test;select count(*) from beacn_cust_nds_port_hold_test;--8042234
6.测试
7.新的业务表（分区表）创建成功且测试通过，删除备份表drop table cust_nds_port_hold_test_bak purge;