测试环境准备

 测试环境准备，在日常工作中需要经常创建测试环境，在这里记录下一自己常用的脚步，以便于方便使用

     创建6个表空间、用户、表(200W行)
    
    tbs1    user1    tab11、tab12
    tbs2    user2    tab21、tab22
    tbs3    user3    tab31、tab32
    tbs4    user4    tab41、tab42、ptab43
    ptbs41
    ptbs42
    
    drop user user1 cascade;
    drop user user2 cascade;
    drop user user3 cascade;
    drop user user4 cascade;
    
    drop tablespace tbs1 including contents and datafiles;
    drop tablespace tbs2 including contents and datafiles;
    drop tablespace tbs3 including contents and datafiles;
    drop tablespace tbs4 including contents and datafiles;
    drop tablespace ptbs41 including contents and datafiles;
    drop tablespace ptbs42 including contents and datafiles;
    
    create tablespace tbs1 datafile '/u01/app/oracle/oradata/reapdbimp/tbs101.dbf' size 1G;
    create tablespace tbs2 datafile '/u01/app/oracle/oradata/reapdbimp/tbs201.dbf' size 1G;
    create tablespace tbs3 datafile '/u01/app/oracle/oradata/reapdbimp/tbs301.dbf' size 1G;
    create tablespace tbs4 datafile '/u01/app/oracle/oradata/reapdbimp/tbs401.dbf' size 1G;
    create tablespace ptbs41 datafile '/u01/app/oracle/oradata/reapdbimp/ptbs41.dbf' size 500M;
    create tablespace ptbs42 datafile '/u01/app/oracle/oradata/reapdbimp/ptbs42.dbf' size 500M;
    
    create user user1 identified by oracle default tablespace tbs1;
    create user user2 identified by oracle default tablespace tbs2;
    create user user3 identified by oracle default tablespace tbs3;
    create user user4 identified by oracle default tablespace tbs4;
    
    grant connect,resource to user1;
    grant connect,resource to user2;
    grant connect,resource to user3;
    grant connect,resource to user4;
    
    select privilege from dba_sys_privs where grantee='USER1'  
    union  
    select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='USER1' );

查所有表空间使用量（11g）

    select a.tablespace_name, round(a.tablespace_size * b.block_size / 1024 / 1024 /1024,3) "totalmsize(G)", 
    round(a.used_space * b.block_size / 1024 / 1024  ,2) "usedmsize(m)", 
    round(a.used_percent, 3) "usedrate(%)" 
    from dba_tablespace_usage_metrics a, dba_tablespaces b 
    where a.tablespace_name = b.tablespace_name;
    
    sqlplus / as sysdba
    
    conn user1/oracle
    
    drop table tab11 purge;
    
    create table tab11 (id number CONSTRAINT id_t11 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id));
    
    begin
    for i in 1..2000000 
    loop
      insert into tab11
        (id,name,addr,hostname)
      values
        (i,'nameis'||i,'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
    
    create index itab11name on tab11(name);
    
     create table tab12 (id number CONSTRAINT id_t12 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id));
    
    BEGIN
    for i in 1 .. 2000000 loop
      insert into tab12
        (id, name, addr, hostname)
      values
        (i, 'nameis'||i, 'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
    
    create index itab12name on tab12(name);
    
    
    drop PROCEDURE Protab1
    create or replace PROCEDURE Protab1
           IS
           BEGIN
           DBMS_OUTPUT.PUT_LINE('Hello World!');
           END;
           /
    BEGIN
    Protab1();
    END;
    /
    
    set serveroutput on
    exec Protab1();
    
    
    conn user2/oracle
    create table tab21 (id number CONSTRAINT id_t21 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id));
    
    BEGIN
    for i in 1 .. 2000000 loop
      insert into tab21
        (id, name, addr, hostname)
      values
        (i, 'nameis'||i, 'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
    
    create index itab21name on tab21(name);
    create table tab22 (id number CONSTRAINT id_t22 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id));
    
    BEGIN
    for i in 1 .. 2000000 loop
      insert into tab22
        (id, name, addr, hostname)
      values
        (i, 'nameis'||i, 'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
    
    create index itab22name on tab22(name);
    
    drop PROCEDURE Protab2
    
    create or replace PROCEDURE Protab2
           IS
           BEGIN
           DBMS_OUTPUT.PUT_LINE('Hello World!');
           END;
           /
    BEGIN
    Protab2();
    END;
    /
    
    set serveroutput on
    exec Protab2();
    
    conn user3/oracle
    create table tab31 (id number CONSTRAINT id_t31 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id));
    
    BEGIN
    for i in 1 .. 2000000 loop
      insert into tab31
        (id, name, addr, hostname)
      values
        (i, 'nameis'||i, 'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
     
    create index itab31name on tab31(name);
    
    create table tab32 (id number CONSTRAINT id_t32 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id));
    
    BEGIN
    for i in 1 .. 2000000 loop
      insert into tab32
        (id, name, addr, hostname)
      values
        (i, 'nameis'||i, 'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
    
    create index itab32name on tab32(name);
    
    drop PROCEDURE Protab3
    
    create or replace PROCEDURE Protab3
           IS
           BEGIN
           DBMS_OUTPUT.PUT_LINE('Hello World!');
           END;
           /
    BEGIN
    Protab3();
    END;
    /
    
    set serveroutput on
    exec Protab3();
    
    
    conn user4/oracle
    create table tab41 (id number CONSTRAINT id_t41 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id));
    
    BEGIN
    for i in 1 .. 2000000 loop
      insert into tab41
        (id, name, addr, hostname)
      values
        (i, 'nameis'||i, 'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
    
    create index itab41name on tab41(name);
    
    
    create table tab42 (id number CONSTRAINT id_t42 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id));
    
    BEGIN
    for i in 1 .. 2000000 loop
      insert into tab42
        (id, name, addr, hostname)
      values
        (i, 'nameis'||i, 'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
    
    create index itab42name on tab42(name);
    
    drop table ptab43 purge;
    
    create table ptab43 (id number CONSTRAINT id_tp43 NOT NULL,name VARCHAR2(40),addr VARCHAR2(60),hostname VARCHAR2(90),PRIMARY KEY (id))
    partition by range(id)
    (
    partition p_tbs41 values less than (800000) tablespace ptbs41,
    partition p_tbs42 values less than (1600000) tablespace ptbs42,
    partition p_tbs4 values less than (2000001) tablespace tbs4
    )
    ;
    
    --partition p_tbs4 values less than(maxvalue)  tablespace tbs4   --不建议使用实际测试的时候 所有的数据都到表空间tbs4中了。
    
    BEGIN
    for i in 1 .. 2000000 loop
      insert into ptab43
        (id, name, addr, hostname)
      values
        (i, 'nameis'||i, 'Theaddreis'||i,'Beijing'||i||'code');
    end loop;
    commit;
    END;
    /
    
    ##LOCAL的索引
    create index iptab43name on ptab43(name) local;
    
    ##GLOBAL的索引
    create index iptab43addr on ptab43(addr) global;
    
    drop PROCEDURE Protab4
    
    create or replace PROCEDURE Protab4
           IS
           BEGIN
           DBMS_OUTPUT.PUT_LINE('Hello World!');
           END;
           /
    BEGIN
      Protab4();
    END;
    /
    
    set serveroutput on
    exec Protab4();