June 2017

PLSQL Test data generators with auto back reference fields

So in another entry I described how to
manually reference back to other fields
to use as input values in other columns for the row generator. What could
be a bit more user friendly than that? Maybe using auto reference fields. So if you are using a generator in one of
your columns that outputs something that can be used as input to another generator, it is now done automatically.

Recursive test data using plsql and the testdata_ninja package

Just the other day I was running a test of a system at work. I have already run several tests
against this system, so I already have a test data generator to generate clients in the system. This
time though I had to generate some orders within the system. So a really quick way to do that is to reuse
the existing generator for the client id reference (a foreign key in the real system) from the orders data
rows. This way I don't have to create an actual client table. I can just reference the plsql test data generator
directly in my definition of my order generator.

orapwd gives OPW-00029 Password complexity failed in #Oracle 12.2

https://uhesse.files.wordpress.com/2017/06/complex1.png?w=600&h=508 600w, htt

My Summer of SQL

I know that right sidebar on my blog has an AWFUL lot of Microsoft events on it.  There’s so many, I’ve begun to use the hashtag #MySummerOfSQL due to it.  For those of you that follow me with Oracle, it doesn’t mean that I’m leaving the Oracle community- not even close.

DIY parallel task execution

We had a question on AskTOM recently, where a poster wanted to rebuild all of the indexes in his schema that had a status of UNUSABLE.  Running the rebuild’s in serial fashion (one after the other) seemed an inefficient use of the server horsepower, and rebuilding each index with a PARALLEL clause also was not particularly beneficial because the issue was more about the volume of indexes rather than the size of each index.

An obvious solution was to use the DBMS_PARALLEL_EXECUTE facility.  Our poster was pleased with our response but then came back asking for help, because they were stuck languishing on an old release for which DBMS_PARALLEL_EXECUTE was not yet present.

Optimizer- Oracle and SQL Server, Hints

So you’re going to see a lot of posts from me in the coming months surrounding topics shared by Oracle and SQL Server.  These posts offer me the opportunity to re-engage with my Oracle roots and will focus on enhancing my SQL Server knowledge for the 2014 and 2016, (2017 soon enough, too) features, which I’m behind in.

I’m going to jump right in with both feet with the topics of hints.  The official, (and generic) definition of a SQL hint is:

“A hint is an addition to a SQL statement that instructs the database engine on how to execute the statement.”

12cR2 Application Containers and Foreign Keys

Application Container brings a new way to share data among databases, and adds a new dimension to referential integrity. A foreign key in an application PDB can reference a row belonging to a root data link table. But then, should a delete on the root validate that there are no orpheans in the application PDBs? And what if those PDBs are closed at the time of this delete? Here is a small example.

If you run this in 12.2.0.1 you will get an error because the search for parent key is done only on the current container. This is considered as a bug: 21955394: CDB:ORA-02291 WHEN FOREIGN KEY REFERS TO THE PRIMARY KEY IN DATA LINK

The example that follows is run with the patch applied to fix this bug.

PLSQL test data from cursor to table or from cursor to csv

When you are creating test data it is usually either to create a quick test table for
a piece of code, or because you need to simulate data extraction to another system. That is why
any generator created with testdata_ninja includes methods to do that quickly. Every generator created
includes a to_table method and a to_csv table function.

It’s just bad code or bad design … most of the time

Some years ago I wrote an article for the UKOUG magazine called “Want a faster database – Take a drive on the M25”.  For those not familiar with the United Kingdom, the M25 is one of its busiest roads (M = “motorway”) and because it moves so much traffic, and runs so close to capacity, it has often been referred to as “the world’s largest car park”.  Many people have probably spent a good part of their lives on the M25 Smile  I used the M25 as a metaphor for how database professionals can focus on the wrong things when trying to solve a performance problem, such as:

“I’m stuck in traffic…perhaps a faster car will help”

ie, throwing CPU at a problem that is not CPU bound will not help things, or

“I’m stuck in traffic…it must be the width of the paint on the lane markings”

12c NSSn process for Data Guard SYNC transport

In a previous post https://blog.dbi-services.com/dataguard-wait-events-have-changed-in-12c/ I mentioned the new processes NSA for ASYNC transport and NSS for SYNC transport. I’m answering a bit late to a comment about the number of processes: yes there is one NSSn process per LOG_ARCHIVE_DEST_n destination in SYNC and the numbers match.

Here is my configuration with two physical standby:
DGMGRL> show configuration
 
Configuration - orcl
 
Protection Mode: MaxPerformance
Members:
orcla - Primary database
orclb - Physical standby database
orclc - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS (status updated 56 seconds ago)