oracle 分区的使用方法,Oracle分区方法

Oracle分区步骤

1 sqlplus sys as sysdba/

2 online redefine

2-1 exec dbms_redefinition.can_redef_table('NTSMSALL','T_TEAM');

2-2 CREATE TABLE NTSMSALL.TMP_T_TEAM…

CREATE TABLE "NTSMSALL"."TMP_T_TEAM"

( "UUID" VARCHAR2(36) NOT NULL ENABLE,

"CTEAMCODE" VARCHAR2(50) NOT NULL ENABLE,

"ULINEID" VARCHAR2(36),

"CLINENAME" VARCHAR2(100) NOT NULL ENABLE,

"IDAYS" NUMBER(*,0) DEFAULT 1 NOT NULL ENABLE,

"DBGNDATE" DATE NOT NULL ENABLE,

"DENDDATE" DATE NOT NULL ENABLE,

"IGUIDES" NUMBER(*,0) DEFAULT 0,

"IQTY" NUMBER(*,0) DEFAULT 0,

"IADULTS" NUMBER(*,0) DEFAULT 0,

"ICHILDS" NUMBER(*,0) DEFAULT 0,

"IBABYS" NUMBER(*,0) DEFAULT 0,

"IOLDS" NUMBER(*,0) DEFAULT 0,

"COPTYPE" VARCHAR2(12) NOT NULL ENABLE,

"CAREA" VARCHAR2(10),

"CNATION" VARCHAR2(400) NOT NULL ENABLE,

"CTYPE" VARCHAR2(20),

"CGUESTTYPE" VARCHAR2(10) DEFAULT '中国人',

"CGUESTNATION" VARCHAR2(20),

"CGUESTCO" VARCHAR2(70),

"CSTARTCITY" VARCHAR2(50),

"CADTITLE" VARCHAR2(80),

"CLONGTRAFFIC" VARCHAR2(255),

"CLOCALTRAFFIC" VARCHAR2(255),

"CHOTELSTANDARD" VARCHAR2(255),

"CFOODSTANDARD" VARCHAR2(255),

"CPROGRAMSTANDARD" VARCHAR2(255),

"CCHILDNOTE" VARCHAR2(255),

"CFEATURE" CLOB,

"CSTANDARD" CLOB,

"CPROMPT" CLOB,

"CPRICEINCUDE" CLOB,

"CPRICENOTINCUDE" CLOB,

"CMEMO" CLOB,

"UUSERID" VARCHAR2(36) NOT NULL ENABLE,

"UDEPTID" VARCHAR2(36) NOT NULL ENABLE,

"UCORPID" VARCHAR2(36) NOT NULL ENABLE,

"UADMINDIVID" VARCHAR2(36) NOT NULL ENABLE,

"CSTATUS" VARCHAR2(10),

"CLOOKSTATUS" VARCHAR2(10) DEFAULT '未审',

"CLOOKMIND" VARCHAR2(200),

"ULOOKMANID" VARCHAR2(36),

"CLOOKTIME" DATE,

"DCREATE" DATE,

"DUPDATE" DATE NOT NULL ENABLE,

"CGUIDEMEMO" VARCHAR2(60),

"UGUIDEID" VARCHAR2(36),

"CCARDCODE" VARCHAR2(20),

"CTRIPMEMO" CLOB,

"CNEEDAUDIT" VARCHAR2(1) DEFAULT 0,

"CORP" VARCHAR2(255 CHAR),

"CBOARDTIME" VARCHAR2(255 CHAR),

"CINLINENO" VARCHAR2(255 CHAR),

"CINPORT" VARCHAR2(255 CHAR),

"COFFTIME" VARCHAR2(255 CHAR),

"COUTLINENO" VARCHAR2(255 CHAR),

"COUTPORT" VARCHAR2(255 CHAR),

"USUPPLIERID" VARCHAR2(255 CHAR))

partition by range(dbgndate)

interval(numtodsinterval(1,'day') )

(partition p2011 values less than(to_date ('2012-01-01','yyyy-mm-dd')));

SQL> alter table inter_table add constraint pk_inter_table primary key (id);

表已更改。

/*

SQL> begin

2 dbms_redefinition.start_redef_table('yangtk', 'orgin_table', 'inter_table',

3 'ID ID, FID FID, NAME NEW_NAME, CREATE_DATE CREATE_DATE, ''NO COMMENTS'' COMMENTS',

4 DBMS_REDEFINITION.CONS_USE_PK);

5 END;

6 /

*/

SQL> select * from (select to_char((dbgndate),'yyyy-mm-dd') from ntsmsall.t_team order by dbgndate desc) bd where rownum<10;

TO_CHAR((DBGNDATE),'

--------------------

5787-10-21

5787-10-20

2015-10-02

2014-07-06

2013-09-02

2013-04-05

2013-02-13

2012-12-24

2012-12-13

Select count(uuid) from ntsmsall.t_team where dbgndate= to_date('5787-10-20', 'yyyy-mm-dd');

update ntsmsall.t_team set dbgndate=to_date('2012-10-20', 'yyyy-mm-dd') where dbgndate= to_date('5787-10-20', 'yyyy-mm-dd');

1 row updated.

Select count(uuid) from ntsmsall.t_team where dbgndate= to_date('5787-10-21', 'yyyy-mm-dd');

update ntsmsall.t_team set dbgndate=to_date('2012-10-21', 'yyyy-mm-dd') where dbgndate= to_date('5787-10-21', 'yyyy-mm-dd');

1 row updated.

2-3 EXEC dbms_redefinition.start_redef_table('NTSMSALL', 'T_TEAM', 'TMP_T_TEAM');

-- tail -n 100 alert_sid.log

2-4

DECLARE

error_count pls_integer := 0;

BEGIN

dbms_redefinition.copy_table_dependents('NTSMSALL', 'T_TEAM', 'TMP_T_TEAM',

0, true, true, true, true,

error_count);

dbms_output.put_line('errors := ' || to_char(error_count));

END;

/

2-5 EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('NTSMSALL', 'T_TEAM', 'TMP_T_TEAM');

2-6 EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('NTSMSALL', 'T_TEAM', 'TMP_T_TEAM');

2-6-MUST disable and drop constraints on tmp_t_team + drop table OR

2-7 DROP TABLE NTSMSALL.TMP_T_TEAM CASCADE CONSTRAINTS;