Re: SYSTEM schema in SYSTEM tablespace

Ed_Stevens_at_nospam.noway.nohow (Ed Stevens) wrote in message news:<3c4f2a41.203752881_at_ausnews.austin.ibm.com>...
> Platform: 8.0.5 on NT 4
>
> I have been often advised that the only schema that should own objects in the
> SYSTEM tablespace is SYS; that even SYSTEM should be assigned to other
> tablespaces just like any other user.
>
> All of our db's still have SYSTEM assigned to the system tablespace (though his
> temp tablespace has been reassigned to a temp ts). I have discussed this with
> my partner and he says he sees no need to create the recommended TOOLS ts and
> move SYSTEM's objects to it. I have nothing to counter except "everyone says to
> do it that way." Is there any demo/test I could do to demonstrate to both of us
> why this is good practice?
>
> And on a related issue, I see that the user SYSTEM is created when you issue a
> CREATE DATABASE, so at that point the only TS available is the system ts. If
> you follow Oracle's procedures, the next thing to do is run CATLOG and CATPROC.
> At the conclusion of those, SYSTEM has objects in the system ts. What's
> standard practice to insure that SYSTEM's objects end up where they really
> should be? Create DB, and then create your TS's before running CATALOG and
> CATPROC?
>
> TIA.
We allow the system objects created as part of the database creation process to go into system. But once we are done with the catalog, catproc, catparr (OPS) process we re-assign system to use another tablespace. Tools may or may not be the most logical choice depending if you use the Oracle tool set (Forms, ReportWriter etc...) and how many tools you have, and what if any default id they use. You may want to distribute them along with your regular application objects.

We have the Discoverer product and some of its tables have grown quite large, quite rapidly, once we forced users to start using it instead of DataQuery 3 (which is fairly old and obsolete). You would not want them filling your system tablespace; Oracle does not behave well when the system tablespace fills. And it is to reduce tablespace fragmentation and resource contention that you should move non-base objects out of the system tablespace.

IMHO -- Mark D Powell -- Received on Wed Jan 23 2002 - 22:02:33 CST