/*###################################################################### */ /*# */ /*# CA (tm) Introscope(R) PowerPack(tm) for Oracle(R) Databases */ /*# Database User Privileges */ /*# */ /*# Version: 9.1.5.0 */ /*# Build: 583031 */ /*# */ /*# CA Wily Introscope(R) Version 9.1 Release 9.1.5.0 */ /*# Copyright (c) 2013 CA. All Rights Reserved. */ /*# Introscope(R) is a registered trademark of CA. */ /*###################################################################### */ CREATE USER iscope_orcl identified by iscope_orcl DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CONNECT TO iscope_orcl; CREATE TABLE iscope_orcl.APM_OPP_ROLLSTAT_TEMP NOLOGGING AS SELECT gets, waits FROM V$ROLLSTAT; CREATE TABLE iscope_orcl.APM_OPP_SGA_TEMP NOLOGGING AS SELECT name, value FROM V$SGA; CREATE TABLE iscope_orcl.APM_OPP_ROWCACHE_TEMP NOLOGGING AS SELECT getmisses, gets FROM V$ROWCACHE; CREATE TABLE iscope_orcl.APM_OPP_LIBRARYCACHE_TEMP NOLOGGING AS SELECT namespace, gethitratio FROM V$LIBRARYCACHE; CREATE TABLE iscope_orcl.APM_OPP_LIBRARYCACHE_TEMP1 NOLOGGING AS SELECT reloads, pins FROM V$LIBRARYCACHE; CREATE TABLE iscope_orcl.APM_OPP_SQLAREA_TEMP NOLOGGING AS SELECT users_opening FROM V$SQLAREA; CREATE TABLE iscope_orcl.APM_OPP_SQLAREA_TEMP1 NOLOGGING AS SELECT sharable_mem, executions FROM V$SQLAREA; CREATE TABLE iscope_orcl.APM_OPP_DB_OBJECT_CACHE_TEMP NOLOGGING AS SELECT sharable_mem FROM V$DB_OBJECT_CACHE; CREATE TABLE iscope_orcl.APM_OPP_SESSTAT_TEMP NOLOGGING AS SELECT sid, statistic#, value FROM V$SESSTAT; CREATE TABLE iscope_orcl.APM_OPP_STATNAME_TEMP NOLOGGING AS SELECT statistic#, name, class, stat_id FROM V$STATNAME; CREATE TABLE iscope_orcl.APM_OPP_FILESTAT_TEMP NOLOGGING AS SELECT phyrds, phywrts, file# FROM V$FILESTAT; CREATE TABLE iscope_orcl.APM_OPP_DBA_DATA_FILES_TEMP NOLOGGING AS SELECT file_id, tablespace_name, file_name FROM dba_data_files; GRANT ALL ON iscope_orcl.APM_OPP_ROLLSTAT_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_SGA_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_ROWCACHE_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_LIBRARYCACHE_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_LIBRARYCACHE_TEMP1 to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_SQLAREA_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_SQLAREA_TEMP1 to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_DB_OBJECT_CACHE_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_SESSTAT_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_STATNAME_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_FILESTAT_TEMP to iscope_orcl; GRANT ALL ON iscope_orcl.APM_OPP_DBA_DATA_FILES_TEMP to iscope_orcl; /*GRANT Select privleges to the ISCOPE_ORCL user for Oracle Performance tables */ begin for x in ( select object_name from user_objects where object_type = 'VIEW' and object_name like 'V\_$%' escape '\' ) loop execute immediate 'grant select on ' || x.object_name || ' to iscope_orcl'; end loop; execute immediate 'grant select on dba_data_files to iscope_orcl'; end; / /* BEGIN FUNCTION */ CREATE OR REPLACE PROCEDURE iscope_orcl.Populate_temp_tables(p_status OUT VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; l_table_count NUMBER; BEGIN p_status := 'FAILURE'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_ROLLSTAT_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_SGA_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_ROWCACHE_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_LIBRARYCACHE_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_LIBRARYCACHE_TEMP1'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_SQLAREA_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_SQLAREA_TEMP1'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_DB_OBJECT_CACHE_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_SESSTAT_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_STATNAME_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_FILESTAT_TEMP'; EXECUTE IMMEDIATE 'TRUNCATE TABLE iscope_orcl.APM_OPP_DBA_DATA_FILES_TEMP'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_ROLLSTAT_TEMP SELECT gets, waits FROM V$ROLLSTAT'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_SGA_TEMP SELECT name, value FROM V$SGA'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_ROWCACHE_TEMP SELECT getmisses, gets FROM V$ROWCACHE'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_LIBRARYCACHE_TEMP SELECT namespace, gethitratio FROM V$LIBRARYCACHE'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_LIBRARYCACHE_TEMP1 SELECT reloads, pins FROM V$LIBRARYCACHE'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_SQLAREA_TEMP SELECT users_opening FROM V$SQLAREA'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_SQLAREA_TEMP1 SELECT sharable_mem, executions FROM V$SQLAREA'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_DB_OBJECT_CACHE_TEMP SELECT sharable_mem FROM V$DB_OBJECT_CACHE'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_SESSTAT_TEMP SELECT sid, statistic#, value FROM V$SESSTAT'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_STATNAME_TEMP SELECT statistic#, name, class, stat_id FROM V$STATNAME'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_FILESTAT_TEMP SELECT phyrds, phywrts, file# FROM V$FILESTAT'; EXECUTE IMMEDIATE 'INSERT /*+ append */ INTO iscope_orcl.APM_OPP_DBA_DATA_FILES_TEMP SELECT file_id, tablespace_name, file_name FROM dba_data_files'; COMMIT; p_status := 'SUCCESS'; EXCEPTION WHEN OTHERS THEN ROLLBACK; p_status := 'FAILURE'; END; / GRANT EXECUTE ON iscope_orcl.Populate_temp_tables TO iscope_orcl; /