创建 导入导出 Oracle 分区表数据.docx

1、创建 导入导出 Oracle 分区表数据导入导出 Oracle 分区表数据Create table(创建分区表)create table BILL_MONTHFEE_ZERO(SERV_ID NUMBER(20) not null,BILLING_CYCLE_MONTH NUMBER(6) not null,DATE_TYPE NUMBER(1),ACC_NBR VARCHAR2(80)partition by range (BILLING_CYCLE_MONTH)(partition p_200407 values less than (200407)tablespace TS_ZIKENs

2、torage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),partition p_200408 values less than (200408)tablespace TS_ZIKENstorage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0);create index idx_bill_monthfee_zero_idx01 on bill_monthfee_zero(billing_cycle_

3、month)tablespace TS_ZIKEN_idxstorage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) nologging;grant all on bill_monthfee_zero to dxsq_dev;-增加分区表alter table BILL_MONTHFEE_ZERO add Partition p_200409values less than (200409) tablespace ts_ziken;-删除一分区alter table part_tbl drop

4、Partition part_tbl_08;-将一个分区分为两个分区alter table bill_monthfee_zero split Partition p_200409 at (200409)into (Partition p_200409_1 tablespace ts_ziken,Partition p_200409_2 tablespace ts_ziken_idx);-合并分区ALTER TABLE bill_monthfee_zeroMERGE PARTITIONS p_200408, p_200409 INTO PARTITION p_all-将分区改名alter tab

5、le bill_monthfee_zero rename Partition p_200408 to p_fee_200408-将分区改表空间alter table bill_monthfee_zero move Partition p_200409tablespace ts_ziken_01 nologging-查询特定分区select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);-添加数据insert into bill_monthfee_zero select * from bill_monthfee_zero partit

6、ion (p_200407)-分区表的导出userid=dxsq/teledoonejndxsq154buffer=102400tables=bill_monthfee:P_200401,file=E:exp_paraexp_dxsq_tables.dmplog=E:exp_paraexp_dxsq_tables.log技巧：删除表中一个字段：alter table bill_monthfee_zero set unused column date_type;添加一个字段：alter table bill_monthfee_zero add date_type number(1);UNIX中O

7、RACLE分区表导出创建分区表：create table test(join_date VARCHAR2(8),name VARCHAR2(50),sex char(1),address varchar2(100)partition by list(statis_date)(partition part_20091101 values(20091101);转移分区表alter table tb_cust_view_day move Partition PART_20100505 tablespace TBS_USER nologging;转移表空间 ，将该表20100505的分区转移至表空间T

8、BS_USER ，这样他就占用的是USER的空间 alter table TB_CUST_INFO_DAY drop partition PART_20100301; 删除表分区 alter table tbname drop partition partname alter table tbname move partition partname tablespace tbspacename 1.分区表 导出 -单表导出 exp test/test file=/home/wtest/mail_cust12.dmp tables=PERSON_MAIL_MON:PART_200912 -多表

9、导出 exp file=part_1.dmp userid=test/testorcl buffer=33554432 recordlength=65535 tables=TABLE_PART1:PART_20091216 TABLE_PART12:PART_20091216 2.普通表 多表导出-月表： 导出201001 的数据exp file=tbale12 userid=test/testorcl buffer=33554432 recordlength=65535 tables=table_A table_B query= where DATE_MONTH = 201001 - 日表

10、导出20100218-20100220的数据exp file=/home/test/table15_16.dmp userid=test/testorcl buffer=33554432 recordlength=65535 tables=table_aa table_bb query= where date_day between 20100218 and 20100220 关键字 说明 ( 默认 ) userid 用户名 / 口令 buffer 数据缓冲区大小 file 输出文件 (EXPDAT.DMP) compress 导入到一个区 (Y) grants 导出权限 (Y) indexe

11、s 导出索引 (Y) direct 直接路径 (N) log 屏幕输出的日志文件 rows 导出数据行 (Y) consistent 交叉表的一致性 (N)full 导出整个文件 (N) owner 所有者用户名列表 tables 表名称列表 recordlength IO 记录的长度 inctype 增量导出类型 record 跟踪增量导出 (Y) triggers 导出触发器 (Y) statistics 分析对象 (ESTIMATE)parfile 参数文件名 constraints 导出的约束条件 (Y) object_consistent 只在对象导出期间设置为读的事务处理 (N)

12、feedback 每 x 行的显示进度 (0) filesize 每个转储文件的最大大小 flashback_scn 用于将会话快照设置回以前状态的 SCN flashback_time 用于获取最接近指定时间的 SCN 的时间 query 用于导出表的子集的 select 子句 resumable 遇到与空格相关的错误时挂起 (N) resumable_name 用于标识可恢复语句的文本字符串 resumable_timeout RESUMABLE 的等待时间 tts_full_check 对 TTS 执行完整的或部分相关性检查 tablespaces 要导出的表空间列表 transport

13、_tablespace 导出可传输的表空间元数据 (N) template 调用 iAS 模式导出的模板名 注意：unix中 oracle多表 用 空格 区分- 导入导出 Oracle 分区表数据-* 导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式，只不过导入导出需要考虑到分区的特殊性，如分区索引，将分区迁移到普通表，或使用原始分区表导入到新的分区表。下面将描述使用imp/exp，impdp/expdp导入导出分区表数据。 有关分区表的特性请参考： Oracle 分区表 SQL server 2005 切换分区表 SQL s

14、erver 2005 基于已存在的表创建分区 有关导入导出工具请参考： 数据泵EXPDP 导出工具的使用 数据泵IMPDP 导入工具的使用 有关导入导出的官方文档请参考： Original Export and Import 一、分区级别的导入导出 可以导出一个或多个分区，也可以导出所有分区(即整个表)。 可以导入所有分区(即整个表)，一个或多个分区以及子分区。 对于已经存在数据的表，使用imp导入时需要使用参数IGNORE=y，而使用impdp，加table_exists_action=append | replace 参数。二、创建演示环境 1.查看当前数据库的版本 SQL select

15、* from v$version where rownum alter session set nls_date_format=yyyy-mm-dd; SQL CREATE TABLE tb_pt ( sal_date DATE NOT NULL, sal_id NUMBER NOT NULL, sal_row NUMBER(12) NOT NULL) partition by range(sal_date) ( partition sal_11 values less than(to_date(2012-01-01,YYYY-MM-DD) , partition sal_12 values

16、less than(to_date(2013-01-01,YYYY-MM-DD) , partition sal_13 values less than(to_date(2014-01-01,YYYY-MM-DD) , partition sal_14 values less than(to_date(2015-01-01,YYYY-MM-DD) , partition sal_15 values less than(to_date(2016-01-01,YYYY-MM-DD) , partition sal_16 values less than(to_date(2017-01-01,YYY

17、Y-MM-DD) , partition sal_other values less than (maxvalue) ) nologging; 3.创建一个唯一索引 CREATE UNIQUE INDEX tb_pt_ind1 ON tb_pt(sal_date) nologging; 4.为分区表生成数据 SQL INSERT INTO tb_pt SELECT TRUNC(SYSDATE)+ROWNUM, dbms_random.random, ROWNUM FROM dual CONNECT BY LEVEL commit; SQL select count(1) from tb_pt

18、partition(sal_11); COUNT(1) - 300 SQL select count(1) from tb_pt partition(sal_other); COUNT(1) - 2873 SQL select * from tb_pt partition(sal_12) where rownum select userenv(language) from dual; USERENV(LANGUAGE) - AMERICAN_AMERICA.ZHS16GBK oraclenode1 $ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 经过上述

19、设置之后再次导出正常，过程略。 2.导出单个分区 oraclenode1 $ exp scott/tiger file=/u02/dmp/tb_pt_sal_16.dmp log=/u02/dmp/tb_pt_sal_16.log tables=tb_pt:sal_16 Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:52:38 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle

20、 Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing o Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set About to export specified tables v

21、ia Conventional Path . . . exporting table TB_PT . . exporting partition SAL_16 366 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings 在上面的导出过程中再次出现了统计信息错误的情况，因此采取了对该对象收集统计信息，但并不能解决该错误，但在exp命令行中增 加statistics=none即可，如下： oraclenode1 $ exp scott/tiger file=/u02/dmp/tb_pt_sal_16.dmp log=/u02/dmp/tb_pt_sal_16.log / tables