Re: remapping output from dbms_metadata.get_ddl
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: spikey.mcmarbles@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Feb 2010 10:18:18 -0800 (PST)
You created the materialized view in the USERS tablespace so why are you trying
to transform ONLINE_TBS?
Should you not be coding this instead:
SQL> begin
2 dbms_metadata.set_remap_param(
3 dbms_metadata.session_transform,
4 'REMAP_TABLESPACE',
5 'USERS', 'FOO');
6 end;
7 /
David Fitzjarrell
________________________________
From: Adric Norris <spikey.mcmarbles@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Fri, February 19, 2010 12:47:43 PM
Subject: remapping output from dbms_metadata.get_ddl
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:
