Thinking Out Loud

March 21, 2013

Hard Coding 101

Filed under: oracle — mdinh @ 4:41 am

Just don’t do it!

I must be naive, but is there a reason prefix schema name for objects owned by the same owner?

There’s no harm if there is never a need to migrate the data from one schema to another. But it’s a night mare when there is.

Here’s a simple analogy. It’s like getting a tattoo of your first girl friend Jane and end up marrying ┬áJill. It’s a night mare.

Here is a test case

LAX:(MDINH@db01)> create or replace trigger mdinh.trig_t before insert on mdinh.t begin null; end;
  2  /

Trigger created.

LAX:(MDINH@db01)>
[oracle@lax:db01]/home/oracle
$ expdp logfile=t.log schemas=mdinh include=trigger dumpfile=t.dmp directory=DATA_PUMP_DIR

Export: Release 11.2.0.3.0 - Production on Wed Mar 20 21:27:10 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA logfile=t.log schemas=mdinh include=trigger dumpfile=t.dmp directory=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /tmp/t.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:27:25

[oracle@lax:db01]/home/oracle
$ impdp schemas=mdinh remap_schema=mdinh:scott include=trigger sqlfile=trigger_ddl.sql dumpfile=t.dmp directory=DATA_PUMP_DIR

Import: Release 11.2.0.3.0 - Production on Wed Mar 20 21:29:22 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01":  /******** AS SYSDBA schemas=mdinh remap_schema=mdinh:scott include=trigger sqlfile=trigger_ddl.sql dumpfile=t.dmp directory=DATA_PUMP_DIR
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:29:27

[oracle@lax:db01]/home/oracle
$ cat /tmp/trigger_ddl.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
-- CONNECT SCOTT
CREATE TRIGGER "SCOTT"."TRIG_T"  before insert on mdinh.t begin null; end;
/

ALTER TRIGGER "SCOTT"."TRIG_T" ENABLE;

ALTER TRIGGER "SCOTT"."TRIG_T"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
[oracle@lax:db01]/home/oracle
$
About these ads

3 Comments »

  1. I listen a couple times about the approach when data and code splits in different schemas. Maybe it is normal for such cases.

    Comment by Sayan Malakshinov — March 21, 2013 @ 11:50 am | Reply

    • Is there a reason prefix schema name for objects owned by the same owner? This is different from when application is referencing 2 schemas.

      Comment by mdinh — March 23, 2013 @ 9:34 pm | Reply

      • I meant, that it if we use one schema now, but we do not know whether we will use the two schemes someday. Or if we using several schemas for code on test server and one – for data, but on production server one for both.

        Comment by Sayan Malakshinov — March 23, 2013 @ 9:56 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 314 other followers

%d bloggers like this: