oracle随机性别,oracle个人技巧 -- 插入随机数据

SQL> INSERT INTO t

2 (keycol, ncol, datacol)

3 SELECT ROWNUM, CASE

4 WHEN DBMS_RANDOM.VALUE (0, 1000) COMMIT ;

Commit complete.

SQL> SELECT DBMS_ROWID.rowid_relative_fno (myrowid) file_no,

2 DBMS_ROWID.rowid_block_number (myrowid) block_no

3 FROM (SELECT sys_op_lbid(1823665, ‘L’, t.ROWID) myrowid

4 FROM t

5 WHERE ncol IS NULL AND ROWNUM

SQL> create table big_bowie (id number, code number, type number, name varchar2(100));

Table created.

SQL> declare

2 i number;

3 begin

4 i:=0;

5 for j in 1..10000 loop

6 for k in 1..100 loop

7 i:=i+1;

8 insert into big_bowie values (i, j, mod(k,4)+1, 'The Rise And Fall Of Ziggy Stardust And The Spiders From Mars');

9 end loop;

10 end loop;

11 commit;

12 end;

13 /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BIG_BOWIE', estimate_percent=>null, method_opt=> 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed

rajesh@10GR2> create table t1

2 nologging

3 as

4 select rownum as id,

5 object_name as data

6 from all_objects

7 order by dbms_random.random;

Table created.

Elapsed: 00:00:07.43

rajesh@10GR2>

rajesh@10GR2>

rajesh@10GR2> alter table t1 add constraint t1_pk primary key(id);

Table altered.

rajesh@ORA11GR2> create table emp

2 partition by list(deptno)

3 (

4 partition p1 values (1),

5 partition p2 values (2),

6 partition p3 values (3),

7 partition p4 values (4),

8 partition p5 values (5),

9 partition pmax values (default)

10 )

11 nologging

12 as

13 select level as empno,

14 dbms_random.string('A',30) as ename,

15 dbms_random.string('B',7) as job,

16 sysdate - mod(level,100) as hire_date,

17 dbms_random.value(1,1000) as comm,

18 dbms_random.value(1,10000) as salary,

19 mod(level,5)+1 as deptno

20 from dual

21 connect by level <= 1000000;

rajesh@ORA11GR2> create table dept

2 as

3 select level as deptno,

4 dbms_random.string('C',10) as dname,

5 dbms_random.string('E',7) as loc

6 from dual

7 connect by level <= 5;.