快速创建1000用户,每用户1000表,1000索引,1000个trigger

联系:QQ(5163721)

标题:快速创建1000用户,每用户1000表,1000索引,1000个trigger

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

有时候为了方便测试,我们需要制造一些复杂的数据,比如这里,我们快速的创建1000用户,每用户1000表,1000索引,1000个trigger

-- create tablespace
create tablespace lunartbs datafile '/u01/oradata/lunar/lunartbs01.dbf' size 30m autoextend on;

-- create directory
create directory dpu as '/test';

declare
stmt varchar2(1000);
begin
	for i in 1..&&1 loop
			-- create user
			stmt := 'create user lunar'||lpad (to_char (i), 4, '0')||' identified by lunar default tablespace lunar temporary tablespace temp';
			execute immediate stmt;
			
			-- grant user
			stmt := 'grant connect, resource,dba to lunar'||lpad (to_char (i), 4, '0');
			execute immediate stmt;
			
			-- for each user create 1000 tables
			for j in 1..1000 loop
					stmt := 'create table lunar'||lpad (to_char (i), 4, '0')||'.tab'||lpad(to_char (j), 3, '0')||' (id number, text varchar2(10))';
					execute immediate stmt;
			end loop;
			
			-- 1000 indexes
			for j in 1..1000 loop
					stmt := 'create index lunar'||lpad (to_char (i), 4, '0')||'.ind'||lpad(to_char (j), 3, '0')||' on lunar'||lpad (to_char (i), 4, '0')||'.tab'||lpad(to_char (j), 3, '0')||' (id)';
					execute immediate stmt;
			end loop;
			
			-- and 1000 triggers
			for j in 1..1000 loop
					stmt := 'create trigger lunar'||lpad (to_char (i), 4,'0')||'.trg'||lpad (to_char (j), 3, '0')||' before insert on lunar'||lpad(to_char (i), 4, '0')||'.tab'||lpad (to_char (j), 3, '0')||' for each row begin null; end;';
					execute immediate stmt;
			end loop;
			
	end loop;
end;
/

此条目发表在 POC和性能调整, Scripts 分类目录,贴了 , 标签。将固定链接加入收藏夹。

快速创建1000用户,每用户1000表,1000索引,1000个trigger》有 1 条评论

  1. zhengsy1203 说:

    女神。要1000个表、索引、触发器lpad(to_char (j), 3, ’0′)这个会报错,截取位数需要4位哦!

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>