remapping output from dbms_metadata.get_ddl

From: Adric Norris <spikey.mcmarbles_at_gmail.com>
Date: Fri, 19 Feb 2010 11:47:43 -0600
Message-ID: <d4beff361002190947h50fdd129h14c872e18b4f6a96_at_mail.gmail.com>

This is probably an easy question for those of you with more experience using dbms_metadata, but I'm finding the documentation a bit opaque at the moment. I'd appreciate any insight you can provide.

What I'm trying to do is extract DDL for a number of materialized views, using the get_ddl function, while remapping the tablespace name to match the eventual target database. Unfortunately the remap isn't working, and I'm not sure what I'm doing wrong. Here's a simplified example:

SQL> create materialized view mv

  2     tablespace users
  3     refresh force on demand

  4 as select * from dual;

Materialized view created.

SQL> begin

  2     dbms_metadata.set_remap_param(
  3        dbms_metadata.session_transform,
  4        'REMAP_TABLESPACE',
  5        'ONLINE_TBS', 'FOO');

  6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV') ddl from dual;

  CREATE MATERIALIZED VIEW "SYSTEM"."MV" ("DUMMY")   ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE   AS select * from dual

While I can certainly post-process the output before running it in the new database, I'd like to understand why it isn't working... will undoubtedly save me some time in the future, if nothing else.

Thanx!

[Let the onslaught of annoying out-of-office notifications begin! :P]

--

"I'm too sexy for my code." -Awk Sed Fred.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 19 2010 - 11:47:43 CST