创建一个partition表:
create table obj
( id number, name char(10))
partition by hash(id)
( partition part1, partition part2);删除obj表:
drop table obj;但是,此时obj的两个分区part1、part2任然存在。请问,此时 我应该如何删除part1和part2?
create table obj
( id number, name char(10))
partition by hash(id)
( partition part1, partition part2);删除obj表:
drop table obj;但是,此时obj的两个分区part1、part2任然存在。请问,此时 我应该如何删除part1和part2?
create table obj
( id number, name char(10))
partition by hash(id)
( partition part1, partition part2);
select * from dba_objects where object_name='OBJ';
DROP TABLE OBJ;
select * from dba_objects where object_name='OBJ';
你要drop分区的话,只能迂回:新建一个表b,把hash数定为你要的数,然后把原来的表a数据导过去,然后再把表a删除,把表b重命名(rename)为表a。
hash是通过函数计算的,删除分区的话,通过函数得到的应该放入这个区的数据没地方放了合并也一样。所以,只能增加,其实,增加的过程,是增加分区,并且把hash的计算因子也增加了。
2 ( id number, name char(10))
3 partition by hash(id)
4 ( partition part1, partition part2);
Table created
SQL> select * from user_tab_partitions;
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
OBJ NO PART2 0 0 2 TDS_DATA 10 1 255 65536 1 2147483645 YES DISABLED DEFAULT NO NO
OBJ NO PART1 0 0 1 TDS_DATA 10 1 255 65536 1 2147483645 YES DISABLED DEFAULT NO NO
SQL> drop table obj purge;
Table dropped
SQL> select * from user_tab_partitions;
TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL GLOBAL_STATS USER_STATS
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ------------ ----------
SQL>