Upgraded to WordPress 3.0
SquareDBA.com has been upgraded to WordPress 3.0. If there are any issues let me know.
Tags: wordpress 3.0
ORA-30926: unable to get a stable set of rows in the source tables
I received the error “ORA-30926: unable to get a stable set of rows in the source tables”. It’s the first time I have seen this error. I was doing a merge statement affecting about 1K rows.
The problem was I was not fully matching on all the of the columns that would make the update portion of the merge statement unique. Duh! Added the additional column to my “WHEN MATCHED” criteria and everything ran fine.
Compressing Partitioned Tables
I recently wrote a script to compress certain partitions in a table. Basically any partition that was loaded and analyzed I wanted to move to a new tablespace and compress the data.
Resetting an Enterprise Manager Agent
There are times when your Enterprise Manager Agent needs to be cleared out and rest. Here are some very simple steps.
Script to shrink a tablespace
To shrink a tablespace in Oracle you actually need to shrink the datafiles that make up the tablespace. You can only shrink a datafile down to the high water mark (HWM). There are several methods to reduce the HWM, but I am not going to address those in this post. If you want to return some space back to the OS or ASM disk you may be able to run a few commands and not need to worry about trying to adjust the HWM.
ORA-00845: MEMORY_TARGET not supported on this system
Today I ran into the error “ORA-00845: MEMORY_TARGET not supported on this system”. I am still not sure exactly why.
I shutdown the instance on a Oracle 11g R2 RAC node. Then a short time later I issued the startup command. The other instance on the other node was running fine and made no changes. I exited SQLPlus and launched SQLPlus again. Issued the startup command and everything started up normally.
Off to do some research . . . . . .
Tags: ORA-00845
Purge the Recyclebin in Oracle
Did you know Oracle has a recycle bin? When you drop a table it goes into the recycle bin assuming you have the feature turned on. If you are trying to reclaim space simply dropping a table will not do it. You need to also purge the table from the recycle bin.
How to export with data pump
I have always used Oracle good old export utility to backup the logical definition of all my databases. In addition to using RMAN backup I feel a logical dump of the database is a good idea. I have now started using Oracle’s data pump utility. I work quite well, but does operate a bit differently that the older export utility.
Oracle RAC Project Starts Tomorrow
A I previously described I will started a new Oracle RAC project. If everything goes as planned I will get my hands on the 2 node setup tomorrow.
I have been busy researching 3PAR and enhancements to 11G. I will be installing 11G and implementing all new data warehouse ETL code.
Strip carriage return from varchar2 column
Have you ever needed to strip a carriage return from varchar2 column? Sometime when loading data from different sources I run into these non-printable characters. SQLLDR loaded them just fine, but I try to move the data into another table with specific datatypes it fails. Why? Because there are character that I cannot see and will not allow the varchar2 data to insert into a number column.
