Sometimes the cause of a TNS error is ….
A couple of months ago one of my customers had a failed data ware house report. There was a ORA-12592 (TNS) error message generated. I turned out not to …
Category: Database
How to make a time consistent export dump using the expdp datapump utility
In those old days when there was the exp utility we made a time consistent export dump by using the consistent=y parameter. But today, in fact a couple of …
How to hide login data of sql-scripts on Windows
One of the often given advices on hardening a database is to run scripts without broadcasting your login data at the same time. According to Arup Nanda in his …
SQL> Select * From Alert_XML_Errors;
August 29, 2014
Database, Databases, DBA Oracle, Development Tools, Oracle, Software Development, SQL
No Comments
Once you are able to show the xml version of the alert log as data in database table Alert_XML, it would be nice to checkout the errors with accompanying …
SQL> Select * From Alert_XML;
By mapping an external table to some text file, you can view the file contents as if it were data in a database table. External tables are available since …
Sqlnet tracing during nightly hours…
A TNS error at night… Sometime ago my data warehouse colleague came to me with a TNS error. At night times he runs his batch jobs in order to …
Materialized views: fast refresh, complete refresh or recreate?
Have you ever wondered why it takes a century to completely refresh your materialized view? I did, so I did some testing. Recently I was asked to support a …
Oracle InMemory compared to indexing
In August 2014 Oracle released its RDBM 12.1.0.2 with a potentially useful and exiting new option: Database InMemory. Upon reading about it it became clear to me that this …
Cloud Control authorization with Active Directory
About 2 months ago I wrote an article about setting up user authentication in Cloud control, based on their account in the Active Directory. As promised, here is the …
Oracle Restart to autostart your oracle database, listener and services on linux.
Half a year ago, my colleague Remco wrote an article on auto starting the listener and the databases after a host reboot. As usual with Oracle, there are several …
Getting my hands on a Virtual Machine with Oracle Database 12.1.0.2 Enterprise Edition – 20-30 minutes from start to finish
NOTE: even though the steps described in this article all work – I am running into a problem with shutting down and starting up the VM again. So at …
Cloud Control: How a Camel can make you cross a Data Guard Mountain
July 9, 2014
Cloud, Database, Databases, DBA Oracle, Enterprise Manager, Oracle, Platform Technology
No Comments
In my recent post I already mentioned it a little bit: Integrating your manual Data Guard Broker configuration in OEM Cloud Control can become a sort of climbing a Mountain. …
Connecting Oracle 11g to RDB database with Oracle Heterogenous Services
One of my clients has been running an Oracle RDB database on VMS for decades. This RDB database stores mission critical data and interfaces with COBOL applications and Oracle …
How to add your Standby Cluster Database to Cloud Control
July 6, 2014
Cloud, Database, Databases, DBA Oracle, Enterprise Manager, Platform Technology
No Comments
The last few months I’ve been working with Oracle Data Guard, reading lots of manuals and (parts of) books, to make smart decisions and get a solid foundation. I consider …
Provisioning an Oracle 11g database VirtualBox VM with Vagrant and Puppet for dummies
Outline This post shows you how to setup an Oracle 11g database VM with the use of Vagrant and Puppet in straightforward steps. Anybody can execute this without knowledge …
SQL: combine inline PL/SQL function with inline view in Oracle Database 12c SQL Query
Oracle Database 7.3 was the first to support inline views – using the select * from (select * from) syntax. Oracle Database 9i added the with clause for subquery …
SQL Challenge: Drilling down into World Cup Football Tag Cloud
In a previous article, I have introduced the World Cup Football tag cloud (https://technology.amis.nl/2014/06/22/sql-challenge-dynamically-producing-a-tag-cloud-for-world-cup-football-matches/). I have shown how tags can be associated with football matches, both explicitly and implicitly …
SQL Challenge: Dynamically producing a tag cloud for World Cup Football matches
Tag clouds are a special way to present information about a set of records. Tags are descriptive labels that are associated with specific records. A tag cloud visualizes the …
SQL Challenge: Find World Cup Football matches with a comeback
As I recently said in another blog post, the World Cup Football is such a wonderful source of data that begs to be digested using SQL in many different …
Cloud Control authentication with Active directory
If your company is preferring Microsoft Active Directory as a source of truth (or at least you´re trying to) you should be using the AD as source for the …
The Performance-button on Oracle Warehouse Builder Design Center
Hereby a somewhat embarrasing story about a performance problem with an Oracle Warehouse Builder – database (11.2.0.3). Embarrasing, while it took too much time to figure out what was …
SQL Challenge – World Cup Football 2014 – Retrieving matches and Calculating Group standings
Few things lend itself so well to SQL explorations as sport related data. The imminent World Cup Football (or Soccer in some deprived parts of the world)2014 in Brazil …
Part 2 – Notes from Finland – impressions from the second day of OUGF 2014 Harmony Conference
After a great party with the Oracle house band doing a veritable “The Commitments”-style performance and some excellent food under a largely clear and sunny Finnish Summer sky and …
Spatial: license-incompliancy while using Oracle Locator ?
As you may know, the use of Oracle Locator is free of charge, while Spatial is an option in Oracle Enterprise Edition which have to be paid for. Spatial …
SQL based Charts – Scatterplot for x,y coordinate data sets (growth curve, euro/dollar ratio)
Today, against all priorities, I have resumed an old hobby: the creation of charts with nothing but SQL to work with. Several years back I created pure SQL Hi-Lo …
Fast delete of many files in LINUX and WINDOWS
One of my customers ( Oracle Database 11gR2 on LINUX ) never noticed the Oracle software LUN filling up until a “df -h” reported 97% used, wondered why, and …
Using Oracle’s Edition-Based Redefinition to support multiple SOA Suite revisions with the database adapter
When using the Oracle SOA Suite for web service development, chances are that you are also using an Oracle database and that you are at some point in need …
Named credentials in OEM12c
In Oracle Enterprise Manager 12c (aka OEM) there is such a great facility called “Named Credentials”. This allows someone to create a pair of username/password which can be used …
PL/SQL vs SQL
There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code …
Using Table Functions
Overview of table functions Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database …
Typical
After a presentation by Lucas Jellema I decided to try something with types in Oracle. One of the issues posed in this presentation was that the type cannot self-reference. …
A short guide to networking in Virtual Box with Oracle Linux inside.
This post is intended to be a dummy guide, best practices, or whatever you call it… about setting up a network while working with virtual boxes on a desk- …
Autostart your oracle database and listener on linux
Have you ever been wondering like me why DBCA doesn’t present you with the option, whilst creating your single instance database, to have it autostarted upon system boot? I …
Easy setup of the query_partition_clause of row_number() for dedup(lication)
![Easy setup of the query_partition_clause of row_number() for dedup(lication)]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Easy setup of the query_partition_clause of row_number() for dedup(lication)")
Finding and getting rid of duplicate records in a table is easy…. The row_number() over ( <query_partition_clause> <order_by_clause> ) analytic function is helpful in determining not only the number …
Configuring a private DNS server on Openfiler for use with Oracle RAC 12C on Virtual Boxes
December 30, 2013
AMIS, Architecture, Database, DBA Oracle, Oracle, Oracle 12, Virtualization and Oracle VM
No Comments
To build an Oracle 12C RAC database – on Virtual Boxes – there’s at least shared storage needed for ASM, and a DNS-server for the SCAN-addresses. Several methods can …
Where is my wallet – loosing the encryption master key in 11g db, compatibility in 12c
A while ago I configured an encrypted column in a table for a customer in database 11g. To achieve this, a wallet had to be created outside the database. …
Steven Feuerstein Master Class. Anti-Pattern PL/SQL Programming + 12c New PL/SQL Features, December 12 and 13 2013
12 and 13 December 2013 AMIS will host a two day masterclas with Oracle PL/SQL guru Steven Feuerstein. The Design Pattern movement reminds us that most of the code …
The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
Oracle OpenWorld is a monster event – 10Ks of attendees, thousands of sessions and 100Ks of private conversations that all help convey and define the message about Oracle’s strategy …
Enriching XMLType data using relational data – XQuery and fn:collection in action
One of my colleagues had an interesting challenge. In an Oracle Database he has a table that contains an XMLType column. The column contains XML documents with people collections. …
JavaOne 2013: Java Mission Control
October 6, 2013
AMIS, Development Tools, Java, Oracle, Oracle 12, Oracle WebLogic Server, Software Development, Software Engineering, Tools, Web HTML5 CSS3, Web/Java
1 Comment
The convergence project between Oracle’s JVMs JRockit and Hotspot is making significant progress. Included in the latest Java 7 JDK update (‘7u40’) is a new powerful monitor tool: Java Mission …