Toad World® Forums

Export DDL - Add|Simple|Materialized View Query Returns Duplicates


#1

This is the stock query below which includes both object_ids and prompts to add the schema name to prevent duplicates. The query is editable inline so it isn’t a show stopper. The object list seems to remove the duplicates if I add the tables after the materialized views. Version 12.1.0.22.

Is it possible to use the Toad defined file extensions from Export DDL? Tables are going to *.sql. Generate Schema Scripts will generate to the defined types in the Options, but I prefer the Export DDL for the combined contents (table, constraints, and indexes).

SELECT o.owner, ‘MATERIALIZED VIEW’ OBJECT_TYPE, O.OBJECT_NAME, O.STATUS, O.LAST_DDL_TIME, null info
from DBA_SNAPSHOTS S, DBA_OBJECTS O
WHERE O.OWNER = S.OWNER
AND O.OBJECT_NAME = S.NAME
AND O.OBJECT_TYPE IN (‘TABLE’, ‘MATERIALIZED VIEW’)
AND O.OWNER = :own
and Upper(o.object_name) like ‘%’
order by 1,2;


#2

I think there are sometimes duplicate rows in dba_snapsots. We really ought to be querying against dba_mviews instead. I’ll take a look at that today.

As for exporting to the file extensions defined in options - yes, it should be doing that, but not all object types have a pre-defined extension. Those just default to .sql. I tried adding one for “Table” but I see that the mapping is not picked up by export DDL. I can take a look at this one too.