- or
No existing idea results
- ~ No ideas found ~
129 results found
-
Synonym Handling w/ "Exclude Schema Names" option Enabled
With the “Exclude Schema Names” option enabled, SCO identifies false-positive mismatches for public synonyms.
In Oracle public synonym DDL w/o owner specified defaults the object owner to current user.
CREATE PUBLIC SYNONYM foo_bar FOR bar
/SELECT *
FROM allsynonyms
WHERE synonymname = 'FOO_BAR'
/SCO happily generates the Public synonym DDL w/o the owner specified (and it can be executed as such). However, on subsequent comparisons the underlying object’s qualified name is used and the public synonyms are all identified as being different.
e.g. CREATE OR REPLACE PUBLIC SYNONYM foobar FOR myschema.bar vs. CREATE OR REPLACE PUBLIC SYNONYM foo_bar FOR barThis behavior feels like a bug.
Suggesting a fix:
Option 1
- Ignore the current schema owner value (source / target side) if the “Exclude Schema Names” option is selected and the target has no (or a different) owner on the target-side DDL
(This option seems more straightforward, but the implicit ambiguity could be considered a bit risky – esp. since the SCO generated public synonym DDL uses CREATE OR REPLACE PUBLIC SYNONYM)Option 2
- One could also make the argument that public synonyms are a bit of a special case – because they are PUBLIC / DB instance wide.
Perhaps the default behavior ought to be for developers to be specific about the public synonym object ownership. Taking this approach, it may make sense to have a special configuration setting specific to excluding the schema name for the public synonym underlying object ownership (i.e. this would support moving objects cross schema while maintaining the public synonym – e.g. myschemav1 to myschemav2).Additional considerations for either solution:
- Synonyms (public or private) are a bit of a special case because you can declare synonyms for another user’s objects.
- Public synonyms are even more of a special case because there is a single instance of the synonym name which is owned by PUBLIC – i.e. there is only one source of truth for the PUBLIC synonym at any point in time.
- SCO's use of the "OR REPLACE" caluse for PUBLIC (global) synonyms is potentially problematic as this can result in an unintentional repointing of the object a synonym points to (or evrn pointing a public synonym to a non-existant object) -- w/o warning!
-- Omitting the "OR REPLACE" clause would result in an error if the PUBLIC synonym already existed
- Also consider the deployment models:
-- Running deployments as SYSTEM (or an execution / super-user account w/ multi-schema – CREATE ANY * - privs)
--- in this case you will most certainly want / need to specify a qualified schema owner for nearly every type of object including synonyms (public or private).
-- Running deployments where the user doing the deployment is the object owner (i.e. not using SYSTEM or a multi-schema privileged user)
--- in this case the need to explicitly identify the object owner is somewhat lessend (assuming current user / deployer owne the object referenced by the synonym), but only somewhat as a synonym can be declared for another schema's objectsHere are some examples:
If deployment user is:
- “SCHEMA1”
-- CREATE OR REPLACE PUBLIC SYNONYM foobar FOR bar (is FOR SCHEMA1.bar - not SYSTEM.bar)
- “SCHEMA2”
-- CREATE OR REPLACE PUBLIC SYNONYM foobar FOR bar (is FOR SCHEMA2.bar - not SYSTEM.bar)“SCHEMA1”
-- CREATE OR REPLACE PUBLIC SYNONYM foobar FOR SCHEMA_2.bar (schema owner is qualified; this is allowed – as I can create a public synonym for another user’s objects)
--- HOWEVER -- this is where the addition of the OR REPLACE clause is actually a bit dangerous, because I can effectively “hi-jack” / repoint a public synonym w/o warning
--- Using only the CREATE PUBLIC SYNONYM… I’d get an error alerting me that the name was already in use and I’d need to resolve that conflict before continuing
-- May be a good idea to force an explicit drop and re-create – or at least make the inclusion of “ OR REPLACE” clause and optional behavior for the public synonyms?“SYSTEM” (or multi-schema privileged user)
-- CREATE OR REPLACE PUBLIC SYNONYM foobar FOR <schemaowner>.bar (will most certainly require a schema owner name, as it is not a good practice to set-up your objects under the SYSTEM user. Likewise you wouldn’t want your deployment user account to be the designated object owner of any unqualified object names the public synonyms point to)
(In this case there is even a greater risk of unqualified object names resulting in a public synonym that points to a non-existant objects -- e.g. SYSTEM.bar)
In Summary:
- Probably best practice to encourage the use of qualified object names for synonyms (public or private)
- May want to option toggle the use of the OR REPLACE clause for public synonyms
-- safest to default to forcing a drop / recreate of a public synonym if the name is already in use
-- some use cases may want to leverage the OR REPLACE clause (see next item), but that may be more of be a special use case (i.e. an override of default behavior)
- There may be some use cases where users intentionally want to omit the schema name of the underlying object, to facilitate re-pointing the underlying object a public synonym resolves toWith the “Exclude Schema Names” option enabled, SCO identifies false-positive mismatches for public synonyms.
In Oracle public synonym DDL w/o owner specified defaults the object owner to current user.
CREATE PUBLIC SYNONYM foo_bar FOR bar
/SELECT *
FROM allsynonyms
WHERE synonymname = 'FOO_BAR'
/SCO happily generates the Public synonym DDL w/o the owner specified (and it can be executed as such). However, on subsequent comparisons the underlying object’s qualified name is used and the public synonyms are all identified as being different.
e.g. CREATE OR REPLACE PUBLIC SYNONYM foobar FOR myschema.bar vs. CREATE OR…1 vote -
GIT should not download the entire repository
When I look at RedGates local files, it has downloaded my whole solution. Shouldn't it just be getting the files in the database folder I specified? For bigger projects this takes a long time.
1 vote -
Synonym Ignore bug
When entering ignore mask into PublicSynonym section, the ignore does not work - all public synonyms are still compared. But if the same ignore mask is entered into Synonym section, public synonyms are affected.
1 vote -
wildcard
Please add advanced filtering in Schema Compare. E.g. wildcard or regex matches to only show objects matching a certain criteria.
3 votes -
Modify Oracle Data Compare to display table name when /verbose option is used on the command line
The current command line option runs but produces no output until the entire compare is completed, the GUI tells you what table it is on and the % complete. The batch process should be able to do the same.
1 vote -
Extend SQL parser to read scripts generated by other tools
If the SQL parser could read DDL generated by other tools (e.g. data modeling tools), then the schema comparison could find differences between the schema as defined by those tools versus a database.
1 vote -
Exclude system-generated interval partitions from comparison and DDL
For background on Oracle's interval partitioning feature, see chapter 4 of Oracle Database Concept for release 12.2.
Our practice is to create interval-partitioned tables with the minimum number of partitions necessary to create the table (usually just one partition), and let Oracle create all others as data arrives requiring them. Between test, QA, and production databases, data arrives at different times, giving rise to automatic creation of different partitions. But because these differences are only a side-effect of different contents, they are not relevant, in the same way that differences in system-generated constraint names are not relevant. Changing the partition key or the interval size would require a rebuild.
Any time there is a CREATE TABLE command for an interval-partitioned table, the command should be limited to only those partitions that are needed to establish the table. In other words, exclude those partitions where DBATABPARTITIONS.INTERVAL='YES', because they are system-generated. The CREATE TABLE command should explicitly list only those partitions where INTERVAL='NO';
An an example, I have an interval-partitioned table named FMEMBERTENUREMMSNAPSHOT. These are the partitions in my test database:
C911233@TCRDS_SACRDSD2R.ssdc.kp.org> l
1 select partitionname, partitionposition, highvalue, interval
2 from dbatabpartitions
3 where tablename='FMEMBERTENUREMMSNAPSHOT'
4 and tableowner='COBIDM'
5* order by partitionposition
C911233@TCRDSSACRDSD2R.ssdc.kp.org> /PARTITIONNAME PARTITIONPOSITION HIGH_VALUE INTERVAL
PARTEMPTY 1 190001 NO
SYSP937 2 201310 YES
SYSP968 3 201311 YES
SYSP966 4 201312 YES
SYSP973 5 201313 YES
SYSP933 6 201402 YES
SYSP970 7 201403 YES
SYSP967 8 201404 YES
SYSP931 9 201405 YES
SYSP932 10 201406 YES
SYSP935 11 201407 YES
SYSP977 12 201408 YES
<skipping many lines for brevity>
SYSP3318 63 201811 YES
SYSP3554 64 201812 YESIf there is a need to generate a CREATE TABLE command, all I really need is shown below, because it is sufficient to establish the table as indicated by INTERVAL='NO' in the query above:
CREATE TABLE cobidm.rgtemp11090455980 (
fmbrtenuremmsnpshtik NUMBER NOT NULL,
dmbrprmyhrnik NUMBER NOT NULL,
<many columns omitted>
crdsprcscntlupdid NUMBER NOT NULL,
CONSTRAINT RGTEMP11090455981 PRIMARY KEY (fmbrtenuremmsnpshtik) USING INDEX (CREATE UNIQUE INDEX cobidm.rgtemp11090455982 ON cobidm.rgtemp11090455980(fmbrtenuremmsnpshtik)
NOCOMPRESS
TABLESPACE cobimediumindex),
CONSTRAINT RGTEMP11090455983 UNIQUE (srcmbrprmyhrnid,srcsnpshtmmnb) USING INDEX (CREATE UNIQUE INDEX cobidm.rgtemp11090455984 ON cobidm.rgtemp11090455980(srcmbrprmyhrnid,srcsnpshtmmnb)
NOCOMPRESS
TABLESPACE cobimediumindex)
) TABLESPACE cobihcc
PARTITION BY RANGE (dsnpshtmmik) INTERVAL (1)
(PARTITION partempty VALUES LESS THAN (190001)
INDEXING ON
TABLESPACE cobi_hcc COLUMN STORE COMPRESS FOR QUERY LOW)
PARALLEL 4;
;What I am getting today is shown below. This has the effect of forcing the creation of all of these partitions whether I have data that needs them or not, which therefore does not follow my model definition. I have to edit the script to remove the unnecessary partitions.
--
-- Script generated by Schema Compare for Oracle 4.0.10.528 on 16/11/2018 07:34:27
SET DEFINE OFF
SET SQLBLANKLINES ONCREATE TABLE cobidm.rgtemp11090455980 (
fmbrtenuremmsnpshtik NUMBER NOT NULL,
dmbrprmyhrnik NUMBER NOT NULL,
<skipping columns for bevity>
crdsprcscntlupdid NUMBER NOT NULL,
CONSTRAINT RGTEMP11090455981 PRIMARY KEY (fmbrtenuremmsnpshtik) USING INDEX (CREATE UNIQUE INDEX cobidm.rgtemp11090455982 ON cobidm.rgtemp11090455980(fmbrtenuremmsnpshtik)
NOCOMPRESS
TABLESPACE cobimediumindex),
CONSTRAINT RGTEMP11090455983 UNIQUE (srcmbrprmyhrnid,srcsnpshtmmnb) USING INDEX (CREATE UNIQUE INDEX cobidm.rgtemp11090455984 ON cobidm.rgtemp11090455980(srcmbrprmyhrnid,srcsnpshtmmnb)
NOCOMPRESS
TABLESPACE cobimediumindex)
) TABLESPACE cobihcc
PARTITION BY RANGE (dsnpshtmmik) INTERVAL (1)
(PARTITION partempty VALUES LESS THAN (190001)
INDEXING ON
TABLESPACE cobihcc COLUMN STORE COMPRESS FOR QUERY LOW,
PARTITION VALUES LESS THAN (201310)
INDEXING ON
TABLESPACE cobimediumdata NOCOMPRESS,
PARTITION VALUES LESS THAN (201311)
INDEXING ON
TABLESPACE cobimediumdata NOCOMPRESS,
PARTITION VALUES LESS THAN (201312)
INDEXING ON
TABLESPACE cobimedium_data NOCOMPRESS,<skipping many lines for brevity>
PARTITION VALUES LESS THAN (201811)
INDEXING ON
TABLESPACE cobimediumdata NOCOMPRESS,
PARTITION VALUES LESS THAN (201812)
INDEXING ON
TABLESPACE cobimediumdata NOCOMPRESS)
PARALLEL 4;For background on Oracle's interval partitioning feature, see chapter 4 of Oracle Database Concept for release 12.2.
Our practice is to create interval-partitioned tables with the minimum number of partitions necessary to create the table (usually just one partition), and let Oracle create all others as data arrives requiring them. Between test, QA, and production databases, data arrives at different times, giving rise to automatic creation of different partitions. But because these differences are only a side-effect of different contents, they are not relevant, in the same way that differences in system-generated constraint names are not relevant. Changing the partition…
4 votes -
Pre-script introduction in the scheme compare deployment script.
Hi , I am trying to understand how to introduce a pre-script ALTER SESSION SET CURRENT_SCHEMA = <> SQL statement in the top of the deployment script which is generated during the schema compare either between source DB vs target DB or TFS vs Target DB.
4 votes -
DB or Version Control URL update functionality
There is no functionality to update an existing project GIT repository or DB URL.
I have to update it under :
ConnectionStore.xml and LinkedDatabase.xml
1 vote -
scripts break out
The scripts can become quite large when comparing tables. Need a way to make the scrips smaller - multiple outputs for same table - in order to have a script that is runnable in sql. For example - when comparing a table that generates 50K records the output script is not editable by most editors - need to break it out in multiple files.
1 vote -
Your Oracle connection via Wallet authentication doesn't appear to work properly.
It's less an idea than a complaint. I'm a seasoned DBA working with a large number of developers. Developers do not have elevated privilege access to our QA or production environments. I'm wanting to set up a wallet so this tool can access those environments without requiring login/password information. I've setup sqlnet.ora and ldap.ora (we don't use tnsnames.ora) and can connect via the wallet entry using SQL*Plus on my local machine without issue. However, your product (Schema Compare) fires off an ORA-01017 (invalid login/password). Yet when I use normal credentialed login procedures via Schema Compare, the test works just fine. I don't know where the disconnect is, but it should be looked at. Considering I'm doing an evaluation for a CI/CD project company-wide, this problem unfortunately may cause us to not use this tool.
It's less an idea than a complaint. I'm a seasoned DBA working with a large number of developers. Developers do not have elevated privilege access to our QA or production environments. I'm wanting to set up a wallet so this tool can access those environments without requiring login/password information. I've setup sqlnet.ora and ldap.ora (we don't use tnsnames.ora) and can connect via the wallet entry using SQL*Plus on my local machine without issue. However, your product (Schema Compare) fires off an ORA-01017 (invalid login/password). Yet when I use normal credentialed login procedures via Schema Compare, the test works just fine.…
1 vote -
separate type spec and type body
Please separate type specifications and type bodies. (e.g. "CREATE TYPE FOO AS OBJECT" and "CREATE OR REPLACE TYPE BODY FOO"). Most of the time our changes are only to the body but Schema Compare will try to recreate the spec on deployment even if the spec has not changed.
This will fail if it has dependencies and make automated builds fail when using the Deployment Suite for Oracle with a build system.
4 votes -
Sorting Database Objects in Sync script
When we create a syncronization script we have diffrent order of objects from time to time. It is not very good for us, because it is hard to find out changes.
Could you please make that the order does not change (for example in alphabetical order).12 votes -
BUG!!!!
This has been an issue with Oracle tools for some time.
A table with a Generated column has a bug which inserts an extra right parentheses in its definition. Can be seen in Source Control as well as Schema Compare.
ColumnName NUMBER(1) GENERATED ALWAYS AS (NVL2("ForeignKey",0,1)) NOT NULL,
tool generates:
ColumnName NUMBER(1) GENERATED ALWAYS AS (NVL2("ForeignKey",0,1))) NOT NULL,
2 votes -
Integrate the Oracle Tools with Oracle SQL Developer
Looking for feedback from our Oracle Users if they would like integration with Oracle SQL Developer.
Launch the tools from within the IDE.
Or Perform Source Control for Oracle Check-in or Get Latest process from within the IDE.Please comment or vote on this topic.
12 votesAs well as your votes, please also add a comment to the issue describing your need in more detail so we understand what the integration should entail.
-
Repository Change History is not Respected
On git when new Features are merged into Develop Branch , Source Control take Database Schema as Good and not the new changes on Develop. If like don't considerate last DDL TIme of an object to diff between new and old.
One example are Hot Fix merged on Develop branch, when a horfix was resolved and you are going to integrate changes in Develop Database, Source Control consider Develop Database as Good schema and not the new Hot Fix Change merged into Develop Branch. Generating Check In changes and not Get In Updates.
3 votes -
Source Control feature for Mercurial Kiln
Please add an option to directly integrate Source Control with the Kiln Repository. We could see no option other than GIT, Team, SVN etc.,
3 votesThe recommendation for this scenario is to use the “link to working folder” option in Source Control for Oracle.
-
REFRESH START WITH - Ignore for Materialized Views (Not just START WITH)
A Nusiance trying to refresh and always comes up..
1 vote -
Update connections to support TLS 1.1 and 1.2
The lib's for external git connections do not use the more secure TLS connection protocols. Please update so users in security conscious environments can connect to secure servers that do not support SSLv3 and TLS v1.0
4 votes -
Comparison Options - materialized view START WITH VALUE
Ignore Section
Should be "REFRESH START WITH value" for Oracle 12.2.0.1 OR "START WITH value.." in other versions.
1 vote
- Don't see your idea?