March 2017

Validating The Length Of An Oracle Database Hashed password?

Q: Can I validate the length of a password in the Oracle database from the password hash? I have been asked this question a number of times over the years. Even someone emailed me to confirm the answer to this....[Read More]

Posted by Pete On 15/03/17 At 07:52 PM

Starting an instance with sqlplus and running into ORA-27302: failure occurred at: skgpwinit6

Recently I was applying the data dictionary part from an (exadata bundle) patch and ran into the following errors:

12.2 Moving Tables Online (Real Cool World)

One of the really cool new features introduced in Oracle Database 12c Release 2 is the capability to MOVE tables ONLINE, while maintaining all the associated index structures. This was possible for table partitions in Oracle Database 12c Release 1 but only for Indexed Organized Tables before then. Rather than show a demo that just moves a table, […]

12c Release 2, set feedback enhancement in SQL PLus

There’s a nice little touch to the “set feedback” command in SQL PLus in 12.2. There is a new “only” clause in the SET FEEDBACK command, so you can run queries but the resulting rows are not shown in the display. They are still queried, fetched and “returned”, but just rendered to the screen. Here’s an example


$ sqlplus hr/hr

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 14 22:59:15 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 11 2017 01:59:20 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

So that’s normal behaviour. Let’s now use the new ONLY option.

Pi Day, March 14

Geeks around the world will be celebrating Pi day, a shameless excuse to put the month before the day like the Americans do so we can talk Mathematics on “3.14” day Smile

So what better way to show how cool SQL can be with some expressions to approach Pi with continuous series

All we really need to know by way of background is the CONNECT BY LEVEL trick to generate arbirtary sequences of row, eg


SQL> select level from dual connect by level <= 5;

     LEVEL
----------
         1
         2
         3
         4
         5

Now we can get some approximations to Pi using the some of the standard SQL functions SQRT and POWER

Default Password Hashes for 11g Oracle Database

I often get Oracle Security related questions from people randomly sent to my inbox or occasionally on Social media and less on on this sites forum. I get questions on average probably 4 times per week in these ways. I....[Read More]

Posted by Pete On 14/03/17 At 06:16 PM

Little things worth knowing: Executing RDA on RAC

Result! I have finally been able to gather a complete RDA (Oracle Remote Diagnostic Agent) output on my 2 node RAC system. After consulting the relevant documentation on MOS-which is spread over at least 42 Doc IDs-I found them not to be very helpful to the degree that some of what I read is actually wrong or contradicting. I put together a short note, primarily to myself, to remind me of the process. I hope you find it useful, too.

The RDA version I used for this post is 8.14.x from MOS March 4th 2017. My RAC nodes are based on Oracle Linux 7.3/UEK 4.

Starting the data collection

Generating randomized incremental values

In the previous entries, I explained the basics of testdata_ninja and then I explained how to create foreign key relations between your test data and existing tables. This time I will show how you can create data that are random but always increasing, either to create time patterns or to create unique id columns for primary keys. I will extend the format that I have already used in the previous entries, so the code will be familiar.

Swingbench with the AWS Trial

Swingbench is a one of the best choices for easy loads on a database.

Test data and foreign keys

In my last entry on testdata_ninja I showed the basics on how to create your own custom test data generators.

Once you have created the simple ones you probably want to extend it a little bit, and create test tables that has relations. So you want to create random data, but random data that actually can join to another table. The way to do this, is with a special data generator construct in the testdata_ninja format. If you read the old entry, you can see that testdata_ninja format is a series of columns seperated by the "@" sign. Each column has at least 3 parts (divided by the "#" character), where the third is the data generator.