[Oracle存储过程批量插入1000万数据]
[以及如何改变一个dbf文件的大小]
[Oracle存储过程批量插入1000万数据以及如何改变一个dbf文件的大小]

--改变一个dbf文件的大小
select * from dba_data_files;--先查找
alter database datafile 'F:\PROGRAM FILES\ORACLEDATABASE\ORCL\IDEA.DBF' autoextend on next 100M maxsize unlimited;

 
注：F:\RDS_DATAFILE\RDS0714.DBF 是已存在的文件
 
 
 
 
 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as idea_service
SQL> EXECUTE TEST_INSERT;
begin test_insert; end;
ORA-01861: 文字与格式字符串不匹配
ORA-06512: 在 "IDEA_SERVICE.TEST_INSERT", line 15
ORA-06512: 在 line 2
SQL> EXECUTE TEST_INSERT;
begin TEST_INSERT; end;
ORA-01861: 文字与格式字符串不匹配
ORA-06512: 在 "IDEA_SERVICE.TEST_INSERT", line 15
ORA-06512: 在 line 2
SQL> EXECUTE TEST_INSERT;
begin TEST_INSERT; end;
ORA-01861: 文字与格式字符串不匹配
ORA-06512: 在 "IDEA_SERVICE.TEST_INSERT", line 15
ORA-06512: 在 line 2
SQL> EXECUTE TEST_INSERT;
begin TEST_INSERT; end;
ORA-01861: 文字与格式字符串不匹配
ORA-06512: 在 "IDEA_SERVICE.TEST_INSERT", line 15
ORA-06512: 在 line 2
SQL> EXECUTE TEST_INSERT;
begin TEST_INSERT; end;
ORA-06592: 执行 CASE 语句时未找到 CASE
ORA-06512: 在 "IDEA_SERVICE.TEST_INSERT", line 17
ORA-06512: 在 line 2
SQL> EXECUTE TEST_INSERT;
begin TEST_INSERT; end;
ORA-01654: 索引 IDEA_SERVICE.IDX_SEND_RECORD_TIME2 无法通过 16 (在表空间 IDEA 中) 扩展
ORA-06512: 在 "IDEA_SERVICE.TEST_INSERT", line 15
ORA-06512: 在 line 2
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS

 
 
-------------------------------------------------------------------------------- ---------- -------------------------
 
改变dbf文件的大小如下：
 

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF 3 SYSAUX 293601280 35840 AVAILABLE 3 YES 3435972198 4194302 1280 293535744 35832 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF 2 UNDOTBS1 1001390080 122240 AVAILABLE 2 YES 3435972198 4194302 640 1001324544 122232 ONLINE
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 1 SYSTEM 513802240 62720 AVAILABLE 1 YES 3435972198 4194302 1280 513736704 62712 SYSTEM
F:\PROGRAM FILES\ORACLEDATABASE\ORCL\HISTDB.DBF 5 IDEA_SERVICE 251658240 30720 AVAILABLE 5 YES 3435972198 4194302 1280 251592704 30712 ONLINE
F:\PROGRAM FILES\ORACLEDATABASE\ORCL\IDEA.DBF 6 IDEA 524288000 64000 AVAILABLE 6 NO 0 0 0 524156928 63984 ONLINE
6 rows selected
SQL> alter database datafile 'F:\PROGRAM FILES\ORACLEDATABASE\ORCL\IDEA.DBF' autoextend on next 100M maxsize unlimited;
Database altered
SQL> EXECUTE TEST_INSERT;
PL/SQL procedure successfully completed
SQL> EXECUTE TEST_INSERT;
PL/SQL procedure successfully completed
SQL>

 
 
 
 
 
相应的插入1000万条数据的存储过程如下：
 
 

CREATE OR REPLACE PROCEDURE Test_Insert
AS
MyCounter int;
temp int;
status int;
phone int;
BEGIN
MyCounter:=1; /*设置变量*/
temp:=1;
phone:=18277710000;
status:=1;
LOOP
insert into send_record( ID,SERVICE_ID,DEAL_ID,RECVER,SENDER,FEE_MAN,MSG_TYPE,SMS_CONTENT,SERVICE_CODE,FEE,TIME,WAY_ID,STATUS,ERR_CODE,STORE_FILE,LINK_ID,WAY_ERROR,IS_READ ) values (SJBZ_SEND_RECORD_SEQ.NEXTVAL,100,370202846,phone,10658088,phone,0,null,'HELP',0,sysdate,'10239',status,0,'20110105/1/506099932',null,'MK:0000',null );
--提交
CASE
WHEN MyCounter = 1000000 THEN
commit;
WHEN MyCounter = 2000000 THEN
commit;
WHEN MyCounter = 3000000 THEN
commit;
WHEN MyCounter = 4000000 THEN
commit;
WHEN MyCounter = 5000000 THEN
commit;
WHEN MyCounter = 6000000 THEN
commit;
WHEN MyCounter = 7000000 THEN
commit;
WHEN MyCounter = 8000000 THEN
commit;
WHEN MyCounter = 9000000 THEN
commit;
WHEN MyCounter = 10000000 THEN
commit;
ELSE
DBMS_OUTPUT.put_line('Johnny test insert table!');
END CASE;
EXIT WHEN MyCounter=10000000 ;--当变量为1000万时跳出循环
MyCounter:=MyCounter+1;
temp:=temp+1;
phone:=phone+1;
--判断
--if (1000000<MyCounter<2000000) then
--status:=1;
--elseif (2000000<=MyCounter<4000000) then
--status:=2;
--elseif (4000000<=MyCounter<6000000) then
--status:=3;
--elseif (7000000<=MyCounter<9000000) then
--status:=4;
--else status:=0;
--endif
END LOOP;
END Test_Insert;