Re: Renaming tablespace by updating SYS.TS$

If the issue is simply that new data is imported and the synonyms must point to it, the process is very simple.

1) Import tablespace(s) with a name that identifies the creation date
2) Recreate synonyms to point to new data
3) Drop tablespace(s) that are out of date

Oracle does not use tablespace names to resolve objects, so the steps of changing tablespace is not needed. It only adds complication without adding any benefit (quite the opposite). Unless the new schema is creating objects in the new tablespace, there is no need to keep the same names. If the new schema is creating objects in the new tablespace, those objects will be removed at the next import, so they are 'throwaway'.

The gist of the whole process is to load data on a daily basis and have the data available as soon as it is loaded. This is not an unusual scenario, even when the data is in the range of 100g. I seriously doubt that all of those other systems require data dictionary modifications to function properly. The process that you have described sounds overly complicated with needless risk. If you are accessing the data by changing synonyms, updating ts$ appears to be unnecessary. Not only that, but it also puts your support in doubt, adds dba management requirements and exposes a security risk. If someone proposed this process on a database I was administering (whether it is development/test/staging/production), I would reject it immediately.

There are no easy answers to the issue, mainly because we are only seeing part of the whole. The real issue here is a process was designed and implemented that is outside the bounds of what is generally acceptable Oracle behavior. We on the list do not know the business, their requirements, the restrictions, political situation, etc. The question that needs to be answered to the list is "What is the business requirement you are trying to satisfy?". Once we know the business requirement, we can offer alternatives.

Daniel W. Fink

Ryan wrote:

>The explanation is in one of the many emails I sent. I apologize. I was
>unclear yesterday. We have 2 schemas. One active that defaults to the
>current tablespace. One inactive. When we import the new tablespace. We
>default the inactive schema to the new tablespace.
>
>We then 'point' the synonyms to the inactive schema. That becomes active.
>The old schema becomes inactive. We then drop the old tablespace.
>
>Ive explained the TS$ update several times. I guess noone has a better
>alternative that fills our requirements without forcing us to purchase a
>third party tool.
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).