Storage Clauses not being generated in Delta Script

jonathan_rush · March 26, 2019, 12:58pm

Hi,
I am using Toad DevOps Toolkit 1.2 to perform a schema compare of two oracle database schemas. In my PowerShell script, I have selected the option to include all storage options in the comparison:

$TDT.CompareSchemas.StorageOptions.IncludeAll()

The generated delta script, however, DOES NOT include the compression clause. If I run the same comparison using TOAD Automation Designer (again with all Storgae Clause Options selected) then the generated delta script DOES correctly include the compression clause.

Am I doing something wrong or is this a bug ?

Thanks in advance for your help,

Jonathan.

JohnDorlon · March 26, 2019, 1:08pm

I don't see any problems in the source.... does it work if you do this after the include all?

$TDT.CompareSchemas.StorageOptions.Compress = $TRUE;

jonathan_rush · March 26, 2019, 1:11pm

I have tried that aswell and it still doesn't include the compression clause.

JohnDorlon · March 26, 2019, 1:40pm

I have a pair of schemas that I use a lot for schema compare testing.
COMP1 and COMP2. This was my test:

(starting with empty schemas)

create table comp1.tables as select * from dba_tables;
create table comp2.tables as select * from dba_tables;
alter table comp2.tables compress;

Then run the following in powershell:

# Start TDT
Write-Host 'Starting TDT'
$TDT = New-Object -ComObject 'Toad.ToadAutoObject'    


#-- Make Connections to Source and Target
Write-Host 'Connecting to Source and Target DBs'
$Source = $TDT.Connections.NewConnection("comp1/comp1@azure_12cr2_plug")
$Target = $TDT.Connections.NewConnection("comp2/comp2@azure_12cr2_plug")


# Set Schema Compare Parameters
Write-Host 'Setting Schema Compare Parameters'
$TDT.CompareSchemas.StorageOptions.IncludeAll()
$TDT.CompareSchemas.TypeOptions.IncludeAll() 
$TDT.CompareSchemas.ObjectTypes.IncludeAll()
$TDT.CompareSchemas.ObjectTypes.SynonymsPrivateForSchema = $FALSE # can only be true when source and target schemas are in different databases.
$TDT.CompareSchemas.ObjectTypes.SynonymsPublic           = $FALSE # can only be true when source and target schemas are in different databases.


#-- Setting Source Connection Information
$TDT.CompareSchemas.Source.Connection             = $Source      
$TDT.CompareSchemas.Source.Schema                 = "COMP1"
$TDT.CompareSchemas.Source.SnapshotOutput         = "c:\Temp\COMP1.XML"


#-- Setting Target Connection Information
$TDT.CompareSchemas.Target.Connection             = $Target
$TDT.CompareSchemas.Target.Schema                 = "COMP2"
$TDT.CompareSchemas.Target.SnapshotOutput         = "c:\Temp\COMP2.XML"

# Running Schema Compare
Write-Host 'Executing Schema Compare...'
$TDT.CompareSchemas.Execute()

# Saving Sync Script to File.
$syncScript = $TDT.CompareSchemas.GetScript()
$SyncScript | Out-File -FilePath "C:\Temp\SyncScript.sql" -Force

# Saving Diff Details report
$DifferenceDetailsHTML = $TDT.CompareSchemas.GetDifferenceDetailsHTML()
$DifferenceDetailsHTML | Out-File -FilePath "C:\Temp\DiffDetails.html" -Force  



# Quit TDT
Write-Host 'Stopping TDT'
$TDT.Quit()

and my sync script looked like:

--------------------------------------------------------------------------
-- Run this script in COMP2@AZURE_12CR2_PLUG to make it look like COMP1@AZURE_12CR2_PLUG.
--
-- Please review the script before using it to make sure it won't cause any unacceptable data loss.
--
-- COMP2@AZURE_12CR2_PLUG schema extracted by user COMP2
-- COMP1@AZURE_12CR2_PLUG schema extracted by user COMP1

ALTER TABLE TABLES 
  NOCOMPRESS ;

JohnDorlon · March 26, 2019, 2:40pm

Hmm...good news and bad news...
Good: I was able to reproduce it in TDT 1.2 using the script above
Bad: I was able to reproduce it in TDT 1.2 using the script above

Seems this was fixed at some point for next version.

jonathan_rush · March 26, 2019, 3:19pm

Okay, so there should be a fix for this in TDT 1.3. Do we know when that will be released ?

JohnDorlon · March 26, 2019, 4:42pm

Hey Jonathan - backing up a bit....I am able to get tdt 1.2 to produce compress/nocompress clauses in schema compare for tables with the test above. I misspoke earlier when I said I could reproduce the problem of it being broken in 1.2.

Can you give me some more details about your scenario?

jonathan_rush · March 27, 2019, 7:32am

Hi John,

So I have a source database with table defined as follows - this table DOES NOT exist in the target database:

CREATE TABLE LND_016_T1
(
DATADLVRY_ID NUMBER(19),
RECORD_ID NUMBER(19),
SNAPSHOT_ID NUMBER(19),
CONSUMER_ID NUMBER(19),
CONSUMER_NAME VARCHAR2(255 CHAR),
MASTER_DATA_ID NUMBER(19),
MASTER_DATA_NAME VARCHAR2(255 CHAR),
SDL_ID NUMBER(19),
CODE VARCHAR2(255 CHAR),
NAME VARCHAR2(255 CHAR),
DISPLAY_NAME VARCHAR2(255 CHAR),
REPORTING_NAME VARCHAR2(255 CHAR),
STATUS VARCHAR2(50 CHAR),
DEFINITION VARCHAR2(4000 CHAR),
DESCRIPTION VARCHAR2(4000 CHAR),
ACTIVE_INDICATOR VARCHAR2(8 CHAR),
LEVEL_NO NUMBER(19),
PARENT_CODE VARCHAR2(255 CHAR),
ACTIVE_FROM_DATE DATE,
ACTIVE_TO_DATE DATE,
ISO_CODE VARCHAR2(50 CHAR),
DOMAIN_CODE VARCHAR2(255 CHAR),
BUSINESS_OWNER VARCHAR2(255 CHAR),
UPDATED_BY VARCHAR2(50 CHAR),
SORT_ORDER VARCHAR2(10 CHAR),
COMMENTS VARCHAR2(4000 CHAR)
)
COLUMN STORE COMPRESS FOR QUERY HIGH
TABLESPACE TS_GRM_LND
PARTITION BY RANGE (DATADLVRY_ID)
INTERVAL( 1)
(
PARTITION P0 VALUES LESS THAN (1)
COLUMN STORE COMPRESS FOR QUERY HIGH
TABLESPACE TS_GRM_LND,
PARTITION VALUES LESS THAN (9)
COLUMN STORE COMPRESS FOR QUERY HIGH
TABLESPACE TS_GRM_LND,
PARTITION VALUES LESS THAN (117)
COLUMN STORE COMPRESS FOR QUERY HIGH
TABLESPACE TS_GRM_LND
);

CREATE INDEX PK016T1 ON LND_016_T1
(DATADLVRY_ID, RECORD_ID)
LOCAL;

CREATE UNIQUE INDEX UQ_PK_016T1 ON LND_016_T1
(DATADLVRY_ID, SDL_ID)
LOCAL;

When I run Toad Automation Designer, to generate a delta script, it produces a script that contains the following statement in which the COMPRESSION clause is visible:

Prompt Table LND_016_T1;
CREATE TABLE LND_016_T1
(
DATADLVRY_ID NUMBER(19),
RECORD_ID NUMBER(19),
SNAPSHOT_ID NUMBER(19),
CONSUMER_ID NUMBER(19),
CONSUMER_NAME VARCHAR2(255 CHAR),
MASTER_DATA_ID NUMBER(19),
MASTER_DATA_NAME VARCHAR2(255 CHAR),
SDL_ID NUMBER(19),
CODE VARCHAR2(255 CHAR),
NAME VARCHAR2(255 CHAR),
DISPLAY_NAME VARCHAR2(255 CHAR),
REPORTING_NAME VARCHAR2(255 CHAR),
STATUS VARCHAR2(50 CHAR),
DEFINITION VARCHAR2(4000 CHAR),
DESCRIPTION VARCHAR2(4000 CHAR),
ACTIVE_INDICATOR VARCHAR2(8 CHAR),
LEVEL_NO NUMBER(19),
PARENT_CODE VARCHAR2(255 CHAR),
ACTIVE_FROM_DATE DATE,
ACTIVE_TO_DATE DATE,
ISO_CODE VARCHAR2(50 CHAR),
DOMAIN_CODE VARCHAR2(255 CHAR),
BUSINESS_OWNER VARCHAR2(255 CHAR),
UPDATED_BY VARCHAR2(50 CHAR),
SORT_ORDER VARCHAR2(10 CHAR),
COMMENTS VARCHAR2(4000 CHAR)
)
COLUMN STORE COMPRESS FOR QUERY HIGH
NO INMEMORY
TABLESPACE TS_GRM_LND
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (DATADLVRY_ID)
INTERVAL( 1)
(
PARTITION P0 VALUES LESS THAN (1)
NO INMEMORY
LOGGING
COLUMN STORE COMPRESS FOR QUERY HIGH
TABLESPACE TS_GRM_LND
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION VALUES LESS THAN (9)
NO INMEMORY
LOGGING
COLUMN STORE COMPRESS FOR QUERY HIGH
TABLESPACE TS_GRM_LND
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION VALUES LESS THAN (117)
NO INMEMORY
LOGGING
COLUMN STORE COMPRESS FOR QUERY HIGH
TABLESPACE TS_GRM_LND
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCACHE
RESULT_CACHE (MODE DEFAULT)
NOPARALLEL
MONITORING;

I then run TDT with a PowerShell script in which the Storage options are set as follows (I explicity set the compression option):

$TDT.CompareSchemas.StorageOptions.IncludeAll()
$TDT.CompareSchemas.StorageOptions.Compress = $true

This produces a delta script that contains the following statement in which the COMPRESSION clause is NOT generated.

Prompt Table LND_016_T1;
CREATE TABLE LND_016_T1
(
DATADLVRY_ID NUMBER(19),
RECORD_ID NUMBER(19),
SNAPSHOT_ID NUMBER(19),
CONSUMER_ID NUMBER(19),
CONSUMER_NAME VARCHAR2(255 CHAR),
MASTER_DATA_ID NUMBER(19),
MASTER_DATA_NAME VARCHAR2(255 CHAR),
SDL_ID NUMBER(19),
CODE VARCHAR2(255 CHAR),
NAME VARCHAR2(255 CHAR),
DISPLAY_NAME VARCHAR2(255 CHAR),
REPORTING_NAME VARCHAR2(255 CHAR),
STATUS VARCHAR2(50 CHAR),
DEFINITION VARCHAR2(4000 CHAR),
DESCRIPTION VARCHAR2(4000 CHAR),
ACTIVE_INDICATOR VARCHAR2(8 CHAR),
LEVEL_NO NUMBER(19),
PARENT_CODE VARCHAR2(255 CHAR),
ACTIVE_FROM_DATE DATE,
ACTIVE_TO_DATE DATE,
ISO_CODE VARCHAR2(50 CHAR),
DOMAIN_CODE VARCHAR2(255 CHAR),
BUSINESS_OWNER VARCHAR2(255 CHAR),
UPDATED_BY VARCHAR2(50 CHAR),
SORT_ORDER VARCHAR2(10 CHAR),
COMMENTS VARCHAR2(4000 CHAR)
)
TABLESPACE TS_GRM_LND
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (DATADLVRY_ID)
INTERVAL( 1)
(
PARTITION P0 VALUES LESS THAN (1)
TABLESPACE TS_GRM_LND
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
),
PARTITION VALUES LESS THAN (9)
TABLESPACE TS_GRM_LND
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION VALUES LESS THAN (117)
TABLESPACE TS_GRM_LND
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 8M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
);

Let me know if you need any more information?

Thanks,

Jonathan.

jonathan_rush · March 27, 2019, 9:47am

Further update: on closer analysis I see that also the LOGGING and the DEGREE (PARALLEL / NOPARALLEL) that are present in the TOAD Automation Designer script are also missing from the TDT generated script (you can see this in the scripts of my previous post). It therefore seems to be a general problem with the storage options. I have updated the title of this thread accordingly.

JohnDorlon · March 27, 2019, 2:12pm

Thank you, that was very helpful. I found the problem for those and a couple other storage parameters.

This is fixed for next version. Also, I am sending you a direct message here in Toad World so please check there.

capitalinfo8 · October 3, 2019, 11:20am

Hi,
I am also using Toad DevOps Toolkit 1.2. I am not getting any problem.

Topic		Replies	Views
Generate schema script - storage option Toad for Oracle - Beta	3	872	August 31, 2020
Question about Schema Compare with Devops Toolkit Toad DevOps Toolkit	5	1492	March 5, 2018
Setting tablespace size thresholds for Generate Schema Script Toad for Oracle & DevOps Toolkit	4	521	April 2, 2012
Generate schema script, MAXSIZE script option Toad for Oracle - Beta	3	800	October 12, 2015
FW: Toad-SS - Release 5.0.2.612 faults on Schema compare Script Generation Toad for SQL Server	3	768	January 13, 2011

Storage Clauses not being generated in Delta Script

Related topics