交换分区

一  创建表分区
SQL>
SQL> create table test_par
  2  (
  3  nid number(10) constraint pk_test_p primary key,
  4  idcard varchar2(18),
  5  pdd date not null
  6  )
  7  partition by range (pdd)
  8  (
  9  partition par_2000 values less than (to_date('2001-01-01','yyyy-mm-dd')),
 10  partition par_2001 values less than (to_date('2002-01-01','yyyy-mm-dd')),
 11  partition par_2002 values less than (to_date('2003-01-01','yyyy-mm-dd')),
 12  partition part_maxvalue values less than (maxvalue)
 13  );

Table created.


--创建全局索引
SQL> create index idx_test_par on test_par(idcard);

Index created.
--插入几条测试数据，分别存放在不同的分区中
SQL>
SQL> insert into test_par values(500,'2000',to_date('20000101','yyyymmdd'));
1 row created.
SQL>
SQL> insert into test_par values(501,'2001',to_date('20010201','yyyymmdd'));
1 row created.
SQL> insert into test_par values(502,'2002',to_date('20020301','yyyymmdd'));
1 row created.
SQL> commit;

Commit complete.

SQL>
----此时索引的状态是有效的
SQL> select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_PAR';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_TEST_PAR                   VALID
-----创建与分区表结构相同的表
SQL> create table test
  2  (
  3  nid number(10) constraint pk_test primary key,
  4  idcard varchar2(18),
  5  pdd date not null
  6  );

Table created.

SQL>

SQL> insert into test select rownum,rpad(rownum,18,'0'),to_date('20020101','yyyymmdd')+rownum from dual connect by rownum<=100;

100 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select min(pdd),max(pdd) from test;

MIN(PDD)  MAX(PDD)
--------- ---------
02-JAN-02 11-APR-02

SQL>
SQL>
SQL> select count(*) from test;

  COUNT(*)
----------
       100
-----交换分区
SQL> alter table test_par exchange partition par_2002 with table test ;

Table altered.

SQL>
SQL>
------此时在test表中，只有一条记录了 ，即原来2002分区中的数据


SQL> select count(*) from test;

  COUNT(*)
----------
         1

SQL>
SQL> select * from test;

       NID IDCARD             PDD
---------- ------------------ ---------
       502 2002               01-MAR-02

SQL>
-----可以看到分区表已经是102条记录
SQL> select count(*) from test_par;

  COUNT(*)
----------
       102

SQL>
--其中我们交换的par_2002分区为100条记录
SQL> select count(*) from test_par partition (par_2002);

  COUNT(*)
----------
       100
       
----此时索引已失效

SQL> select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_PAR';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_TEST_PAR                   UNUSABLE

SQL>


*****---
显示时间输出格式：  
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
是在当前会话
*****---

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31399171/viewspace-2126562/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31399171/viewspace-2126562/