这几天一直在导数据。由于数据量实在是太大了。
表空间被撑满了。 可以使用
添加表空间文件来实现这一方法~~
Oracle删除或更新、插入数据时。会执行回滚数据操作
如果数据量大时。可以使用存储过程来执行操作。
—————–例子——————————
as
type ridArray is table of rowid index by binary_integer;
type dtArray is table of varchar2(50) index by binary_integer;
v_rowid ridArray;
v_mid_to_delete dtArray;
begin
这几天一直在导数据。由于数据量实在是太大了。
表空间被撑满了。 可以使用
添加表空间文件来实现这一方法~~
Oracle删除或更新、插入数据时。会执行回滚数据操作
如果数据量大时。可以使用存储过程来执行操作。
—————–例子——————————
as
type ridArray is table of rowid index by binary_integer;
type dtArray is table of varchar2(50) index by binary_integer;
v_rowid ridArray;
v_mid_to_delete dtArray;
begin
select mid,rowid bulk collect into v_mid_to_delete,v_rowid from temp_mid_hubei_bak where rownum<10001;
forall i in 1 .. v_mid_to_delete.COUNT
delete from SSF where mid = v_mid_to_delete(i);
— DBMS_OUTPUT.PUT_LINE(to_char(v_mid_to_delete.COUNT)||’ records deleted from hubei_SSF !!!’);
forall i in 1 .. v_rowid.COUNT
delete from temp_mid_hubei_bak where rowid = v_rowid(i);
— DBMS_OUTPUT.PUT_LINE(to_char(v_rowid.COUNT)||’ records deleted from temp_mid_hubei_bak !!!’);
end;
/
然后构造始终按照10000条循环批量删除的过程：
create or replace procedure exec_forall
(
p_RowCount in number, — Total need to delete rows count
p_ExeCount in number — Every times need to delete rows count
)
as
n_RowCount number:=0; — Yet needed to delete rows count
n_ExeTimes number:=0; — execute times(loop times)
n_delete number:=0; — really delete rows count
begin
n_RowCount := p_RowCount;
while n_RowCount >0 loop
EXECUTE IMMEDIATE ‘begin del_hubei_SUBSREGINFO_forall; end;’;
commit;
if n_RowCount>p_ExeCount then
n_RowCount:= n_RowCount-p_ExeCount;
n_ExeTimes := n_ExeTimes + 1;
else
n_ExeTimes := n_ExeTimes + 1;
n_delete := n_RowCount;
n_RowCount:= n_RowCount-p_ExeCount;
end if;
DBMS_OUTPUT.PUT_LINE(‘———‘||to_char(n_ExeTimes)||’———–‘);
n_delete := n_delete+p_ExeCount*(n_ExeTimes-1);
end loop;
DBMS_OUTPUT.PUT_LINE(‘Full Finished!!!’);
DBMS_OUTPUT.PUT_LINE(‘Totally ‘||to_char(n_delete)||’ records deleted. !!!’);
end;
/
——–四种分页查询方法—————————-
1.
select top m * into 临时表(或表变量) from tablename order by columnname — 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
2.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
3.如果tablename里没有其他identity列，那么：
select identity(int) id0,* into #temp from tablename
取n到m条的语句为：
select * from #temp where id0 >=n and id0 <= m
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,’select into/bulkcopy’,true
4.如果表里有identity属性，那么简单：
select * from tablename where identitycol between n and m
近期评论