Square DBA - Oracle DBA Talk

Locating the Oracle Database Version

Ever need to know what version your database is running?  Well as the DBA you should already know.  I also know what major version I am running on all of my instances, however I am not always shure what patchset I am running.  To look it up it is pretty simple.

SQL> SELECT * FROM SYS.V_$VERSION;

or

SQL> SELECT * FROM v$instance

GTD Tasks in Outlook

As I have posted before I am always looking to streamline my to-do lists.  I think I have come up with the ultimate solution (at least for me).  Anything I use must sync with Outlook.  We use MS Exchange here at work.  People send me tasks through Outlook and I receive Outlook tasks from our MS Project server so I cannot get away from Outlook.  A great deal of my tasks come from emails I receive so again Outlook is central to creating tasks.

I try to follow a lot of the GTD methodologies.  Since a fair amount of tasks are based on emails I was looking for a way to streamline that process.  I created macros that help me classify emails on my to-do list.  I actually created 3 macros (Next actions, Waiting, & Someday).
These macros do the following:
1, Takes the items that is selected and creates a task
2. Attaches the selected item to the task
3. Sets the task’s category
4. Sets a reminder for certain tasks

Here are the instructions for creating the macros. (The directions are slightly different for Outlook 2007)

1. Start Outlook
2. Tools | Macros | Security
3. Choose “Medium”, which will prompt you on whether or not you want to run macros (VBA). You may need to restart Outlook at this point in order for that setting to take effect, I’m not sure.
4. Tools | Macros | Visual Basic Editor
5. Doubleclick on This Outlook Session on the left, which will open the code window on the right
6. In the code window, paste the code from the attached file (it is attached to this post)
7. View | Immediate to make the immediate window show up

Next I added these macros to my standard toolbar.

1. View | Toolbars | Customize
2. On the Commands tab, click Macros on the left-hand side
3. Drag the appropriate macro to the standard toolbar (say, next to the “Send/Receive“ button).
4. Right click on the button and change the Name field if you want to shorten the text or assign a different accelerator key or an image
5. Click Close

Now when I get an email (or any other Outlook item) I can click one of these new buttons and create a task with the item attached.

Stupid Errors

Stupid errors are always the most frustrating.  Today while running a SQL statement for an adhoc report I received a “ORA-12801: error signaled in parallel query server P050” followed by “ORA-01722: invalid number”.  I knew the invalid number error was the real culprit and not the parallel query error.  The statement runs for 15 minutes before erroring out so I am thinking the error occurs on some row in the results.  My SQL statement uses several functions, some Oracle functions and some created by myself.  So my first thought was one of my functions was not dealing with the data properly.  Looking at each function I could not find any issues.  My next step was going to be put my SQL into a procedure so I could handle the exception and find the problem row(s).  Then I spotted the problem.

Right there at the beginning of my SQL statement I wrote “select substr(summary_date, ‘YYYYMM’)”, instead of “select to_char(summary_date, ‘YYYYMM’)”.  Since this is not a syntax error I did not see it right away.  It also did not produce the error right away since the substr function is not applied until after the items in the where clause are evaluated.

In the end sometimes issues are so simple they are right in front of your face.  For the rest of today I will be telling myself to keep it simple stupid.

Need help with Oracle?

Do you need help with Oracle ?  I am offering to help with your Oracle database issues.  Why?  Well I enjoy helping others, I find that I sharpen my skills when I need to teach others, and help bring traffic to this website.  If you have a question feel free to post it on my Oracle forums and I will make every attempt to help you out.

Shrink table segment that experienced large delete

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.

Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space. With Oracle Database 10g, you can now shrink segments directly, without taking the tablespaces or the objects offline. The process of shrinking a segment :

1. Enable row movement : ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
2. Recover space and amend the high water mark (HWM): ALTER TABLE scott.emp SHRINK SPACE; OR
3. Recover space, but don’t amend the high water mark (HWM) : ALTER TABLE scott.emp SHRINK SPACE COMPACT;
4. Recover space for the object and all dependant objects : ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Rebuild indexes & gather statistics after table shrink.

Just a quick tip.  When you finish a table segment shrink, it is a good practice to rebuild any indexes on that table as well as gather statistics on that table. These tasks help Oracle to create a better execution plan for the SQL query related to the table.

Oracle Podcasts

I am looking for a god Oracle podcast.  Do you know of any?  I did subscribe to one podcast fro OTN, but I am looking for others.  I spend about two hours in the car daily and normally devote that time to news, talk radio, and music.  I catch a couple of podcasts on photography and I thought an Oracle Podcast would be a good addition.

Managing Oracle with an iPhone

My next (personal) project will be managing my Oracle databases with an iPhone. Yes I do need to still buy an iPhone first, but that is just a small detail. I already built and Oracle DBA application that I can use from my current phone so I will just take some tweaking to work on an iPhone. Yet I am thinking about a complete recoding of the application. My current application is custom built and not really designed to handle all Oracle databases. It is specific to many of the jobs running in my data warehouse. From any web browser I can see what is running on my database and perform simple DBA tasks. While there are some nice applications out there to manage a database remotely I want to have something a bit more custom.

My current application is built with an Oracle backend (of course) and running ASP as the front end. I think my new application will be running PL/SQl pages or PHP. I may even move to Oracle Express as my backend database with Oracle Application Express (Oracle APEX). This would allow me to freely distribute the full application. No matter how I go with this new application it will be slow going. This will be a personal project and with kids, a wife, and a full-time job my new iPhone Oracle application will not be on the top of my list.

Reclaim lost space with tune2fs

File system Reserved Block Count is supposed to reduce Linux file system defragmentation, to allow root user login for maintenance or to allow Linux system logging facility to function properly in case file system running low of free disk space.

File system utility called tune2fs can be used to tune Linux ext2 / ext3 file system parameters, such as adjusting file system reserved block count, frequency of file system force-check based, maximal time between two file system checks, behavior of the kernel code when errors are detected, overriding the default ext3 journal parameters, etc.

tune2fs -l /dev/hda

or

dumpe2fs -h /dev/vg0/lvol1

could be used to inspect a file system superblock information. Take note on these fields of both tune2fs and dumpe2fs command output:

Reserved block count: 3399024
Free blocks: 4997248
Free inodes: 42473835
First block: 0
Block size: 4096

The block size is measured in byte unit. In this case, it’s 4,096 byte or 4KB. So, the Reserved Block Count: 3399024 is equal to 13,922,402,304 byte or roughly equivalent to 14GB.

Now, if execute the command

tune2fs -m 0 /dev/vg0/lvol1

will set the percentage of file system reserved block count to 0% for /dev/vg0/lvol1 file system (the first column of df command output). It’s OK to totally disable file system reserved block count, if the file system is not used by root user account or storing system log and system/program temp files (e.g. /var and /tmp). For example, file system dedicated to Oracle datafiles are safe to disable file system reserved block count.

So, if you’re running a Linux machine with 320GB storage array and haven’t tune the file system reserved block count, you may tune it now to claim back as much as 16GB of free disk space!

Moving an Oracle tablespace

Today I needed to move a tablespace between mount points. My primary mount points were starting to fill up and I needed to make some breathing room. I recently added secondary storage to the server. I say secondary because the storage just added is on less spindles and slightly slower disks. The idea is to move less accessed data to the secondary storage. One on of my first moves involves the USERS tablespace. In this particular database the USERS tablespace is simple used to store temp type tables and is not used for anything “production”. Here are the steps I followed.

In SQLPlus:

alter tablespace USERS offline;

OS Level:

cd /u01/app/oracle/oradata/dw2

cp users01.dbf /storage/oradata/dw2/users01.dbf

Back in SQLPlus:

alter tablespace USERS
rename datafile /u01/app/oracle/oradata/dw2/users01.dbf
to /storage/oradata/dw2/users01.dbf;

alter tablespace USERS online;