RE: Switch between two statspack ( perfstat ) schemas

One way would be to have PERFSTAT1 and PERFSTAT2, each of which has complete set of PERFSTAT schema objects.  

Then, define a user PERFSTAT, that has no objects of his own, but has full select,insert,update,delete privs on all objects in both PERFSTAT1 and PERFSTAT2. Then, create a logon trigger for PERFSTAT that does 'alter session set current_schema = perfstat1'. When you want to change over, just re-create the trigger with 'alter session set current_schema = perfstat2'.  

-Mark
 

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ranko Mosic Sent: Friday, April 21, 2006 12:53 PM
To: _oracle_L_list
Subject: Switch between two statspack ( perfstat ) schemas

Hi List,
I need to switch between two statspack schemas every 3 months ( tables become too big so we want to
switch back and forth with snapshot truncate and dbms_job modification during the switch ).
I'll have perfstat1 and perfstat2 users and each of them will have it's own set of statspack tables.

The question is: how do I make it transparent to the user so they always connect to the same user.
Let's say they connect to perfstat1. How do I make them use perfstat2 tables/objects ?
Local tables always takes precedence when referenced, no matter what I do with synonyms.
If exec snapshot.snap it will always exeecute perfstat1 proc. Oracle V9x ( irrelevant I guess).
--

Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional Phone: 416-450-2785
email: mosicr_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMo sicMain.html  

--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 21 2006 - 12:01:21 CDT