exp导出分区表数据

2024-07-21 02:51:27

字体：大中小

来源：转载

供稿：网友

---- 创建range分区表

create table pdba (id number, time date) partition by range (time)( partition p1 values less than (to_date('2017-1-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2017-2-1', 'yyyy-mm-dd')), partition p3 values less than (to_date('2017-3-1', 'yyyy-mm-dd')), partition p4 values less than (maxvalue) );------ 批量插入数据 DECLARE v_id INTEGER := 0 ; v_date DATE := to_date('2016-12-31','yyyy-mm-dd'); BEGIN LOOP v_id := v_id + 1; v_date := v_date + 1; INSERT INTO PDBA VALUES(v_id,v_date ); EXIT WHEN v_id = 150 ; END LOOP; END ;------ 查询表分区情况 select table_name,partition_name from user_tab_partitions where table_name='PDBA';---- 查询表内数据情况 SELECT 'PDBA-p1', COUNT(*) FROM PDBA PARTITION(P1) UNION SELECT 'PDBA-p2', COUNT(*) FROM PDBA PARTITION(P2) UNION SELECT 'PDBA-p3', COUNT(*) FROM PDBA PARTITION(P3) UNION

SELECT 'PDBA-p4', COUNT(*) FROM PDBA PARTITION(P4) ;

--导出命令

--只导一个分区

C:/Users/lzhpc>exp index_ts/1 grants=y file=123.dmp tables=pdba:p1

--导两个分区

C:/Users/lzhpc>exp index_ts/1 grants=y file=123.dmp tables=pdba:p2,pdba:p3

--导出整个表

C:/Users/lzhpc>exp index_ts/1 grants=y file=123.dmp tables=pdba

上一篇：sql关键字exists原理

下一篇：【SQLSERVER】output用法解析