-
1. Re: Database Diff Foreign keys order
1 person found this helpfulWe use Database difference to manage structure upgrades between dev and prod.
I would first point out that is a BAD practice to use since all code, and necessary support data (e.g. lookup table data) should be maintained in a version control system.
All changes to PROD should use a CONTROLLED process
1. check out the official code from version control
2. make your changes
3. test the changes and fix any issues
4. check in the changed code to version control
5. create a deployment script that will execute you DDL/DML in the CORRECT order and check that script into version control
6. create a 'backout' script that will UNDO any changes your deployment script made and check that script into version control
Then to deploy the changes a DBA will:
1. check out your scripts from version control
2. execute those scripts in the proper environment.
When developers create a lot of new tables with foreign keys, we have a big issue with this process
That problem goes away when you use BEST PRACTICE and follow the steps I mentioned above.
as tables seem to be created in alphabetical order, they always have some FK referencing PK on new tables not yet created.
In general there isn't ANY way for Oracle or Sql Dev to know the CORRECT order for your objects.
And objects can also have circular references which can make it IMPOSSIBLE to create the objects in an order that won't invalidate other objects. For example, a table can depend on a function while that function depends on the same or other tables.
Is there a way to ask database diff to create all fk constraints in the end of the script ? Or another way to manage this ? We perhaps don't use the right tool .
Notwithstanding what I said above that is a reasonable question to ask.
Unfortunately the issue is that the Oracle package(s) that Sql Develop uses don't include that functionality. You would need to submit an enhancement request to Oracle using your MOS account to request such changes.j
Sql Developer could add functionality to try to rearrange the order of things at the object level (e.g. all FKs after all tables) but there are no workarounds for that in the current product.
You can submit an enhancement request for that on the Oracle Sql Developer Exchange web page
https://apex.oracle.com/pls/apex/f?p=43135:1
.
-
2. Re: Database Diff Foreign keys order
The process described in my original post, is only a brief summary of our deployment process.
We use staging servers before prod to create and update scripts. Then scripts are stored etc ..
This process could certainly be better and we try to improve it each day.
Here our issue is that sqldev needs a long time to generate the database diff, and generates a script that is not useable as is.
Then some people have another well known tool that makes the diff quite quickly and generates a script which handles this issue but creating all fk after tables and pk. And they ask why we don't use it instead of sqldev
For a lot of reasons I prefer to use sqldeveloper, but this is missing and before to submit an enhancement request I prefer to ask in order to be sure that I don't miss an option.
Regards,
--
Bertrand
-
3. Re: Database Diff Foreign keys order
For a lot of reasons I prefer to use sqldeveloper, but this is missing and before to submit an enhancement request I prefer to ask in order to be sure that I don't miss an option.
I am not aware of any option to control the order of generation.
Your request is certainly reasonable to have tables created before the constraints on them.
-
4. Re: Database Diff Foreign keys order
1 person found this helpfulWe're running code that comes from the EM/change management pack features, so much of it is out of our control. But, it's possible we could influence the order of object scripting as you are looking for, but I'm not sure so I've asked our dev to take a look at this.
-
-
6. Re: Database Diff Foreign keys order
developer confirms, there's nothing more we can do here. making it better would require changes on the EM side
-
7. Re: Database Diff Foreign keys order
Thank you for trying.
I will try to find another way.
Regards,
--
Bertrand
-
8. Re: Database Diff Foreign keys order
I will try to find another way.
Just to be clear it isn't reasonable for Sql Dev to try to make any incompatible changes to code produced by other units. The only way I see that it could be done is if the output of the management pack was a CLOB or XML that would allow easy sorting by name and object type - but that starts going down/up a slippery slope.
IMHO it is a BUG if the management pack if you really do have a use case where DDL for a FK/other constraint is produced in a file PRIOR to the DDL for the main table/view/object that it is dependent on.
That execution order can NOT possible work. I haven't used that management pack for comparison but the other Oracle packages (e.g. DBMS_METADATA) produce can produce their output as CLOBS or XML. Either of those formats lends itself to ORDERING the results using code.
I can't think of any reason why the management pack code would be any different.
In particular is makes no sense that Oracle would expect a user to MANUALLY extract and rearrange code - the whole purpose of these packs is to make it EASIER to manage objects.
Please produce a SIMPLE test case (e.g. one table and constraints) that produces the objects out of order and submit it with a bug report.
-
9. Re: Database Diff Foreign keys order
One more question please Jeff,
Change management pack is part of entreprise edition.
So will I break licencing rules if I use database différences on a SE/SE2 Database ?
Regards,
--
Bertrand
-
10. Re: Database Diff Foreign keys order
1 person found this helpfulNo. The stuff SQLDev uses is exempt from the change management (and now lifecycle) management packs.
Register and Participate in Oracle's online communities. Learn from thousands of experts, get answers to your questions and share knowledge with peers.