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