Re: Creating Tables

Apart from the emphatic warnings, so eloquently posted,

"NOOOOOOOOO".
if that adds any useful emphasis.

Hypothetically, say, you are building an enterprise or multi-unit system.

SST might be State Sales Tax.
IRS might be payroll deductions for your national tax authority.

And so forth.

SVAT might be for Value-Added Tax, an idea that has fortunately not caught on among us barbarians, at least in these parts. We do not even understand the taxes we are already paying, which I understand is not uncommon elsewhere.

In any case:

to echo my colleague again,

"NOOOOO". Never create, change, touch, or even make funny faces at anything owned by SYS. I would advise against any meaningful amount of humor similarly directed at SYSTEM. Nowadays with CTXSYS and God only knows what else, it generally is a good policy to leave untouched, places that Oracle has set aside for some purpose, unless you are sure beyond doubt, what you are getting into.

Create usernames with default tablespaces for objects and for TEMP that are not SYSTEM. (Create the tablespaces first... sigh.)

It would not be unusual to have a layout such as:

AR_TBL
AR_IDX
PAY_TBL
PAY_IDX ..
etc
to store application specific data and the indices that relate to the data.

That was given just as an academic example. And there are numerous people in this newsgroup who would fervently argue a completely different strategy for you.

In any case, if there is one single issue I would risk money on wagering, it would be DO NOT EVER put anything that Oracle does not own, or create, in the SYSTEM tablespace. The data dictionary lives there, everybody's PL/SQL lives there, etc.

Given the volatility of the group lately, I wonder whether we can even gain consensus on that issue.

Sybrand is completely correct by starting off with "ARRRRGGGGHhhh..." etc. (Correction.)

I would have prefaced it with AIIIGH, then the NOOOO.

Among the reasons this is the root of all evil, is that you are not free to do automatic or manual 'garbage collection', in SYSTEM; every other tablespace can be crunched, if it comes to the worst, and performance is dreadful, by EXPORTing and then IMPORTing the entire application's objects.

In the case of the SYSTEM tablespace, however, it generally means rebuilding the entire database (since SYSTEM holds things like all the dictionary structures, permissions and security, etc).

But there are ways even to get through that, if that is what you have to do.

Please keep us apprised of your progress.

Finally, if you absolutely, positively are in a mess due to this, the problem can be corrected, but it is a procedure that Oracle documents, but does not brag loudly or widely about.

RSH. "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:ud3jv9ppdne8b_at_corp.supernews.com...
> Comments embedded
> "mtwalla" <mtwalla_at_bluefishgroup.com> wrote in message
> news:Q_jA8.4781$VQ6.602987_at_news.uswest.net...
> > If I am creating tables/database objects specific to an application who
> > should be the owner of those tables? Should I create them to be owned by
> SYS
>
> AAARRRRRRRRRRRGGGGGGGGGGGGGGGGGGGGGGGGGG
> NEVER EVER do this. SYS is the owner of the datadictionary and is not
> exported. You should your hands have chopped off for even considering it.
>
>
> > and then create public synonymns on the tables/objects? Or should I have
> an
> > application user who owns the tables/objects
> YES and if so, should I create
> > Synonyms on them?
>
> YES. In an ideal world the owner should have revoked create session
> privilege after the deed or the account locked,. as you can't protect the
> owner of the tables against itself. Too many people are always connect as
> application owner in Oracle, and as the user Oracle on their O/S. One slip
> of the finger and you have havoc.
> Or is there some other approach altogether?
> Hardcode the owner of the object in front of every object name in selects
> etc.
> Bad idea (tm)
> Thanks! I know
> > how to create tables, write the DDL, etc. I'm just fuzzy on the
ownership
> of
> > the objects.
> >
> >
> >
> >
>
> Please do not crosspost and multipost.
>
> Regards
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
Received on Thu May 02 2002 - 20:13:09 CDT