To reverse engineer an existing database into a relational model, I used SQL Developer Data Modeler, a free data modeling and database design tool from Oracle.
I had a problem with the tool. I could not save the model. It appeared to be saved but when I reopened the .dmd file, the relational model was nowhere to be found.
I tried all kinds of combinations on my Windows 7 64-bit laptop, like using JDK 6 vs. JDK 7, 32 bit vs. 64 bit versions, etc. No luck.
Then I stumbled upon this Oracle Forum thread while searching for a solution online. The poster suggested that enabling support for version control in the tool solved the issue.
I had versioning support disabled in SQLdev Data Modeler.
Following the hints in the forum post, I enabled it (Tools > Preferences > Extensions > toggle Versioning Support). Restarted SQLdev Data Modeler, and voila! I can now save my relational models!
There was no way I could have guessed that versioning support was interfering with saving relational models. I am guessing this is a bug.Comments Off on When Versioning Support Interferes With Saving Your Relational Model | Filed in Oracle, Tips | Tags: data-modeler, sql-developer
ALTER TABLE sys.dual ADD (dummy2 VARCHAR2(1));
You never, never never never, as in never ever – touch any of the SYS tables – not to insert into them, not to delete from them, not to update them and especially not to alter them.Comments Off on Never Ever Do That | Filed in Oracle, Tips | Tags: sql
Dimitrios Kalogirou offers good advice:
Write self descriptive code ! Your code should be read like sentences. Avoid smart shortcuts and tricks because they break the reading… I use code comments when the code is not really self documenting. Comments should convey what code cannot. They should explain the reasons for a specific design decision, they should explain what code is supposed to achieve and why.Comments Off on Code Comments Gone Wrong | Filed in Tips | Tags: programming
This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at davidclement.org.
I’ve found the following query handy for investigating an ORA-12012 in an alert log. ORA-12012 is a dbms_scheduler execution error and it provides a job number without a name, where the Scheduler views give job names without numbers. So, to relate the job number to a name:
SELECT d.job_name, d.job_action FROM dba_scheduler_jobs d JOIN sys.scheduler$_job s ON d.job_action = s.program_action WHERE s.obj# = &job_number;
Maybe this will be useful for others as well.Comments Off on Handy Oracle Scheduler Troubleshooting Tip | Filed in Oracle, Tips | Tags: dbms_scheduler
Comments Off on Gathering fixed object stats may reduce recompilation time post Oracle upgrade | Filed in Oracle, Tips
In larger sized databases with many objects and components our recommendation is always to gather first fixed object stats prior to start the recompilation. Some time ago I’ve learned from to very different customer database projects that these stats will speed up the efficient job creation for recompilation. And last week I’ve got this feedback from an EBS 220.127.116.11 to 18.104.22.168 upgrade project:
- Approx 120,000 objects invalid post database upgrade
- Recompilation without fixed object stats: 14:44 hrs
- Recompilation with fixed object stats: 12:09 hrs
Time it took to gather fixed object stats: 00:07 hrs
- Benefit: 7 minutes to gather fixed object stats decreased the recompilation time by 2:35hrs
(or by 18%)
- How to gather fixed object stats in Oracle 11g:
SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This is a guest post by David Clement, a veteran database engineer, a coworker and a friend of mine. You can find him online at davidclement.org.
Here is an interesting feature that’s worth knowing about because of the potential for strange bugs. When you run a SQL script in the Unix shell, if you use a user-defined exception, you have to take care to prevent failure and success from returning the same result.
The following code snippet shows a normal way to return an error code to the Unix shell from a SQL script. I saved this as return_error_code.sql:3 Comments | Filed in Oracle, Tips | Tags: gotcha, linux, pl/sql, shell, sql, unix
When calling functions from SQL, you had better not rely on how often the functions get called, in what order, or whatever. In short, assume nothing. And remember – SQL rewrites kick in and we rewrite your SQL all of the time. Don’t rely on side effects.Comments Off on What you need to know about calling functions from SQL | Filed in Oracle, Tips | Tags: sql
Kurt Van Meerbeeck points out the fact that SGA re-sizes are occurring after an upgrade to 11.2 despite the fact that automatic memory management (AMM/ASMM) is disabled via the MEMORY_TARGET and SGA_TARGET parameters being set to zero.
It turns out that this is an expected behavior in 11.2. From Oracle Support note 1269139.1:
Cause:Comments Off on If you think you disabled Automatic Memory Management in Oracle DB 11.2, think again | Filed in Oracle, Tips | Tags: dba
This is expected behavior in 11.2 for immediate memory allocation requests, which added this as a new feature when automatic memory management was disabled.
Ignore the resize operation which is performed to prevent an ORA-4031 error from being raised, or set the parameter _MEMORY_IMM_MODE_WITHOUT_AUTOSGA to false in the instance to disable this feature with the consequence that in future an ORA-4031 error would be raised.