Re: Schema Naming Standards
Date: Wed, 27 May 2015 08:47:00 -0500 (CDT)
Message-ID: <af0d2d12d7426a46a52fc95cb6324e73.squirrel_at_society.servebeer.com>
Mark replies:
> Just wanted to add one more thought. It's almost always smart to *not* tie
> your schema to the environment. For example, it's a bad idea to have an
> app called ACME, and the schema/app owner schema called ACME_DEV in
> ACME_TST in test, ACME_PRD in prod, etc. It will hugely complicate things
> like refreshes from prod to test, dev, etc. (Yes I've seen/worked on such
> a system. Big pain.....)
I'll add the perfunctory "It Depends" here. For our ERP (JD Edwards), we have 7 Production DBs and 7 Dev/Test/Training/whatever DBs. The schema names for each of the lower environment DBs is different per the apparent standard of the ERP.
The ERP is multi-tier web-based, and the schema names for the generated SQLs are stored in a single location of each environment's definition. The only thing I need to do from a schema standpoint in order to refresh data is to add a few REMAP_SCHEMAs and REMAP_TABLESPACEs to the impdp over a dblink, and we're good to go.
There is no direct SQL interface for production purposes. The only SQLs are occasional data fixes, where I need to make sure that the schema names are changed from their testing to production.
Dead simple. For this system. No, I wouldn't normally hope that schema names change between environments, but it works very well here from the DBA point-of-view.
My $.02,
Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 27 2015 - 15:47:00 CEST