Hi Listers,
why does
DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM,'EMIT_SCHEMA',
FALSE);
not work for get_ddl on Materialized View Logs, or am I missing something?
I tried this:
<...>
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM,'EMIT_SCHEMA',
FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
-- Dynamic cursor for big loop over all MV Logs is defined here
for rec in (
select LOG_OWNER, LOG_TABLE
from dba_mview_logs
where log_owner = upper(USERNAME)
)
-- now loop over all MV Logs matching above WHERE condition
loop
dbms_output.put_line ('-- '||rec.log_owner ||'.'|| rec.log_table);
-- make DDL CREATE string
DDL_MV_LOG :=
dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG',rec.log_table,rec.log_owner);
dbms_output.put_line (DDL_MV_LOG);
dbms_output.put_line
('----------------------------------------------------------------------');
end loop;
<...>
and get:
CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."RACK"
PCTFREE 10 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "IWACS"
WITH PRIMARY KEY, ROWID EXCLUDING NEW VALUES
but I would expect:
CREATE MATERIALIZED VIEW LOG ON "RACK"
PCTFREE 10 PCTUSED 30 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "IWACS"
WITH PRIMARY KEY, ROWID EXCLUDING NEW VALUES
As you can see, all the other SESSION_TRANSFORM keys I did set, worked well:
PRETTY, SQLTERMINATOR, STORAGE.... but EMIT_SCHEMA "false" does not.
A few lines of code later (same concept of implicit cursor etc.), I extract the
DDL of the materialized view itself, and wow, there EMIT_SCHEMA "false" is
respected:
Code:
<...>
DDL_MV := dbms_metadata.get_ddl('MATERIALIZED_VIEW',rec.object_name,rec.owner);
<...>
Result:
CREATE MATERIALIZED VIEW "MAT_ABCFEFINNODEVIEW" <...>
Did one of you ever got across this? Is there an explanation, or is it just in
my head? ;)
Thank you very much in advance, much appreciated!
Best regards
--
Martin Klier | Performing Databases GmbH
Managing Partner | Senior DB Consultant
Oracle ACE
martin.klier@xxxxxxxxxxxxxxxxx | https://www.performing-databases.com ;