1.建立分割槽表
sec@ora10g> create table t_partition_range (id number,name varchar2(50))
2 partition by range(id)(
3 partition t_range_p1 values less than (10) tablespace tbs_part01,
4 partition t_range_p2 values less than (20) tablespace tbs_part02,
5 partition t_range_p3 values less than (30) tablespace tbs_part03,
6 partition t_range_pmax values less than (maxvalue) tablespace tbs_part04
7 );

Table created.

2.模擬插入資料
sec@ora10g> insert into t_partition_range values (5,'Andy1');

1 row created.

sec@ora10g> insert into t_partition_range values (15,'Andy2');

1 row created.

sec@ora10g> insert into t_partition_range values (25,'Andy3');

1 row created.

sec@ora10g> insert into t_partition_range values (35,'Andy4');

1 row created.

sec@ora10g> insert into t_partition_range values (45,'Andy5');

1 row created.

sec@ora10g> commit;

Commit complete.

3.查詢每一個分割槽中包含的資料
sec@ora10g> select * from t_partition_range partition(t_range_p1);

        ID NAME
---------- --------------------------------------------------
         5 Andy1

sec@ora10g> select * from t_partition_range partition(t_range_p2);

        ID NAME
---------- --------------------------------------------------
        15 Andy2

sec@ora10g> select * from t_partition_range partition(t_range_p3);

        ID NAME
---------- --------------------------------------------------
        25 Andy3

sec@ora10g> select * from t_partition_range partition(t_range_pmax);

        ID NAME
---------- --------------------------------------------------
        35 Andy4
        45 Andy5

4.exp匯出分割槽表資料
4.1.匯出一個分割槽演示
ora10g@testdb /exp$ exp sec/sec file=sec.dmp tables=t_partition_range:t_range_p1

Export: Release 10.2.0.1.0 - Production on Fri Jul 10 16:08:31 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table              T_PARTITION_RANGE
. . exporting partition                     T_RANGE_P1          1 rows exported
Export terminated successfully without warnings.

4.2.匯出兩個分割槽演示
ora10g@testdb /exp$ exp sec/sec file=sec.dmp tables=t_partition_range:t_range_p1,t_partition_range:t_range_p2

Export: Release 10.2.0.1.0 - Production on Fri Jul 10 16:08:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table              T_PARTITION_RANGE
. . exporting partition                     T_RANGE_P1          1 rows exported
. . exporting partition                     T_RANGE_P2          1 rows exported
Export terminated successfully without warnings.

4.3.全部匯出效果
ora10g@testdb /exp$ exp sec/sec file=sec.dmp tables=t_partition_range

Export: Release 10.2.0.1.0 - Production on Fri Jul 10 16:16:08 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table              T_PARTITION_RANGE
. . exporting partition                     T_RANGE_P1          1 rows exported
. . exporting partition                     T_RANGE_P2          1 rows exported
. . exporting partition                     T_RANGE_P3          1 rows exported
. . exporting partition                   T_RANGE_PMAX          2 rows exported
Export terminated successfully without warnings.

-- The End --

【實驗】【PARTITION】exp匯出分割槽表資料

相關文章