Oracle sample table

Create sample table and loop


declare
i number := 0;
cursor s1 is SELECT * FROM tab1 WHERE col1 = 'value1' FOR UPDATE;

begin

for c1 in s1 loop
update tab1 set col1 = 'value2'
WHERE current of s1;

i := i + 1; -- Commit after every X records X=1000
if i > 1000 then
commit;
i := 0;
end if;

end loop;
commit;
end;
/


-- ------------------------------

CREATE TABLE TEST
(
ID NUMBER(10) NULL,
DATUM DATE NULL,
NAME VARCHAR2(10) NULL
);

declare
i number := 1000;
begin
while i>1 loop
insert into TEST
values (1, sysdate+i,'joop');

i := i - 1;
commit;

end loop;
commit;
end;
/

-- ------------------------------

CREATE TABLE TEST2
(
i number NULL,
ID NUMBER(10) NULL,
DATUM DATE NULL,
DAG VARCHAR2(10) NULL,
NAME VARCHAR2(10) NULL
);

declare
i number := 1;
j date;
k varchar2(10);
begin
while i<1000000 loop
j:=sysdate+i;
k:=TO_CHAR(SYSDATE+i,'DAY');
insert into TEST2
values (i,1, j, k,'joop');

i := i + 1;
commit;

end loop;
commit;
end;
/


-- ------------------------------

CREATE TABLE TEST3
(
ID NUMBER(10) NULL,
DATUM DATE NULL,
DAG VARCHAR2(10) NULL,
VORIG VARCHAR2(10) NULL,
NAME VARCHAR2(10) NULL
);


declare
i number := 1;
j date;
k varchar2(10);
l varchar2(10);
begin
while i<1000 loop
j:=sysdate+i;
k:=TO_CHAR(SYSDATE+i,'DAY');
l:=TO_CHAR(SYSDATE+i-1,'DAY');
insert into TEST3
(ID,DATUM,DAG,VORIG,NAME)
values (i, j, k, l,'joop');

i := i + 1;
commit;

end loop;
commit;
end;
/

from
http://www.akadia.com/services/ora_build_big_tables.html

An «ALL_OBJECTS» Table with 1'000'000 Rows

The following Code is from Tom Kyte (http://asktom.oracle.com)

-- Create Table with same structure as ALL_TABLES from Oracle Dictionary
create table bigtab
as
select rownum id, a.*
from all_objects a
where 1=0;
alter table bigtab nologging;

-- Fill 1'000'000 Rows into the Table
declare
l_cnt number;
l_rows number := 1000000;
begin
-- Copy ALL_OBJECTS
insert /*+ append */
into bigtab
select rownum, a.*
from all_objects a;
l_cnt := sql%rowcount;
commit;

-- Generate Rows
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into bigtab
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 bigtab
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

alter table bigtab add constraint
bigtab_pk primary key(id);

A Table with Random Data and same Size as ALL_OBJECTS

CREATE TABLE bigtab (
id NUMBER,
weight NUMBER,
adate DATE
);

INSERT INTO bigtab (id, weight, adate)
SELECT MOD(ROWNUM,1000),
DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects
/
51502 rows created.

A Table which can be used for Partition Tests

The ID of the table can be used for Range Partitioning

create table bigtab (
id number(12,6),
v1 varchar2(10),
padding varchar2(50)
)
nologging -- just to save a bit of time
/

insert /*+ append ordered full(s1) use_nl(s2) */
into bigtab
select
3000 + trunc((rownum-1)/500,6),
to_char(rownum),
rpad('x',50,'x')
from
all_objects s1, -- you’ll need the privilege
all_objects s2
where
rownum <= 1000000
/
commit;

ID V1 PADDING
---------- ---------- --------------------------------------------------
3000 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.002 2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.004 3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.006 4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.008 5 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.01 6 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.012 7 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.014 8 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3000.016 9 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

A Table with Date's which can be used for Partition Tests

This code is from http://www.oracle-base.com

CREATE TABLE bigtab (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);

DECLARE
l_lookup_id NUMBER(10);
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;

INSERT INTO bigtab (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
END LOOP;
COMMIT;
END;
/

SQL> select id,to_char(created_date,'DD.MM.YYYY'),
lookup_id, data
from bigtab where rownum < 10;

ID TO_CHAR(CR LOOKUP_ID DATA
---------- ---------- ---------- -----------------------------
1 21.08.2007 3 This is some data for 1
2 21.08.2006 1 This is some data for 2
3 21.08.2005 2 This is some data for 3
4 21.08.2006 1 This is some data for 4
5 21.08.2007 3 This is some data for 5
6 21.08.2005 2 This is some data for 6
7 21.08.2007 3 This is some data for 7
8 21.08.2006 1 This is some data for 8
9 21.08.2005 2 This is some data for 9