Have the users connect to an account named PERFSTAT on which you create
an AFTER LOGON trigger that performs "ALTER SESSION SET CURRENT_SCHEMA =
[ PERFSTAT1 | PERFSTAT2 ]", as appropriate? You can add a CREATE OR
REPLACE statement for the trigger to the scripts you use to "switch" the
identities, perhaps?
Wouldn't it make more sense to range-partition the tables that get too
large in PERFSTAT by SNAP_ID? That way, you can implement some "rolling
window" logic so that you don't delete all your history every 3 months.
I imagine that there are only a few tables that grow "too large".
Partition-pruning will probably help the performance of your queries to
the point where you wouldn't even feel the need to implement the
"rolling-window" logic anyway.
Just a thought...
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 <mailto:mosicr@xxxxxxxxxx>
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMosicMain.html
-- //www.freelists.org/webpage/oracle-l
