Hello All,
Our developers us Toad to generate DDL to check into version control.
We are currently using toad 16.3 and 17.0
We want to run our integrity checker against stateful objects. The same way we do stateless objects.
I am having trouble matching the toad output. I assume Toad has its proprietary code doing a lot of cleanup behind the scenes to generate the output.
I picked a complicated table out the gate so I didnt get excited thinking this would be easy.
I was trying to fix the table ddl to share it but there was so much to remove and foreign key references but here are a few things Im trying to iron out.
We dont want to have indexs grouped with table ddl. I say that because if there is a performance issue and an index needs to be added on the fly we would then have an integrity checker warning till its removed or a new release is done and we dont use it but Oracle has the auto indexing feature.
TOAD Script options:
How can I remove below from the table ddl with the script options?
CREATE BITMAP INDEX SURG_USAGE_IX03 ON SURG_USAGE
(TRN_MTH)
TABLESPACE L1_IDX
LOCAL (
PARTITION FY2014
TABLESPACE L1_IDX
(SUBPARTITION FY2014_M_04
TABLESPACE L1_IDX,
the columns in Toad output remove precision if its 0. This is an easy replace but didnt know your thoughts. both seem fine to me so not a big deal to ignore.
SURG_USAGE_SEQ_ID NUMBER(11),
Oracle ddl output
SURG_USAGE_SEQ_ID NUMBER(11,0),
I started working on this yesterday so forgive the ugly multiple replaces. I do plan on opening an Oracle SR about some of the things showing up in the DDL that seem like they should be removed with some of their transformation settings.
SQL> SET TRIM ON
SQL> SET TRIMSPOOL ON
SQL> SET SERVEROUT ON SIZE 100000
SQL> SET LONG 2147483
SQL> SET LONGCHUNKSIZE 300000
SQL> SET PAGESIZE 0
SQL> WHENEVER SQLERROR CONTINUE
SQL> BEGIN
2 --DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',TRUE);
3 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'EXPORT',TRUE); -- this will give sub partition DDL
4 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'COLLATION_CLAUSE','NEVER'); -- teams not using nor do we see teams using any time soon
5 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',TRUE); -- if false it will omit constraints
6 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',FALSE); -- if set to true it will it will do a seperate alter table
7 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'EMIT_SCHEMA',FALSE);
8 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'FORCE',TRUE); -- force build views
9 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
10 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PHYSICAL_PROPERTIES',FALSE);-- doesnt seem to do anything
11 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SIZE_BYTE_KEYWORD',TRUE);
12 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
13 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
14 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE); -- generates TS info
15 END;
16 /
PL/SQL procedure successfully completed.
SQL> SELECT REPLACE (
2 REPLACE (
3 REPLACE (
4 REPLACE (
5 REPLACE (
6 REPLACE (DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, OWNER), '"')
7 , ',0),', '),')
8 ,'PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255')
9 ,' NO INMEMORY')
10 ,') SEGMENT CREATION IMMEDIATE',')')
11 ,'NOCOMPRESS READ WRITE ,',',')
12 FROM DBA_OBJECTS
13 WHERE 1 = 1 AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = ('SURG_USAGE') AND OWNER IN ('SUPERCOOL')
14 /
-- Compare between EXPORT TRANS FALSE, TOAD, TRUE
I also plan on reaching out to our windows team because I know powershell has some really nice file clean up functions.
Any easy ideas on how to clean this up? I have looked into linking the team coding but having issues due to cer log ins.
Thanks
Dave
.
