Reading Windows Event Log for Oracle Databases

Mythological creatures take many forms. Some have horns and hooves. Some have bodies combined from the union of different species. Some have scales and wings and breath fire.

I'm that rarest of all mythological creatures: The sort who runs production Oracle databases on Windows :-D

Changing SGA & PGA memory for Oracle

The following can give good info on target memory advice after your system has been running for some time. It is cleared during restart
 select * from V$MEMORY_TARGET_ADVICE ;

BACKUP your SPFILE before doing any radical changes.
Example location ~ D:\oracle\product\12.1.0\dbhome_1\database

To set pga to 1 gb and sga to 3 g, enter the following commands:

ALTER SYSTEM SET pga_aggregate_target=1G SCOPE = SPFILE;
ALTER SYSTEM SET sga_target=3G SCOPE = SPFILE;

You will need to restart your Oracle database.

And, Bob's your uncle, you should have your PGA and SGA set to the new target values.

Creating Hash Values for Strings In MSSQL, DB2 UDB for LUW, and Oracle

It will often occur that we need to search a large character string in a relational database, something like an email address, postal address, etc.

For the most part, when we do such searches, we're searching for a sub-string within that large string of characters.

However, for instances where we know we need an equivalency search, we can make things more efficient by generating a hash of the string and then storing that in an indexed column.

This can be done in all 3 of the major RDBMS platforms.  Links below:

MSSQL uses the CHECKSUM() function.
DB2 LUW uses the DBMS_UTILITY.GET_HASH_VALUE() function.
Oracle uses the ORA_HASH function.

This article by Jeff Reinhard over at SQLServerCentral.com is what inspired me to look for the similar function in DB2 & Oracle, and it does a very nice job of explaining the use case for this using an e-mail address scenario.

If anyone knows how to do this in postgressql or MySQL, I'd love to have you add a few words or link in the comments. Thanks! :)

MOVING ORACLE DATA FILES (Addendum)

There is a very good article on moving Oracle data files to be found here: https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm

Unfortunately, it does no include a final step to bring the  tablespace back online.
The command is simple:

ALTER TABLESPACE USERS
 ONLINE;

So, execute the procedure in the link above, and when finished, make sure you bring your table-space back online.

Regular Expressions in SQL Server, DB2 UDB, and Oracle

I was a bit surprised that not all RDBMS platforms offer robust, native support for regular expressions.

Oracle:

Oracle introduced this in 10g. It is implemented in roughly the way I would expect and looks like it's pretty useful.
Reference

DB2:

DB2 seems to have this capability in 9.7. NOTE that I have not checked to see if it was available in earlier releases.  However, it appears you have to go through the XQuery interface. I'm not surprised by this, but a bit disappointed.  I had hoped for an implementation more like Oracle's. I think that the DB2 way is going to be cumbersome for the average user.

Example:

db2 "with val as (
 select t.text
 from texts t
 where xmlcast(xmlquery('fn:matches(\$TEXT,''^[A-Za-z 0-9]*$'')') as integer) = 0
)
select * from val"

Reference1, IBM DB2 for LUW 9.7 information center reference

MS SQL Server: 

SQL Server, it seems, requires you to roll your own solution or look to some sort of 3rd party.  I'm very surprised by this.

There are 3rd party libraries that appear to be pretty robust.  One example can be found here: SQL Server Central article on RegEx in MSSQL Server

I suppose that they want us to use the full text search, which appears to have robust features, and address the same purpose.  Still, just a bit surprised ...

Well, THIS saved me a lot of hastle

Oracle SQL Developer, rather inexplicably in my view, doesn't come out-of-the-box with a good facility to set the schema.  Even the set active schema command gets ignored.

This extension provides a handy drop-down box that allows you to set the active schema.  Brilliant :-)

http://javaforge.com/project/schemasel