News, views, tips and tricks on Oracle and other fun stuff

When Versioning Support Interferes With Saving Your Relational Model

Oracle SQL Developer Data Modeler icon 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 have TortoiseSVN installed on my laptop and use it to interface with a subversion repository.

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 | Filed in Oracle, Tips | Tags: ,

Never Ever Do That

ALTER TABLE sys.dual ADD (dummy2 VARCHAR2(1));

Tom Kyte:

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 | Filed in Oracle, Tips | Tags:

Code Comments Gone Wrong

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 | Filed in Tips | Tags:

What is the Oracle Learning Library?

It’s a really useful resource. The Oracle Learning Library allows you to search for free online learning content on the internet. The content comes from a variety of sources, including OTN, YouTube and Blogs.

Continue reading…

Comments Off | Filed in Links, Oracle, Tips | Tags:

Handy Oracle Scheduler Troubleshooting Tip

This is a guest post by David Clement, a veteran database engineer and a friend of mine. You can find him online at

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 | Filed in Oracle, Tips | Tags:

Gathering fixed object stats may reduce recompilation time post Oracle upgrade

Mike Dietrich:

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 to 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:
Comments Off | Filed in Oracle, Tips

User Exceptions Gotcha, When an Error Appears as a Success

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

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:

Continue reading…

3 Comments | Filed in Oracle, Tips | Tags: , , , , ,

Oracle DB 11g Password Expiry Gotcha

Jeff Hunter:

Something I discovered recently is that the DEFAULT profile for Oracle 11g sets the PASSWORD_LIFE_TIME to 180 instead of UNLIMTED by default.  Applications will encounter an “ORA-28002: the password will expire within X days” error message if you keep the default value.

To change the PASSWORD_LIFE_TIME, you:
ALTER PROFILE default LIMIT password_life_time UNLIMITED;

Read more about 11g new security related features here.

1 Comment | Filed in Links, Oracle, Tips | Tags: , , ,

What you need to know about calling functions from SQL

Tom Kyte:

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 | Filed in Oracle, Tips | Tags:

If you think you disabled Automatic Memory Management in Oracle DB 11.2, think again

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:

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.
Comments Off | Filed in Oracle, Tips | Tags: