RE: Switch between two statspack ( perfstat ) schemas

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <ranko.mosic@xxxxxxxxx>, "_oracle_L_list" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 21 Apr 2006 13:01:21 -0400

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxx
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMo
sicMain.html 
 

Other related posts: