Re: Schema Naming Standards

From: Rich Jesse <>
Date: Wed, 27 May 2015 08:47:00 -0500 (CDT)
Message-ID: <>

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,

Received on Wed May 27 2015 - 15:47:00 CEST

Original text of this message