oracle创建大表测试,Oracle创建用于测试的大表

个人实验的时候有时会用到一些大表，在此分享用dba_objects来创建测试大表的方法： 首先建立测试表 SYS@ORCLcreate table test nologging as select rownum id,a.* from dba_objects a where 1=2; Table created. 插入500万条数据： SYS@ORCLdeclare l_cnt n

个人实验的时候有时会用到一些大表，，在此分享用dba_objects来创建测试大表的方法：

首先建立测试表

SYS@ORCL>create table test nologging as select rownum id,a.* from dba_objects a where 1=2;

Table created.

插入500万条数据：

SYS@ORCL>declare

l_cnt number;

l_rows number:=&1;

begin

insert /*+ append */ into test select rownum,a.* from dba_objects a;

l_cnt:=sql%rowcount;

commit;

while(l_cnt

loop

insert /*+ append */ into test select rownum+l_cnt,

owner,object_name,subobject_name,

object_id,data_object_id,

object_type,created,last_ddl_time,

timestamp,status,temporary,

generated,secondary

from sales

where rownum<=l_rows-l_cnt;

l_cnt:=l_cnt+sql%rowcount;

commit;

end loop;

end; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

22 /

Enter value for 1: 5000000

old 3: l_rows number:=&1;

new 3: l_rows number:=5000000;

PL/SQL procedure successfully completed.