普通表txn轉換成分割槽表

一 建立普通表txn

SQL> create table txn as select level as id from dual connect by level

SQL> desc txn

Name Null? Type

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

ID NUMBER

二 建立表空間

SQL> create tablespace t1 datafile "/home/oracle/t1.dbf" size 5M;

SQL> create tablespace t2 datafile "/home/oracle/t2.dbf" size 5M;

SQL> create tablespace t3 datafile "/home/oracle/t3.dbf" size 5M;

三 建立分割槽表，命名為txn_1

SQL> create table txn_1(id number) partition by range(id)

2 (

3 partition part1 values less than(10) tablespace t1,

4 partition part2 values less than(20) tablespace t2,

5 partition part3 values less than(30) tablespace t3

6 );

四 匯出普通表資料

[oracle@ogg1 ~]$ exp chen/chen file=txn.dmp tables=txn

五 更改表名

SQL> rename txn to txn_old;

SQL> rename txn_1 to txn;

六 將資料匯入到分割槽表中

[oracle@ogg1 ~]$ imp chen/chen file=txn.dmp fromuser=chen touser=chen ignore=y

七 檢視分割槽表

SQL> col table_name for a10

SQL> col partition_name for a10;

SQL> select table_name,partition_name from user_tab_partitions;

TABLE_NAME PARTITION_

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

TXN PART1

TXN PART2

TXN PART3

SQL> select * from txn partition(part2);

ID

----------

10

11

12

13

14

15

16

17

18

19

10 rows selected.