DBMS_METADATA transform EMIT_SCHEMA not respected w/ MV Logs
Date: Tue, 22 Aug 2017 15:34:15 +0200 (CEST)
Message-ID: <2070237274.2406.1503408855159.JavaMail.zimbra_at_performing-db.com>
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_at_performing-db.com | https://www.performing-databases.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 22 2017 - 15:34:15 CEST