## ALTER USER IDENTIFIED BY VALUES

December 3 2013

One problem that occasionally pops up is that a DBA needs to recreate a user with a password that he does not know. This happened to me when I had to create a schema with the same password on a development database. After I set an initial password for the developer, he exclaimed that he wanted the same password that he had for the schema X, but he did not want to tell me the password (don’t ask here, that’s a completely other story).

One way to do this is to use `ALTER USER ... IDENTIFIED BY VALUES`. Using the excellent article on the ALTER USER commands from Laurent Schneider, we can generate a dynamic SQL query to set a password without knowing the password itself!

## Algorithm to find first available number

December 1 2013

So recently I stumbled across a programming quiz to which I later returned because it somehow fascinated me.

## Problem

Finding the first available number (or the smallest missing number) in a list is a common problem in Computer Science (for example for Defragmenting or generating keys) and describes the search for the smallest natural number, which is not part of a set X of natural numbers. X is a set of distinct natural numbers (and being a set, is not ordered).

We are now looking for a function with linear worst-case time complexity O(n).

## Example

We define X as a set of distinct natural numbers:

X = {23,9,12,0,11,1,13,7,21,14,5,4,17,19,3,6,2}

So in this set, we find that the number 8 is the first available number (smallest missing number). So running the algorithm over the above set should return 8.

## Java Service Wrapper 3.5.22 for Windows x64

October 14 2013

After fiddling with the NMAKE file and a nudge from a nice commenter, I hereby provide the latest version of the Tanuki Service Wrapper for Windows x64.

## OPatch 11.1.0.10.1 on AIX: Unhandled exception (Segmentation error)

October 10 2013

One thing that you don’t want to see while performing an update of a database with the OPatch utility is a SEGFAULT. In my scenario, I simply wanted to list all the installed patches for an ORACLE_HOME and therefore issued opatch lsinventory:

``````\$ cd \$ORACLE_HOME/OPatch
\$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.1.0.10.1

Unhandled exception
Type=Segmentation error vmState=0x00060000
[..]``````

## “ORA-38342: heat map not enabled” on Oracle 12.1.0.1

September 9 2013

As with all the new features that Oracle 12c brings us, we want to test them out! So one of the lesser known features in the new release is the Automatic Data Optimization with the Heat Map mechanism. Oracle describes the feature in a White Paper:

Heat Map is a new feature in Oracle Database 12c that automatically tracks usage information at the row and segment levels. Data modification times are tracked at the row level and aggregated to the block level, and modification times, full table scan times, and index lookup times are tracked at the segment level. Heat Map gives you a detailed view of how your data is being accessed, and how access patterns are changing over time. Programmatic access to Heat Map data is available through a set of PL/SQL table functions, as well as through data dictionary views.

Sounds great, how can we use it? Read the rest of this entry »

## Oracle MEMORY_TARGET minimum size

August 29 2013

For my own reference, as I was asked what the minimum memory size for an Oracle database is. It turns out, that both in Oracle 11g R2 (11.2) as well as in Oracle 12c (12.1), the minimum value for MEMORY_TARGET (sum of SGA and PGA) is 152MB:
Read the rest of this entry »

## Java Service Wrapper 3.5.20 for Windows x64

August 16 2013

Another commenter noted that there is a new version of the Tanuki Java Service Wrapper available. I hereby provide a build of the latest version of the wrapper, version 3.5.20.

## SwissICT Database Specialist Portrait

July 29 2013

This month I had the honor to be featured on the SwissICT website as a database specialist. SwissICT is Switzerlands largest special interest group for information technology.

## Oracle PSU 11.1.0.7.15: “genclntsh: Failed to link libclntsh.so.11.1″

July 24 2013

So here is yet another troubleshooting post. Today I was applying another PSU for an older Oracle 11.1.0.7 database and received the following error after issuing \$ORACLE_HOME/OPatch/opatch apply:

``````The following warnings have occurred during OPatch execution:
1) OUI-67215:
OPatch found the word "failed" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status
genclntsh: Failed to link libclntsh.so.11.1
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status

2) OUI-67215:
OPatch found the word "failed" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status
genclntsh: Failed to link libclntsh.so.11.1
/usr/bin/ld: crti.o: No such file: No such file or directory
collect2: ld returned 1 exit status``````

## Oracle 12c: ORA-00942 on CREATE PLUGGABLE DATABASE

July 2 2013

I was eager to try out the new Pluggable Database feature of the newly released Oracle 12c Database. I installed the software, created the database (see my post about the “ENABLE PLUGGABLE DATABASE” clause which I forgot the first time around) and then wanted to create a new Pluggable Database (PDB) like so:

``````SQL> CREATE PLUGGABLE DATABASE kdb121p1 ADMIN USER simon IDENTIFIED BY tiger;
CREATE PLUGGABLE DATABASE kdb121p1 ADMIN USER simon IDENTIFIED BY tiger
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist``````

Oh, really strange, what kind of error message is that?
Read the rest of this entry »