remapping output from dbms_metadata.get_ddl
- From: Adric Norris <spikey.mcmarbles@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Feb 2010 11:47:43 -0600
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.
Other related posts:
