Database

Oracle Database 12.2 became available for download last week. This weekend I’ve been playing with Docker and created a container with it. The whole process is so easy, thanks to the work by Gerald Venzl at Oracle. You’ll find all the information you need in his blog post . Though the post is for version 12.1.0.2, the dockerfiles have already been updated for 12.2.0.1, and you can download it from Github using the link he provides in the post. ...

If you need to delete all rows in a table that has parent keys for other tables’ foreign keys, and the foreign keys constraints have not been defined with “on delete cascade”, you can do a recursive delete with the following simple procedure. This is typically something you will do only in a test or development database, and not in production. As always, it is a good thing to understand this procedure before you execute it: ...

As suggested by Tim Hall , aka Oracle-Base.com, this post is about my favourite feature in the Oracle world. My favourite product is the good old database, and my favourite feature is simply the fact that I don’t lose data! Never once have I lost data due to software errors, and after many hard landings the database always manages to come back online after wading through the redo logs and make sure the data remains consistent. ...

I have been using SQL Developer since it required some goodwill to work. Some of the motivation came from an annoyance with Toad sessions in the database. Iremember that I back then googled “how to block Toad users from the database”. Anyway, these days most developers around me are using SQL Developer. But quite a few are learning only the least to get their job done. The following list is what I use often, and some of itresults in “How did you do that?” when they are looking over my shoulder. The shortcuts here refer to the Windows version since that is what I’m currently using at work. You can easily remap them from Preferences. ...

At UKOUG Tech conference in Liverpool last year I had an interesting talk with a DBA that had two years of experience. Being 23 years the DBA naturally stood out from the crowd. I talked about my thoughts on what is important for a DBA, and since I spend way too much on user group activities and conferences I argued that going to such events are useful for a DBA, and continued with other ramblings about what I think is important for a DBA to do. I hope I stopped soon enough, but stop talking and start listening is an art. Anyway, I decided to write down a list of recommendations for a new DBA. This is what I think is important. ...

This is a common error message after upgrade of the database: ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance In 11g (last tested with 11.2.0.3) even if your start up the database from an old type pfile without any obsolete parameters and then create an spfile from memory like this: create spfile from memory; Oracle will insert two obsolete parameters into the spfile. This query will list obsolete parameters in your spfile: ...

The garbage in this case is the recycle bin in the database. Just as in any tidy house you should not wait too long before you empty the garbage bin. I’ve come across two reasons to empty it. One is the ORA-3297 that is thrown when you try to downsize a data file you think have lots of free space at the end. I once wrote this SQL to find the lower limit that I can reduce the data file to: ...

If your database is using anything else than UTF8 as database character set you may consider to migrate from it. Oracle states in the Globalization Guide (10gR2): At the top of the list of character sets Oracle recommends for all new system deployment is the Unicode character set AL32UTF8. Depending on what characters you actually have in your database you have three options on how to do this: Changing the character set with the package CSALTER. Only data dictionary is migrated. ...

I’ll probably need this in the not-so-distant future. Here is the link to Noon’s write-up when I need it. His blog is recommended, by the way.

Any database that has gone into production is a legacy database according to Tom Kyte . I agree, but to me some databases give me associations to legacy more often than others. In a post at the OraStory blog Dom complains about companies that hire contractors for a maximum of one year, and due to the fact that it takes half a year to understand the business and the data, you hardly get to show what you can deliver. ...