Re: remapping output from dbms_metadata.get_ddl
- From: Adric Norris <spikey.mcmarbles@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Feb 2010 15:33:25 -0600
I wasn't able to get the desired results via the get_ddl function, but
eventually got there by dropping to the lower-level APIs. Here's the
working example:
SQL> variable h number
SQL> variable th number
SQL> begin
2 :h := dbms_metadata.open('MATERIALIZED_VIEW');
3 dbms_metadata.set_filter(:h, 'SCHEMA', user);
4 dbms_metadata.set_filter(:h, 'NAME', 'MV');
5
6 :th := dbms_metadata.add_transform(:h, 'MODIFY');
7 dbms_metadata.set_remap_param(:th, 'REMAP_TABLESPACE', 'USERS',
'FOO');
8 :th := dbms_metadata.add_transform(:h, 'DDL');
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.fetch_clob(:h) 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 "FOO"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS select * from dual
SQL> exec dbms_metadata.close(:h)
PL/SQL procedure successfully completed.
Thanx!
--
"I'm too sexy for my code." -Awk Sed Fred.
Other related posts:
