Thursday, February 6, 2014

TOAD USER SCRIPT

SCRIPT TO CREATE TOAD USER :

I really had to struggle a lot to get this script.Creating these objects as toad user will allow u to gathers stats for disk ,data file and table spaces growth and trending.

This scipt need to be run as toad user to create all the tables & ddl needed for toad user and space management graphs.



REM This script was created by version 10.6.1.3 of the TOAD Server Side Objects Wizard
DEFINE OWNER = 'TOAD'

Prompt ============================================================================
Prompt Creating the TOAD User
Prompt ============================================================================
Prompt Creating the TOAD User
CREATE USER TOAD IDENTIFIED BY toad1234
DEFAULT TABLESPACE TOAD
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON TOAD;

Prompt Granting System Privileges to TOAD
Grant UNLIMITED TABLESPACE to TOAD;
Grant ALTER SESSION to TOAD;
Grant CREATE SEQUENCE to TOAD;
Grant CREATE SESSION to TOAD;
Grant CREATE SYNONYM to TOAD;
Grant CREATE TRIGGER to TOAD;
Grant CREATE PUBLIC SYNONYM to TOAD;
Grant CREATE TABLE to TOAD;
Grant CREATE VIEW to TOAD;
Grant CREATE PROCEDURE to TOAD;



Prompt ============================================================================
Prompt Granting CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM
Prompt ============================================================================
Prompt Granting CREATE PUBLIC SYNONYM to TOAD
GRANT CREATE PUBLIC SYNONYM TO TOAD;

Prompt Granting DROP PUBLIC SYNONYM to TOAD
GRANT DROP PUBLIC SYNONYM TO TOAD;


Prompt ============================================================================
Prompt Adding necessary grants to TOAD
Prompt ============================================================================
Prompt Granting SELECT ANY DICTIONARY to TOAD
GRANT SELECT ANY DICTIONARY TO &&OWNER;


Prompt ============================================================================
Prompt Granting EXECUTE ANY PROCEDURE to %s (Used for the Profiler)
Prompt ============================================================================
Prompt Granting EXECUTE ANY PROCEDURE to TOAD (Used for the Profiler)
GRANT EXECUTE ANY PROCEDURE TO TOAD;


Prompt ============================================================================
Prompt Granting CREATE ROLE
Prompt ============================================================================
Prompt Granting CREATE ROLE to TOAD (Used for Team Coding)
GRANT CREATE ROLE TO TOAD;


Prompt ============================================================================
Prompt Granting CREATE VIEW
Prompt ============================================================================
Prompt Granting CREATE VIEW to TOAD
GRANT CREATE VIEW TO TOAD;


Prompt ============================================================================
Prompt Connecting as TOAD
Prompt ============================================================================
Prompt Connecting as TOAD
CONNECT TOAD/toad1234@QPDEV_BV
Prompt 


Prompt ============================================================================
Prompt Creating TOAD Profiler Objects in TOAD schema
Prompt ============================================================================
DEFINE tablespace_info = ' TABLESPACE TOAD'
DEFINE ALLT = DBA_TRIGGERS
DEFINE ALLS = DBA_SOURCE

Prompt Creating table PLSQL_PROFILER_RUNS
CREATE TABLE &&OWNER..plsql_profiler_runs
(
  runid           NUMBER primary key, -- unique run identifier, from plsql_profiler_runnumber
  related_run     NUMBER,             -- runid of related run (for client-server correlation)
  run_owner       VARCHAR2(32),       -- user that executed the procedure
  run_proc        VARCHAR2(256),      -- procedure that was executed
  run_date        DATE,               -- start time of run
  run_comment     VARCHAR2(2047),     -- user provided comment for this run
  run_total_time  NUMBER,             -- elapsed time for this run
  run_system_info VARCHAR2(2047),     -- currently unused
  run_comment1    VARCHAR2(256),      -- additional comment
  spare1          VARCHAR2(256)       -- unused
)
&&tablespace_info;

Prompt Adding comment to PLSQL_PROFILER_RUNS
COMMENT ON TABLE &&OWNER..plsql_profiler_runs IS
        'Run-specific information for the PL/SQL profiler';
       
Prompt Creating table PLSQL_PROFILER_UNITS
CREATE TABLE &&OWNER..plsql_profiler_units
(
  runid              NUMBER references &&OWNER..plsql_profiler_runs ON DELETE cascade,
  unit_number        NUMBER,             -- internally generated library unit #
  unit_type          VARCHAR2(32),       -- library unit type
  unit_owner         VARCHAR2(32),       -- library unit owner name
  unit_name          VARCHAR2(32),       -- library unit name
  unit_timestamp     DATE,
    -- timestamp on library unit, can be used to detect changes to unit between runs
  total_time         NUMBER DEFAULT 0 NOT NULL,
  spare1             NUMBER,             -- unused
  spare2             NUMBER,             -- unused
  primary key (runid, unit_number)
)
&&tablespace_info;

Prompt Adding comment to PLSQL_PROFILER_UNITS
COMMENT ON TABLE &&OWNER..plsql_profiler_units IS
        'Information about each library unit in a run';
       
Prompt Creating table PLSQL_PROFILER_DATA
CREATE TABLE &&OWNER..plsql_profiler_data
(
  runid           NUMBER,           -- unique (generated) run identifier
  unit_number     NUMBER,           -- internally generated library unit #
  line#           NUMBER NOT NULL,  -- line number in unit
  text            VARCHAR2(4000),   -- source for the line
  total_occur     NUMBER,           -- number of times line was executed
  total_time      NUMBER,           -- total time spent executing line
  min_time        NUMBER,           -- minimum execution time for this line
  max_time        NUMBER,           -- maximum execution time for this line
  spare1          NUMBER,           -- unused
  spare2          NUMBER,           -- unused
  spare3          NUMBER,           -- unused
  spare4          NUMBER,           -- unused
  primary key (runid, unit_number, line#),
  foreign key (runid, unit_number) references &&OWNER..plsql_profiler_units ON DELETE CASCADE)
&&tablespace_info;

Prompt Adding comment to PLSQL_PROFILER_DATA
COMMENT ON TABLE &&OWNER..plsql_profiler_data IS
        'Accumulated data from all profiler runs';
Prompt Creating package spec TOAD_PROFILER
CREATE OR REPLACE PACKAGE &&OWNER..toad_profiler is
  procedure rollup_unit(run_number IN number, UnitNumber IN number,
    UnitType IN varchar2, UnitOwner IN varchar2, UnitName IN varchar2);
  procedure rollup_run(run_number IN number);
  procedure rollup_all_runs;
end toad_profiler;
/
Prompt Creating package body TOAD_PROFILER
CREATE OR REPLACE PACKAGE BODY &&OWNER..toad_profiler is
  -- compute the total time spent executing this unit - the sum of the
  -- time spent executing lines in this unit (for this run)
  procedure rollup_unit(run_number IN number, UnitNumber IN number,
    UnitType IN varchar2, UnitOwner IN varchar2, UnitName IN varchar2) is
  TYPE TSourceTable IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
  SourceTable TSourceTable;
  TriggerBody long;
  FoundTriggerSource boolean;
  Cnt number;
  LnStart number;
  LnEnd   number;
  Pos number;
  vText varchar2(4000);
  IsWrapped boolean;
  TotalTime number;
  -- Select the lines for the unit to find source code
  cursor cLines(run_number number, UnitNumber number) is
    select line# from plsql_profiler_data
    where runid = run_number and unit_number = UnitNumber;
  begin
    select sum(total_time) into TotalTime
      from plsql_profiler_data
      where runid = run_number and unit_number = UnitNumber;
    if TotalTime IS NULL then
      TotalTime := 0;
    end if;
    update plsql_profiler_units set total_time = TotalTime
    where runid = run_number and unit_number = UnitNumber;
    -- Get trigger source into index-by table
    if UnitType = 'TRIGGER' then
      begin
        FoundTriggerSource := True;
        select trigger_body into TriggerBody
          from dba_triggers where owner = UnitOwner and trigger_name = UnitName;
      exception
        when NO_DATA_FOUND then
          FoundTriggerSource := False;
      end;
      if FoundTriggerSource then
        Cnt     := 1;
        LnStart := 1;
        loop
          LnEnd := INSTR(TriggerBody, CHR(10), 1, Cnt);
          if (LnEnd = 0) then
            SourceTable(Cnt) := SubStr(TriggerBody, LnStart);
          else
            SourceTable(Cnt) := Substr(TriggerBody, LnStart, (LnEnd-LnStart));
          end if;
          LnStart := LnStart + (LnEnd-LnStart)+1;
          Cnt := Cnt+1;
          exit when (lnEnd = 0);
        end loop;
      end if;
    -- see if the code is wrapped
    else
      begin
        select upper(text) into vtext from dba_source s
          where s.type = UnitType and s.owner = UnitOwner and
                s.name = UnitName and s.line = 1;
        IsWrapped := (INSTR(vText, ' WRAPPED') > 0);
      exception
        when NO_DATA_FOUND then
          IsWrapped := False;
      end;
    end if;

    -- Get the source for each line in unit
    Cnt := 1;
    for linerec in cLines(run_number, UnitNumber) loop
      if UnitType = 'TRIGGER' then
        if FoundTriggerSource then
          vText := SourceTable(linerec.line#);
        else
          if Cnt = 1 then
            vText := '<source unavailable>';
          else
            vText := null;
          end if;
        end if;
      else
        if IsWrapped then
          if Cnt = 1 then
            vText := '<wrapped>';
          else
            vText := null;
          end if;
        else
          begin
            select text into vtext from dba_source s
              where s.type = UnitType and s.owner = UnitOwner and
                    s.name = UnitName and s.line = linerec.line#;
          exception
            when NO_DATA_FOUND then
              vText := null;
          end;
        end if;
      end if;
      -- store the source line
      update plsql_profiler_data d set d.text = vText
      where d.runid = run_number and d.unit_number = UnitNumber and
            d.line# = linerec.line#;
      Cnt := Cnt+1;
    end loop;
  end rollup_unit;
  -- rollup all units for the given run
  procedure rollup_run(run_number IN number) is
    tabpos number;
    comment varchar2(2047);
    proc varchar2(256 );
    --
    -- only select those units which have not been rolled up yet
    cursor cunits(run_number number) is
      select unit_number, unit_type, unit_owner, unit_name
        from plsql_profiler_units
        where runid = run_number and total_time = 0
        order by unit_number asc;
  begin
    -- Fix Oracle's calling a 'PACKAGE' a 'PACKAGE SPEC'
    update plsql_profiler_units set unit_type = 'PACKAGE'
    where runid = run_number and unit_type like 'PACKAGE SPEC%';
    -- parse the RUN_COMMENT column to get the procedure name
      -- (note: this replaces the BI_PLSQL_PROFILER_RUNS trigger.
    select run_proc, run_comment into proc, comment
      from plsql_profiler_runs where runid = run_number;
    if proc is null then
      tabpos := INSTR(comment, CHR(8));
        if tabpos > 0 THEN
          proc := SUBSTR(comment, tabpos+1);
          comment := SUBSTR(comment, 1, tabpos-1);
        else
          proc := 'ANONYMOUS BLOCK';
        end if;
        update plsql_profiler_runs
          set run_owner = USER, run_proc = proc, run_comment = comment
          where runid = run_number;
    end if;
    for unitrec in cunits(run_number) loop
      rollup_unit(run_number, unitrec.unit_number, unitrec.unit_type,
                  unitrec.unit_owner, unitrec.unit_name);
    end loop;
  end rollup_run;
  -- rollup all runs
  procedure rollup_all_runs is
    cursor crunid is
      select runid from plsql_profiler_runs order by runid asc;
  begin
    for runidrec in crunid loop
      rollup_run(runidrec.runid);
    end loop crunid;
    commit;
  end rollup_all_runs;
end toad_profiler;
/
Prompt Creating sequence PLSQL_PROFILER_RUNNUMBER
CREATE SEQUENCE &&OWNER..plsql_profiler_runnumber START WITH 1 NOCACHE;


Prompt ============================================================================
Prompt Adding public synonyms for Profiler objects
Prompt ============================================================================

Prompt Creating public synonym PLSQL_PROFILER_DATA
CREATE OR REPLACE PUBLIC SYNONYM PLSQL_PROFILER_DATA FOR &&OWNER..PLSQL_PROFILER_DATA;

Prompt Creating public synonym PLSQL_PROFILER_UNITS
CREATE OR REPLACE PUBLIC SYNONYM PLSQL_PROFILER_UNITS FOR &&OWNER..PLSQL_PROFILER_UNITS;

Prompt Creating public synonym PLSQL_PROFILER_RUNS
CREATE OR REPLACE PUBLIC SYNONYM PLSQL_PROFILER_RUNS FOR &&OWNER..PLSQL_PROFILER_RUNS;

Prompt Creating public synonym PLSQL_PROFILER_RUNNUMBER
CREATE OR REPLACE PUBLIC SYNONYM PLSQL_PROFILER_RUNNUMBER FOR &&OWNER..PLSQL_PROFILER_RUNNUMBER;

Prompt Creating public synonym TOAD_PROFILER
CREATE OR REPLACE PUBLIC SYNONYM TOAD_PROFILER FOR &&OWNER..TOAD_PROFILER;


Prompt ============================================================================
Prompt Granting privileges to PUBLIC on Profiler objects
Prompt ============================================================================
Prompt Granting SELECT, INSERT, UPDATE, DELETE on PLSQL_PROFILER_DATA to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..PLSQL_PROFILER_DATA TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on PLSQL_PROFILER_UNITS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..PLSQL_PROFILER_UNITS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on PLSQL_PROFILER_RUNS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..PLSQL_PROFILER_RUNS TO PUBLIC;


Prompt Granting SELECT on PLSQL_PROFILER_RUNNUMBER to PUBLIC
GRANT SELECT ON &&OWNER..PLSQL_PROFILER_RUNNUMBER TO PUBLIC;


Prompt Granting EXECUTE on TOAD_PROFILER to PUBLIC
GRANT EXECUTE ON &&OWNER..TOAD_PROFILER TO PUBLIC;




Prompt ============================================================================
Prompt Creating TOAD Security table
Prompt ============================================================================
Prompt Creating table TOAD.TOAD_RESTRICTIONS
CREATE TABLE TOAD.TOAD_RESTRICTIONS (
  USER_NAME  VARCHAR2(32)  NOT NULL,
  FEATURE    VARCHAR2(20)  NOT NULL,
  CONSTRAINT TOAD_RES_PK
  PRIMARY KEY ( FEATURE, USER_NAME ) )
 TABLESPACE TOAD;



Prompt ============================================================================
Prompt Creating/Upgrading Team Coding objects
Prompt ============================================================================
DEFINE tablespace_info = ' TABLESPACE TOAD'
DEFINE tablespace_ind_info = ' TABLESPACE TOAD'
DEFINE TC_ADMIN = TC_ADMIN_ROLE
DEFINE TC_MGR = TC_MGR_ROLE
DEFINE TC_LDR = TC_LDR_ROLE
Prompt Creating role TC_ADMIN_ROLE
CREATE ROLE TC_ADMIN_ROLE;
Prompt Creating role TC_MGR_ROLE
CREATE ROLE TC_MGR_ROLE;
Prompt Creating role TC_LDR_ROLE
CREATE ROLE TC_LDR_ROLE;
/*
The table TC_MASK has a column called OBJECT_NAME, whose length is normally
set to 649 in order to accommodate a 2K block size on a pre-9i database.  If you 
have extremely long file paths, and your database allows you to increase the size of the
column, then you may wish to increase the size of this column after running this script,
by running:
ALTER TABLE TC_MASK MODIFY OBJECT_NAME VARCHAR2(2000);
*/
DEFINE path_length = 2000
Prompt Creating table TC_FILEEXT
CREATE TABLE &&OWNER..tc_fileext
(
    vcs_objtype               varchar2(20) NOT NULL,
    vcs_objext                varchar2(10) NOT NULL
)
    &&tablespace_info
;

Prompt Adding primary key to TC_FILEEXT
ALTER table &&OWNER..tc_fileext
    add primary key (vcs_objtype)
    using index 
    &&tablespace_ind_info
;

Prompt Inserting procedure extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('PROCEDURE', 'PRC');

Prompt Inserting trigger extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('TRIGGER', 'TRG');

Prompt Inserting function extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('FUNCTION', 'FNC');

Prompt Inserting package extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('PACKAGE', 'PKS');

Prompt Inserting package body extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('PACKAGE BODY', 'PKB');

Prompt Inserting type extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('TYPE', 'TYP');

Prompt Inserting type body extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('TYPE BODY', 'TPB');

Prompt Inserting view extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('VIEW', 'VW');

Prompt Creating table TC_OBJSTATUS
CREATE TABLE &&OWNER..tc_objstatus
(
    psl_object_id               NUMBER ,
    psl_object_type             VARCHAR2(12) NOT NULL,     
    psl_object_owner            VARCHAR2(30) NOT NULL,     
    psl_object_name             VARCHAR2(2000) NOT NULL,     
    psl_checked_out             VARCHAR2(1) NOT NULL,
    psl_checked_out_by          VARCHAR2(30) NOT  NULL,
    psl_check_out_timestamp     DATE NOT NULL,
    psl_check_in_timestamp      DATE,
    psl_frozen                  VARCHAR2(1) NOT NULL,
    psl_frozen_by               VARCHAR2(30),
    psl_frozen_timestamp        DATE,
    psl_comments                VARCHAR2(2000),
    constraint tc_objstatus_pk 
      primary key (psl_object_id) 
      using index &&tablespace_ind_info
)
    &&tablespace_info
;

Prompt Creating index TC_OBJSTATUS_NDX
CREATE UNIQUE index &&OWNER..tc_objstatus_ndx
    on tc_objstatus (psl_object_id, psl_checked_out, psl_frozen, psl_checked_out_by)
    &&tablespace_ind_info
;
Prompt Creating table TC_OBJSTATUS_VCS
CREATE TABLE &&OWNER..tc_objstatus_vcs
(
    psl_object_id NUMBER NOT NULL,
    project_id NUMBER,
    filename VARCHAR2(2000),
    locked_by VARCHAR2(255)
)
    &&tablespace_info
;
Prompt Adding primary key to TC_OBJSTATUS_VCS
ALTER table &&OWNER..tc_objstatus_vcs
    add constraint tc_objstatus_vcs_pk 
    primary key (psl_object_id)
    using index &&tablespace_ind_info
;
Prompt Creating sequence TC_SCRIPT_ID
CREATE SEQUENCE &&OWNER..tc_script_id
  INCREMENT BY -1
  MINVALUE -2147483648
  MAXVALUE -10
  NOCYCLE
  NOORDER
  NOCACHE
;
Prompt Creating table TC_GROUP
CREATE TABLE &&OWNER..tc_group
(
    project_id            NUMBER NOT NULL,
    project_name          VARCHAR2(255) NOT NULL,
    workdir               VARCHAR2(2000),
    creation_date         DATE NOT NULL,
    last_modified_date    DATE,
    author                VARCHAR2(30),
    checked_out           INTEGER,
    checked_out_timestamp DATE,
    checked_out_by        VARCHAR2(30),
    checked_in_timestamp  DATE,
    vcp_project           VARCHAR2(2000),
    vcs_db                VARCHAR2(2000),
    frozen                VARCHAR2(1) DEFAULT 'N',
    frozen_by             VARCHAR2(30),
    frozen_timestamp      DATE,
    version               VARCHAR2(6)
)
    &&tablespace_info
;

Prompt Adding primary key to TC_GROUP
ALTER table &&OWNER..tc_group 
    add primary key (project_id)
    using index &&tablespace_ind_info
;

Prompt Creating sequence TC_GROUP_ID
CREATE SEQUENCE &&OWNER..TC_GROUP_ID
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 2147483647
  NOCYCLE
  NOORDER
  NOCACHE
;
Prompt Creating table TC_MASK
CREATE TABLE &&OWNER..tc_mask
(
    project_id                 number NOT NULL,
    object_name                varchar2(&&path_length) NOT NULL,
    object_type                number NOT NULL,
    schema_name                varchar2(30) NOT NULL,
    exclude                    number NOT NULL,
    comments                   varchar2(1000)
)
    &&tablespace_info
;

Prompt Adding primary key to TC_MASK
ALTER table &&OWNER..tc_mask 
    add constraint tc_mask_pk
      primary key (project_id, object_name, object_type, schema_name, exclude)
      using index &&tablespace_ind_info;

Prompt Creating table TC_USERMAPPING
CREATE TABLE &&OWNER..tc_usermapping
(
    project_id  number NOT NULL,
    username    varchar2(30),
    schema      varchar2(30)
)
    &&tablespace_info
;
Prompt Creating table TC_CONFIG
CREATE TABLE &&OWNER..tc_config
(
    options                VARCHAR2(16),
    set_working_directory  VARCHAR2(255),
    script_ext             VARCHAR2(255),
    vcs_vcp_name           NUMBER,
    vcs_db                 VARCHAR2(255),
    vcs_sub_vcp            VARCHAR2(255),
    frozen                 VARCHAR2(1),
    frozen_by              VARCHAR2(30),
    frozen_timestamp       DATE,
    version                VARCHAR2(16)
)
    &&tablespace_info
;

Prompt Inserting Config defaults
INSERT INTO &&OWNER..tc_config
            (script_ext, vcs_vcp_name, frozen, version)
     VALUES ('*.sql;', -1, 'N', '0060TOAD08500000');

Prompt Creating table TC_TIMESTAMP
CREATE TABLE &&OWNER..tc_timestamp (
    tp_timestamp        date,
    project_timestamp   date,
    um_timestamp        date
)
    &&tablespace_info
;

Prompt Inserting null row into TC_TIMESTAMP
INSERT INTO &&OWNER..tc_timestamp
            (tp_timestamp, project_timestamp, um_timestamp)
     VALUES (NULL, NULL, NULL);
Prompt Creating trigger TC_TIMESTAMP_OBJSTATUS
CREATE OR REPLACE TRIGGER &&OWNER..tc_timestamp_objstatus
  AFTER INSERT OR DELETE OR UPDATE
  ON &&OWNER..tc_objstatus
  REFERENCING OLD AS OLD NEW AS NEW
BEGIN
  UPDATE &&OWNER..tc_timestamp
     SET tp_timestamp = SYSDATE;
END;
/

Prompt Creating trigger TC_TIMESTAMP_MASK
CREATE OR REPLACE TRIGGER &&OWNER..tc_timestamp_mask
  AFTER INSERT OR DELETE OR UPDATE
  ON &&OWNER..tc_mask
  REFERENCING OLD AS OLD NEW AS NEW
BEGIN
  UPDATE &&OWNER..tc_timestamp
     SET project_timestamp = SYSDATE;
END;
/

Prompt Creating trigger TC_TIMESTAMP_GROUP
CREATE OR REPLACE TRIGGER &&OWNER..tc_timestamp_group
  AFTER INSERT OR DELETE OR UPDATE
  ON &&OWNER..tc_group
  REFERENCING OLD AS OLD NEW AS NEW
BEGIN
  UPDATE &&OWNER..tc_timestamp
     SET project_timestamp = SYSDATE;
END;
/

Prompt Creating trigger TC_TIMESTAMP_USERMAPPING
CREATE OR REPLACE TRIGGER &&OWNER..tc_timestamp_usermapping
  AFTER INSERT OR UPDATE OR DELETE
  ON &&OWNER..tc_usermapping
  REFERENCING NEW AS NEW OLD AS OLD
BEGIN
  UPDATE &&OWNER..tc_timestamp
     SET um_timestamp = SYSDATE;
END;
/
Prompt Creating trigger TC_DELETE_GROUP
CREATE OR REPLACE TRIGGER &&OWNER..tc_delete_group
  BEFORE DELETE
  ON &&OWNER..tc_group
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
BEGIN
  DELETE FROM &&OWNER..tc_mask
        WHERE project_id = :OLD.project_id;

  DELETE FROM &&OWNER..tc_usermapping
        WHERE project_id = :OLD.project_id;

  DELETE FROM &&OWNER..tc_objstatus_vcs
      WHERE project_id = :OLD.project_id;
  /* the tc_delete_obj trigger (below) takes care of the tc_objstatus table */  
END;
/

Prompt Creating trigger TC_DELETE_OBJ
CREATE OR REPLACE TRIGGER &&OWNER..tc_delete_obj
  BEFORE DELETE
  ON &&OWNER..tc_objstatus_vcs
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
begin
  DELETE FROM &&OWNER..tc_objstatus
        WHERE psl_object_id = :OLD.psl_object_id;
end;
/

SET DEFINE ON
Prompt Granting SELECT, UPDATE on TC_FILEEXT to PUBLIC
GRANT SELECT, UPDATE ON &&OWNER..TC_FILEEXT TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS_VCS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS_VCS TO PUBLIC;


Prompt Granting SELECT on TC_GROUP_ID to PUBLIC
GRANT SELECT ON &&OWNER..TC_GROUP_ID TO PUBLIC;


Prompt Granting SELECT on TC_GROUP to PUBLIC
GRANT SELECT ON &&OWNER..TC_GROUP TO PUBLIC;


Prompt Granting SELECT on TC_MASK to PUBLIC
GRANT SELECT ON &&OWNER..TC_MASK TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_USERMAPPING to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_USERMAPPING TO PUBLIC;


Prompt Granting SELECT on TC_CONFIG to PUBLIC
GRANT SELECT ON &&OWNER..TC_CONFIG TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_TIMESTAMP to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_TIMESTAMP TO PUBLIC;


Prompt Granting SELECT on TC_SCRIPT_ID to PUBLIC
GRANT SELECT ON &&OWNER..TC_SCRIPT_ID TO PUBLIC;


DEFINE TC_ADM = TC_ADMIN_ROLE
Prompt Granting SELECT, UPDATE on TC_FILEEXT to &&TC_ADMIN
GRANT SELECT, UPDATE ON &&OWNER..TC_FILEEXT TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS_VCS to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS_VCS TO &&TC_ADMIN;


Prompt Granting SELECT on TC_GROUP_ID to &&TC_ADMIN
GRANT SELECT ON &&OWNER..TC_GROUP_ID TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_GROUP to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_GROUP TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_MASK to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_MASK TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_USERMAPPING to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_USERMAPPING TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_CONFIG to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_CONFIG TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_TIMESTAMP to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_TIMESTAMP TO &&TC_ADMIN;


Prompt Granting SELECT on TC_SCRIPT_ID to &&TC_ADMIN
GRANT SELECT ON &&OWNER..TC_SCRIPT_ID TO &&TC_ADMIN;


DEFINE TC_MGR = TC_MGR_ROLE
Prompt Granting SELECT on TC_FILEEXT to &&TC_MGR
GRANT SELECT ON &&OWNER..TC_FILEEXT TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS_VCS to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS_VCS TO &&TC_MGR;


Prompt Granting SELECT on TC_GROUP_ID to &&TC_MGR
GRANT SELECT ON &&OWNER..TC_GROUP_ID TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_GROUP to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_GROUP TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_MASK to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_MASK TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_USERMAPPING to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_USERMAPPING TO &&TC_MGR;


Prompt Granting SELECT on TC_CONFIG to &&TC_MGR
GRANT SELECT ON &&OWNER..TC_CONFIG TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_TIMESTAMP to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_TIMESTAMP TO &&TC_MGR;


Prompt Granting SELECT on TC_SCRIPT_ID to &&TC_MGR
GRANT SELECT ON &&OWNER..TC_SCRIPT_ID TO &&TC_MGR;


DEFINE TC_LDR = TC_LDR_ROLE
Prompt Granting SELECT on TC_FILEEXT to &&TC_LDR
GRANT SELECT ON &&OWNER..TC_FILEEXT TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE (PSL_FROZEN, PSL_FROZEN_BY, PSL_FROZEN_TIMESTAMP) on TC_OBJSTATUS to &&TC_LDR
GRANT SELECT, INSERT, UPDATE (PSL_FROZEN, PSL_FROZEN_BY, PSL_FROZEN_TIMESTAMP) ON &&OWNER..TC_OBJSTATUS TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS_VCS to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS_VCS TO &&TC_LDR;


Prompt Granting SELECT on TC_GROUP_ID to &&TC_LDR
GRANT SELECT ON &&OWNER..TC_GROUP_ID TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE on TC_GROUP to &&TC_LDR
GRANT SELECT, INSERT, UPDATE ON &&OWNER..TC_GROUP TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_MASK to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_MASK TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_USERMAPPING to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_USERMAPPING TO &&TC_LDR;


Prompt Granting SELECT on TC_CONFIG to &&TC_LDR
GRANT SELECT ON &&OWNER..TC_CONFIG TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_TIMESTAMP to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_TIMESTAMP TO &&TC_LDR;


Prompt Granting SELECT on TC_SCRIPT_ID to &&TC_LDR
GRANT SELECT ON &&OWNER..TC_SCRIPT_ID TO &&TC_LDR;


Prompt Creating public synonym QUEST_COM_TEAM_CODING
CREATE OR REPLACE PUBLIC SYNONYM QUEST_COM_TEAM_CODING FOR &&OWNER..TC_CONFIG;


Prompt ============================================================================
Prompt Creating/Upgrading CodeXpert objects
Prompt ============================================================================
DEFINE TSP_TABS = ' TABLESPACE TOAD'
DEFINE TSP_INDS = ' TABLESPACE TOAD'
Prompt Creating table CX_CATEGORYS
CREATE TABLE &&OWNER..CX_CATEGORYS
(
  CAT_ID       INTEGER                     NOT NULL,
  DESCRIPTION  VARCHAR2(20)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_CATEGORYS
ALTER TABLE &&OWNER..CX_CATEGORYS ADD (
  PRIMARY KEY
 (CAT_ID)
    USING INDEX
     &&TSP_INDS
); Prompt Creating table CX_TYPES
CREATE TABLE &&OWNER..CX_TYPES
(
  TYP_ID       INTEGER                     NOT NULL,
  DESCRIPTION  VARCHAR2(20)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_TYPES
ALTER TABLE &&OWNER..CX_TYPES ADD (
  PRIMARY KEY
 (TYP_ID)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_BASERULES
CREATE TABLE &&OWNER..CX_BASERULES
(
  RUL_ID      INTEGER                      NOT NULL,
  CAT_ID      INTEGER                      NOT NULL,
  SEV_ID      INTEGER                      NOT NULL,
  TYP_ID      INTEGER                      NOT NULL,
  DEFINITION  VARCHAR2(1000)               NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_BASERULES
ALTER TABLE &&OWNER..CX_BASERULES ADD (
  PRIMARY KEY
 (RUL_ID)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_SEVERITYS
CREATE TABLE &&OWNER..CX_SEVERITYS
(
  SEV_ID       INTEGER                     NOT NULL,
  DESCRIPTION  VARCHAR2(20)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_SEVERITYS
ALTER TABLE &&OWNER..CX_SEVERITYS ADD (
  PRIMARY KEY
 (SEV_ID)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_RULESETS
CREATE TABLE &&OWNER..CX_RULESETS
(
  TITLE     VARCHAR2(100)                  NOT NULL,
  AUTHOR    VARCHAR2(50),
  CREATED   DATE                           NOT NULL,
  MODIFIED  DATE
)
 &&TSP_TABS;
Prompt Adding primary key to CX_RULESETS
ALTER TABLE &&OWNER..CX_RULESETS ADD (
  PRIMARY KEY
 (TITLE)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_SETRULES
CREATE TABLE &&OWNER..CX_SETRULES
(
  RUL_ID  INTEGER                          NOT NULL,
  TITLE   VARCHAR2(100)                    NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_SETRULES
ALTER TABLE &&OWNER..CX_SETRULES ADD (
  PRIMARY KEY
 (RUL_ID, TITLE)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_XPERTLINES
CREATE TABLE &&OWNER..CX_XPERTLINES
(
  RUNNAME     VARCHAR2(100)                NOT NULL,
  ITEMNO      INTEGER                      NOT NULL,
  LINENO      INTEGER                      NOT NULL,
  LINEPOS     INTEGER                      NOT NULL,
  RUL_ID      INTEGER                      NOT NULL,
  TITLE       VARCHAR2(100)                NOT NULL
)
 &&TSP_TABS;
Prompt Creating table CX_XPERTITEMS
CREATE TABLE &&OWNER..CX_XPERTITEMS
(
  RUNNAME   VARCHAR2(100)                  NOT NULL,
  ITEMNO    INTEGER                        NOT NULL,
  INSTANCE  VARCHAR2(20)                   NOT NULL,
  "SCHEMA"  VARCHAR2(50)                   NOT NULL,
  OBJNAME   VARCHAR2(50)                   NOT NULL,
  OBJTYPE   VARCHAR2(100),
  SCRIPT    CLOB
)
 &&TSP_TABS;
Prompt Adding primary key to CX_XPERTITEMS
ALTER TABLE &&OWNER..CX_XPERTITEMS ADD (
  PRIMARY KEY
 (RUNNAME, ITEMNO)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_METRICS
CREATE TABLE &&OWNER..CX_METRICS
(
  RUNNAME      VARCHAR2(100)               NOT NULL,
  ITEMNO       INTEGER                     NOT NULL,
  SCORE        FLOAT(126)                  NOT NULL,
  MET_NAME     VARCHAR2(50)                NOT NULL,
  MET_MEANING  VARCHAR2(50)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_METRICS
ALTER TABLE &&OWNER..CX_METRICS ADD (
  PRIMARY KEY
 (RUNNAME, ITEMNO, MET_NAME, MET_MEANING)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_XPERTRUN
CREATE TABLE &&OWNER..CX_XPERTRUN
(
  RUN_ID      INTEGER                      NOT NULL,
  RUNNAME     VARCHAR2(100)                NOT NULL,
  RUNDATE     DATE                         NOT NULL,
  RUNCOMMENT  VARCHAR2(1000)
)
 &&TSP_TABS;
Prompt Adding primary key to CX_XPERTRUN
ALTER TABLE &&OWNER..CX_XPERTRUN ADD (
  PRIMARY KEY
 (RUNNAME)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating sequence CX_XPERTRUN_ID
CREATE SEQUENCE &&OWNER..CX_XPERTRUN_ID
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;
Prompt Creating trigger CX_XPERTRUN_INSERT
CREATE TRIGGER &&OWNER..CX_XPERTRUN_INSERT
 BEFORE INSERT ON &&OWNER..CX_XPERTRUN
 FOR EACH ROW
DECLARE
   tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   SELECT &&OWNER..CX_XPERTRUN_ID.NEXTVAL INTO tmpVar FROM dual;

   :NEW.RUN_ID := tmpVar;

END CX_XPERTRUN_INSERT;
/
Prompt Creating table CX_METRICRANGES
CREATE TABLE &&OWNER..CX_METRICRANGES
(
  MET_NAME     VARCHAR2(50)                NOT NULL,
  MET_MEANING  VARCHAR2(50)                NOT NULL,
  RANGE_LO     FLOAT(126)                  NOT NULL,
  RANGE_HI     FLOAT(126)                  NOT NULL,
  COLOR        VARCHAR2(50)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_METRICRANGES
ALTER TABLE &&OWNER..CX_METRICRANGES ADD (
  PRIMARY KEY
 (MET_NAME, MET_MEANING)
    USING INDEX
     &&TSP_INDS
);
Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_CATEGORYS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_CATEGORYS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_SEVERITYS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_SEVERITYS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_TYPES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_TYPES TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_BASERULES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_BASERULES TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_RULESETS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_RULESETS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_SETRULES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_SETRULES TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_XPERTRUN to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_XPERTRUN TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_XPERTITEMS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_XPERTITEMS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_XPERTLINES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_XPERTLINES TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_METRICS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_METRICS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_METRICRANGES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_METRICRANGES TO PUBLIC;


Prompt Creating public synonym QUEST_COM_CODEXPERT
CREATE PUBLIC SYNONYM QUEST_COM_CODEXPERT FOR &&OWNER..CX_XPERTRUN;

Prompt Creating public synonym CX_XPERTRUN
CREATE PUBLIC SYNONYM CX_XPERTRUN FOR &&OWNER..CX_XPERTRUN;

Prompt Creating public synonym CX_XPERTLINES
CREATE PUBLIC SYNONYM CX_XPERTLINES FOR &&OWNER..CX_XPERTLINES;

Prompt Creating public synonym CX_XPERTITEMS
CREATE PUBLIC SYNONYM CX_XPERTITEMS FOR &&OWNER..CX_XPERTITEMS;

Prompt Creating public synonym CX_TYPES
CREATE PUBLIC SYNONYM CX_TYPES FOR &&OWNER..CX_TYPES;

Prompt Creating public synonym CX_SEVERITYS
CREATE PUBLIC SYNONYM CX_SEVERITYS FOR &&OWNER..CX_SEVERITYS;

Prompt Creating public synonym CX_SETRULES
CREATE PUBLIC SYNONYM CX_SETRULES FOR &&OWNER..CX_SETRULES;

Prompt Creating public synonym CX_RULESETS
CREATE PUBLIC SYNONYM CX_RULESETS FOR &&OWNER..CX_RULESETS;

Prompt Creating public synonym CX_METRICS
CREATE PUBLIC SYNONYM CX_METRICS FOR &&OWNER..CX_METRICS;

Prompt Creating public synonym CX_METRICRANGES
CREATE PUBLIC SYNONYM CX_METRICRANGES FOR &&OWNER..CX_METRICRANGES;

Prompt Creating public synonym CX_CATEGORYS
CREATE PUBLIC SYNONYM CX_CATEGORYS FOR &&OWNER..CX_CATEGORYS;

Prompt Creating public synonym CX_BASERULES
CREATE PUBLIC SYNONYM CX_BASERULES FOR &&OWNER..CX_BASERULES;




Prompt ============================================================================
Prompt Creating/Upgrading Data Generation objects
Prompt ============================================================================
Prompt Creating package spec TOAD_DATAGEN
@"C:\Program Files (x86)\Quest Software\Toad for Oracle 10.6\data_gen.pks"
Prompt Creating package body TOAD_DATAGEN
@"C:\Program Files (x86)\Quest Software\Toad for Oracle 10.6\data_gen.plb"
Prompt Granting EXECUTE on TOAD_DATAGEN to PUBLIC
GRANT EXECUTE ON &&OWNER..TOAD_DATAGEN TO PUBLIC;



Prompt Creating public synonym TOAD_DATAGEN
CREATE OR REPLACE PUBLIC SYNONYM TOAD_DATAGEN FOR &&OWNER..TOAD_DATAGEN;


No comments :

Post a Comment