Feed aggregator

Open database read only

Tom Kyte - Tue, 2018-01-30 12:06
What is a command to change DB open mode from read write to read only without restarting the instance
Categories: DBA Blogs

How to find last inserted records from table

Tom Kyte - Tue, 2018-01-30 12:06
HI TOM, PLEASE EXPLAIN How to find last inserted records from table? IN ORACLE? THANKS BALA
Categories: DBA Blogs

Obtain all JSON keys from JSON columns

Tom Kyte - Tue, 2018-01-30 12:06
Hello, Is there a way to get all the keys that exist in a JSON column? JSON_VALUE function provides an easy way to access a value for a given key, but is there a way to obtain all the key names? Thanks, Gabi
Categories: DBA Blogs

Multiple SQL Profiles for one SQL

Tom Kyte - Tue, 2018-01-30 12:06
Is it possible to have multiple SQL Profiles for same query?
Categories: DBA Blogs

Database Performance Different Plans after Database Migration

Tom Kyte - Tue, 2018-01-30 12:06
I am facing the issue with database performance. Earlier, my database was running on XSeries Platform, Solaris OS. Then I migrated whole data and object to SPARC Platform. Both Servers have a bit different specs. For XSeries, data stores on S...
Categories: DBA Blogs

Re-enable of foreign key constraint takes a long time

Tom Kyte - Tue, 2018-01-30 12:06
I have two databases on Solaris 11 64bit. They both have the same schemas. One is approx. 1GB and the other is approx. 10GB. On both databases, there is a parent and child table with similar number of records (~300k records each). Fr...
Categories: DBA Blogs

conceptual question regarding ODBC driver

Tom Kyte - Tue, 2018-01-30 12:06
Hi Tom: I used to work as a analyst who developed SQL queries mainly using SQL developer and SSIS, and recently I have moved to a new team which I have slightly different role , leading me to some questions I never consider before. I am not so sur...
Categories: DBA Blogs

New Report Manager Update Available for EBS 12.1.3

Steven Chan - Tue, 2018-01-30 11:24

Oracle Report Manager is an online report distribution system that allows you to produce and manage point-in-time reports. Oracle Report Manager users can be either report producers or report consumers. Report producers submit, publish, and set security for reports. Report consumers view and approve reports.

We have just released a Report Manager update for EBS 12.1.3 with new functionality:

  • Repository Management Security

    Additional security capabilities have been added to the Oracle Report Manager Repository to restrict individual user actions on folders and reports.  A new user interface lets you define the users who have viewer and owner privileges for a folder or report. Users must have either viewer or owner privilege to view a secured report. Only users with owner privileges can perform actions such as moving, archiving, deleting, or updating the security privilege assignments for a secured folder or report, publishing reports or adding subfolders within a secured folder, or updating or creating a menu item for a secured report.

You can download the latest Report Manager for EBS 12.1.3 update here:

This is a cumulative bundle patch update that includes all previously-released Report Manager updates for EBS 12.1.3.


Related Articles

Categories: APPS Blogs

Oracle Backup and Recovery in the Cloud

Gerger Consulting - Tue, 2018-01-30 10:17
Attend our free webinar on February 13th and learn how you can implement a robust backup and recovery solution for your Oracle database running in the cloud.

About the Webinar:
A common misconception is that once you move your database to the cloud, you are done and data protection stops being your problem; it automatically backs itself up, recovers magically and of course it all happens securely. Sadly, nothing can be further from the truth.
In reality, the cloud opens up new opportunities but it also comes with its own challenges, especially when it comes to implementing a secure, reliable and fast database backup and recovery solution.
In this webinar you'll learn about how the cloud changes your database backup&recovery strategy and what solutions are available to you to use with your Oracle Database in the cloud.
Specifically, we'll cover the following topics:
  • Backup and recovery strategies for databases running in the cloud
  • Pros and cons of various backup and recovery strategies
  • How to do native, multi-cloud data protection
  • How to streamline and automate backups in the cloud to reduce costs and improve efficiency
We will also do a live demo with Oracle database running in the Amazon Cloud.

Categories: Development

Partner Webcast - Oracle Big Data Cloud Service – Compute Edition: Getting Started Hands-on

Oracle Cloud provides several big data services and deployment models. The big data service choices enable you or your customer to start at the cost and capability level suitable to your use case and...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Miroglio Fashion Deploys Oracle Retail Omnichannel Solutions in 7 Months

Oracle Press Releases - Tue, 2018-01-30 08:00
Press Release
Miroglio Fashion Deploys Oracle Retail Omnichannel Solutions in 7 Months Italian Fashion Company Offers Customers a Rich Shopping Experience with Oracle Retail Xstore and Oracle Retail Customer Engagement

Redwood Shores, Calif.—Jan 30, 2018

Today, Oracle announced that Italian fashion company Miroglio Fashion deployed the Oracle Retail Xstore Point-of-Service and Oracle Retail Customer Engagement to drive innovation and create connections between product, data and people. The Italian fashion retailer operates more than 1,100 stores, six commerce sites and 2,400 wholesale outlets in 34 countries and has 12 distinctive womenswear brands as Elena Mirò, Fiorella Rubino, Motivi and Oltre.

“As the consumer continues to evolve, our business must follow and innovate. With Oracle Retail, our physical and digital locations can now operate as part of a network to better serve our customer with unified stock management and unified shopping experiences across channels. Customers are empowered to engage with our brand as they choose,” said Hans Hoegstedt, CEO of Miroglio Fashion.

Miroglio Fashion launched the Retail 4.0 program as an initiative to foster innovation, drive efficiency and increase performance. With the help of Oracle Retail Consulting, Miroglio Fashion implemented Oracle Retail Xstore Point-of-Service and Oracle Retail Customer Engagement. In 2013, Oracle quickly declared Oracle Retail Xstore Point-of-Service as the strategic Omnichannel platform to deliver more innovation to the market.

“We needed speed, efficiency and reliability to execute toward the vision of our Retail Innovation Program. Our leadership team declared explicit business targets. We designed the roll-out and a progressive migration process in advance of the implementation,” said Francesco Cavarero, Group Chief Information Officer, Miroglio Fashion.

“We are thankful for the collaboration with the IT and business leadership teams of Miroglio Fashion. The clarity and focus of the project and commitment to a vanilla implementation allowed my team to deliver on-time and on-budget in seven working months,” said Lou Frio, Vice President of Global Consulting, Oracle Retail. “Miroglio Fashion leverages a remote deployment process to keep an aggressive yet healthy pace for the rollout.”

Oracle will convene a global community of retail leadership at Oracle Industry Connect April 10-11, 2018 in New York, NY. Oracle Industry Connect provides attendees with intimate peer networking opportunities in addition to over 30 customer-led presentations on adapting to market changes, simplifying operations and empowering authentic brand experiences. To learn more about Oracle Industry Connect 2018 and register to attend visit: www.oracle.com/oracleindustryconnect/

Contact Info
Matt Torres
About Miroglio Fashion

Miroglio Fashion is the company in the leading textiles and apparel Miroglio Group—founded in 1947—which is specialized in womenswear. Today it is the number three company in Italy in this market. It creates, produces and distributes 12 brands through 1,188 branded sales outlets, 6 e-commerce websites and a wholesale network of 2,400 stores.

Miroglio Fashion’s growth path draws on the qualities that have made Italian entrepreneurship great, such as focusing on people, distinctive brand offerings, attention to detail, and an intimate, humane shopping experience, all backed up by latest generation technology.


About Oracle Retail

Oracle provides retailers with a complete, open, and integrated suite of best-of-breed business applications, cloud services, and hardware that are engineered to work together and empower commerce. Leading fashion, grocery, and specialty retailers use Oracle solutions to anticipate market changes, simplify operations and inspire authentic brand interactions. For more information, visit our website at www.oracle.com/retail.

About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit 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

Matt Torres

  • 415-595-1584

Index Skip Scan: Potential Use Case or Maybe Not ? (Shine On You Crazy Diamond)

Richard Foote - Tue, 2018-01-30 05:57
While answering a recent question on a LinkedIn forum, it got me thinking whether there’s a potential use case for using an INDEX SKIP SCAN I hadn’t previously considered. I’ve discussed Index Skip Scans previously (as I did here), a feature introduced around Oracle9i that allows an index to be considered by the CBO even […]
Categories: DBA Blogs

Histogram Threat

Jonathan Lewis - Tue, 2018-01-30 02:07

Have you ever seen a result like this:

SQL> select sql_id, count(*) from V$sql group by sql_id having count(*) > 1000;

------------- ----------
1dbzmt8gpg8x7	   30516

A client of mine who had recently upgraded to RAC, using DRCP (database resident connection pooling) for an application using PHP was seeing exactly this type of behaviour for a small number of very simple SQL statements and wanted to find out what was going on because they were also seeing an undesirable level of contention in the library cache when the system load increased.

In this note I just want to highlight a particular detail of their problem – with an example – showing how easily histograms can introduce problems if you don’t keep an eye out for the dangers.

One of their queries really was as simple as this:

select count(*), sum(skew2) from t1 where skew = :b1;

And I’m going to use this query to model the problem. All I have to do is arrange for a data set that results in a hybrid (or height-balanced) histogram being created on the skew column, and then run the query lots of times with different input bind values. In the case of the client there were around 18,000 possible values for the column, and the number of rows per value varied from 1 to about 20,000 – but whatever the number of rows selected the optimum execution plan was always going to be an indexed access.

rem     Script:         acs_child_cursors.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2018

create table t1 (
        id, skew, skew2, padding
with generator as (
                rownum id
        from dual
        connect by
                level <= 3e3
        rownum  id,
        g1.id   id1,
        g2.id   id2,
        generator       g1,
        generator       g2
        g2.id <= g1.id     -- > comment to avoid WordPress format issue
order by
        g2.id, g1.id

alter table t1 modify skew not null;
alter table t1 modify skew2 not null;

create index t1_skew on t1(skew);

                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1 for columns skew size 254'
--              method_opt       => 'for all columns size 1'

variable b1 number
exec :b1 := 0;

set termout off
set termout on

set linesize 120
set trimspool on

column sql_text format a55

        child_number, plan_hash_value, executions,
where   sql_id = 'b82my582cnvut'

The data set contains 3,000 distinct values for skew and the way I’ve generated the rows means that the value N will appear N times – so there’s one row with the value 1 and 3,000 rows with the value 3,000 and so on for a total of 4,501,500 rows. If you want to run the tes the code is likely to take a couple of minutes to complete, requiring roughly 700 MB of disk space.

The mechanism of the script start_1000 is something I published a few years ago, and essentially it executes a script called start_1.sql 1,000 times which, for this test, contains the following two lines:

exec :b1 := :b1 + 1

select count(*), sum(skew2) from t1 where skew = :b1;

The net effect of the 3 calls to start_1000.sql is that my simple SQL statement is called once in turn for each value of skew from 1 to 3,000. The SQL_ID of the statement is ‘b82my582cnvut’ which I’ve used to query v$sql when the run is complete, with the following result:

------------ --------------- ---------- - - - -------------------------------------------------------
	   0	  1041516234	    498 Y N N select count(*), sum(skew2) from t1 where skew = :b1
	   1	  1041516234	     25 Y Y N select count(*), sum(skew2) from t1 where skew = :b1
	   2	  1041516234	    104 Y Y N select count(*), sum(skew2) from t1 where skew = :b1
	   3	  1041516234	    308 Y Y N select count(*), sum(skew2) from t1 where skew = :b1
	   4	  1041516234	    429 Y Y N select count(*), sum(skew2) from t1 where skew = :b1
	   5	  1041516234	    640 Y Y N select count(*), sum(skew2) from t1 where skew = :b1
	   6	  1041516234	     31 Y Y N select count(*), sum(skew2) from t1 where skew = :b1
	   7	  1041516234	    305 Y Y N select count(*), sum(skew2) from t1 where skew = :b1
	   8	  1041516234	    660 Y Y Y select count(*), sum(skew2) from t1 where skew = :b1

9 rows selected.

I’ve got 9 child cursors, all with the same execution plan, all except the last labelled as not shareable (you may find that you don’t get exactly the same result, on repeated tests I got between 5 and 9 cursors). Given enough time all these cursors except the last (shareable) one would be aged out of the library cache. In the case of the client, who had a shared pool that was probably quite a bit larger than needed, the number of non-shareable cursors could get very large and they were hanging around for ages. Typically most of the cursors would report an execution count less than 30, with many showing just one or two executions and a handful showing execution counts in the region of several hundred (and that hanful were the ones that were still marked as shareable).

After eliminating the critical histogram (using dbms_stats.delete_column_stats()) and eliminating the redundant child cursors (using dbms_pool.purge()) the massive proliferation stopped happening and the performance threat disappeared. The only issue then was to change the table preferences for stats collection on this table to add the clause “for columns size 1 skew” so that the histogram would not be recreated on the next gather.

Further Observations.

I suspect that part of the client’s probem – something that exaggerated the count rather than causing it – could be attributed to using DRCP (database resident connection pool) which probably still has some quirky bits of behaviour. It was also true that the client’s connection pool was probably much bigger than it needed to be so if there were any funny little bits of emergent behaviour at scale the client would probably have hit them.

The problem of escalating child cursors is one that Oracle has been working on for quite a long time, and there’s a (hidden) parameter that was introduced late in 11gR2 (though I think that the 11g mechanism first appeared through a fix control) to allow Oracle to mark a parent cursor obsolete if it acquired too many child cursors.  There’s a note on MoS that the client had read on this topic: Doc ID: 2298504.1: Cursor Mutex X Wait Events: After Upgrading To 12.2″ which looked as if it was describing their symptoms so they had set this parameter (_cursor_obsolete_threshold) from 8192 (the 12.2 default) down to 1024 (the default for 12.1 and earlier versions). This had been of some help with the library cache problem.  When the sql_id at the top of this article reported 30,516 child cursors that would be 29 “obsolete” parent cursors with 1,024 childs cursor and one “live” parent cursor with 820 child cursors.

You can appreciate that if Oracle has to find a parent cursor and pin it while walking a chain of 30,516 child cursors that’s likely to take a lot more time than walking a chain of 30 parent cursors (while holding a library cache mutex, perhaps) to find the one non-obsolete parent, then acquiring the parent mutex to walk a chain of 820 child cursor.

I don’t know the exact details of the mechanisms involved with manipulating the various chains – but there are likely to be times when one process needs exclusive mutexes/latches to change a chain while other processes are holding shared mutexes/latches to search the chain. When you’ve got 30,000 child cursors in total the important questions become: “where’s the trade-off between making the child chains shorter and making the parent search longer ?” (I’d expect – or hope – that the Oracle developers had actually found a way to code the obsolence so that the new parent was first in the search, and the rest were never visited, of course.)

One of the suggestions I made to try to alleviate the problem – which I had assumed was due in part to the degree of concurrent execution of the statement – was to mark the cursor as “hot” This resulted in 36 differet sql_ids for the same statement (the client machine had 72 CPUs). This had some effect but ultimately meant that there were 36 chains of parents that would eventually end up with lots of child cursors – the only difference was the rate at which the total cursor count was growing (a lot slower), balanced against the threat that they might all manage to get to 30,000+ child cursors! Instead, as a quick and dirty workaround, I supplied the client with a script that could be run at quieter moments during the day to call dbms_shared_pool.purge() for the sql_id to flush all its cursors from the library cache.

One final oddity – which isn’t from the client site. When I changed my test above to avoid generating the histogram (using the commented out method_opt “for all columns size 1”) I found that I still got two child cursors; the first ended up marked as bind-aware but insensitive and non-shareable, the second appeared (time and time again) when my bind value got to 499 and was marked bind-sensitive, bind-aware and shareable.  I still have no idea why this happened.


When repeating the test I started with a “flush shared_pool” – but I’ve removed this line from the code above in case anyone ran it without considering the possible side effects. You do need to clear previous copies of the key statement from the library cache, though, if you want to avoid confusing the issue on repeated runs of the test.


Oracle Utilities Application Framework V4. Release Summary

Anthony Shorten - Mon, 2018-01-29 20:51

The latest release of Oracle Utilities Application Framework, namely (or 4.3 SP5 for short) will be included in new releases of Oracle Utilities products over the next few months. This release is quite diverse with a range of new and improved capabilities that can be used by implementations of the new releases.

The key features included in the release including the following:

  • Mobile Framework release - The initial release of a new REST based channel to allow Oracle Utilities products to provide mobile device applications. This release is a port of the Mobile Communication Platform (MCP) used in the Oracle Mobile Workforce Management product to the Oracle Utilities Application Framework. This initial release is restricted to allow Oracle Utilities products to provide mobile experiences for use with an enterprise. As with other channels in the Oracle Utilities Application Framework, it can be deployed alone or in conjunction with other channels.
  • Support For Chrome for Business - In line with Oracle direction, the Oracle Utilities Application Framework supports Chrome for Business as a browser alternative. A new browser policy, in line with Oracle direction, has been introduced to clarify support arrangement for Chrome and other supported browsers. Check individual product release notes for supported versions.
  • Improved Security Portal - To reduce effort in managing security definitions within the product, the application service portal has been extended to show secured objects or objects that an application service is related to.
  • Attachment Changes - In the past to add attachments to object required custom UI maps to link attachment types to objects. In this release, a generic zone has been added reducing the need for any custom UI Maps. The attachment object now also records the extension of the attachment to reduce issues where an attachment type can have multiple extensions (e.g. DOC vs DOCX).
  • Support for File Imports in Plug In Batch - In past releases Plug In Batch was introduced as a configuration based approach to replace the need for Java programming for batch programming. In the past, SQL processing and File Exports where supported for batch processing. In this release, importing files in CSV, Fixed format or XML format are now supported using Plug In Batch (using Groovy based extensions). Samples are supplied with the product that can be copied and altered accordingly.
  • Improvements in identifying related To Do's - The logic determining related To Do's has been enhanced to provide additional mechanisms for finding related To Do's to improve closing related work. This will allow a wider range to To Do's to be found than previously determined.
  • Web Service Categories - To aid in API management (e.g. when using Integration Cloud Service and other cloud services) Web Service categories can be attached to Inbound Web Services, Outbound Message Types and legacy XAI services that are exposed via Inbound Web Services. A given web service or outbound message can be associated with more than one category. Categories are supplied with the product release and custom categories can be added.
  • Extended Oracle Web Services Manager Support - In past releases Oracle Web Services Manager could provide additional transport and message security for Inbound Web Services. In this release, Oracle Web Services Manager support has been extended to include Outbound Messages and REST Services.
  • Outbound Message Payload Extension - In this release it is possible to include the Outbound Message Id as part of the payload as a reference for use in the target system.
  • Dynamic URL support in Outbound Message - In the past Outbound Message destinations were static to the environment. In this release the URL used for the destination can vary according to the data or dynamically assembled programmatically if necessary.
  • SOAP Header Support in Outbound Messages - In this release it is possible to dynamically set SOAP Header variables in Outbound Messages.
  • New Groovy Imports Step Type - A new step type has been introduced to define classes to be imported for use in Groovy members. This promotes reuse and allows for coding without the need for the fully qualified package name in Groovy Library and Groovy Member step types. 
  • New Schema Designer - A newly redesigned Schema Editor has been introduced to reduce total cost of ownership and improve schema development. Color coding has been now included in the raw format editor.
  • Oracle Jet Library Optimizations - To improve integration with the Oracle Jet libraries used by the Oracle Utilities Application Framework, a new UI Map fragment has been introduced to include in any Jet based UI Map to reduce maintenance costs.
  • YUI Library Removal - With the desupport of the YUI libraries, they have been removed from this release in the Oracle Utilities Application Framework. Any custom code directly referencing the YUI libraries should use the Oracle Utilities Application Framework equivalent function.
  • Proxy Settings now at JVM level - In past release, proxy settings were required on individual connections where needed. In this release, the standard HTTP Proxy JVM options are now supported at the container/JVM layer to reduce maintenance costs.

This is just a summary of some of the new features in the release. A full list is available in the release notes of the products using this service pack.

Note: Some of these enhancements have been back ported to past releases. Check My Oracle Support for those patches.

Over the next few weeks, I will be writing a few articles about a few of these enhancements to illustrate the new capabilities.

Performance issue while processing Huge XML file

Tom Kyte - Mon, 2018-01-29 18:06
I'm seeing performance issue while processing the xml file which has more then 10K records, it's working fine if the file has 100 records. Below is the sample procedure I'm using.. <code>++++++++++++++++ CREATE OR REPLACE PROCEDURE process_xml_fi...
Categories: DBA Blogs

Audit logon and logoff of specific users eg sys and system

Tom Kyte - Mon, 2018-01-29 18:06
Hi, I need to write a trigger to audit logon and logoff activities of specific users 'SYS' and 'SYSTEM'. I have one trigger but its not inserting records into the table mentioned in the trigger. Please, help me to fix the issue.Your help will be...
Categories: DBA Blogs

How to unpivot table data with out displaying column names

Tom Kyte - Mon, 2018-01-29 18:06
Hi Tom, Am working on Oracle DB and below query <code> select t1.id as dbid, t2.mid as askid, t3.m2idd as amid from table1 t1, table2 t2, table3 t3 where t1.actid = t2.senid and t2.denid = t2.mkid ...
Categories: DBA Blogs

SQL query that returns the difference between the latest

Tom Kyte - Mon, 2018-01-29 18:06
Problem Statement [question redacted] Comment from Connor, Jan 29 1018 ================================ You can see from our initial answer that were thrilled that you provided us a complete test case. However, when things seem too good to b...
Categories: DBA Blogs

SQL Query Optimization

Tom Kyte - Mon, 2018-01-29 18:06
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator