PL/SQL: Generate random values

When you need random values in the Oracle-world you can get them by using the package DBMS_RANDOM. If not already there, you can install the package with the script “dbmsrand.sql” in “ORACLE_HOME/RDBMS/ADMIN”.

The package offers the two functions VALUE and STRING to generate random values for numbers, strings or mixed cases.

When called without any parameter DBMS_RANDOM.VALUE delivers values between 0 and 1:

select dbms_random.value num from dual connect by level <5;

       NUM
----------
0,44323453888163585343305347384952888762
0,27061509862121968713431350682787319082
0,50719289299767089592254463623923727779
0,42666962176478158926225395583206159572

The “connect by level <5" can be omitted as it’s used here just to present more than one example-row.

You can also define a range of values from which to get random values (here it is the range from “1 <= x < 100"):

select dbms_random.value(1,100) num from dual connect by level <5;
       NUM
----------
74,68117799104242371065908850142160589183
25,20810462171063688648794689871086371162
64,05205571240469040169889477158705792932
77,16423950749394682603580224777136966975

To get rid of the fractional digits you can ROUND the values:

select round(dbms_random.value(1,100),0) num from dual connect by level <5;
       NUM
----------
34
81
35
72

Keep in mind, that the generated values are always BELOW the upper bound (it is NOT included in the range).

To limit the result to even numbers you could wrap it with modulo:

select * from (
select round(dbms_random.value(1,100),0) num from dual connect by level <5
)
where mod(num,2)=0;

To just get odd numbers you have to set “mod(num,2)=1”. But don’t trust that this example will get you ALWAYS a number or a constant number of rows! The modulo is just checked against the result-set of the inner query. If that result-set doesn’t contain a even (or odd) number, it will return nothing.

For random strings there are a few options:

 'u', 'U' - returning string in uppercase alpha characters
 'l', 'L' - returning string in lowercase alpha characters
 'a', 'A' - returning string in mixed case alpha characters
 'x', 'X' - returning string in uppercase alpha-numeric characters
 'p', 'P' - returning string in any printable characters.
select dbms_random.string('u',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
QHQPNZTPMJ
HNHFPOKTXU
CGDQFAKMSA
OMOJDCBQVZ
select dbms_random.string('l',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
fdoeswqqpe
zkicmviacg
zhbbtkqjwl
npwmzvcuhu
select dbms_random.string('a',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
hAVpQccUja
UUwcLqQEZB
rsVpKjKNKx
EJKyUflprQ
select dbms_random.string('x',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
6DV2519VE8
D2XV0YJ7JK
A58FMX7FWS
LIEZB4BBX4
select dbms_random.string('p',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
%VttvY &F@
k33]'bp7fQ
V)w6Nno"sO
7'7p62:'zp

DBMS_RANDOM documentation:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_random.htm#sthref4646

See here how to build an odd/even-random-number-generator:
PL/SQL: Building an odd/even-random-number-generator

Windows: Starting applications with admin-privileges

Usually you shouldn’t do your daily work under an admin-account for security reasons. But from time to time you will have the need to run a certain application or command with administrative privileges.
To keep you from the hassle of switching between your normal- and admin-account you can use the “RUNAS”-command on the command line:

runas /user:johndoe_admin "C:\path\to\your\adminrights_needing_tool.cmd"

After “/user:” you have to enter the name of your admin-account. Press ENTER and you will be prompted for the password of that account. After that the called program will be started and executed with admin-privileges until you terminate it.

It may be helpful to have a Windows command-shell that is already started with admin-rights, so that every command executed under that shell is “admin” too. You can create a link for the following command to get that shell:

runas /user:johndoe_admin "cmd /T:E0"

The parameter “/T:E0” sets the background-color of that shell to yellow so that you can easily distinguish it from your normal shells and are aware of the excessive rights bound to that shell.

It could be the fact that you are not able to start the explorer.exe with admin-rights. This as annoying when you must set some directory or file permissions or have to use the control panel. The solution for this is to create a link to the Internet Explorer (iexplore.exe):

runas /user:johndoe_admin "c:\Program Files\Internet Explorer\IEXPLORE.EXE -e c:"

This command opens the IE with admin-rights, points it to drive “C:” and makes it behave just like the normal file-explorer. As here is no way to make this “admin-explorer” look differently, you should be careful and close it as soon as you no longer need it.

Starting Excel with two windows

When opening two or more files in MS-Excel, each file is shown as a separate entry in the taskbar. But when you click on each of these entries they are all opened in one single instance of Excel – in one single window.

If you have the need to compare two files you may want to bring them up in two windows – especially when you have two displays connected to your PC. To start a second instance of Excel you can press CTRL while starting Excel via startmenu. If you hold down CTRL too long, you will be prompted if you want to start Excel in protected mode – there should be no need to do so and you can just click “NO”.

Oracle: DST struck my RMAN

On monday november 1st I discovered this error in one of my RMAN-logs:

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 11/01/2010 02:46:36
ORA-01455: converting column overflows integer datatype

The Sunday before we had a switch from daylight saving time (DST) back to normal time (minus 1 hour between 2 a.m. and 3 a.m.). Unfortunately my backup run exactly in that very hour and so I had a backupset whose completion time was before its starting time. My Oracle 9.2 RMAN couldn’t cope with that “paradox” and refused to execute commands like “LIST BACKUP” or “CROSSCHECK BACKUP”.

Metalink, or MOS (My Oracle Support), told me that this is fixed in 10.1.0.2. In 9.2 I should query “rc_backup_piece” for all backups “where start_time > completion_time”:

select piece#, bp_key, start_time, completion_time from rc_backup_piece
 where start_time > completion_time;

And then delete these “wrong” backups.

Pity me I still run that DB in NOCATALOG-mode and so I had no view to point that query to. So first I had to set up a RMAN-Catalog on another DB and synchronised my DB’s “RMAN” with it.

After I got the number of the incriminated backupset I deleted it. But when I run RMAN in NOCATALOG-mode afterwards I still got that error – using a catalog all was fine.

Seems as if RMAN didn’t logged the DELETE in my controlfile. The synchronization only goes from controlfile to catalog. So I had a wrong record in my CTL.

Knowing that the space for RMAN-data in CTLs is limited, I gave a try to reduce the control_file_record_keep_time:

alter system set control_file_record_keep_time=1;

Hoping that the wrong data would age out the next day. Well it took a few days longer than that but finally it worked as expected and my CTL is clean now.

More notes on MOS:
Bug 2665255
ID 222392.1

Edit 2012-03-05:
Here’s an action-log of a recent case in that matter:

15$ sqlplus rmancat@mbfas

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 5 14:54:38 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> alter session set NLS_DATE_FORMAT='dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select piece#, bp_key, start_time, completion_time from rc_backup_piece where start_time > completion_time;

    PIECE#     BP_KEY START_TIME          COMPLETION_TIME
---------- ---------- ------------------- -------------------
         1      32126 30.10.2011 02:55:18 30.10.2011 02:07:52

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options



22$ rman catalog rmancat/pwd@mbfas target sys/pwd@ws95

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: WS95 (DBID=1646722213)
connected to recovery catalog database

RMAN> delete backuppiece 32126;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
32126   32124   1   1   AVAILABLE   DISK        /usr2/oracle/mnt2/oradata/ws95/backup/WS95_61mqc68k_1_1_20111030.rmn

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/usr2/oracle/mnt2/oradata/ws95/backup/WS95_61mqc68k_1_1_20111030.rmn recid=8335 stamp=765860118
Deleted 1 objects

RMAN>