【實驗】【PARTITION】exp匯出分割槽表資料
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 --
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 --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-608844/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Oracle查詢Interval partition分割槽表內資料Oracle
- 分割槽Partition
- 測試分割槽表部分匯出
- oracle資料匯出匯入（exp/imp）Oracle
- 分割槽partition知識點
- Oracle 12.1.0.2 expdp匯出分割槽表資料遇到BUG慢的原因和解決方法Oracle
- Spark學習——分割槽Partition數Spark
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- exp匯出表中特定條件的表
- Oracle Partition 分割槽詳細總結Oracle
- 分割槽函式Partition By的基本用法函式
- Kafka分割槽分配策略（Partition Assignment Strategy）Kafka
- MySQL資料表分割槽手記MySql
- 分割槽表-實戰
- oracle分割槽表和分割槽表exchangeOracle
- ORACLE刪除-表分割槽和資料Oracle
- hive 動態分割槽插入資料表Hive
- Mysql表分割槽實現MySql
- Mysql表分割槽實操MySql
- MySQL全面瓦解29：使用Partition功能實現水平分割槽MySql
- oracle分割槽表和非分割槽表exchangeOracle
- 物化檢視分割槽實驗
- 分割槽函式partition by的基本用法【轉載】函式
- oracle 更改分割槽表資料 ora-14402Oracle
- MySql資料分割槽操作之新增分割槽操作MySql
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- PG的非分割槽表線上轉分割槽表
- zabbix上對mysql資料庫做分割槽表MySql資料庫
- MySQL的nnodb引擎表資料分割槽儲存MySql
- AppBoxFuture: 大資料表分割槽的3種策略APP大資料
- 11g後設資料匯入19c分割槽表建立不成功
- 【MYSQL】 分割槽表MySql
- 非分割槽錶轉換成分割槽表
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 移動分割槽表和分割槽索引的表空間索引
- PostgreSQL使用表繼承實現分割槽表SQL繼承