oracle split 怎么解决 default 分区,ORACLE基础操作【02】 -->split分区表

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

--分区表的定义

CREATE TABLE "AIKI"."PT1"

( "OBJECT_ID" NUMBER,

"STATUS" VARCHAR2(8)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

PARTITION BY RANGE ("OBJECT_ID")

(PARTITION "P01" VALUES LESS THAN (10000)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" NOCOMPRESS ,

PARTITION "P02" VALUES LESS THAN (20000)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" NOCOMPRESS ,

PARTITION "P03" VALUES LESS THAN (30000)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" NOCOMPRESS ,

PARTITION "P04" VALUES LESS THAN (32000)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" NOCOMPRESS ,

PARTITION "P05" VALUES LESS THAN (33000)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" NOCOMPRESS ,

PARTITION "P06" VALUES LESS THAN (34000)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" NOCOMPRESS ,

PARTITION "P07" VALUES LESS THAN (37000)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" NOCOMPRESS ,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" NOCOMPRESS )

TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION NUM_ROWS BLOCKS

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

AIKI PT1 P01 1 1241680 2734

AIKI PT1 P02 2 1256920 2886

AIKI PT1 P03 3 1279260 2926

AIKI PT1 P04 4 250680 585

AIKI PT1 P05 5 128240 298

AIKI PT1 P06 6 128220 320

AIKI PT1 P07 7 387520 912

AIKI PT1 PMAX 8 4546800 10232

SQL> alter table pt1 split partition pmax at (38000) into(partition p08,partition pmax) parallel 8;

Table altered.

SQL> select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,NUM_ROWS,BLOCKS from dba_tab_partitions where table_name='PT1';

TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION NUM_ROWS BLOCKS

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

AIKI PT1 P01 1 1241680 2734

AIKI PT1 P02 2 1256920 2886

AIKI PT1 P03 3 1279260 2926

AIKI PT1 P04 4 250680 585

AIKI PT1 P05 5 128240 298

AIKI PT1 P06 6 128220 320

AIKI PT1 P07 7 387520 912

AIKI PT1 P08 8

AIKI PT1 PMAX 9

9 rows selected.

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from dba_ind_partitions where index_owner='AIKI' and index_name='IDX_PT1';

INDEX_NAME PARTITION_NAME STATUS

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

IDX_PT1 P01 USABLE

IDX_PT1 P02 USABLE

IDX_PT1 P03 USABLE

IDX_PT1 P04 USABLE

IDX_PT1 P05 USABLE

IDX_PT1 P06 UNUSABLE

IDX_PT1 P07 USABLE

IDX_PT1 P08 UNUSABLE

IDX_PT1 PMAX UNUSABLE

SQL> alter table pt1 split partition pmax at (39000) into(partition p09,partition pmax) update indexes parallel 8;

Table altered.

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from dba_ind_partitions where index_owner='AIKI' and index_name='IDX_PT1';

INDEX_NAME PARTITION_NAME STATUS

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

IDX_PT1 P01 USABLE

IDX_PT1 P02 USABLE

IDX_PT1 P03 USABLE

IDX_PT1 P04 USABLE

IDX_PT1 P05 USABLE

IDX_PT1 P06 UNUSABLE

IDX_PT1 P07 USABLE

IDX_PT1 P08 UNUSABLE

IDX_PT1 P09 USABLE

IDX_PT1 PMAX USABLE

SQL> exec dbms_stats.gather_table_stats('AIKI','PT1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',ESTIMATE_PERCENT=>5);

BEGIN dbms_stats.gather_table_stats('AIKI','PT1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',ESTIMATE_PERCENT=>5); END;

*

ERROR at line 1:

ORA-20000: index "AIKI"."IDX_PT1" or partition of such index is in unusable state

ORA-06512: at "SYS.DBMS_STATS", line 20337

ORA-06512: at "SYS.DBMS_STATS", line 20360

ORA-06512: at line 1

SQL> ALTER INDEX IDX_PT1 REBUILD PARTITION P06 PARALLEL 4;

ALTER INDEX IDX_PT1 REBUILD PARTITION P08 PARALLEL 4;

Index altered.

SQL>

Index altered.

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from dba_ind_partitions where index_owner='AIKI' and index_name='IDX_PT1';

INDEX_NAME PARTITION_NAME STATUS

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

IDX_PT1 P01 USABLE

IDX_PT1 P02 USABLE

IDX_PT1 P03 USABLE

IDX_PT1 P04 USABLE

IDX_PT1 P05 USABLE

IDX_PT1 P06 USABLE

IDX_PT1 P07 USABLE

IDX_PT1 P08 USABLE

IDX_PT1 P09 USABLE

IDX_PT1 PMAX USABLE

10 rows selected.

SQL> exec dbms_stats.gather_table_stats('AIKI','PT1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',ESTIMATE_PERCENT=>5);

PL/SQL procedure successfully completed.

SQL> select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,NUM_ROWS,BLOCKS from dba_tab_partitions where table_name='PT1';

TABLE_OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION NUM_ROWS BLOCKS

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

AIKI PT1 P01 1 1241860 2734

AIKI PT1 P02 2 1246480 2886

AIKI PT1 P03 3 1274280 2926

AIKI PT1 P04 4 254740 585

AIKI PT1 P05 5 127360 298

AIKI PT1 P06 6 127640 320

AIKI PT1 P07 7 384060 912

AIKI PT1 P08 8 128720 328

AIKI PT1 P09 9 127740 320

AIKI PT1 PMAX 10 4281420 9664

说明：此操作在操作期间应关注表空间的变化，特别是归档日志的产生(量很大,如果split对象较大);