锐客网

  1. 首页 > 生活百科 > it技术 > >

oracle如何批量 Oracle如何批量换表结构

数据IT技术

Oracle数据的批量插入 前两天接到一个需求——需要编程将SQL Server中的数据插入至Oracle 数据大约有 多万条记录 开始的时候我采取了直接构建SQL插入的方式 结果耗时太长 为了提高性能我上网找了资料 最终采用DataAdapter批量插入至Oracle 提高了性能
代码如下
一 直接构建SQL语句插入
VB NET
sw Start()
Read Z J from SQL Server
Dim sqlCmd As New SqlCommand()
sqlCmd Connection = sqlConnection
sqlCmd CommandText = SELECT * FROMZ J
Dim sqlDr As SqlDataReader
sqlDr = sqlCmd ExecuteReader()
Dim cmdInsertZ J As New OracleCommand()
cmdInsertZ J Connection = oraConnection
cmdInsertZ J CommandText = BuildSQLStatement(SQLType Insert z j )
Dim plantLever material oldMaterialNum materialDescription As Object
While sqlDr Read()
plantLever = ReadSqlDataReader(sqlDr )
material = ReadSqlDataReader(sqlDr )
oldMaterialNum = ReadSqlDataReader(sqlDr )
materialDescription = ReadSqlDataReader(sqlDr )
Insert to Oracle table Z J
cmdInsertZ J Parameters AddWithValue( :plantLever plantLever)
cmdInsertZ J Parameters AddWithValue( :material material)
cmdInsertZ J Parameters AddWithValue( :oldMaterialNum oldMaterialNum)
cmdInsertZ J Parameters AddWithValue( :materialDescription materialDescription)
cmdInsertZ J ExecuteNonQuery()
End While
sw Stop()
Loger Info( Reading z j form sql sever used sw Elapsed TotalSeconds ToString())
二 采用DataAdapter实现批量插入
VB NET
sw Start()
Read Z J from SQL Server
Dim sqlCmd As New SqlCommand()
sqlCmd Connection = sqlConnection
sqlCmd CommandText = SELECT * FROMZ J
Dim sqlDr As SqlDataReader
sqlDr = sqlCmd ExecuteReader()
Dim cmdInsertZ J As New OracleCommand()
cmdInsertZ J Connection = oraConnection
cmdInsertZ J CommandText = BuildSQLStatement(SQLType Insert z j )
Dim dtSqlZ J As New DataTable
dtSqlZ J Columns Add( plantLever )
dtSqlZ J Columns Add( material )
dtSqlZ J Columns Add( oldMaterialNum )
dtSqlZ J Columns Add( materialDescription )
Dim plantLever material oldMaterialNum materialDescription As Object
While sqlDr Read()
plantLever = ReadSqlDataReader(sqlDr )
material = ReadSqlDataReader(sqlDr )
oldMaterialNum = ReadSqlDataReader(sqlDr )
materialDescription = ReadSqlDataReader(sqlDr )
dtSqlZ J Rows Add(plantLever material oldMaterialNum materialDescription)
End While
sw Stop()
Loger Info( Reading z j form sql sever used sw Elapsed TotalSeconds ToString())
sw Start()
Dim oraDa As New OracleDataAdapter()
oraDa InsertCommand = cmdInsertZ J
oraDa InsertCommand Parameters Add( :plantLever OracleType Char plantLever )
oraDa InsertCommand Parameters Add( :material OracleType Char material )
oraDa InsertCommand Parameters Add( :oldMaterialNum OracleType Char oldMaterialNum )
oraDa InsertCommand Parameters Add( :materialDescription OracleType Char materialDescription )
oraDa InsertCommand UpdatedRowSource = UpdateRowSource None
oraDa UpdateBatchSize =Adjust the batch size based on testing result
oraDa Update(dtSqlZ J)
sw Stop()
lishixinzhi/Article/program/Oracle/201311/18480
如何批量修改oracle数据库中某一个表中的某一列数据？最好的方法是批量修改,即每次修改5000条(一次修改不要超过一万条,否则影响性能). 虽然在11g中，我们也可以选择使用merge命令，但你的这种情况最好先修改一部分然后看看影响，毕竟在生产环境作这样的操作风险很大 。如果是误操作 ， 最好还是请DBA来恢复，虽然这样做会被挨骂，但总比错上加错，最后连挨骂的机会都没有要好得多 。如果对这些修改真的有信心 ， 而只是从性能考虑，那可以用下面的方法(pk_col 是表的主键
1、构建临时表进行主键关联更新
需求：T1表有千万级别的数据量，需要更新这个表的字段a ， b满足2个条件的记录 。
做法：一般业务会将条件通过excel表格提供给开发，那么开发首先需要将这个excel表格的内容插入到临时表T2中 ， 考虑到a ， b都不是主键 ， 那么需要将a ， b转化成主键后再插入到T2表中，T2表中还可以保存更新前的数据，方便做数据回滚，T2表中有数据后 ， 就可以执行下面脚本进行更新操作:
ps：c，d是需要更新的操作 ， e，f是条件 。必须强调的是id必须是主键
1、构建临时表进行主键关联更新
需求：T1表有千万级别的数据量，需要更新这个表的字段a，b满足2个条件的记录 。
做法：一般业务会将条件通过excel表格提供给开发 ， 那么开发首先需要将这个excel表格的内容插入到临时表T2中，考虑到a，b都不是主键，那么需要将a，b转化成主键后再插入到T2表中，T2表中还可以保存更新前的数据，方便做数据回滚，T2表中有数据后，就可以执行下面脚本进行更新操作:
ps：c ， d是需要更新的操作，e ， f是条件 。必须强调的是id必须是主键
oracle怎样批量删除和修改下面一段示例说明了oracle的基本操作insert、update、delete产生的undo的大小和记录条数 。
--insert操作无论是1条或者多条，一次产生一条undo数据
SQL create table a (b number, c varchar2(30));
Table created
SQL insert into a values (1,'zhangsan');
1 row inserted
【oracle如何批量 Oracle如何批量换表结构】SQL
SQL select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
1 1
SQL insert into a select rownum,rownum from dual connect by rownum=50;
50 rows inserted
SQL
SQL select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
1 2
--delete操作产生的undo条数取决于delete操作本身影响的记录数SQL delete from a;
51 rows deleted
SQL
SQL select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
1 53
SQL insert into a select rownum,rownum from dual connect by rownum=50;
50 rows inserted
SQL
SQL select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
1 54
--update操作产生的undo条数取决于被update的数据的原值和新值存在差异的记录数量
SQL update a set c=rownum;
50 rows updated
SQL
SQL select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
---------- ----------
2 57
SQL update a set c=rownum 1000;
50 rows updated
SQL
SQL select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;
USED_UBLK USED_UREC
如何加速Oracle大批量数据处理？一、提高DML操作的办法：\x0d\x0a简单说来：\x0d\x0a1、暂停索引，更新后恢复.避免在更新的过程中涉及到索引的重建.\x0d\x0a2、批量更新，每更新一些记录后及时进行提交动作.避免大量占用回滚段和或临时表空间.\x0d\x0a3、创建一临时的大的表空间用来应对这些更新动作．\x0d\x0a\x0d\x0a4、批量更新，每更新一些记录后及时进行提交动作.避免大量占用回滚段和或临时表空间.\x0d\x0a\x0d\x0a5、创建一临时的大的表空间用来应对这些更新动作．\x0d\x0a\x0d\x0a6、加大排序缓冲区\x0d\x0aalter session set sort_area_size=100000000;\x0d\x0ainsert into tableb select * from tablea;\x0d\x0acommit;\x0d\x0a\x0d\x0a如果UPDATE的是索引字段，就会涉及到索引的重建，暂停索引不会提高多少的速度，反而有可能降低UPDATE速度 ， \x0d\x0a因为在更新是索引可以提高数据的查询速度，重建索引引起的速度降低影响不大 。\x0d\x0a\x0d\x0aORACLE优化修改参数最多也只能把性能提高15%，大部分都是SQL语句的优化！\x0d\x0a\x0d\x0aupdate总体来说比insert要慢 ：\x0d\x0a几点建议：\x0d\x0a1、如果更新的数据量接近整个表 ， 就不应该使用index而应该采用全表扫描\x0d\x0a2、减少不必要的index，因为update表通常需要update index\x0d\x0a3、如果你的服务器有多个cpu ， 采用parellel hint，可以大幅度的提高效率\x0d\x0a另外，建表的参数非常重要，对于更新非常频繁的表，建议加大PCTFREE的值，以保证数据块中有足够的空间用于UPDATE，从而降低CHAINED_ROWS 。\x0d\x0a\x0d\x0a二、各种批量DML操作：\x0d\x0a（1）、oracle批量拷贝：\x0d\x0aset arraysize 20\x0d\x0aset copycommit 5000\x0d\x0acopy from username/password@oraclename append table_name1\x0d\x0ausing select * from table_name2;\x0d\x0a（2）、常规插入方式：\x0d\x0ainsert into t1 select * from t;\x0d\x0a为了提高速度可以使用下面方法，来减少插入过程中产生的日志:\x0d\x0aalter table t1 nologging;\x0d\x0ainsert into t1 select * from t;\x0d\x0acommit;\x0d\x0a(3)、CTAS方式：\x0d\x0acreate table t1\x0d\x0aas\x0d\x0aselect * from t;\x0d\x0a为了提高速度可以使用下面方法，来减少插入过程中产生的日志 ， 并且可以制定并行度:\x0d\x0acreate table t1 nologging parallel(degree 2) as select * from t;\x0d\x0a（4）、Direct-Path插入：\x0d\x0ainsert /* append*/ into t1 select * from t;\x0d\x0acommit;\x0d\x0a为了提高速度可以使用下面方法 ， 来减少插入过程中产生的日志：\x0d\x0aalter table t1 nologging;\x0d\x0ainsert /* append*/ into t1 select * from t;\x0d\x0a \x0d\x0aDirect-Path插入特点：\x0d\x0a1、append只在insert ? select ?中起作用，像insert /*append */ into t values(?)这类的语句是不起作用的 。在update、delete操作中，append也不起作用 。\x0d\x0a2、 Direct-Path会使数据库不记录直接路径导入的数据的重做日志，会对恢复带来麻烦 。\x0d\x0a3、 Direct-Path直接在表段的高水位线以上的空白数据块中写数据 ， 不会重用高水位线以下的空间，会对空间的使用造成一定的浪费，对查询的性能也会造成一定的影响 。而常规插入会优先考虑使用高水位线之下有空闲空间存在的数据块 。因此理论上Direct-Path插入会比常规插入速度更快，因为Direct-Path直接使用新数据块，而常规插入要遍历freelist获取可用空闲数据块，如果同 nologging 配合，这种速度优势会更加明显 。\x0d\x0a4、 以append方式插入记录后，要执行commit，才能对表进行查询 。否则会出现错误：ORA-12838: 无法在并行模式下修改之后读/修改对象 。\x0d\x0a5、 用append导入数据后，如果没有提交或者回滚 ， 在其他会话中任何对该表的DML都会被阻塞（不会报错），但对该表的查询可以正常执行 。\x0d\x0a6、 在归档模式下，要把表设置为nologging ， 然后以append方式批量添加记录，才会显著减少redo数量 。在非归档模式下 ， 不必设置表的 nologging属性，即可减少redo数量 。如果表上有索引，则append方式批量添加记录 ， 不会减少索引上产生的redo数量，索引上的redo 数量可能比表的redo数量还要大 。\x0d\x0a7、 数据直接插入数据文件，绕过buffer cache并且忽略了引用完整性约束 。\x0d\x0a8、 不管表是否在nologging 下，只要是 directinsert ， 就不会对数据内容生成undo 。\x0d\x0a9、 Oracle在Direct-Path INSERT 操作末尾 ， 对具有索引的表执行索引维护，这样就避免了在drop掉索引后，再rebuild 。\x0d\x0a10、Direct-Path INSERT比常规的插入需要更多的空间 。因为它将数据插入在高水位之上 。并行插入非分区表需要更多的空间，因为它需要为每一个并行线程创建临时段 。\x0d\x0a11、在插入期间，数据库在表上获得排他锁，用户不能在表上执行并行插入、更新或者删除操作 ， 并行的索引创建和build也不被允许 。但却可以并行查询，但查询返回的是插入之前的结果集 。\x0d\x0a（5）、并行DML：\x0d\x0a如果你的服务器有多个cpu，采用parellel hint，可以大幅度的提高效率\x0d\x0aALTER SESSION ENABLE PARALLEL DML;\x0d\x0a\x0d\x0aINSERT /*PARALLEL(tableA, 2) */INTO tableA \x0d\x0aSELECT * FROM tableB;\x0d\x0a\x0d\x0a为了提高速度可以使用下面方法，来减少插入过程中产生的日志：\x0d\x0a\x0d\x0a INSERT /*PARALLEL(tableA, 2) */INTO tableA NOLOGGING\x0d\x0aSELECT * FROM tableB;\x0d\x0a\x0d\x0aoracle默认并不会打开PDML，对DML语句必须手工启用 。即需要执行\x0d\x0aalter table enable parallel dml命令 。\x0d\x0a \x0d\x0a并行DML特点：\x0d\x0a1、在并行DML模式中，默认的就是DIRECT-PATH插入 ， 为了运行并行DML模式 ， 必须满足以下条件：\x0d\x0aa、必须是Oracle企业版；\x0d\x0ab、必须在session中使并行DML生效，执行以下sql语句：\x0d\x0aALTER SESSION { ENABLE | FORCE } PARALLEL DML;\x0d\x0ac、必须指定table的并行属性，在创建的时候或者其他时候，或者在insert操作时使用“PARALLEL”提示 。\x0d\x0ad、为了使Direct-Path Insert模式失效，在INSERT语句中指定“NOAPPEND”提示，覆盖并行DML模式 。\x0d\x0a2、并行Direct-Path INSERT到分区表：\x0d\x0a类似于serial Direct-Path INSERT，每个并行操作分配给一个或者多个分区，每个并行操作插入数据到各自的分区段的高水位标志之上，commit之后，用户就能看到更新的数据 。\x0d\x0a3、并行Direct-Path INSERT到非分区表：\x0d\x0a每个并行执行分配一个新的临时段，并插入数据到临时段 。当commit运行后，并行执行协调者合并新的临时段到主表段，用户就能看到更新的数据 。\x0d\x0a4、Direct-Path INSERT可以使用Log或者不使用Log 。\x0d\x0a5、另外不得不说的是，并行不是一个可扩展的特性，只有在数据仓库或作为DBA等少数人的工具在批量数据操作时利于充分利用资源，而在OLTP环境下使用并行需要非常谨慎 。事实上PDML还是有比较多的限制的，例如不支持触发器，引用约束 ， 高级复制和分布式事务等特性，同时也会带来额外的空间占用，PDDL同 样是如此 。
oracle怎么批量导入导需要用exp和imp命令，具体用法如下：
1. 获取帮助
imp help=y
2. 导入一个完整数据库
imp system/manager file=bible_db log=dible_db full=y ignore=y
3. 导入一个或一组指定用户所属的全部表、索引和其他对象
imp system/manager file=seapark log=seapark fromuser=seapark
imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)
4. 将一个用户所属的数据导入另一个用户
imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy
imp system/manager file=tank log=tank fromuser=(seapark,amy)
touser=(seapark1, amy1)
5. 导入一个表
imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)
6. 从多个文件导入
imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4)
log=paycheck, filesize=1G full=y
7. 使用参数文件
imp system/manager parfile=bible_tables.par
bible_tables.par参数文件：
#Import the sample tables used for the Oracle8i Database Administrator's
Bible. fromuser=seapark touser=seapark_copy file=seapark log=seapark_import
8. 增量导入
imp system./manager inctype= RECTORE FULL=Y FILE=A
-------------------------------------------------------------------------------------------------------------------------------------------
1. 获取帮助
exp help=y
2. 导出一个完整数据库
exp system/manager file=bible_db log=dible_db full=y
3. 导出数据库定义而不导出数据
exp system/manager file=bible_db log=dible_db full=y rows=n
4. 导出一个或一组指定用户所属的全部表、索引和其他对象
exp system/manager file=seapark log=seapark owner=seapark
exp system/manager file=seapark log=seapark owner=(seapark,amy,amyc,harold)
注意：在导出用户时，尽管已经得到了这个用户的所有对象，但是还是不能得到这些对象引用的任何同义词 。解决方法是用以下的SQL*Plus命令创建一个脚本文件，运行这个脚本文件可以获得一个重建seapark所属对象的全部公共同义词的可执行脚本，然后在目标数据库上运行该脚本就可重建同义词了 。
SET LINESIZE 132
SET PAGESIZE 0
SET TRIMSPOOL ON
SPOOL c:\seapark.syn
SELECT 'Create public synonym '||synonym_name
||' for '||table_owner||'.'||table_name||';'
FROM dba_synonyms
WHERE table_owner = 'SEAPARK' AND owner = 'PUBLIC';
SPOOL OFF
5. 导出一个或多个指定表
exp seapark/seapark file=tank log=tank tables=tank
exp system/manager file=tank log=tank tables=seapark.tank
exp system/manager file=tank log=tank tables=(seapark.tank,amy.artist)
6. 估计导出文件的大小
全部表总字节数：
SELECT sum(bytes)
FROM dba_segments
WHERE segment_type = 'TABLE';
seapark用户所属表的总字节数：
SELECT sum(bytes)
FROM dba_segments
WHERE owner = 'SEAPARK'
AND segment_type = 'TABLE';
seapark用户下的aquatic_animal表的字节数：
SELECT sum(bytes)
FROM dba_segments
WHERE owner = 'SEAPARK'
AND segment_type = 'TABLE'
AND segment_name = 'AQUATIC_ANIMAL';
7. 导出表数据的子集(oracle8i以上)
NT系统：
exp system/manager query='Where salad_type='FRUIT'' tables=amy.salad_type
file=fruit log=fruit
UNIX系统：
exp system/manager query=\"Where salad_type=\'FRUIT\'\" tables=amy.salad_type
file=fruit log=fruit
8. 用多个文件分割一个导出文件
exp system/manager
file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4)
log=paycheck, filesize=1G tables=hr.paycheck
9. 使用参数文件
exp system/manager parfile=bible_tables.par
bible_tables.par参数文件：
#Export the sample tables used for the Oracle8i Database Administrator's Bible.
file=bible_tables
log=bible_tables
tables=(
amy.artist
amy.books
seapark.checkup
seapark.items
)
10. 增量导出
“完全”增量导出(complete)，即备份整个数据库
exp system/manager inctype=complete file=990702.dmp
“增量型”增量导出(incremental)，即备份上一次备份后改变的数据
exp system/manager inctype=incremental file=990702.dmp
“累计型”增量导出(cumulative) ， 即备份上一次“完全”导出之后改变的数据
exp system/manager inctype=cumulative file=990702.dmp
Oracle 批量插入数据怎么做一个例子你看看
create or replace procedure p_test is
v_dayvarchar2(20);
nextdayvarchar2(20);
cur_valuenumber;
yes_valuenumber;
thismonth_totalvalue number;
lastmonth_totalvalue number;
inumber;
tempdayvarchar2(20);
day_countinteger;
begin
execute immediate 'truncate table DM_KPID_IW_AREA';
v_day := '20070101';
-- lastmonth_totalvalue := 0;
for k in 0 .. 11 loop
tempday:= to_char(add_months(to_date(v_day,
'yyyymmdd'),
k),
'yyyymmdd');
yes_value:= 0;
thismonth_totalvalue := 0;
-- 这里好像有一个比较方便的函数吧 ， 记不清了
day_count := to_char(last_day(to_date(tempday, 'yyyymmdd')), 'dd');
for i in 0 .. day_count - 1 loop
nextday:= to_char(to_date(tempday, 'yyyymmdd')i,
'yyyymmdd');
cur_value:= trunc(dbms_random.value(1, 100), 0);
thismonth_totalvalue := thismonth_totalvaluecur_value;
begin
select t.thismonth_totalvalue
into lastmonth_totalvalue
from dm_kpid_iw_area t
where t.day_id =
--考虑如3月30日之类这样的情况，前一月没有对应的日子的情况，
--如果此时上月值为0的话，则应该为：
--to_char(add_months(to_date(nextday, 'yyyymmdd'), -1),
--'yyyymm') || substr(nextday, 7, 2);
to_char(add_months(to_date(nextday, 'yyyymmdd'), -1),
'yyyymmdd');
exception
when no_data_found then
lastmonth_totalvalue := 0;
end;
insert into dm_kpid_iw_area
(day_id,
cur_value,
yes_value,
thismonth_totalvalue,
lastmonth_totalvalue)
values
(nextday,
cur_value,
yes_value,
thismonth_totalvalue,
lastmonth_totalvalue);
yes_value := cur_value;
commit;
end loop;
end loop;
end;
oracle如何批量的介绍就聊到这里吧 ， 感谢你花时间阅读本站内容，更多关于Oracle如何批量换表结构、oracle如何批量的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：手机通讯录java源代码，java手机通讯录怎么用首字母排序

    下一篇：服务器拒绝连接是怎么回事 服务器拒绝接入怎么办