Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Scottish Oracle Conference

I recently spent a rather pleasant day at the scottish conference of the ukoug. This was held at the Radisson SAS Hotel just by the central rail station and split into a number of streams, from management through to dba. The organisation of the event was excellent, particularly given the fact that some of the [...]

SQL Developer Data Modeling Update

Oracle has released an 'early adopter' version of the the data modeling enhancements to SQL Developer.

See the OTN article for details.

I haven't tried it yet, it will be interesting to see just how well it works.

Virtual columns in 11g

Store expressions as virtual columns in Oracle 11g. October 2008

Upgrade Experience for Patchkit 11.1.0.7

The first patchkit for 11g - 11.1.0.7 - came out a few weeks ago, for Linux systems. Unlike most other patchkits, this one contains some new functionalities. Oracle patchkits are usually only bugfixes with al patches regression tested collectively; not added features. This one does have some new (albeit minor) features.

The upgrade was relatively easy but took a long time - about 30 minutes. I encountered one issue and a roadblock. Here is the annoying roadblock

A Very Import Pre-req

Do not forget to check for this pre-requisite. This is the time_zone check, as specified in the patchkit readme file.

SQL> select version from v$timezone_file;

VERSION
----------
4

In my case it returned 4, which means no further action is necessary. Had it returned something else, I would have to follow the instructions mentioned in MetaLink Note 568125.1.

Asking for Email for Security Notification

It was interesting to note that the installer asked me to enter my Email address to send me updates on security. Funny; I get that already anyway. So I ignored and clicked "Next". Nope; it popped a little window asking me to confirm that I do not need email. Sure, I confirmed and pressed Next. No, the same issue, it asked me again to confirm and so on. Vicious circle!

There was a little checkbox below that said "track security via MetaLink" and asked my MetaLink password (not "id", which it presumed to be my email). I checked that box and entered my email and password, and it allowed me to go further.

This is a little awkward. Not everyone will want to get the email. And in any case, a mere email will explain little about the security issues. And why would anyone want to embed his/her MetaLink password in a response file?

Cool Feature: The Pre-upgrade Script

Oracle now provides a script utlu111i.sql in the $OH/rdbms/admin directory. Run this script before you shut the database down for upgrade. It will tell a lot about the issues you may need to fix before trying the upgrade.

The Issue

The issue I encountered was while running the catupgrd.sql script. After applyng the patch, I started up with the UPGRADE option and then executed catupgrd.sql script, which promptly failed. From the error message (which, I unfortunately, I couldn't capture), it was clear that the failure was due to the presence of Data Vault feature. I apparently installed Data Vault option while installing the software. The message says it clearly:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Well, I need to remove it before I can upgrade. It needed a recomp of the Oracle software:

oracle@prolin1$ cd $ORACLE_HOME/rdbms/lib
oracle@prolin1$ make -f ins_rdbms.mk dv_off

Next, I relinked the Oracle software:

oracle@prolin1$ relink oracle

Now the catupgrd.sql script ran successfully as expected. It took about 1 hour to complete.

SQL> select * from v$version
2 /

BANNER
-----------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Undocumented Oracle Functions

Undocumented functions in Oracle are always fun, and you just may find something useful.

The caveat of course is that they are undocumented. They can change without notice between releases or patch levels, so building apps that depend on them may be unwise.

They are often quite useful from a DBA perspective when used in SQL scripts.

Here are a few that I've played with. These are all found in Oracle 10.2.0.3

These functions have one thing in common - they have a prefix of SYS_OP_

Some of these appear to be identical to documented functions.

I don't know of any official explanation regarding the purpose of undocumented functions that seem to mimic documented functions. It could be that the source for the documented functions are separate from those that are documented, ensuring that functionality will not change for an undocumented function that is used in the Oracle kernel, even though its documented doppelganger may change in future releases.

In any case, undocumented functions are always interesting, and here are a few to play with.

Just keep in mind that these are undocumented, and as such may change or disappear entirely in future releases

sys_op_vacand - Return the binary AND of two raw values. Results are in hex

SELECT sys_op_vecand(hextoraw('FF'),hextoraw('FE')) from dual;
FE

16:13:12 SQL>SELECT sys_op_vecand(hextoraw('C3'),hextoraw('7E')) from dual;
42

sys_op_vecor - Return the binary OR of two raw values. Results are in hex

16:14:39 SQL>SELECT sys_op_vecor(hextoraw('FF'),hextoraw('FE')) from dual;
FF

sys_op_vecxor - Return the binary XOR of two raw values. Results are in hex

16:14:39 SQL>SELECT sys_op_vecor(hextoraw('FF'),hextoraw('FE')) from dual;
FF

sys_op_vecbit - Return the value of the bit at position N in a raw value

The return value is 0 or 1

This is an interesting function as it can be used to determine the value of bits in a number. If for instance some flags are stored in a bit vector and you need to know the value of the 3 bit, this is an easy way to do it.

I believe the upper limit on the number of bits is 127.

prompt
define decnum=10
prompt &&decnum dec = 1010 bin

16:16:27 SQL>select 'Bit 0 is ' || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),0) from dual;
Bit 0 is 0

16:16:27 SQL>select 'Bit 1 is ' || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),1) from dual;
Bit 1 is 1

16:16:27 SQL>select 'Bit 2 is ' || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),2) from dual;
Bit 2 is 0

16:16:27 SQL>select 'Bit 3 is ' || sys_op_vecbit(SYS_OP_NUMTORAW(&&decnum),3) from dual;
Bit 3 is 1

sys_op_bitvec - This appears to be for used to build a bit vector, but I haven't figured out
how to use it. Please let me know if you do.

sys_op_map_nonnull - This has been thouroughly discussed on Eddie Awad's blog:
sys_op_map_nonnull discussion

sys_op_descend - Returns value as it would be used in a descending index. This is essentially
reverse() function with output in hex.

16:32:41 SQL>select sys_op_descend('ABC') from dual;
BEBDBCFF

sys_op_undescend - The inverse of sys_op_descend. Well, almost

17:12:59 SQL>select sys_op_undescend(sys_op_descend('ABC')) from dual
17:12:59 2 /

414243

Notice the output is in the original order, but in decimal rather than hex.

sys_op_dump - dump the data from an ADT/UDT (Abtract/User Data Type)

16:54:13 SQL>CREATE OR REPLACE TYPE my_adt AS OBJECT (
16:54:13 2 last_name varchar2(30),
16:54:13 3 first_name varchar2(30),
16:54:13 4 id number(6)
16:54:13 5 )
16:54:13 6 /
16:54:13 SQL>
16:54:13 SQL>
16:54:13 SQL>select sys_op_dump(my_adt('still','jared',234987)) from dual;

('still','jared',234987)

I don't use objects in the database, but this would likely be useful for someone that does.

sys_op_guid - this appears to be identical to sys_guid

17:00:50 SQL>select sys_guid(), sys_op_guid() from dual;

52BA7CF06BB488ECE040010A7C646200 52BA7CF06BB588ECE040010A7C646200

Most Striking Object at Open World 08

What was the most amazing thing I witnessed for the first time at OOW 08?

Hands down, it is the compostible drinking cup! Yes, compostible, not recyclable. It looks just like any other plastic (not paper) drink cup you see at almost any public water dispensers. The difference? It's made of corn syrup, I was told, not plastic and hence compostible. Wow!

I am not a green fanatic; but I consider myself a responsible adult concerned about the environment doing his share to reduce the landfills, pollutions and paper consumption. I do things that are practical: I don't print something I can read on the monitor; project emails, powerpoints on the screen/projector while conferring with colleagues rather than printing; use back sides of printouts to scribble; use 2-sided printing; donate kids' toys and cloths to charity rather than throw them in trash and so on. But there are some things I just couldn't jettison; at least not yet. One of them was the ubiquitous plastic drinking cup and the bottled water. The convenience of the water bottle was just too much to ignore and my lazy bones reigned over my conscience and I always gravitated, albeit a little guiltly, to the water bottle.

Not any more. I hope these compostible corn syrup based polymer material makes its way to all things plastic - bottles, cups, packaging and so on. The material is called polylactic acid (PLA), which is a polymer made from lactic acid from strachy produce like corn, wheat, patato and beet. However, due to its low melting point, it's not suitable for hot liquids, at least not yet. There is a compostible version - paper cups lines with PLA instea dof petroleum based products. But that's still paper; not 100% PLA.

According to a Smithsonian article, producing this PLA requires 65% less energy and emits 68% fewer greenhouse gases. Wow! That's good enough for me.

But, is it all rosy and smell nice? Well, afraid not. The biggest caveat: the PLA decomposes in a controlled composting facility, not the backyard composting bin. you need something of industrail strength - the sort used by municipalities and large industrial plants. Do they exist? Yes, for commercial use; but almost none for residential use. So, that's the catch. While the material is compostible; the facility to compost is not available.

But I am not going to look at it as glass half full. This is a major first step. Perhaps the ecological and political pressures will force the residential facilities to open up as well. Until then, let the power be with PLA.

OOW'08 Oracle 11g New Features for DBAs

It was my second session at Open World this year. It was full with 332 attendees with a whopping 277 attendees on wait list! the room capacity was 397. Of course, the room did have some fragmentation and not everyone could make it.

Here is the abstract:

There is a world outside the glittering marketing glitz surrounding Oracle 11g. In this session, a DBA and author of the popular 11g New Features series on OTN covers features that stand out in the real world and make your job easier, your actions more efficient and resilient, and so on. Learn the new features with working examples: how to use Database Replay and SQL Performance Analyzer to accurately predict the effect of changes and Recovery Manager (RMAN) Data Recovery Advisor to catch errors and corruption so new stats won't cause issues.

Thank you very much for those who decided to attend. I hope you found it useful. Here is the presentation. You can download it from the Open World site too. Please note, the companion site to see al working examples and a more detailed coverage is still my Oracle 11g New Features Series on Oracle Technology Network.

Data Modeling with SQL Developer

Unlike Open World 2007 there were many database oriented sessions at Oracle Open World 2008. There were many good performance oriented sessions, so many in fact that there were several conflicts in the schedule, and I had to pick one in several time slots that had multiple choices.

One of the more interesting sessions (for me anyway) at OOW 2008 was a session not on database performance, but on data modeling.

The SQL Developer team has been hard at working creating a data modeling plugin for SQL Developer.

This appears to be a very full featured tool, and appears to be the answer to the question "What will replace Oracle Designer?"

While Designer is much more than a data modeling tool, that is one of the core features of the tool, and many folks have used it just for its data modeling capabilities.

The new ERD tool is no lightweight, it is quite full featured from a database modeling and design standpoint.

Some of the features included:

  • Domains generated from data
  • Real logical and physical modeling, not just one model with 2 different names.
  • The ability to reverse engineer several schemas at once and have them appear not only as a master model, but each individually as a sub model.
  • Sub model views may be created on sets of objects as well.
  • The tool can determine all tables related to a table through FKs and create a sub model based on that set.
  • Two forms of notation: Barker and IE
  • Many options for displaying sub/super types (D2k fans rejoice!)
  • Glossary - a predefined set of names. These can be used to enforce naming conventions for entities, tables and relations.
  • Schema comparison with DDL change generation

Also of note, in addition to Oracle schemas can be imported from SQL Server, DB2, or any ODBC connected database.

The repository can be either file based, or database based.
There are two versions of the tool, a plugin to SQL Developer, and a stand alone version. The stand alone version will use only the file based repository.

Now for the bad news.

The release date has not been established. The only release information given was 'sometime in the 2009 calendar year'. As the database repository has not yet been designed, the long time to release is understandable.

And finally, licensing has not been established. It might be free, it might not. If not, at least we can hope for reasonably priced. Personally I thinking having a decent data modeling tool that comes free of charge with SQL Developer would contribute to higher quality databases, as more people would use a real database designer rather than a drawing tool.

There was probably more that didn't make it into my notes.
Suffice it to say this is a great development for data modelers and database designers.

Following a few screen shots taken during the presentation.

Partition-wise dependencies in 10g release 2

Modify partitions without invalidating dependant objects. October 2007 (updated August 2008)

AWR Usage Poll

A number of recent threads in the Oracle-L list have made it pretty clear that Automated Workload Repository (AWR) is a tool that you are expected to use when troubleshooting a database problem.

Never mind the fact that AWR is still a product that is licensed separately from the database, and that a large segment of the Oracle DBA population doesn't seem to realize that. Or that Active Session History (ASH) is part of AWR, and falls under the same license restrictions.

So I conducted a poll regarding the use of AWR. AWR Usage Poll. If you haven't in the AWR Poll, please do so.

While the web site does provide a chart of results, those results don't include the extra comments made by poll takers. You may are may not be able to download all the results, I'm not sure if that is restricted to the poll owner.

Nonetheless, I have compiled the results from a 100 or so respondents in to an Excel workbook, along with a few charts. You may find some of the additional comments of interest as well. AWR Usage Results

Draw your own conclusions regarding these results. I think it interesting to that AWR appears to be quite widely used. Personally I fall into the category of not using it because of the expense. I may work on changing that for a couple of key servers, as AWR is not that expensive, but in a small shop, spending $20k on feature that is not often needed is sometimes a hard sell.

One question I purposely left out was "Do you use AWR even though you have not licensed it"? While it might satisfy the curiosity of some (including me) I didn't want to give any Oracle sales people (or Oracle attorneys for that matter) any reasons to contact me regarding the poll.

In retrospect a good question would have been: "Did you realize AWR/ASH is a separately licensed product?". Too late to add that now, but bringing that up quite often leads to lively discussion.

Another interesting bit was that a few people have extended STATSPACK in some way, even using it on Oracle 10g+. One even mentioned the excellent repository of statspack scripts assembled by Tim Gorman. Tim Gorman's Statspack Scripts