Re: Switch between two statspack ( perfstat ) schemas

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...

Ranko Mosic wrote:
> 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 <mailto:mosicr_at_rogers.com>
> http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
>
>

--
http://www.freelists.org/webpage/oracle-l