Oracle vs MySql concat syntax

Was messing around with a MySql database today and was wondering why my query was turning up zero results when I know where should be results.  No error, just zero results returned.   Found out it was because of a difference in syntax when trying to concatenate.  This was the syntax I was using, which would work fine in Oracle:

SELECT yearID, AB, H, R, HR, RBI FROM baseball_batting b, baseball_players p
                   where yearID = '2017' and (p.nameFirst||' '||p.nameLast = '$name2' or p.nameGiven = '$name2') and b.playerID = p.playerID


Turns out I needed to use this:

SELECT yearID, AB, H, R, HR, RBI FROM baseball_batting b, baseball_players p
                   where yearID = '2017' and (concat(p.nameFirst,' ',p.nameLast) = '$name2' or p.nameGiven = '$name2') and b.playerID = p.playerID

So to break out the important part here:

Oracle:
p.nameFirst||' '||p.nameLast

MySql:
concat(p.nameFirst,' ',p.nameLast)

Missing OOW

For the second year in a row no budget available for work to send me to OOW.  Missing it, maybe next year.

Woohoo, got my Oracle 12c Certification certificate!!!

When my boss told me I needed to upgrade my Oracle certification due to a vendor requirement, I was a little concerned that I would have to take all five exams or however many they have these days since my last Oracle certification was 8i.  I was very happy to find out that I actually did still have the option to do the one test upgrade exam for 12c.  After a few weeks of some heavy duty exam cram sessions, I took the test and qualified for my 12c certification and this week I officially received my certificate.

OEM Real Time ADDM Monitoring During System Hang

Was able to use this handy feature of Oracle Enterprise Manager that I just learned of recently whilst studying for my Oracle 12c certification exam.  Pretty cool feature.  To get to it in OEM 13c navigate to your database instance, then click the Performance drop down menu and choose Real Time ADDM.  Enter your credentials and click Submit.  It's that simple!

How To Find Your Characterset in Oracle

Got a request today from an application owner asking what NLS characterset we are using in our Oracle database (10g). Just ran the following simple query to find out:

select * from NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET';

Move An Oracle Datafile To A New Location

Had a filesystem fill up on an AIX server today and there was nothing of significant size that I could delete. I could have asked the AIX admin to add some more space, but since it was just a training environment and there were other filesystems with available space, I chose to move some of the datafiles. It's pretty easy to do. Here are the steps.

1. Shutdown the database
2. Physically move (or copy if you want to be extra safe) the datafiles to their new location.
3. Mount the database (startup mount)
4. Rename the datafiles in Oracle with the following command:
alter database rename file 'old full path and file name' to 'new full path and file name';
ex. alter database rename file '/u81/oradata/train/ilawtrain_01.dbf' to '/u91/oradata/train/ilawtrain_01.dbf';

5. Open the database (alter database open)
6. Verify that the datafiles are in the new location in dba_data_files
7. If the datafiles are in the new location as expected and you copied the files instead of moving them, you can now safely delete the old copies of the datafile. Again if you want to be safe, rename the old copies and wait a day or two to delete them.

Explorer.exe is Using Too Much Memory

I tend to go long periods of time without rebooting my Windows based PC, because I sometimes need to remotely connect to my work PC from home, and I just don't want to close all those programs I have open. This sometimes leads to the explorer.exe program using up a lot of memory. If my PC starts running slow, or I hear the hard drive spinning for an extended period of time, I'll hit ctl-alt-del and click on the "Processes" tab and sort by "Mem Usage". Usually explorer.exe is using about 30,000k or so, but I've seen it get up to almost 200,000k. In this pic, it's using 34,204k.



If I see that is is using more than 50,000k of memory, I'll watch it for a minute and if it doesn't drop back down, I'll go ahead and highlight it by clicking once on it, and then click the "End Process" button. You will get a warning message about terminating a process, but I have never had any problems from terminating this program. However, to be safe make sure that you have saved any important projects that you are working on at the time. Explorer.exe is basically your Windows shell, so once you end the process, your task bar and start button will disappear. Your programs will still be running and you will still be able to see them. Next, click on the "Applications" tab and then click on the "New Process" button. It will bring up a box where you will enter the name of the application, in this case enter explorer.exe and click "OK". You should now see your task bar and start button once again, and if you click on the "Processes" tab and sort by "Mem Usage", the explorer.exe program should now be using much less memory than it was before.