1.创建业务用户表空间

假设使用了OMF管理，不需要明确指定数据目录(判定是否使用了OMF技术，查看db_create_file_dest参数配置：show parameter db_create_file_dest)

-- 数据表空间
create tablespace dbs_d_jingyu datafile size 30M autoextend off;
-- 临时表空间
create temporary tablespace temp_jingyu tempfile size 30M autoextend off;
-- 索引表空间(可选)
create tablespace dbs_i_jingyu datafile size 30M autoextend off;

假设文件系统管理，且未使用OMF管理，规划的数据目录是/oradata1

-- 数据表空间
create tablespace dbs_d_jingyu datafile '/oradata1/datafiles/dbs_d_jingyu01.dbf' size 30M autoextend off;
-- 临时表空间
create temporary tablespace temp_jingyu tempfile '/oradata1/tempfiles/temp_jingyu01.tmp' size 30M autoextend off;
-- 索引表空间(可选)
create tablespace dbs_i_jingyu datafile '/oradata1/datafiles/dbs_i_jingyu01.dbf' size 30M autoextend off;

假设ASM磁盘组，指定磁盘组是+DATA，具体路径OMF管理

-- 数据表空间
create tablespace dbs_d_jingyu datafile '+DATA' size 30M autoextend off;
-- 临时表空间
create temporary tablespace temp_jingyu tempfile '+DATA' size 30M autoextend off;
-- 索引表空间(可选)
create tablespace dbs_i_jingyu datafile '+DATA' size 30M autoextend off;

2.创建业务用户

-- 假设创建用户 jingyu 密码 jingyu，默认临时表空间 temp_jingyu， 默认数据表空间 dbs_d_jingyu。
CREATE USER jingyu IDENTIFIED BY jingyu
  TEMPORARY TABLESPACE temp_jingyu
  DEFAULT TABLESPACE dbs_d_jingyu
  QUOTA UNLIMITED ON dbs_d_jingyu;

3.赋予用户权限

-- 赋予普通业务用户权限
grant resource, connect to jingyu;
-- 赋予DBA用户权限
grant dba to jingyu;

4.创建业务表

新建业务用户登录，创建T1,T2两张业务表，并插入测试数据。

-- 业务用户登录
conn jingyu/jingyu
-- 删除T1,T2两张表
drop table t1 cascade constraints purge;
drop table t2 cascade constraints purge;
-- 创建T1,T2两张表
create table t1( id number not null, n number, contents varchar2(4000) ) tablespace dbs_d_jingyu;
create table t2( id number not null, t1_id number not null, n number, contents varchar2(4000) ) tablespace dbs_d_jingyu;
-- 初始化向T1,T2表插入随机测试数据
execute dbms_random.seed(0);
set timing on
insert into t1  select rownum, rownum, dbms_random.string('a',50)   from dual   connect by level <= 100   order by dbms_random.random;
commit;  
insert into t2  select rownum, rownum, rownum, dbms_random.string('b',50)  from dual  connect by level <= 100000  order by dbms_random.random;
commit;
-- 查询T1,T2表数据量
select count(1) from t1;
select count(1) from t2;

5.创建索引

-- 创建T1表字段n的索引idx_t1_n
create index idx_t1_n on t1(n) tablespace dbs_i_jingyu;
-- 创建T2表字段id的索引idx_t2_t1id
create index idx_t2_t1id on t2(t1_id) tablespace dbs_i_jingyu;

6.业务查询SQL

-- 业务查询SQL 1
select * from t1, t2 where t1.id = t2.t1_id and t1.n = 19;
-- 业务查询SQL 2
select * from t1, t2 where t1.id = t2.t1_id;

7.删除业务用户及数据

-- 删除业务用户jingyu
drop user jingyu cascade;

8.删除业务表空间

-- 删除数据表空间及其文件
drop tablespace dbs_d_jingyu including contents and datafiles;
-- 删除索引表空间及其文件
drop tablespace dbs_i_jingyu including contents and datafiles;
-- 删除临时表空间及其文件
drop tablespace temp_jingyu including contents and datafiles;

Oracle数据库对很多开发人员而言，基本就只是一个存储数据的仓库，只不过这个仓库功能非常强大，额外提供了很多好用的功能，需要的时候会用就好，不会纠结于某个细节。而对很多DBA而言，正好相反，喜欢沉溺于某些细节上，对某些小知识点对应的原理理解的非常透彻，但却往往很少会站在开发层面认识Oracle。
本文旨在构造一份相对较全面的测试数据，对开发常用的对象都模拟创建一份测试用例，DBA就可以直接拿去做一些基本测试。

环境：Oracle 11.2.0.4

1.初始化测试数据

初始化测试数据的脚本主要包含了如下内容：

1.表空间创建
2.用户创建及赋权
3.表创建
4.索引创建
5.视图、同义词、序列、dblink
6.存储过程、函数、包、定时任务、触发器

脚本：initData.sh

#!/bin/bash
#name：initData.sh
#function：to initial data for test.
#usage: oracle用户登录，执行 sh initData.sh > /tmp/initData.log

#logon database
sqlplus -S / as sysdba <<EOF


prompt ============================
prompt ==  summary         
prompt ============================
prompt
prompt 1.表空间创建 
prompt 2.用户创建及赋权
prompt 3.表创建
prompt 4.索引创建
prompt 5.视图、同义词、序列、dblink
prompt 6.存储过程、函数、包、定时任务、触发器
prompt

prompt ============================
prompt == 1.表空间创建 
prompt ============================
prompt Tablespace：DBS_D_JINGYU
prompt Tablespace：DBS_I_JINGYU
prompt TEMPORARY Tablespace：TEMP_JINGYU
prompt 
-- 数据表空间
create tablespace dbs_d_jingyu datafile size 30M autoextend on maxsize 500M;
-- 临时表空间
create temporary tablespace temp_jingyu tempfile size 30M autoextend on maxsize 500M;
-- 索引表空间(可选)
create tablespace dbs_i_jingyu datafile size 30M autoextend on maxsize 500M;


prompt ============================
prompt == 2.用户创建及赋权
prompt ============================
prompt User：jingyu 权限：connect, resource
prompt User：ludan  权限：connect, resource, dba
prompt
-- 假设创建用户 jingyu 密码 jingyu，默认临时表空间 temp_jingyu， 默认数据表空间 dbs_d_jingyu。
CREATE USER jingyu IDENTIFIED BY jingyu
  TEMPORARY TABLESPACE temp_jingyu
  DEFAULT TABLESPACE dbs_d_jingyu
  QUOTA UNLIMITED ON dbs_d_jingyu;

-- 假设创建用户 ludan 密码 ludan，默认临时表空间temp_jingyu， 默认数据表空间 dbs_d_jingyu。
CREATE USER ludan IDENTIFIED BY ludan
  TEMPORARY TABLESPACE temp_jingyu
  DEFAULT TABLESPACE dbs_d_jingyu
  QUOTA UNLIMITED ON dbs_d_jingyu;

-- 赋权
grant resource, connect to jingyu;
grant resource, connect, dba to ludan;


prompt ============================
prompt == 3.表创建
prompt ============================
prompt 

prompt 3.1 普通堆表
prompt ============================
prompt
--3.1 普通堆表
--prepare 
conn /as sysdba
alter user scott identified by tiger account unlock;
conn scott/tiger
grant select on emp to jingyu;
grant select on dept to jingyu;

-- 业务用户登录
conn jingyu/jingyu
-- 删除 emp
--drop table emp cascade constraints purge;
--drop table dept cascade constraints purge;
-- 创建emp
create table emp tablespace dbs_d_jingyu as select * from scott.emp;
create table dept tablespace dbs_d_jingyu as select * from scott.dept;
-- 查询T1,T2表数据量
select count(1) from emp;
select count(1) from dept;
-- 创建后面JOB用到的表
create table t_times(col1 date);
create table t_sum(col1 date, col2 number);
create table t_times_old as select * from t_times where 1=2;

-- 业务用户登录
conn ludan/ludan
-- 创建 t_obj
create table t_obj as select * from dba_objects;
-- 查询 数据量
select count(1) from t_obj;

prompt 3.2 分区表
prompt ============================
prompt
--3.2 分区表
conn ludan/ludan
create table t_obj_part
partition by range(CREATED)
(
  partition P2015 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace dbs_d_jingyu,
  partition P2016 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace dbs_d_jingyu,
  partition P2017 values less than (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace dbs_d_jingyu,
  partition P2018 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace dbs_d_jingyu
) as select * from t_obj;

select count(1) from t_obj_part partition(P2018);

prompt 3.3 全局临时表
prompt ============================
prompt
--3.3 全局临时表
conn ludan/ludan
create global temporary table t_tmp_session on commit preserve rows as select * from dba_objects where 1 = 2;
create global temporary table t_tmp_transaction on commit delete rows as select * from dba_objects where 1 = 2;

prompt 3.4 索引组织表
prompt ============================
prompt
--3.4 索引组织表
conn jingyu/jingyu
--select dbms_metadata.get_ddl('TABLE',upper('emp'),'JINGYU') from dual;

CREATE TABLE emp_iot
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
        primary key(empno)
   )organization index;

insert into emp_iot select * from emp;
commit;

--select * from jingyu.emp where empno = 7788;
--select * from jingyu.emp_iot where empno = 7788;

--select * from jingyu.emp where ename = 'SCOTT';
--select * from jingyu.emp_iot where ename = 'SCOTT';

prompt 3.5 簇表
prompt ============================
prompt
--3.5 簇表
conn jingyu/jingyu

create cluster shc
(cust_id number,
order_dt timestamp SORT)
hashkeys 10000
hash is cust_id
size 8192;

create table cust_orders(
cust_id number,
order_dt timestamp SORT,
order_number number,
username varchar2(30),
ship_addr number,
bill_addr number,
invoice_num number
)
cluster shc(cust_id, order_dt);

--select * from dba_objects where object_name = 'SHC';

prompt 3.6 外部表
prompt ============================
prompt
--3.6 外部表
conn /as sysdba
!mkdir -p /home/oracle/external_table
create or replace directory external_table as '/home/oracle/external_table';
grant read,write on directory external_table to jingyu;

conn jingyu/jingyu
create table ext_emp (ename,job,sal,dname)
organization external
(type oracle_datapump default directory external_table location('ext_emp'))
as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;


prompt ============================
prompt == 4.索引创建
prompt ============================
prompt 主键、外键、唯一索引、普通索引、位图索引、函数索引
prompt

prompt 4.1 普通B-Tree索引
prompt ============================
prompt
--4.1 普通B-Tree索引
--ludan.idx_t_obj_name
conn ludan/ludan
create index idx_t_obj_name on t_obj(object_name) tablespace dbs_i_jingyu;
--jingyu.empno
conn jingyu/jingyu
create index idx_emp_1 on emp(empno, ename) tablespace dbs_i_jingyu;
create index idx_emp_2 on emp(mgr) tablespace dbs_i_jingyu;

prompt 4.2 唯一索引
prompt ============================
prompt
--4.2 唯一索引
conn ludan/ludan
create unique index ux_t_obj_id on t_obj(object_id) tablespace dbs_i_jingyu;

prompt 4.3 位图索引
prompt ============================
prompt
--4.3 位图索引
conn jingyu/jingyu
create bitmap index bx_emp_job on emp(job) tablespace dbs_i_jingyu;

prompt 4.4 函数索引
prompt ============================
prompt
--4.4 函数索引
conn jingyu/jingyu
create index fx_emp_hiredate on emp(to_char(hiredate,'yyyy-mm-dd')) tablespace dbs_i_jingyu;
--select * from jingyu.emp where to_char(hiredate,'yyyy-mm-dd') = '1981-11-17';

prompt 4.5 主键、外键
prompt ============================
prompt
--4.5 主键、外键
conn jingyu/jingyu
alter table emp add constraint pk_emp_empno primary key(empno);
alter table dept add constraint pk_dept_deptno primary key(deptno);
alter table emp add constraint fk_emp_references_dept foreign key(deptno) references dept(deptno);


prompt ============================
prompt == 5.视图、同义词、序列、dblink
prompt ============================
prompt 视图、同义词、序列、dblink
prompt

prompt 5.1 视图
prompt ============================
prompt
--5.1 视图
conn /as sysdba
grant create view to jingyu;

conn jingyu/jingyu
create view emp_high_sal as select * from emp where sal > 3000;

prompt 5.2 同义词
prompt ============================
prompt
--5.2 同义词
conn ludan/ludan
create synonym s_emp for jingyu.emp;
create synonym s_dept for jingyu.dept;

--public synonym
create public synonym pubic_emp for jingyu.emp;
create public synonym pubic_dept for jingyu.dept;

prompt 5.3 序列
prompt ============================
prompt
--5.3 序列
conn jingyu/jingyu

--drop sequence seq_1;
create sequence seq_1 start with 1 increment by 1;

--drop table t_test_seq purge;
create table t_test_seq(id number, name varchar2(20));
insert into t_test_seq values(seq_1.nextval, 'jingyu');
insert into t_test_seq values(seq_1.nextval, 'jingyu');
insert into t_test_seq values(seq_1.nextval, 'jingyu');
commit;

prompt 5.4 dblink
prompt ============================
prompt
--5.4 dblink
--tnsnames.ora
--JYZHAO =
--  (DESCRIPTION =
--    (ADDRESS = (PROTOCOL = TCP)(HOST = jyrac-scan)(PORT = 1521))
--    (CONNECT_DATA =
--      (SERVER = DEDICATED)
--      (SERVICE_NAME = jyzhao)
--    )
--  )

--connect user
conn ludan/ludan
--private dblink
create database link to_jyzhao_jy connect to jingyu identified by jingyu using 'JYZHAO';
--public dblink
create public database link to_jyzhao_ld connect to ludan identified by ludan using 'JYZHAO';

prompt ============================
prompt == 6.存储过程、函数、包、定时任务、触发器
prompt ============================
prompt 存储过程、函数、包、定时任务
prompt

prompt 6.1 存储过程：
prompt ============================
prompt
--6.1 存储过程：
--功能：可以输入雇员名，新工资，可以修改雇员的工资。
conn jingyu/jingyu
create or replace procedure p_update_sal(v_ename varchar2,v_newsal number) is
    begin
        update emp set sal=v_newsal where ename=v_ename;
        commit;
    end;
/

prompt 6.2 函数：
prompt ============================
prompt
--6.2 函数：
--功能：输入雇员的姓名，返回该雇员的年薪。
conn jingyu/jingyu
create function f_yearSal(empName varchar2) return number is
    yearSal number(7,2);
    begin
        select sal * 12 + nvl(comm,0) * 12 into yearSal from emp where ename=empName;
        return yearSal;
    end;
/

--SQL> 调用函数
--var income number;
--call f_yearSal('SCOTT') into:income;
--set serveroutput on
--exec dbms_output.put_line(:income);

prompt 6.3 包：
prompt ============================
prompt
--6.3 包
--package
create package pkg_emp is
    procedure p_update_sal(v_ename varchar2,v_newsal number);
    function f_yearSal(empName varchar2) return number;
end;
/

--package body
create or replace package body pkg_emp is
    procedure p_update_sal(v_ename varchar2,v_newsal number) is
        begin
            update emp set sal=v_newsal where ename=v_ename;
            commit;
        end;
    function f_yearSal(empName varchar2) return number is
        yearSal number(7,2);
        begin
            select sal * 12 + nvl(comm,0) * 12 into yearSal from emp where ename=empName;
            return yearSal;
        end;
    end;
/


--调用包中的过程：
--call pkg_emp.p_update_sal('SMITH',133);
--调用包中的函数：
--var abc number
--call pkg_emp.f_yearSal('SMITH') into:abc;

prompt 6.4 JOB：
prompt ============================
prompt
--6.4 JOB
--select OWNER, JOB_NAME, JOB_STYLE, JOB_TYPE, STATE from DBA_SCHEDULER_JOBS;
--select JOB_NAME, JOB_STYLE, JOB_TYPE, STATE from USER_SCHEDULER_JOBS;
conn ludan/ludan

--作业功能：每秒向T_TIMES表插入当前系统时间，运行1w次后终止。
BEGIN
sys.dbms_scheduler.create_job( 
job_name => '"JINGYU"."J_INSERT"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
  -- Insert PL/SQL code here
  insert into t_times values(sysdate);
  commit;
end;',
repeat_interval => 'FREQ=SECONDLY',
start_date => to_timestamp_tz('2015-12-09 05:05:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Insert the current date into the T_TIMES table.',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"JINGYU"."J_INSERT"', attribute => 'max_runs', value => 10000); 
END;
/

--ENABLE(启动作业)
exec dbms_scheduler.enable('"JINGYU"."J_INSERT"');
--DISABLE(禁用作业)
--exec dbms_scheduler.disable('"JINGYU"."J_INSERT"');
--DROP_JOB（删除作业）
--exec dbms_scheduler.drop_job('"J_INSERT"');



prompt 6.5 触发器：
prompt ============================
prompt
--6.5 Trigger
conn jingyu/jingyu
--功能：只要有更新emp表的操作，就会记录sal字段的变化。
--drop table aud_salary_history;
create table aud_salary_history(
empno NUMBER(4),
old_sal NUMBER(7,2),
new_sal NUMBER(7,2),
change_time varchar2(50)
);

create or replace trigger tri_sal after update
on emp
    for each row
begin
    insert into aud_salary_history VALUES
  (:OLD.empno, :OLD.sal, :NEW.sal, TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
end;
/

--update emp set sal = 1000 where empno=7369;
--select * from aud_salary_history;
--rollback;

prompt ============================
prompt ===initData Completed!!!====
prompt ============================

EOF

2.清空测试数据

清空测试数据的脚本主要包含如下内容：

1.删除用户
2.删除表空间
3.删除public对象

脚本：delData.sh

#!/bin/bash
#name：delData.sh
#function：to delete data for test.
#usage: oracle用户登录，执行 sh delData.sh > /tmp/delData.log

#logon database
sqlplus -S / as sysdba <<EOF

prompt ============================
prompt == 1.Drop User
prompt ============================
prompt 
--drop user 
--select s.username, s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr and s.username = 'JINGYU';
select s.username, s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr and s.username is not null;
drop user ludan cascade;
drop user jingyu cascade;

prompt ============================
prompt == 2.Drop Tablespace
prompt ============================
prompt 
--drop tablespace
drop tablespace dbs_d_jingyu including contents and datafiles;
drop tablespace dbs_i_jingyu including contents and datafiles;
drop tablespace temp_jingyu including contents and datafiles;

prompt ============================
prompt == 3.Drop Public Objects
prompt ============================
prompt 
--public dblink
select dbms_metadata.get_ddl('DB_LINK',DB_LINK,'PUBLIC') FROM DBA_DB_LINKS where owner='PUBLIC';
drop public database link TO_JYZHAO_LD;
select dbms_metadata.get_ddl('DB_LINK',DB_LINK,'PUBLIC') FROM DBA_DB_LINKS where owner='PUBLIC';

--public synonym
SELECT DBMS_METADATA.GET_DDL('SYNONYM',a.SYNONYM_NAME,a.owner) FROM DBA_SYNONYMS a where a.owner ='PUBLIC' and table_owner in ('JINGYU','LUDAN');
drop public synonym PUBIC_DEPT;
drop public synonym PUBIC_EMP;
SELECT DBMS_METADATA.GET_DDL('SYNONYM',a.SYNONYM_NAME,a.owner) FROM DBA_SYNONYMS a where a.owner ='PUBLIC' and table_owner in ('JINGYU','LUDAN');

--external tables
select * from dba_external_tables;
--外部表会随用户被删除而删除,但外部表真实的文件还在，需要删除掉（rm命令危险，故注释掉手工执行）：
--!rm -rf /home/oracle/external_table

prompt ============================
prompt ===delData Completed!!!====
prompt ============================

EOF