sql_sql_id_html.sql version 1.4

[Update]: there is now a non-Diagnostics Pack version here.

[Update]: version 1.6 is now out. More on this blogpost.

A collegue pointed out that sql_sql_id_html.sql doesn’t show binds with the timestamp data type. It turns out that for bind variables with the timestamp data type the column value_string of v$sql_bind_capture is empty, but the column value_anydata contains that data. That column has a data type called anydata and you can retrieve it with the function anydata.accesstimestamp. I found a handy SQL snippet that helped me to adjust my SQL statements for this. I’ve tried my generated SQL*Plus bind variable statements with binds with the timestamp data type and that now works too.

I demoed the previous version my script at work and showed the output of it to my team last week. I had only a couple of minutes for this. I explained that I created a new version that generates output with an HTML menu. “For example, suppose I want to find the execution plan”, I said, “then I just can click on ..” and then it dawned on me: the links to the execution plan (from memory and AWR) were missing. So that was kind of a bummer. Of course I fixed that in version 1.4 as well.

I also fixed the issue that the SQL text contains spaces every so often. It now wont show an ininvited space up to 4000 characters. I fixed that in stats_sql_id_html.sql as well.


About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning, Oracle scripts and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s