Feed aggregator

SQL Server 2016: New Dynamic Management Views (DMVs)

Yann Neuhaus - Fri, 2017-09-29 08:32

In SQL Server 2016, you will discover a lot of new Dynamic Management Views(DMVs).
In this article, I will just give you a little overview of these useful views for us as DBA.

SQL Server 2012 has 145 DMVs and SQL Server 2014 has 166 DMVs.
Now, SQL Server 2016 has 185 DMVs.

How to see it?

It is very easy to have a look using the sys.all_objects view:

SELECT * FROM sys.all_objects WHERE TYPE=’V’ AND NAME LIKE ‘dm_%’ order by name ASC


From SQL Server 2012 to SQL Server 2014, we can notice that a lot of new DMVs comes with the In-Memory technology with the syntax “dm_xtp_xxxxxxxx” or “dm_db_xtp_xxxxxxxx”

In SQL Server 2016, a lot of new “dm_exec_xxxxxxxx” is present.

All definitions for these views come from the Microsoft documentation or web site.

To begin, you will see 10 DMVs for the PolyBase technology:

  • dm_exec_compute_node_status
  • dm_exec_dms_workers

A useful msdn page resumes all DMVs for these new views here

Other dm_exec_xxx views are basically usefull like:

  • dm_exec_query_optimizer_memory_gateways
    • Returns the current status of resource semaphores used to throttle concurrent query optimization.
    • Microsoft Reference here
  • dm_exec_session_wait_stats
    • Returns information about all the waits encountered by threads that executed for each session
    • Microsoft Reference here

3 new DMVs for the Columstore technology:

  • dm_column_store_object_pool
  • dm_db_column_store_row_group_operational_stats
    • Returns current row-level I/O, locking, and access method activity for compressed rowgroups in a columnstore index.
    • Microsoft Reference here
  • dm_db_column_store_row_group_physical_stats
    • Provides current rowgroup-level information about all of the columnstore indexes in the current database
    • Microsoft Reference here

2 new DMVs for Stretch Databases in the database context and with rda(remote database archive):

  • dm_db_rda_migration_status
    • For the current database, list of state information of the remote data archive schema update task.
    • Microsoft Reference here

This list can change if a Service Pack is  applied.
It is just for you to have a little reference view about these useful views! 8-)


Cet article SQL Server 2016: New Dynamic Management Views (DMVs) est apparu en premier sur Blog dbi services.

“_suppress_identifiers_on_dupkey” – the SAP workaround for bad design

Yann Neuhaus - Fri, 2017-09-29 08:01

In SQL, ‘upsert’ is a conditional insert or update: if the row is there, you update it, but if it is not there, you insert it. In Oracle, you should use a MERGE statement for that. You are clearly doing it wrong if you code something like:

when dup_val_on_index then update...

But it seems that there are many applications with this bad design, and Oracle has introduced an underscore parameter for them: “_suppress_identifiers_on_dupkey”. You won’t be surprised that this one is part of the long list of parameters required for SAP.

Let’s investigate this.

Insert – Exception – Update

So the idea is to try first an insert, rely on the unique constraint (primary key) to get an exception if the row exists, and in this case update the existing row. There are several flows with that.

The first problem, is that it is not as easy as it looks like. If a concurrent session deletes the row between you insert and update, then the update will fail. You have to manage this. The failed insert cannot leave a lock on the rows that was not inserted.

The second problem is that the SQL engine is optimized for transactions which commit. When the ‘dup_val_on_index’ on index occurs, you have already inserted the table row, updated some indexes, etc. And all that has to be rolled back when the exception occurs. This generates unnecessary contention on the index leaf block, and unnecessary redo.

Then the third problem, and probably the worst one, is that an exception is an error. And error management has lot of work to do, such as looking into the dictionary for the violated constraint name in order to give you a nice error message.

I’ve created the following table:

create table demo as select * from dual;
create unique index demo on demo(dummy);

And I’ve run 10 million inserts on it, all with duplicates:

exec for i in 1..1e7 loop begin insert into demo values('x'); exception when others then null; end; end loop;

Here is some extracts from the AWR on manual snapshots taked before and after.

Elapsed: 20.69 (mins)
DB Time: 20.69 (mins)

This has run for 20 minutes.

Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 100.00
Execute to Parse %: 33.34 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 92.31 % Non-Parse CPU: 94.90
Flash Cache Hit %: 0.00

The ‘Execute to Parse %’ show that 2/3 of statements are parsed each time.

SQL ordered by Gets DB/Inst: CDB1/CDB1 Snaps: 19-20
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> %Total - Buffer Gets as a percentage of Total Buffer Gets
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Total Buffer Gets: 180,125,740
-> Captured SQL account for 127.7% of Total
Buffer Gets Elapsed
Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
1.80094E+08 1 1.800942E+08 100.0 1,239.8 99.5 .3 frvpzg5yubp29
Module: java@VM104 (TNS V1-V3)
BEGIN for i in 1..1e7 loop begin insert into demo values('x'); exception when ot
hers then null; end; end loop; END;
1.60094E+08 10,000,000 16.0 88.9 983.1 100.3 .4 319ypa1z41aba
Module: java@VM104 (TNS V1-V3)
49,999,995 9,999,999 5.0 27.8 201.1 103.2 0 2skwhauh2cwky
select o.name, u.name from obj$ o, user$ u where o.obj# = :1 and o.owner# = u.u
19,999,998 9,999,999 2.0 11.1 148.5 98.9 0 2jfqzrxhrm93b
select /*+ rule */ c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# =
cd.con# and cd.enabled = :1 and c.owner# = u.user#

My failed inserts have read on average 16 blocks for each attempt. that’s too much for doing nothing. And in addition to that, I see two expensive statements parsed and executed each time: one to get the object name and one to get the constraint name.
This is how we can retreive the error message which is:

ORA-00001: unique constraint (SCOTT.DEMO) violated

This is a big waste of resource. I did this test in PL/SQL but if you cumulate all worst practices and run those inserts row by row, then you will see those colors:

The Orange is ‘Log File Sync’ because you generate more redo than necessary.
The Green is ‘CPU’ because you read more blocks than necessary.
The read is ‘SQL*Net break/reset to client’ when the server process sends the error.


When you set “_suppress_identifiers_on_dupkey” to true, Oracle will not return the name of the constraint which is violated, but only the information which is already there in the session context.

Here is the message that you get:

ORA-00001: unique constraint (UNKNOWN.obj#=73375) violated

Where 73375 is the OBJECT_ID of the index where the unique constraint exception has been violated.

You have less information, but it is faster:

Elapsed: 15.45 (mins)
DB Time: 15.48 (mins)

There is no Soft Parse overhead:

Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 96.43
Execute to Parse %: 99.98 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 90.38 % Non-Parse CPU: 99.95
Flash Cache Hit %: 0.00

Our statement is the only one using the CPU and reads less blocks:

SQL ordered by Gets DB/Inst: CDB1/CDB1 Snaps: 21-22
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> %Total - Buffer Gets as a percentage of Total Buffer Gets
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Total Buffer Gets: 110,132,467
-> Captured SQL account for 81.8% of Total
Buffer Gets Elapsed
Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
1.10091E+08 1 1.100906E+08 100.0 926.2 98.8 1 frvpzg5yubp29
Module: java@VM104 (TNS V1-V3)
BEGIN for i in 1..1e7 loop begin insert into demo values('x'); exception when ot
hers then null; end; end loop; END;
90,090,580 10,000,000 9.0 81.8 515.7 99.1 1.9 319ypa1z41aba
Module: java@VM104 (TNS V1-V3)

This parameter is a workaround for bad design, but not a solution.

Update – no rows – Insert

In order to avoid all this rollback and exception management overhead, there is another idea. Start with the update and, when no row was found, insert it. This is easy with the ROWCOUNT.

update ...
if SQL%ROWCOUNT = 0 then insert ...

This is more efficient but still subject to a concurrent session inserting the row between your update and you insert. But at least, you manage the different scenario with a condition on ROWCOUNT rather than with an exception, which is more scalable.

So what?

Always use the database in the expected way. Exceptions and Errors are not for the normal scenario of the use-case. Exceptions should be unusual. The solution is to use the MERGE statement which has been implemented exactly for this reason: do an upsert without the error management overhead and with the statement isolation level which prevents errors in a multi-user environment.


Cet article “_suppress_identifiers_on_dupkey” – the SAP workaround for bad design est apparu en premier sur Blog dbi services.

Businesses Transform Customer Engagements with Oracle Live Experience Cloud

Oracle Press Releases - Fri, 2017-09-29 07:00
Press Release
Businesses Transform Customer Engagements with Oracle Live Experience Cloud Accelerates Transformation through Rich, Highly Personalized Mobile-First Interactions

Redwood Shores, Calif.—Sep 29, 2017

Oracle today announced Oracle Live Experience Cloud, a customer engagement service for the mobile generation. With the mobile and digital landscape shaping the way customers interact with businesses, companies must quickly adapt to changing expectations to deliver frictionless, real-time, contextual experiences across channels. With Oracle Live Experience Cloud, users can address these new requirements and bring a new dimension to their mobile and business applications by being able to serve customers in the way that best meets their needs, be it HD voice, HD video, screen sharing, and annotations.

As such, businesses will have the ability to quickly resolve customer issues, drive greater customer loyalty, and increase satisfaction by engaging users on the right channel at the right time. Agents will also be empowered to deliver better customer experiences by having access to contextual customer data and insights, cutting call times and limiting customer frustration.

“Nearly 70% of IT and business leaders say ‘improving customer experience’ is the goal of their digital transformation initiative, and advancements in the contact center are crucial to success,” says Robin Gareiss, president of Nemertes Research1. “Successful digital transformation requires short time to market. By leveraging a cloud-based solution, organizations can start seeing improvements in CSAT scores, revenue, and customer retention immediately. What’s more, the ability to retain context across channels from within the native app is a huge development and will dramatically boost customer satisfaction.”

A recent Oracle report titled “The Future of Enterprise Communications: The Cloud Redefines Customer Experience” noted that while 65 percent of companies agree communications embedded within cloud applications will become the dominant way of communicating with employees, suppliers and customers, many currently lack the ability to do so effectively. Oracle Live Experience Cloud enables embedded contextual data and business analytics so users can easily switch between channels without losing key information already shared. Regardless of the customer’s preferred channel, the user will enjoy a more streamlined experience while the business gains valuable customer insights that can be leveraged within its core business applications.

“Nothing is more aggravating than dealing with a call center or service desk where you are stuck in a long, dehumanized loop of menu options with a slow resolution,” said Doug Suriano, senior vice president, general manager, Oracle Communications. “With Oracle Live Experience Cloud, businesses can eliminate customer friction points by harnessing the power of contextual communications and real-time engagement capabilities to offer a personalized and highly interactive digital experience that builds customer loyalty and improves business outcomes.”

 A cloud-native solution, Oracle Live Experience Cloud can be easily integrated into web and mobile apps and used to proactively engage customers at key moments of their individual journey. It modernizes existing Call Center and CRM solutions, supporting enterprise digital transformation efforts employees to deliver contextual and responsive cross-channel engagements that satisfy the customer and ultimately drive sales. Finally, businesses can optimize engagement success by measuring interactions in real-time and provisioning updates to further improve overall business results.

Features of Oracle Live Experience Cloud
  • Real time communication capabilities - HD voice, HD video, screen sharing, annotating
  • In application channels and mobile controls
  • Rules based contextual  routing for all channels
  • Escalate from chat bot to live assistance
  • Customer context
  • Real-time recording, search and playback
  • Integrated Analytics
  • Pre-built API integrations for key CRM systems
  • Modern desktop agent experience
  • Design personalized engagement scenarios based on context, history and business priorities
  • Insights on individual and overall service team performance, engagement success KPIs, with supervisor and administrator views.
  • Encryption and Bring Your Own Key capabilities
  • Elastic network, compute, and storage resources optimized at all layers for real time communication service
Contact Info
Katie Barron
Kristin Reeves
Blanc & Otus
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at oracle.com.

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality and should not be relied upon in making purchasing decisions. The development, release and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation.

1 Nemertes 2017-18 Contact Center and Customer Engagement Benchmark Research Study | https://nemertes.com/

Talk to a Press Contact

Katie Barron

  • +1.202.904.1138

Kristin Reeves

  • +1.415.856.5145

Calling Procedure Parallel

Tom Kyte - Fri, 2017-09-29 05:06
I have below procedure which in turn calls two other Procedures. It calls and works fine but the two procs runs serial. I want to run them parallel and get the results on the main procs cursor. How do I do that? I tried with dbms_job.submit but could...
Categories: DBA Blogs

Getting sub-string from two Clobs object and compare those substrings

Tom Kyte - Fri, 2017-09-29 05:06
Hi, I am new to CLOB objects but seems like I need to get my hands dirty on this. I have a CLOB column in my table and I need to get item SKU values from this column separated by commas. This is hoe my CLOB Column value looks like. ------- <...
Categories: DBA Blogs

External table concepts

Tom Kyte - Fri, 2017-09-29 05:06
Hi All, I am new to oracle external table concepts. Have a very basic query - if i have a csv with the below columns Col1, Col2, Col3 Col4 .... Coln and i want to insert only Col3 & Col4 into an oracle external table , what would be my ...
Categories: DBA Blogs

sql query to update a table based on data from other table

Tom Kyte - Fri, 2017-09-29 05:06
Hi, Looks like my other similar questions got closed, so asking a new question. I have a cust_bug_data table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows: <code>create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER N...
Categories: DBA Blogs

Oracle Big Data Cloud Service – Compute Edition

Yann Neuhaus - Fri, 2017-09-29 03:00

In this blog post, we will see how to create a Big Data cluster through the Oracle Cloud Services. If you want more details about the Oracle Big Data cloud services offering, you can refer to my previous blog Introduction to Oracle Big Data.

First, you need to create your trial account through the following link: https://cloud.oracle.com/tryit. Note that, when you create your trial account, all information (phone number, address, credit card…), must be from the same country. Otherwise, you will get an error message.

Then you will get an email from Oracle with your connection information. The 4 main connection information are:

During the first connection you need to change your password and answer to 3 secret questions.

You are now login into the Oracle Cloud Services Dashboard. Select the “Big Data – Compute Edition” service to create your cluster.

BDCS-CE Dashboard

Click on “Service” and “Create Service”.

BDCS-CE Create Cluster

First, complete the service information. Cluster name, description… and click on “Next”.

BDCS-CE Cluster creation

Then, you enter the details of your Big Data cluster (configuration, credentials, storage…).

Cluster configuration:

Use the “full” deployment. It will provision a cluster with Spark, MapReduce, Zeppelin, Hive, Spark Thrift, Big Data File System.


Generate an ssh public key and insert it (see screenshot below). Update or keep the current Administrative user / password which is very important for the next operations.


Oracle Public Cloud is working with Object storage container. Which means that, a storage container can be used by all cloud services. For the Big Data Service you need to use an existing storage container or create one. The storage container name must follow a specific syntax.


Example: https://axxxxxx.storage.oraclecloud.com/v1/Storage-axxxxxx/dbistorage

You can find the complete configuration below.

BDCS-CE Configuration Overview

Confirm your cluster configuration and click on “Next”.

During the cluster deployment, you can take the time to read the documentation: https://docs.oracle.com/en/cloud/paas/big-data-cloud/index.html

Once your services has been deployed, you can access to the Big Data Cluster Console, to monitor your cluster and access it.

BDCS-CE Cluster Overview


BDCS-CE Cluster Console

OBDCS-CE Monitoring

You have now deployed an Big Data cluster composed by 3 nodes, based on HortonWorks distribution with the following tools:

  • HDFS = Hadoop Distributed FileSystem
  • YARN = Resources management for the cluster
  • Hive = Data Warehouse for managing large data sets using SQL
  • Spark= Data processing framework
  • Pig = High-level platform for creating programs that runs on Hadoop
  • ZooKeeper = Hadoop cluster scheduler
  • Zeppelin = Data scientist workbench, web based.
  • Alluxio = Memory speed virtual distributed storage
  • Tez = Framework for YARN-based, Data Processing Applications In Hadoop

Your Oracle Big Data cluster, through Oracle Big Data Cloud Service – Compute Edition is now ready to use.

Enjoy ;-)


Cet article Oracle Big Data Cloud Service – Compute Edition est apparu en premier sur Blog dbi services.

Introduction to Oracle Big Data Services

Yann Neuhaus - Fri, 2017-09-29 01:00

Since few years, Oracle decided to move forward in the Big Data area, as their main competitor. The goal of this blog post is to explain you, how the Oracle Big Data offering is composed.

As the Oracle Big Data offering is continuously improving, I’m always open to your feedback :-)

Oracle Big Data offering is split in 2 parts:

  • On-Premise
  • Public Cloud

Note: It’s important to know, that the 2 main Big Data distribution on the market are Cloudera and Hortonworks. We will see later how Oracle stands with this 2 main distributions.

On-premise: Oracle Big Data Appliance:

The main product of the Oracle Big Data offering is the Oracle Big Data Appliance. OBDA is an engineered systems based on the Cloudera distribution. The Big Data appliance offers you an easy-to-deploy solution with Cloudera manager for managing a Big Data cluster including a complete Hadoop ecosystem ready-to-use.

Oracle Big Data Appliance starts with a “Starter” rack of 6 nodes for a storage capacity of 96TB. Below the details configuration per nodes.

Oracle X6-2 server:

  • 2 × 22-Core Intel ® Xeon ® E5 Processors
  • 64GB Memory
  • 96TB disk space

Oracle Big Data Appliance is a combination of open source software and proprietary software from Oracle (i.e Oracle Big Data SQL). Below a high-level overview of Big Data Appliance software.

Screen Shot 2017-09-27 at 08.25.45

Oracle Big Data Cloud Machine:

On customer side, Oracle offers the Oracle Big Data Cloud Machine (BDCM). Fully managed by Oracle as it’s a PaaS service (Platform as a Service), based on customer infrastructures, designed to provide Big Data Cloud Service. The BDCM is a Big Data Appliance managed and operated by Oracle in customer’s data center.

The Big Data Cloud Machine starts with a “Starter Pack” of 3 nodes. Below the minimal configuration:

  • 3 nodes
  • 32 OCPU’s per node
  • 256GB RAM per node
  • 48TB disk space per node

Oracle Big Data Cloud Machine princing: https://cloud.oracle.com/en_US/big-data/cloudmachine/pricing

Oracle Public Cloud:

Oracle provides several deployment and services for Big Data:

  • Oracle Big Data Cloud Services
  • Oracle Big Data Cloud Services – Compute Edition
  • Event Hub Cloud Services (Kafka as a Service)
  • Oracle Big Data SQL Cloud Service

Oracle public cloud services, including Big Data, is available in two payment methods, metered and non-metered.

  • Metered: You are charged on the actual usage of the service resource :
    • OCPU/hour
    • Environment/hour
    • Host/hour
    • For the storage : GB or TB/month
  • Non-metered: Monthly or annual subscription for a service and it’s not depending on the resources usage. Charging is performed monthly.

For more information you can refer to the following links:


Oracle Big Data Cloud Services:

OBDCS is a dedicated Big Data Appliance in the public cloud. An engineered system managed and pre configured by Oracle. OBDCS is a large system from the start with Terabytes of storage.

The offering starts with a “Starter pack” of 3 nodes, including:

  • Platform as a Service
  • 2 payments methods: metered and non-metered
  • SSH connection to cluster nodes
  • Cloudera’s Distribution including Apache Hadoop, Enterprise Data Hub Edition
  • Oracle Big Data Connectors
  • Oracle Copy to Hadoop
  • Oracle Big Data Spatial and Graph

The cost entry is very high, that’s why this service is recommended for large and more mature business cases.

Pricing information: https://cloud.oracle.com/en_US/big-data/big-data/pricing

Oracle Big Data Cloud Services – Compute Edition:

OBDCS-CE provides you a dedicated Hadoop cluster based on Hortonworks distribution. The cost entry is smaller than Oracle Big Data Cloud Service, that’s why this service is more suitable for small business use case and proof and concept.

OBDCS-CE offering details:

  • Platform as a Service
  • 2 payments methods: metered and non-metered
  • Apache Hadoop cluster based on Hortonworks distribution
  • Free number of nodes for the deployment – 3 nodes is the minimum for a High Availability cluster, recommended for production. You can actually have one node clusters, but this is obviously not recommended.
  • Apache Zeppelin for Hive and Spark analytic
  • 3 access methods:
    • BDCS-CE console (GUI)
    • REST API
    • SSH

Pricing information: https://cloud.oracle.com/en_US/big-data-cloud/pricing

Summary Engineered systems PaaS On-Premise (customer side) - Big Data Appliance (BDA)- Big Data Cloud Machine (BDA managed by Oracle) Oracle Cloud Machine (OCM)  + BDCS – Compute edition Oracle Public Cloud Big Data Cloud Service (BDCS) – a BDA in Oracle public cloud – Cloudera distribution Big Data Cloud Service – Compute edition – Hortonworks distribution

More details about Oracle PaaS offering:


I hope, this blog will help you to better understand the Oracle Big Data offering and products.


Cet article Introduction to Oracle Big Data Services est apparu en premier sur Blog dbi services.

Scheduler Jobs Do Not Run Automatically

Michael Dinh - Thu, 2017-09-28 21:09

After you have followed – IF: Jobs Do Not Run Automatically (Doc ID 2084527.1) – without any success,
then check to see if services have been created and are running.

RAC DB is and was cloned from standby.

It just so happens, service as defined from the SQL below was not created:

select c.SERVICE
from dba_scheduler_jobs j, dba_scheduler_job_classes c
and j.JOB_NAME=UPPER('&jobname')

To be honest, I was not able to find the issue and team mate did.

What I found very, very strange is manually running the job using exec dbms_scheduler.run_job is successful.

The manual job ran successfully without the service created and on the wrong node for where the service is defined
(the service is defined to run on node 2, while the manual run is from node 1).

Another unsolved mystery.

What are the benefits of Manufacturing Dashboards?

Nilesh Jethwa - Thu, 2017-09-28 16:15

Today in the US economy, the major players in the manufacturing industry are electronics, automobile, steel, consumer goods, and telecommunications. And as they offer more advanced products, including tablets and smartphones. These technological advancements significantly influence consumer lifestyles.

Along with these changes, the global manufacturing industry is currently embracing a new key player called metrics based manufacturing. This is actually the latest trend that industries need to consider in their sales funnel. So, what does this mean?

Read more at http://www.infocaptor.com/dashboard/manufacturing-dashboards-what-are-their-benefits

Updating records with many-to-1 linked table relationship

Tom Kyte - Thu, 2017-09-28 10:46
I have an MS_ACCESS Query to convert to Oracle SQL. Access Query <code>UPDATE target_table T INNER JOIN source_table S ON T.linkcolumn = S.linkColumn SET T.field1 = S.field1, T.field2 = S.field2, T.field3 = S.field3;</code> Note: T...
Categories: DBA Blogs

Native dynamic sql - Refcursor

Tom Kyte - Thu, 2017-09-28 10:46
Tom, Here is an example...that i want to change one function to avoid redundant information. create or replace package p_ref_cursor is type ret_ref_cursor is ref cursor; end p_ref_cursor; / drop table "tab1"; create table "tab1" ...
Categories: DBA Blogs

ORA-06502 with CHAR parameter. What am I missing?

Tom Kyte - Thu, 2017-09-28 10:46
Sorry to bother you with a ORA-06502 error. But I'm not understanding this behavior. As I saw, the length fits (see the dbms_output in result showing that the length is 16). The only thing i can think is that in the procedure proc, pl/sql is...
Categories: DBA Blogs

Windows and .NET sessions at Openworld

Christian Shay - Thu, 2017-09-28 09:25
Interested in Oracle Database on Windows performance and security, Active Directory, or .NET development topics? At Oracle Openworld SF next week there's a host of Windows and .NET sessions, hands on labs, and demogrounds for you to check out.

Here's the list of Windows sessions and demogrounds with times.
And here's the list of .NET development sessions, hands on lab, and demogrounds with times.

Use schedule builder to reserve your seats in any of those sessions before they fill up.

You can also visit us at our booth at the Moscone West "Exchange" (formerly known as "Demogrounds"). We'll have .NET experts as well as Oracle Database on Windows experts standing by to answer your questions or to give you a demo.

You can find us using this handy dandy map (we are on the left side of the exhibition hall with other Oracle application development booths) - click the image to enlarge:

See you at the show!!!

Announcing the dbi OpenDB Appliance

Yann Neuhaus - Thu, 2017-09-28 07:04

As already announced on Twitter and LinkedIn here is the blog post to describe our OpenDB appliance in more detail. I am sure you wonder what this is about so let me explain why we are doing this. What we do see day by day at our customers is that more and more databases get consolidated on to a VMWare deployment. This is not only true for the smaller ones of those but also for the critical, potentially much bigger ones. What makes it complicated, especially for smaller companies that do not necessarily have the knowhow for the specific database, is that you need to apply the best practices not only to the database deployment but also to the operating system and the VMWare deployment. But even if you have this already in place: Do you know how to deploy the PostgeSQL binaries, how to setup a PostgreSQL instance, how to monitor and how to backup and restore all that? Do you know how to do this with MySQL/MariaDB, MongoDB, Cassandra? If your answer to this is no but you need to have a PostgreSQL/MySQL/MariaDB/MongoDB/Cassandra instance ready quite fast then the dbi OpenDB Appliance might be the solution for you. Let’s dig into some details.


A typical use case: You are forced to support an application which is running on a specific database. What do you do? Quickly setup a Linux VM, download the installer, clicking next, next, next and hopefully make the application connect to what you just installed and then cross your fingers and hope that never ever something goes wrong? You laugh? There are deployments out there which got setup in exactly this way. Another option would be to hire someone who is experienced in that area. This will not help you either as you’d at least need two people (because people tend to want to go to holidays from time to time). The next option would be to work together with external consultants which probably will work as long as you work with the right ones. Completely outsourcing the stuff is another option (or even going to the cloud), if you want to do that. With the dbi OpenDB Appliance you’ll get another option: We deliver a fully pre-configured VMWare based virtual machine image which you can easily plug into your existing VMWare landscape. Can that work? Let me explain what you would get:

As said just before you get an image which you can import into your VMWare ESX. I said this image is pre-configured, what does that mean? Well, when you start it up it boots into a CentOS 7.3 x64 Linux operating system. No magic, I know :) Additionally you’ll get four pre-configured disks:

/       15GB    The Linux operating system
/boot	1GB	The boot images (kernels)
/u01	50GB	All files belonging to the OpenDB itself
                All required DMK packages
                All source files (PostgreSQL, MariaDB, MongoDB, Cassandra)
                The Linux yum repositories
                The HOMEs of all product installations
                The admin directories for the initialized products
/u02	10GB	The data files belonging to the initialized products
/u03	10GB	The redo/wal files belonging to the initialized products
/u04	10GB	Backups

You are not supposed to touch the root, /boot and /u01 partitions but of course you will be able to resize /u02 to /u04. The 10GB provided initially are just meant as minimum setup. Resize your VMWare disk images (vmdks) and the dbi OpenDB command line utility offers you to resize the file systems as well with just a single call. At this point you probably wonder what the dbi OpenDB command line utility is about. In short this is a wrapper around our various DMK packages. Using one of the various DMK packages you can deploy and monitor databases even today. The command line utility makes use of that and wraps around the various DMKs. The interface is menu driven to make it as easy as possible for you and helps you with initializing the appliance (setting the hostname, network configuration and disk resizing). In addition you can install the products we support and create database instances on top of that without knowing the details. We take care of implementing the best practices in the background (kernel settings, file system layout, initialization parameters, …). But that is not all: We’ll go a step further and implement monitoring, alerting and backup procedures as well. The idea is that you really do not need to take care of such things: It just comes when you setup a product.

To give you an idea you’ll get something like this when you fire up the command line utility:

=                                                                                            =
=                                                                                            =
=       _ _    _    ___                 ___  ___     _             _ _                       =
=    __| | |__(_)  / _ \ _ __  ___ _ _ |   \| _ )   /_\  _ __ _ __| (_)__ _ _ _  __ ___      =
=   / _  | '_ \ | | (_) | '_ \/ -_) ' \| |) | _ \  / _ \| '_ \ '_ \ | / _  | ' \/ _/ -_)     =
=   \__,_|_.__/_|  \___/| .__/\___|_||_|___/|___/ /_/ \_\ .__/ .__/_|_\__,_|_||_\__\___|     =
=                       |_|                             |_|  |_|                             =
=                                                                                            =
=                                                                                            =
=      Please make a selection from the menu below (type 'q' to exit):                       =
=                                                                                            =
=      1. Deploy a database home                                                             =
=      2. List the deployed database homes                                                   =
=      3. Setup a database instance                                                          =
=      4. List the deployed database instances                                               =
=                                                                                            =
=     10. Stop and remove a database instance                                                =
=     11. Remove a database home                                                             =
=                                                                                            =
=                                                                                            =
=     99. Initialize the appliance                                                           =
=                                                                                            =
=                                                                                            =
 Your input please: 

You would start by “Initialize the appliance” to set your preferred host name, to initialize the network and to provide the monitoring credentials. Once done you can go on and start deploying product homes (e.g. PostgreSQL) and instances on top of that. Of course you can deploy multiple instances on the same home and you can install several homes of the same product version.

What do we mean by a “product”? A product is what we support with a specific release of the appliance. Initially this probably will be:

  • PostgreSQL 9.6.5
  • PostgreSQL 9.5.9

So the menu would offer you something like this for deploying the binaries:

=                                                                                            =
=                                                                                            =
=       _ _    _    ___                 ___  ___     _             _ _                       =
=    __| | |__(_)  / _ \ _ __  ___ _ _ |   \| _ )   /_\  _ __ _ __| (_)__ _ _ _  __ ___      =
=   / _  | '_ \ | | (_) | '_ \/ -_) ' \| |) | _ \  / _ \| '_ \ '_ \ | / _  | ' \/ _/ -_)     =
=   \__,_|_.__/_|  \___/| .__/\___|_||_|___/|___/ /_/ \_\ .__/ .__/_|_\__,_|_||_\__\___|     =
=                       |_|                             |_|  |_|                             =
=                                                                                            =
=                                                                                            =
=      Please make a selection from the menu below (type 'q' to exit, 'b' to go back):       =
=                                                                                            =
=                                                                                            =
=     000 - PostgreSQL 9.6.5                                                                 =
=     001 - PostgreSQL 9.5.9                                                                 =
=                                                                                            =
=                                                                                            =
 Your input please: 

Once you have deployed the homes you require you can list them:

=                                                                                            =
=                                                                                            =
=       _ _    _    ___                 ___  ___     _             _ _                       =
=    __| | |__(_)  / _ \ _ __  ___ _ _ |   \| _ )   /_\  _ __ _ __| (_)__ _ _ _  __ ___      =
=   / _  | '_ \ | | (_) | '_ \/ -_) ' \| |) | _ \  / _ \| '_ \ '_ \ | / _  | ' \/ _/ -_)     =
=   \__,_|_.__/_|  \___/| .__/\___|_||_|___/|___/ /_/ \_\ .__/ .__/_|_\__,_|_||_\__\___|     =
=                       |_|                             |_|  |_|                             =
=                                                                                            =
=                                                                                            =
=      Please make a selection from the menu below (type 'q' to exit, 'b' to go back):       =
=                                                                                            =
=                                                                                            =
=     The following homes are available for deploying instances on:                          =
=                                                                                            =
=                                                                                            =
=     pg965:/u01/app/opendb/product/PG96/db_5/:dummy:9999:D                                  =
=     PG959:/u01/app/opendb/product/PG95/db_9/:dummy:9999:D                                  =
=     PG959_1:/u01/app/opendb/product/PG95/db_9_0:dummy:9999:D                               =
=     PG965_1:/u01/app/opendb/product/PG96/db_5_0:dummy:9999:D                               =
=                                                                                            =
=                                                                                            =
 Your input please: 

Here you can see that you can have multiple homes of the same release (two for PostgreSQL 9.6.5 and two for PostgreSQL 9.5.9 in this case). The path and naming for a home follow our best practices and are generated automatically. Having the homes you can start deploying you instances:

=                                                                                            =
=                                                                                            =
=       _ _    _    ___                 ___  ___     _             _ _                       =
=    __| | |__(_)  / _ \ _ __  ___ _ _ |   \| _ )   /_\  _ __ _ __| (_)__ _ _ _  __ ___      =
=   / _  | '_ \ | | (_) | '_ \/ -_) ' \| |) | _ \  / _ \| '_ \ '_ \ | / _  | ' \/ _/ -_)     =
=   \__,_|_.__/_|  \___/| .__/\___|_||_|___/|___/ /_/ \_\ .__/ .__/_|_\__,_|_||_\__\___|     =
=                       |_|                             |_|  |_|                             =
=                                                                                            =
=                                                                                            =
=      Please make a selection from the menu below (type 'q' to exit, 'b' to go back):       =
=                                                                                            =
=                                                                                            =
=     Please specify an alias for your new instance                                          =
=       The alias needs to be at least 4 characters                                          =
=       The alias needs to be at most  8 characters                                          =
=                                                                                            =
=                                                                                            =
=                                                                                            =
 Your input please: MYINST1 

What happens in the background then is that the PostgreSQL cluster is initialized, started and added to the auto start configuration (systemd) so that the instance will properly shutdown when the appliance is stopped and comes up when the appliance is started. Listing the deployed instances is possible, too, of course:

=                                                                                            =
=                                                                                            =
=       _ _    _    ___                 ___  ___     _             _ _                       =
=    __| | |__(_)  / _ \ _ __  ___ _ _ |   \| _ )   /_\  _ __ _ __| (_)__ _ _ _  __ ___      =
=   / _  | '_ \ | | (_) | '_ \/ -_) ' \| |) | _ \  / _ \| '_ \ '_ \ | / _  | ' \/ _/ -_)     =
=   \__,_|_.__/_|  \___/| .__/\___|_||_|___/|___/ /_/ \_\ .__/ .__/_|_\__,_|_||_\__\___|     =
=                       |_|                             |_|  |_|                             =
=                                                                                            =
=                                                                                            =
=      Please make a selection from the menu below (type 'q' to exit, 'b' to go back):       =
=                                                                                            =
=                                                                                            =
=     The following instances are currently deployed:                                        =
=                                                                                            =
=                                                                                            =
=     MYINST1:/u01/app/opendb/product/PG96/db_5/:/u02/opendb/pgdata/MYINST1:5432:Y           =
=                                                                                            =
=                                                                                            =
 Your input please: 

The cronjobs for monitoring, alerting and backup have been created as well:

[opendb@opendb ~]$ crontab -l
00 01 * * * /u01/app/opendb/local/dmk/dmk_postgres/bin/dmk-pg-dump.sh -s MYINST1 -t /u04/opendb/pgdata/MYINST1/dumps >/dev/null 2>&1
58 00 * * * /u01/app/opendb/local/dmk/dmk_postgres/bin/dmk-pg-badger-reports.sh -s MYINST1 >/dev/null 2>&1
*/10 * * * * /u01/app/opendb/local/dmk/dmk_postgres/bin/dmk-check-postgres.sh -s MYINST1 -m  >/dev/null 2>&1

With every new release/update of the appliance we plan to include more products such as MariaDB/MongoDB/Cassandra, provide patch sets for the existing ones and update the Linux operating system. Updates will be delivered as tarballs and the command line utility will take care of the rest, you do not need to worry about that. You can expect updates twice a year.

To visualize this:

/u02 will hold all the files that contain your user data. /u03 is there for redo/wal/binlog where required and /u04 is for holding the backups. This is fixed and must not be changed. Independent of which product you choose to deploy you’ll get a combination of pcp (Performance Co-Pilot) and vector to do real time performance monitoring of the appliance (of course configured automatically).

Alerting will be done by a combination of third party (open source) projects and DMK. The tools we’ll use for PostgreSQL will be check_postgres and pgbadger, for example. For the other products we’ll announce what we will use when it will be included in a future release.

In addition to the VMWare template you can have the appliance also in the Hidora Cloud as a pay as you go service (although that is not fully ready).

If you have any questions just send as an email to: opendb[at]dbi-services[dot]com


Cet article Announcing the dbi OpenDB Appliance est apparu en premier sur Blog dbi services.

Commonwealth Edison, Baltimore Gas & Electric Awarded the 2017 Oracle Sustainability Innovation Awards

Oracle Press Releases - Thu, 2017-09-28 06:45
Press Release
Commonwealth Edison, Baltimore Gas & Electric Awarded the 2017 Oracle Sustainability Innovation Awards Leading Utility Companies Recognized for Energy Efficiency Programs

Redwood Shores, Calif.—Sep 28, 2017

Oracle today announced that Commonwealth Edison (ComEd) and Baltimore Gas and Electric (BGE) will be presented with the 2017 Oracle Sustainability Innovation Awards at Oracle OpenWorld. The awards recognize customers that are committed to making environmental issues a priority across the enterprise. ComEd and BGE showcased unique ways to advance energy efficiency through innovative green practices using Oracle technology. ComEd leadership was distinguished by earning the 2017 Oracle Chief Sustainability Office of the Year award among all nominations.

“We are inspired by the successes achieved by ComEd and BGE as part of their core commitments to a sustainable future. Both companies have implemented Oracle technologies in a way that improved eco-efficiencies, innovation and transparency,” said Rodger Smith, senior vice president and general manager, Oracle Utilities. “We’re honored to present them with the 2017 Oracle Sustainability Innovation Award during Oracle OpenWorld this year.”

Both companies have made significant impact by employing the Oracle Utilities Opower Energy Efficiency solution. The solution includes Home Energy Reports (HERs) and online web tools that provide customers with information about their energy consumption, thereby encouraging conservation. Since 2009, ComEd has generated over 1.1 TWh in energy savings; BGE has used the program since 2012 to save 420,000 MWh. The two organizations have also successfully implemented Peak Management programs, which encourage customers to reduce their energy consumption when electricity demand is high. Because of this BGE has reduced peak demand by 16 percent for participating customers, and ComEd has reduced Greenhouse gases by 10 percent.

“We’re delighted to be recognized for our commitment to sustainable practices,” said Mark Case, vice president of Regulatory Policy and Strategy for BGE who leads energy efficiency initiatives for Exelon’s utilities. “We’ve seen great success and look forward to continuing to work with Oracle to drive energy efficiency.”

“At ComEd, we believe that meeting the energy needs of today and tomorrow will depend heavily on our ability to collaborate with our customers,” Val Jensen, senior vice president, Customer Operations at ComEd. “Working with Oracle gives us more opportunities to leverage data-driven insights and engage with our customers to drive energy efficiency.”

Contact Info
Valerie Beaudett
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe, and Asia. For more information about Oracle, please visit us at www.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Valerie Beaudett

  • +1.650.400.7833

Searching wikipedia from the command line

Yann Neuhaus - Thu, 2017-09-28 06:41

Wouldn’t it be nice if you could search wikipedia from the command line? I often need to quickly look up a definition or want to know more about a specific topic when I am working on the command line. So here is how you can do it …

What you need is npm and wikit. On my debian based system I can install both with:

$ sudo apt-get install npm
$ sudo npm install wikit -g
$ sudo ln -s /usr/bin/nodejs /usr/bin/node

The link is to avoid the following issue:

$ wikit postgresql
/usr/bin/env: ‘node’: No such file or directory

For Fedora/RedHat/Centos you should use yum:

$ sudo yum install npm -y
$ sudo npm install wikit -g

Once you have that you can use wikit to query wikipedia (summary):

$ wikit postgresql
 PostgreSQL, often simply Postgres, is an object-relational database management system
 (ORDBMS) with an emphasis on extensibility and standards compliance. As a database
 server, its primary functions are to store data securely and return that data in
 response to requests from other software applications. It can handle workloads ranging
 from small single-machine applications to large Internet-facing applications (or
 for data warehousing) with many concurrent users; on macOS Server, PostgreSQL is
 the default database; and it is also available for Microsoft Windows and Linux (supplied
 in most distributions). PostgreSQL is ACID-compliant and transactional. PostgreSQL
 has updatable views and materialized views, triggers, foreign keys; supports functions
 and stored procedures, and other expandability. PostgreSQL is developed by the PostgreSQL
 Global Development Group, a diverse group of many companies and individual contributors.
 It is free and open-source, released under the terms of the PostgreSQL License,
 a permissive software license.

Cool. When you want to read the output in your default browser instead of the console you can do this as well by adding then “-b” flag:

$ wikit postgresql -b

When you want to open the “disambiguation” page in your browser:

$ wikit postgresql -d


Changing the language is possible as well with the “-lang” switch:

$ wikit --lang de postgresql 
 PostgreSQL (englisch [,pəʊstgɹɛs kjʊ'ɛl]), oft kurz Postgres genannt, ist ein freies,
 objektrelationales Datenbankmanagementsystem (ORDBMS). Seine Entwicklung begann
 in den 1980er Jahren, seit 1997 wird die Software von einer Open-Source-Community
 weiterentwickelt. PostgreSQL ist weitgehend konform mit dem SQL-Standard ANSI-SQL
 2008, d.h. der Großteil der Funktionen ist verfügbar und verhält sich wie definiert.
 PostgreSQL ist vollständig ACID-konform (inklusive der Data Definition Language),
 und unterstützt erweiterbare Datentypen, Operatoren, Funktionen und Aggregate. Obwohl
 sich die Entwicklergemeinde sehr eng an den SQL-Standard hält, gibt es dennoch eine
 Reihe von PostgreSQL-spezifischen Funktionalitäten, wobei in der Dokumentation bei
 jeder Eigenschaft ein Hinweis erfolgt, ob dies dem SQL-Standard entspricht, oder
 ob es sich um eine spezifische Erweiterung handelt. Darüber hinaus verfügt PostgreSQL
 über ein umfangreiches Angebot an Erweiterungen durch Dritthersteller, wie z.B.
 PostGIS zur Verwaltung von Geo-Daten. PostgreSQL ist in den meisten Linux-Distributionen
 enthalten. Apple liefert ab der Version Mac OS X Lion (10.7) PostgreSQL als Standarddatenbank

Quite helpful …


Cet article Searching wikipedia from the command line est apparu en premier sur Blog dbi services.

Oracle Applications Cloud User Experience Strategy Day– Directions for User Experience

Amis Blog - Wed, 2017-09-27 18:02

imageToday – Wednesday 27th of September – saw close to 50 people gathering for the OAUX (Oracle Applications User Experience) Strategy Day. Some attendees joined from remote locations on three continents, while most of us had assembled in the UX Spaces Lab at Oracle’s Redwood Shores HQ – equipped with some interesting video and audio equipment.

IMG_9972 Some important themes for this day:

  • The key message of Simplicity, Mobility and Extensibility is continued; simplicity means: a user experience that is to the point, only drawing a user’s attention to relevant items, only presenting meaningful data and allowing a task to be handled most efficiently.

    In order to achieve this simplicity, quite a bit of smartness is required: User context interpreted by smart apps lead to Simple UX, with Chat, Voice Input and Conversational UIs.and fully automated processes at the pinnacle. Machine learning is at the heart of this smartness – deriving information from the context, presenting relevant choices en defaults based on both context and historical patterns

  • Enterprise Mobility is a key element in the user experience – with a consistent experience yet tailored to the device (one size does not fit all at all) and the ability to start tasks on one device and continue with them on different devices and a later point in time. The experience should be light on data. Only show the absolute essential information.

  • The latest Oracle Cloud Applications Release – R13 – has some evolution in the UX and UI.

  • There is a move away from using icons to interact with the application for navigation – more towards search & notifications. The ability to tailor the look & feel (theming, logo, heading, integrate external UIs) has improved substantially.


  • Conversational UI for the Enterprise is rapidly becoming relevant. Conversational UI for the enterprise complements and replaces current Web&Mobile UI – for quick, simple, mini transaction and smart capture. The OAUX team discerns four categories of interactions that conversational interfaces are initially most likely to be used for: Do (quick decisions, approvals, data submission), Lookup (get information), Go To (use conversation as starting point for a deeplink context rich navigation to a dedicated application component) and Decision Making (provide recommendations and guidance to users).

    Some examples of conversational UIs – low threshold user to system interaction for simple questions,requests, actions and submissions


    Jeremy Ashley introduced the term JIT UI – just in time UI: widgets (buttons, selection lists) that are mixed in with the text based conversational UI (aka chat) to allow easy interaction when relevant; this could also include dynamically generated visualizations for more complex presentation of data.

    The OAUX makes an RDK (Rapid Development Kit) available for Conversational UI – or actually the first half of the RDK – the part that deals with designing the conversational UI. The part about the actual implementation will follow with the launch of the Oracle Intelligent Bot Cloud Service and associated technology and tooling.


    This new RDK can be found at :  https://t.co/m7AuSBJw5J . It contains many guidelines on designing conversations – about how to address users, what information and interaction to provide.

  • Another brand new RDK is soon to be released for Oracle JET – aligned with JET 4.0, that is to be released next week at Oracle OpenWorld 2017. This RDK support development of Oracle JET rich client applications with the same look and feel as the R13 ADF based Oracle SaaS apps. Assuming that there will be a long  period of coexistence between ADF based frontends and Oracle JET powered user interfaces, it seems important to be able to develop an experience in JET that is very similar to the one users already are used to in the existing SaaS applications.


    Additionally, the JET RDK will provide guidelines on how to developer JET applications. These guidelines were created in collaboration between the SaaS foundation and development teams, the JET product development team and the OAUX team. They are primarily targeted at Oracle’s own development teams that embrace JET for building SaaS App components and other developers creating extensions on top of Oracle SaaS. However, these guidelines are very useful for any development team that is using JET for developing any applications. The guidance provided by the RDK resources – as well as potentially the reusable components provided as part of the RDK – embodies best experiences and the intent of the JET team and provides a relevant headstart to teams that otherwise have to invent their own wheels.

    Here is a screenshot of the sample JET application (R13 style) provided with the RDK:


  • Updates – aligned with Cloud Apps Release 13 – are released for MAF and ADF. Go to https://github.com/oracle/apps-cloud-ui-kit to find all resources

    Here is a screenshot of the ADF demo application provided with the ADF RDK:IMG_0013

Some other observations

Any data in a user interface has to be justified. Why should it be there? What will you use it for? What happens if it is not shown? Less is more (or at least: better)

Different generations of users prefer different styles of navigation & interaction; ideally the UX is personalized to cater for that.

An overview of all activities of the OAUX team during Oracle OpenWorld 2017:


The post Oracle Applications Cloud User Experience Strategy Day– Directions for User Experience appeared first on AMIS Oracle and Java Blog.

ORA-00060 : Deadlock detected while waiting for resource in Multi-threaded Java Batch process

Tom Kyte - Wed, 2017-09-27 16:26
Hi Tom I have a multi-threaded batch process running on Production that fails due to "ORA-00060 : Deadlock detected while waiting for resource". I am getting following error message: <code>02:25:25,899 [CobolThread 34] ERROR Error executing upd...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator