Thinking Out Loud

July 13, 2013

Absolute versus Relative Reference

Filed under: oracle — mdinh @ 6:14 pm

It’s important for software developers to understand the differences between absolute versus relative references.

Whether it’s from an Excel spreadsheet or from an oracle database. it’s an easy mistake to make.

I have created a test case where object “abso” , “refe” to represent absolute and relative reference, respectively.

Create test case:

LAX:(APPU@db01)> @test
LAX:(APPU@db01)> drop table abso purge;

Table dropped.

LAX:(APPU@db01)> create table abso (id int);

Table created.

LAX:(APPU@db01)> create or replace view abso_v as select id from appu.abso;

View created.

LAX:(APPU@db01)> create or replace trigger appu.abso_bi before insert on appu.abso for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

LAX:(APPU@db01)> create trigger appu.abso_bd before delete on appu.abso for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

LAX:(APPU@db01)> create or replace procedure abso_proc as
  2  l_id pls_integer;
  3  begin
  4  select id into l_id from appu.abso;
  5  end;
  6  /

Procedure created.

LAX:(APPU@db01)> drop table refe purge;

Table dropped.

LAX:(APPU@db01)> create table refe (id int);

Table created.

LAX:(APPU@db01)> create or replace view refe_v as select id from refe;

View created.

LAX:(APPU@db01)> create or replace trigger refe_bi before insert on refe for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

LAX:(APPU@db01)> create trigger refe_bd before delete on refe for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

LAX:(APPU@db01)> create or replace procedure refe_proc as
  2  l_id pls_integer;
  3  begin
  4  select id into l_id from refe;
  5  end;
  6  /

Procedure created.

LAX:(APPU@db01)>

Perform metadata export:

[oracle@lax:db01]/home/oracle
$ cat expdp_metadata.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
dumpfile=metadata.dmp
logfile=metadata.log
schemas=APPU
content=METADATA_ONLY
exclude=INDEX_STATISTICS,TABLE_STATISTICS
metrics=Y
[oracle@lax:db01]/home/oracle
$ expdp parfile=expdp_metadata.par

Export: Release 11.2.0.3.0 - Production on Sat Jul 13 10:28:41 2013

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

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 parfile=expdp_metadata.par
Processing object type SCHEMA_EXPORT/USER
     Completed 1 USER objects in 1 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
     Completed 1 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
     Completed 1 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Completed 1 DEFAULT_ROLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Completed 1 PROCACT_SCHEMA objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 2 TABLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     Completed 2 PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     Completed 2 ALTER_PROCEDURE objects in 3 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
     Completed 2 VIEW objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 4 TRIGGER objects in 7 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /tmp/metadata.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:09

[oracle@lax:db01]/home/oracle
$

Perform metadata import with remap_schema – creating SQL file:

[oracle@lax:db01]/home/oracle
$ cat impdp_metadata.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
dumpfile=metadata.dmp
schemas=APPU
remap_schema=APPU:NEWAPP
logfile=metadata.log
include=PROCEDURE,TRIGGER,VIEW
sqlfile=metadata.sql
metrics=Y
[oracle@lax:db01]/home/oracle
$ impdp parfile=impdp_metadata.par

Import: Release 11.2.0.3.0 - Production on Sat Jul 13 10:36:55 2013

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

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 parfile=impdp_metadata.par
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     Completed 2 PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     Completed 2 ALTER_PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
     Completed 2 VIEW objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 4 TRIGGER objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 10:36:58

Do you see what’s wrong with this picture?

[oracle@lax:db01]/home/oracle
$ cat /tmp/metadata.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/PROCEDURE/PROCEDURE
-- CONNECT NEWAPP
CREATE procedure abso_proc as
l_id pls_integer;
begin
select id into l_id from appu.abso; <<<
end;
/
CREATE procedure refe_proc as
l_id pls_integer;
begin
select id into l_id from refe;
end;
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ALTER PROCEDURE "NEWAPP"."ABSO_PROC"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2013-07-13 10:24:27'
/

ALTER PROCEDURE "NEWAPP"."REFE_PROC"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED
    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'

 REUSE SETTINGS TIMESTAMP '2013-07-13 10:24:28'
/
-- new object type path: SCHEMA_EXPORT/VIEW/VIEW
CREATE  FORCE VIEW "NEWAPP"."ABSO_V" ("ID") AS
  select id from appu.abso; <<<
CREATE  FORCE VIEW "NEWAPP"."REFE_V" ("ID") AS
  select id from refe;
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
CREATE TRIGGER "NEWAPP"."ABSO_BD"  before delete on appu.abso for each row <<<
begin
null;
end;
/

ALTER TRIGGER "NEWAPP"."ABSO_BD" ENABLE;

ALTER TRIGGER "NEWAPP"."ABSO_BD"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
CREATE TRIGGER refe_bi before insert on refe for each row
begin
null;
end;
/

ALTER TRIGGER "NEWAPP"."REFE_BI" ENABLE;

ALTER TRIGGER "NEWAPP"."REFE_BI"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
CREATE TRIGGER "NEWAPP"."ABSO_BI"  before insert on appu.abso for each row <<<
begin
null;
end;
/

ALTER TRIGGER "NEWAPP"."ABSO_BI" ENABLE;

ALTER TRIGGER "NEWAPP"."ABSO_BI"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
CREATE TRIGGER refe_bd before delete on refe for each row
begin
null;
end;
/

ALTER TRIGGER "NEWAPP"."REFE_BD" ENABLE;

ALTER TRIGGER "NEWAPP"."REFE_BD"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
[oracle@lax:db01]/home/oracle
$

Thank goodness for sed !!!

Remove schema reference using sed:

  • sed -i ‘s/appu\.//g’ /tmp/metadata.sql
  • sed -i ‘s/\”NEWAPP\”\.//g’ /tmp/metadata.sql

Why did I remove “NEWAPP” when it’s the correct schema?

Just to show you sed function.

[oracle@lax:db01]/home/oracle
$ sed -i 's/appu\.//g' /tmp/metadata.sql
[oracle@lax:db01]/home/oracle
$ sed -i 's/\"NEWAPP\"\.//g' /tmp/metadata.sql
[oracle@lax:db01]/home/oracle
$ cat /tmp/metadata.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/PROCEDURE/PROCEDURE
-- CONNECT NEWAPP
CREATE procedure abso_proc as
l_id pls_integer;
begin
select id into l_id from abso;
end;
/
CREATE procedure refe_proc as
l_id pls_integer;
begin
select id into l_id from refe;
end;
/
-- new object type path: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ALTER PROCEDURE "ABSO_PROC"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED
PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
REUSE SETTINGS TIMESTAMP '2013-07-13 10:24:27'
/
ALTER PROCEDURE "REFE_PROC"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED
PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'

REUSE SETTINGS TIMESTAMP '2013-07-13 10:24:28'
/
-- new object type path: SCHEMA_EXPORT/VIEW/VIEW
CREATE FORCE VIEW "ABSO_V" ("ID") AS
select id from abso;
CREATE FORCE VIEW "REFE_V" ("ID") AS
select id from refe;
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
CREATE TRIGGER "ABSO_BD" before delete on abso for each row
begin
null;
end;
/

ALTER TRIGGER "ABSO_BD" ENABLE;

ALTER TRIGGER "ABSO_BD"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
;
CREATE TRIGGER refe_bi before insert on refe for each row
begin
null;
end;
/

ALTER TRIGGER "REFE_BI" ENABLE;

ALTER TRIGGER "REFE_BI"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
;
CREATE TRIGGER "ABSO_BI" before insert on abso for each row
begin
null;
end;
/

ALTER TRIGGER "ABSO_BI" ENABLE;

ALTER TRIGGER "ABSO_BI"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
;
CREATE TRIGGER refe_bd before delete on refe for each row
begin
null;
end;
/

ALTER TRIGGER "REFE_BD" ENABLE;

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

1 Comment »

  1. So true Michael. As a good practice check dba_views dba_triggers, dba_source before expdp/impdp . Learned that from Luke :) .
    Regards
    GregG

    Comment by Greg — July 14, 2013 @ 7:25 am | Reply


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 318 other followers

%d bloggers like this: