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;.