Feed aggregator

pushing predicate into union-all view

Tom Kyte - Fri, 2018-11-16 15:06
Hi, LiveSQL link: https://livesql.oracle.com/apex/livesql/s/hjml6z0yg45qznob5sebg53vk I have the big table with an index on ID: <code> create table tst1 as select level id, mod(level, 10) code from dual connect by level < 1000000; create...
Categories: DBA Blogs

EBS Releases 12.1 and 12.2 certified with SLES 12

Steven Chan - Fri, 2018-11-16 12:08

I am pleased to announce that Oracle E-Business Suite Releases 12.1.3 and 12.2.6 (or higher) are now certified with SUSE Linux Enterprise Server (SLES) 12 on x86-64.

Installations of E-Business Suite on this operating system require specific patches to the latest startCD prior to installing, followed by the application of the 12.1.3 RUP or the 12.2.6 RUP (or higher) for EBS 12.1 and 12.2 respectively. Cloning of existing EBS 12.1.3 or 12.2 environments to SLES 12 is also certified using the standard Rapid Clone process.

There are also requirements to upgrade technology components such as the Oracle Database (to or and Fusion Middleware components as necessary. All requirements, known issues, patches needed, etc. are noted in the Installation and Upgrade Notes (IUN) below and must be reviewed and implemented.

For more information on requirements, please review the following documents:


Categories: APPS Blogs

Nvarchar to Varchar2 conversion (UTF8 to AL32UTF8)

Tom Kyte - Thu, 2018-11-15 20:46
We are planning to convert all NVarchar fields to Varchar2 fields as we're going to change our character set and since Oracle recommends AL32UTF8 character set encoding. My question is it 100% sure that all characters from Nvarchar (UTF8) can be conv...
Categories: DBA Blogs

sql plan management - difference in defining parameters at system and session level

Tom Kyte - Thu, 2018-11-15 20:46
Hi Tom, I am very new to performance tuning. there's something that I am unclear about sql plan management. which one is faster - 1. setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE at session level (inside function body) and OPTIMIZE...
Categories: DBA Blogs

Moving Oracle DB from one server to another

Tom Kyte - Thu, 2018-11-15 20:46
Hi, I am having an Oracle 11g database in an AIX linux server. I am planning to move this to a different server with same OS. I will be using same version of Oracle database in target DB as well. I have multiple schema in source database and in t...
Categories: DBA Blogs

SQL Slowdown ? A short list of potential reasons

Hemant K Chitale - Thu, 2018-11-15 20:14
Jonathan Lewis has published a short list of potential reasons why you might see a slowdown in SQL execution.  With newer releases 12.2, 18c and 19c, the list may have to be expanded.

Categories: DBA Blogs

AWS: Billing - how to delete a route 53

Dietrich Schroff - Thu, 2018-11-15 14:58
After playing around with AWS containers
i took a look at my billing page:

So let's delete this service.
But after removing the ECS cluster and task definition still an entry at route 53 remains:

The resource hostedzone/Z3JCO1N1BVHCKX can only be managed through servicediscovery.amazonaws.com (arn:aws:servicediscovery:eu-west-1:803404058350:namespace/ns-so7m3qbqbatzmlgn)

But the solution is the aws cli (for installation take a look here):
schroff@zerberus:~/AWS$ aws servicediscovery list-services

    "Services": [


            "Id": "srv-46ffbkbwzupvblsb",

            "Arn": "arn:aws:servicediscovery:eu-west-1:803404058350:service/srv-46ffbkbwzupvblsb",

            "Name": "my-nginx-service"



            "Id": "srv-nicoewsbpufb3tlk",

            "Arn": "arn:aws:servicediscovery:eu-west-1:803404058350:service/srv-nicoewsbpufb3tlk",

            "Name": "my-ecs-service-on-fargate"




schroff@zerberus:~/AWS$ aws servicediscovery delete-service --id srv-46ffbkbwzupvblsb
schroff@zerberus:~/AWS$ aws servicediscovery delete-service --id srv-nicoewsbpufb3tlk


schroff@zerberus:~/AWS$ aws servicediscovery list-namespaces


    "Namespaces": [


            "Type": "DNS_PRIVATE",

            "Id": "ns-so7m3qbqbatzmlgn",

            "Arn": "arn:aws:servicediscovery:eu-west-1:803404058350:namespace/ns-so7m3qbqbatzmlgn",

            "Name": "local"



Take the id and delete this namespace:
schroff@zerberus:~/AWS$ aws servicediscovery delete-namespace --id=ns-so7m3qbqbatzmlgn


    "OperationId": "4kdit33kf7kfuawscpfgifcrdktynen5-jog7l6h7"


And the the hosted zone was gone:

Oracle JET UI on Top of Oracle ADF With Visual Builder

Shay Shmeltzer - Thu, 2018-11-15 13:22

At Oracle OpenWorld this year I did a session about the future of Oracle ADF, and one of the demos I did there was showing the powerful combination of Oracle ADF backend with a new Oracle JET UI layer and how Oracle Visual Builder makes this integration very simple.

While we have many happy Oracle ADF customers, we do hear from some of them about new UI requirements that might justify thinking about adopting a new UI architecture for some modules. These type of requirements align with an industry trend towards adopting a more client centric UI architecture that leverages the power of JavaScript on the client. While ADF (which is more of a server centric architecture) does let you leverage JavaScript on the client and provides hook points for that in ADF Faces, some customers prefer a more "puristic" approach for new user interfaces that they are planning to build. Oracle's solution for such a UI architecture is based on Oracle JET - an open source set of libraries we developed and share with the community at http://oraclejet.org.

Oracle Visual Builder provides developers with a simpler approach to building Oracle JET based UIs - for both web and on-device mobile applications. Focusing on a visual UI design approach it drastically reduce the amount of manual coding you need to do to create JET based UIs. 

UIs that you build in Visual Builder connect at the back to REST services, and this is where you can leverage Oracle ADF. In version 12 of JDeveloper we introduced the ability to publish ADF Business Components as REST services through a simple wizard. Note that out-of-the-box you get a very powerful set of services that support things like query by example, pagination, sorting and more. If you haven't explored this functionality already, check out the videos showing how to do it here, and this video covering cloud hosting these services.

Once you have this ADF based REST services layer - you'll be glad to hear that in Visual Builder we have specific support to simplify consuming these REST services. Specifically - we understand the meta-data descriptions that these REST services provide and then are able to create services and endpoints mapping for you.

ADF Describe Dialog in Service Connection

You leverage our "Service from specification" dialog to add your ADF services to your Visual Builder app - and from that point on, it's quite simple to build new JET UIs accessing the data.

In the video below I show how simple it is to build a JET-based on-device mobile app that leverage a set of REST services that were created from Oracle JDeveloper 12. Check it out:

Categories: Development


Jonathan Lewis - Thu, 2018-11-15 07:13

The title is the name of an Oracle hint that came into existence in Oracle and made an appearance recently in a question on the rarely used “My Oracle Support” Community forum (you’ll need a MOS account to be able to read the original). I wouldn’t have found it but the author also emailed me the link asking if I could take a look at it.  (If you want to ask me for help – without paying me, that is – then posting a public question in the Oracle (ODC) General Database or SQL forums and emailing me a private link is the strategy most likely to get an answer, by the way.)

The question was about a very simple query using a straightforward index – with a quirky change of plan after upgrading from to Setting the optimizer_features_enable to ‘’ in the system re-introduced the 10g execution plan. Here’s the query:

   FROM   DW1.t1
  WHERE   t1.C1 = '0001' 
    AND   t1.C2 IN ('P', 'F', 'C')
    AND   t1.C3 IN (

Information supplied: t1 holds about 500 million rows at roughly 20 rows per block, the primary key index is (c1, c2, c3, c4), there are just a few values for each of c1, c2 and c4, while c3 is “nearly unique” (which, for clarity, was expanded to “the number of distinct values of c3 is virtually the same as the number of rows in the table”).

At the moment we don’t have any information about histograms and we don’t known whether or not “nearly unique” might still allow a few values of c3 to have a large number of duplicates, so that’s something we might want to follow up on later.

Here are the execution plans – the fast one (from 10g) first, then the slow (12c) plan – and you should look carefully at the predicate section of the two plans:

10g (pulled from memory with rowsource execution statistics enabled)
| Id  | Operation                    | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
|   0 | SELECT STATEMENT             |                  |      1 |        |      6 |00:00:00.01 |      58 |      5 |
|   1 |  INLIST ITERATOR             |                  |      1 |        |      6 |00:00:00.01 |      58 |      5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1               |     18 |      5 |      6 |00:00:00.01 |      58 |      5 |
|*  3 |    INDEX RANGE SCAN          | PK_T1            |     18 |      5 |      6 |00:00:00.01 |      52 |      4 |

Predicate Information (identified by operation id):
   3 - access("T1"."C1"='0001' AND (("T1"."C2"='C' OR "T1"."C2"='F' OR
              "T1"."C2"='P')) AND (("C3"='18110034450001' OR "C3"='18110034450101' OR
              "C3"='18110034450201' OR "C3"='18110034450301' OR "C3"='18110034450401' OR


12c (from explain plan)
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                     |                  |     1 |   359 |     7   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                     |                  |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1               |     1 |   359 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | PK_T1            |     1 |       |     6   (0)| 00:00:01 |
Predicate Information (identified by operation id):
   3 - access("T1"."C1"='0001' AND ("T1"."C2"='C' OR "T1"."C2"='F' OR
       filter("C3"='18110034450001' OR "C3"='18110034450101' OR
              "C3"='18110034450201' OR "C3"='18110034450301' OR
              "C3"='18110034450401' OR "C3"='18110034450501')

When comparing plans it’s better, of course, to present the same sources from the two systems, it’s not entirely helpful to have the generated plan from explain plan in one version and a run-time plan with stats in the other – given the choice I’d like to see the run-time from both. Despite this, I felt fairly confident that the prediction would match the run-time for 12c and that I could at least guess the “starts” figure for 12c.

The important thing to notice is the way that the access predicate in 10g has split into an access predicate followed by a filter predicate in 12c. So 12c is going to iterate three times (once for each of the values  ‘C’, ‘F’, ‘P’) and then walk a potentially huge linked list of index leaf blocks looking for 6 values of c3, while 10g is going to probe the index 18 times (3 combinations of c2 x six combinations of c3) to find “nearly unique” rows which means probably one leaf block per probe.

The 12c plan was taking minutes to run, the 10g plan was taking less than a second. The difference in execution time was probably the effect of the 12c plan ranging through (literally) thousands of index leaf blocks.

There are many bugs and anomalies relating to in-list iteration and index range scans and cardinality calculations – here’s a quick sample of v$system_fix_control in

select optimizer_feature_enable ofe, sql_feature, bugno, description
from v$system_fix_control
	optimizer_feature_enable between '' and ''
and	(   sql_feature like '%CBO%'
	 or sql_feature like '%CARDINALITY%'
and	(    lower(description) like '%list%'
	 or  lower(description) like '%iterat%'
	 or  lower(description) like '%multi%col%'
order by optimizer_feature_enable, sql_feature, bugno

OFE        SQL_FEATURE                      BUGNO DESCRIPTION
---------- --------------------------- ---------- ----------------------------------------------------------------   QKSFM_CBO_5259048              5259048 undo unused inlist
           QKSFM_CBO_5634346              5634346 Relax equality operator restrictions for multicolumn inlists   QKSFM_CBO_7148689              7148689 Allow fix of bug 2218788 for in-list predicates   QKSFM_CBO_5139520              5139520 kkoDMcos: For PWJ on list dimension, use part/subpart bits   QKSFM_CBO_6818410              6818410 eliminate redundant inlist predicates   QKSFM_CBO_9069046              9069046 amend histogram column tracking for multicolumn stats   QKSFM_CARDINALITY_11876260    11876260 use index filter inlists with extended statistics
           QKSFM_CBO_10134677            10134677 No selectivity for transitive inlist predicate from equijoin
           QKSFM_CBO_11834739            11834739 adjust NDV for list partition key column after pruning
           QKSFM_CBO_11853331            11853331 amend index cost compare with inlists as filters
           QKSFM_CBO_12591120            12591120 check inlist out-of-range values with extended statistics   QKSFM_CARDINALITY_12828479    12828479 use dynamic sampling cardinality for multi-column join key check
           QKSFM_CARDINALITY_12864791    12864791 adjust for NULLs once for multiple inequalities on nullable colu
           QKSFM_CARDINALITY_13362020    13362020 fix selectivity for skip scan filter with multi column stats
           QKSFM_CARDINALITY_14723910    14723910 limit multi column group selectivity due to NDV of inlist column
           QKSFM_CARDINALITY_6873091      6873091 trim histograms based on in-list predicates
           QKSFM_CBO_13850256            13850256 correct estimates for transitive inlist predicate with equijoin   QKSFM_CARDINALITY_19847091    19847091 selectivity caching for inlists
           QKSFM_CARDINALITY_22533539    22533539 multi-column join sanity checks for table functions
           QKSFM_CARDINALITY_23019286    23019286 Fix cdn estimation with multi column stats on fixed data types
           QKSFM_CARDINALITY_23102649    23102649 correction to inlist element counting with constant expressions
           QKSFM_CBO_17973658            17973658 allow partition pruning due to multi-inlist iterator
           QKSFM_CBO_21057343            21057343 order predicate list
           QKSFM_CBO_22272439            22272439 correction to inlist element counting with bind variables

There are also a number of system parameters relating to inlists that are new (or have changed values) in when compared with – but I’m not going to go into those right now.

I was sufficiently curious about this anomaly that I emailed the OP to say I would be happy to take a look at the 10053 trace files for the query – the files probably weren’t going to be very large given that it was only a single table query – but in the end it turned out that I solved the problem before he’d had time to email them. (Warning – don’t email me a 10053 file on spec; if I want one I’ll ask for it.)

Based on the description I created an initial model of the problem – it took about 10 minutes to code:

rem     Tested on,

drop table t1 purge;

create table t1 (
	c1 varchar2(4) not null,
	c2 varchar2(1) not null,
	c3 varchar2(15) not null,
	c4 varchar2(4)  not null,
	v1 varchar2(250)

insert into t1
with g as (
	select rownum id 
	from dual
	connect by level <= 1e4 -- > hint to avoid wordpress format issue
	chr(65 + mod(rownum,11)),
        rownum <= 1e5 -- > hint to avoid wordpress format issue

create unique index t1_i1 on t1(c1, c2, c3, c4);

                method_opt => 'for all columns size 1'

alter session set statistics_level = all;
set serveroutput off

prompt	==========================
prompt	Default optimizer features
prompt	==========================

        /*+ optimizer_features_enable('') */
	t1.c1 = '0001' 
AND	t1.c2 in ('H', 'F', 'C')
AND	t1.c3 in (

select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));

        /*+ optimizer_features_enable('') */
	t1.c1 = '0001' 
AND	t1.c2 in ('H', 'F', 'C')
AND	t1.c3 in (

select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));

alter session set statistics_level = all;
set serveroutput off

The two queries produced the same plan – regardless of the setting for optimizer_features_enable – it was the plan originally used by the OP’s 10g setting:

| Id  | Operation                    | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
|   0 | SELECT STATEMENT             |       |      1 |        |    20 (100)|      0 |00:00:00.01 |      35 |
|   1 |  INLIST ITERATOR             |       |      1 |        |            |      0 |00:00:00.01 |      35 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     18 |      2 |    20   (0)|      0 |00:00:00.01 |      35 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |     18 |      2 |    19   (0)|      0 |00:00:00.01 |      35 |

Predicate Information (identified by operation id):
   3 - access("T1"."C1"='0001' AND (("T1"."C2"='C' OR "T1"."C2"='F' OR "T1"."C2"='H')) AND
              (("T1"."C3"='18110034450001' OR "T1"."C3"='18110034450101' OR "T1"."C3"='18110034450201' OR
              "T1"."C3"='18110034450301' OR "T1"."C3"='18110034450401' OR "T1"."C3"='18110034450501')))

There was one important difference between the 10g and the 12c plans – in 10g the cost of the table access (hence the cost of the total query) was 20; in 12c it jumped to 28 – maybe there’s a change in the arithmetic for costing the iterator, and maybe that’s sufficient to cause a problem.

Before going further it’s worth checking what the costs would look like (and, indeed, if the plan is possible in both versions) if we force Oracle into the “bad” plan. That’s where we finally get to the hint in the title of this piece. If I add the hint /*+ num_index_keys(t1 t1_i1 2) */ what’s going to happen ? (Technically I’ve included a hint to use the index, and specified the query block name to make sure Oracle doesn’t decide to switch to a tablescan):

            index_rs_asc(@sel$1 t1@sel$1 (t1.c1 t1.c2 t1.c3 t1.c4))
            num_index_keys(@sel$1 t1@sel$1 t1_i1 2)
FROM        t1
        t1.c1 = '0001'
AND        t1.c2 in ('H', 'F', 'C')
AND        t1.c3 in (

| Id  | Operation                            | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
|   0 | SELECT STATEMENT                     |       |      1 |        |   150 (100)|      0 |00:00:00.01 |     154 |      1 |
|   1 |  INLIST ITERATOR                     |       |      1 |        |            |      0 |00:00:00.01 |     154 |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      3 |     18 |   150   (2)|      0 |00:00:00.01 |     154 |      1 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 |      3 |     18 |   142   (3)|      0 |00:00:00.01 |     154 |      1 |

Predicate Information (identified by operation id):
   3 - access("T1"."C1"='0001' AND (("T1"."C2"='C' OR "T1"."C2"='F' OR "T1"."C2"='H')))
       filter(("T1"."C3"='18110034450001' OR "T1"."C3"='18110034450101' OR "T1"."C3"='18110034450201' OR
              "T1"."C3"='18110034450301' OR "T1"."C3"='18110034450401' OR "T1"."C3"='18110034450501'))

This was the plan from – and again the plan for was identical except for costs which became 140 for the index range scan and 141 for the table access. At first sight it looks like 10g may be using the total selectivity of the entire query as the scaling factor for the index clustering_factor to find the table cost while 12c uses the cost of accessing the table for one iteration (rounding up) before multiplying by the number of iterations.

Having observed this detail I thought I’d do a quick test of what happened by default if I requested 145 distinct values of c3. Both versions defaulted to the access/filter path rather than the pure access path – but again there was a difference in costs. The 10g index cost was 140 with a table access cost of 158, while 12c had an index cost of 179 and a table cost of 372. So both versions switch plans at some point – do they switch at the same point ? Reader, I could not resist temptation, so I ran a test loop. With my data set the 12c version switched paths at 61 values in the in-list and 10g switched at 53 values –

Conclusion: there’s been a change in the selectivity calculations for the use of in-list iterators, which leads to a change in costs, which can lead to a change in plans; the OP was just unlucky with his data set and stats. Possibly there’s something about his data or stats that makes the switch appear with a much smaller in-list than mine.


When I responded to the thread on MOSC with the suggestion that the problem was in part due to statistics and might be affected by out of date stats (or a histogram on the (low-frequency) c2 column) the OP noted that stats hadn’t been gathered since some time in August – and found that the 12c path changed to the efficient (10g) one after re-gathering stats on the table.


Oracle Accelerates Data Insights for Retailers with Oracle Digital Assistant

Oracle Press Releases - Thu, 2018-11-15 07:00
Press Release
Oracle Accelerates Data Insights for Retailers with Oracle Digital Assistant Integration of Core Retail Technology with Conversational AI Powers Targeted and Contextual Offers that Engage Customers and Drive Results

Redwood Shores, Calif.—Nov 15, 2018

Enabling retailers to build personalized customer experiences as well as voice-enabled assistants to help employees work smarter and more productively, Oracle Retail solutions are now integrated with Oracle Digital Assistant. Together, these offerings empower retailers to find answers to critical business questions such as, "What's the current margin on our new BOGO offer for trendsetters?" faster than ever before.

Announced at Oracle OpenWorld 2018, Oracle Digital Assistant leverages artificial-intelligence (AI) to understand context, derive intent, and identify and learn user behaviors and patterns to automate routine tasks proactively on behalf of the user. By integrating the technology with Oracle Retail Offer Optimization Cloud Service, analysts can easily streamline location-specific sales forecasting, promotional deployment and performance, approval automation and target prioritization.

To see these technologies in action, visit: https://youtu.be/UcDTQa7Ff-w.

"Most retailers think about conversational AI in the context of the store or e-commerce, however, retailers can now apply conversational AI to their core operations', via voice or text, to accelerate productivity and optimize processes," said Mike Webster, Senior Vice President and General Manager, Oracle Retail. "Smart digital interactions are an integral part of our everyday life as we query Alexa, Google Home and Siri for recommendations. This latest integration between Oracle Digital Assistant and Oracle Retail Offer Optimization Cloud Service brings the power and simplicity of voice to retail operations, speeding time to insight and action."

Built on Oracle Cloud Infrastructure, Oracle Digital Assistant goes well beyond standard chatbots available today that provide simple, single skilled, linear responses. By applying AI for natural language processing (NLP), natural language understanding (NLU) and machine learning (ML), Oracle is in a uniquely positioned to leverage its breadth and depth in enterprise applications to offer a digital assistant that can truly span the enterprise.

"Going forward digital assistants will transform how merchants, planners, and marketers collaborate, engage their company's information assets, and how they work," said Greg Girard, program director of intelligent product merchandising and marketing, IDC Retail Insights. "As digital assistants become more conversationally and analytically skillful and more aware of their users' intent and context we'll see more incisive decisions, made quicker, to deliver better business outcomes. Retailers should bring digital assistants into their digital transformation planning assumptions now."

In April, Oracle launched the next generation of promotion, markdown and offer optimization capabilities as a cloud service with the launch of Oracle Retail Offer Optimization Cloud Service. With these new updates retailers can analyze promotion and pricing decisions for the entire product lifecycle while providing consumers with targeted and contextual offers.

Contact Info
Matt Torres
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 www.oracle.com.

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.


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

  • +1.415.595.1584

[BLOG] 15 Must Know Things on Oracle EBS (R12) on Cloud for Beginners

Online Apps DBA - Thu, 2018-11-15 03:32

Are you a Beginner who wants to move ahead in the journey towards learning EBS on Cloud? If yes, then visit: https://k21academy.com/ebscloud16 to learn about: ✔Various Cloud Service Models ✔The 2 main tiers of Oracle EBS(R12) ✔The ways to deploy EBS on Cloud & much more… Are you a Beginner who wants to move ahead […]

The post [BLOG] 15 Must Know Things on Oracle EBS (R12) on Cloud for Beginners appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Log DML, DDL and DCL user activity

Tom Kyte - Thu, 2018-11-15 02:26
Hello, Ask TOM Team. I want to know if there's a straightforward (not using triggers or things like that lol) way to log DML, DDL and DCL user activity on specific objects (12c). I do not know if <b>Database Vault</b> can help me with that. Any Do...
Categories: DBA Blogs

DB link is not working between 2 databases

Tom Kyte - Thu, 2018-11-15 02:26
Hi, We have 2 databases DB A and DB B. we have created db link between 2 dbs as a2b; in DB A, we have below table_A and data, <code>create table table_a (emp_id number, emp_name varchar2(30)) / insert into table_a values (1,'Test1') / ...
Categories: DBA Blogs

ORA-64610: bad depth indicator with Utl_Call_Stack

Tom Kyte - Thu, 2018-11-15 02:26
Hi, I have a database in Oracle 12.2.0 There I have deployed a PL/SQL logic which is called from an update trigger. From this logic, following code segment is called to get the format call stack. <code>FUNCTION Format_Stack___ ...
Categories: DBA Blogs

Super Lock an Oracle Database

Pete Finnigan - Thu, 2018-11-15 02:26
I started this blog post a few weeks ago and kept adding to it from time to time but I have been incredibly busy helping people secure data in their Oracle databases that it has taken a long time to....[Read More]

Posted by Pete On 14/11/18 At 02:20 PM

Categories: Security Blogs

How to Launch PeopleSoft Cloud Manager Using a Pre-built Image for Oracle Cloud Infrastructure

PeopleSoft Technology Blog - Wed, 2018-11-14 23:58

With the latest updates to Oracle Cloud Infrastructure, you now have a cool new way of launching instances.  This new feature makes it easier than ever before to launch a PeopleSoft Cloud Manager instance.  We received your feedback on how downloading and uploading Cloud Manager was taking too much time.  I’m very happy to say that we’ve heard you, and now we have delivered a surprisingly simple solution.

Before creating the Cloud Manager instance, you should have set up your networking (VCN and subnet) and configured all required security rules.

To launch a new Cloud Manager instance, navigate to your tenancy and go to the dashboard, as shown here, or the Instances page.  From the dashboard, click the option Create a VM instance. 

You will now see the new pages to create an instance as shown below.  Enter the name for your instance and select the availability domain (AD) in which you want to create your Cloud Manager instance. 

Next step is to select the Cloud Manager image. By default, Oracle Linux 7.5 image is chosen.  Click Change Image Source to search for the Cloud Manager image.

On the Browse All Images page, select the Oracle Images tab.  Here you’ll find the latest Cloud Manager image for OCI.  Select the image and accept the terms and restrictions after reading them. Click Select Image to use the chosen image.

Choose the Virtual Machine instance type and select a VM shape of your choice. 

Select a compartment in which the Cloud Manager instance will be created.  Select a VCN and a subnet for the Cloud Manager instance configuration. Click Create to deploy the instance.

There you go!  Creating a Cloud Manager instance is now so easy. 

Note that you still need to download the Oracle Linux Image for Cloud Manager from My Oracle Support, upload it to object storage and import as a custom image.  After setting up Cloud Manager instance, continue from Downloading the Oracle Linux Image and Uploading to Object Storage step in the install guide here.

After you have the Oracle Linux image, and the Cloud Manager instance is provisioned and running, SSH into the instance and follow the instructions in the install guide to run the Cloud Manager Instance Configuration Wizard.

Enable Index To Search For NULLs By Adding Constant to Index List. But Some Constants Better Than Others (Never Let Me Down)

Richard Foote - Wed, 2018-11-14 18:29
By default, Oracle doesn’t index an entry if all columns within the index are NULL. However, (as I’ve blogged previously), it’s possible to index all possible NULL values by simply adding a constant value to the index column list. Importantly, the CBO knows when a column has all it’s NULL values indexed and can potentially […]
Categories: DBA Blogs

AWS: Installing aws cli (Amazon Web Service Commandline)

Dietrich Schroff - Wed, 2018-11-14 14:15
The management of aws can be done via the webpage (https://console.aws.amazon.com) or via aws cli.

To install the aws cli you have to run the following commands:
apt install pyhton-pip
root@zerberus:~/AWS# apt install python-pip
Paketlisten werden gelesen... Fertig
Abhängigkeitsbaum wird aufgebaut.
Statusinformationen werden eingelesen.... Fertig
Die folgenden Pakete wurden automatisch installiert und werden nicht mehr benötigt:
btrfs-tools geoip-database-extra libcryptui0a libjs-openlayers seahorse-daemon
Verwenden Sie »apt autoremove«, um sie zu entfernen.
Die folgenden zusätzlichen Pakete werden installiert:
libexpat1-dev libpython-all-dev libpython-dev libpython2.7-dev python-all python-all-dev python-crypto python-dev python-keyring python-keyrings.alt python-pip-whl python-secretstorage python-setuptools python-wheel
python-xdg python2.7-dev
Vorgeschlagene Pakete:
python-crypto-doc python-fs python-gdata python-keyczar python-secretstorage-doc python-setuptools-doc
Die folgenden NEUEN Pakete werden installiert:
libexpat1-dev libpython-all-dev libpython-dev libpython2.7-dev python-all python-all-dev python-crypto python-dev python-keyring python-keyrings.alt python-pip python-pip-whl python-secretstorage python-setuptools
python-wheel python-xdg python2.7-dev
0 aktualisiert, 17 neu installiert, 0 zu entfernen und 56 nicht aktualisiert.
Es müssen 31,2 MB an Archiven heruntergeladen werden.
Nach dieser Operation werden 49,0 MB Plattenplatz zusätzlich benutzt.
Möchten Sie fortfahren? [J/n]
Holen:1 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 libexpat1-dev amd64 2.2.5-3 [122 kB]
Holen:2 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 libpython2.7-dev amd64 2.7.15~rc1-1 [28,2 MB]
Holen:3 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 libpython-dev amd64 2.7.15~rc1-1 [7.684 B]
Holen:4 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 libpython-all-dev amd64 2.7.15~rc1-1 [1.092 B]
Holen:5 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python-all amd64 2.7.15~rc1-1 [1.076 B]
Holen:6 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python2.7-dev amd64 2.7.15~rc1-1 [286 kB]
Holen:7 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python-dev amd64 2.7.15~rc1-1 [1.256 B]
Holen:8 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python-all-dev amd64 2.7.15~rc1-1 [1.100 B]
Holen:9 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python-crypto amd64 2.6.1-8ubuntu2 [244 kB]
Holen:10 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python-secretstorage all 2.3.1-2 [11,8 kB]
Holen:11 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python-keyring all 10.6.0-1 [30,6 kB]
Holen:12 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python-keyrings.alt all 3.0-1 [16,7 kB]
Holen:13 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 python-pip-whl all 9.0.1-2.3~ubuntu1 [1.652 kB]
Holen:14 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 python-pip all 9.0.1-2.3~ubuntu1 [151 kB]
Holen:15 http://de.archive.ubuntu.com/ubuntu bionic/main amd64 python-setuptools all 39.0.1-2 [329 kB]
Holen:16 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 python-wheel all 0.30.0-0.2 [36,4 kB]
Holen:17 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 python-xdg all 0.25-4ubuntu1 [31,3 kB]
Es wurden 31,2 MB in 7 s geholt (4.521 kB/s).
Vormals nicht ausgewähltes Paket libexpat1-dev:amd64 wird gewählt.
(Lese Datenbank ... 415946 Dateien und Verzeichnisse sind derzeit installiert.)
Vorbereitung zum Entpacken von .../00-libexpat1-dev_2.2.5-3_amd64.deb ...
Entpacken von libexpat1-dev:amd64 (2.2.5-3) ...
Vormals nicht ausgewähltes Paket libpython2.7-dev:amd64 wird gewählt.
Vorbereitung zum Entpacken von .../01-libpython2.7-dev_2.7.15~rc1-1_amd64.deb ...
Entpacken von libpython2.7-dev:amd64 (2.7.15~rc1-1) ...
Vormals nicht ausgewähltes Paket libpython-dev:amd64 wird gewählt.
Vorbereitung zum Entpacken von .../02-libpython-dev_2.7.15~rc1-1_amd64.deb ...
Entpacken von libpython-dev:amd64 (2.7.15~rc1-1) ...
Vormals nicht ausgewähltes Paket libpython-all-dev:amd64 wird gewählt.
Vorbereitung zum Entpacken von .../03-libpython-all-dev_2.7.15~rc1-1_amd64.deb ...
Entpacken von libpython-all-dev:amd64 (2.7.15~rc1-1) ...
Vormals nicht ausgewähltes Paket python-all wird gewählt.
Vorbereitung zum Entpacken von .../04-python-all_2.7.15~rc1-1_amd64.deb ...
Entpacken von python-all (2.7.15~rc1-1) ...
Vormals nicht ausgewähltes Paket python2.7-dev wird gewählt.
Vorbereitung zum Entpacken von .../05-python2.7-dev_2.7.15~rc1-1_amd64.deb ...
Entpacken von python2.7-dev (2.7.15~rc1-1) ...
Vormals nicht ausgewähltes Paket python-dev wird gewählt.
Vorbereitung zum Entpacken von .../06-python-dev_2.7.15~rc1-1_amd64.deb ...
Entpacken von python-dev (2.7.15~rc1-1) ...
Vormals nicht ausgewähltes Paket python-all-dev wird gewählt.
Vorbereitung zum Entpacken von .../07-python-all-dev_2.7.15~rc1-1_amd64.deb ...
Entpacken von python-all-dev (2.7.15~rc1-1) ...
Vormals nicht ausgewähltes Paket python-crypto wird gewählt.
Vorbereitung zum Entpacken von .../08-python-crypto_2.6.1-8ubuntu2_amd64.deb ...
Entpacken von python-crypto (2.6.1-8ubuntu2) ...
Vormals nicht ausgewähltes Paket python-secretstorage wird gewählt.
Vorbereitung zum Entpacken von .../09-python-secretstorage_2.3.1-2_all.deb ...
Entpacken von python-secretstorage (2.3.1-2) ...
Vormals nicht ausgewähltes Paket python-keyring wird gewählt.
Vorbereitung zum Entpacken von .../10-python-keyring_10.6.0-1_all.deb ...
Entpacken von python-keyring (10.6.0-1) ...
Vormals nicht ausgewähltes Paket python-keyrings.alt wird gewählt.
Vorbereitung zum Entpacken von .../11-python-keyrings.alt_3.0-1_all.deb ...
Entpacken von python-keyrings.alt (3.0-1) ...
Vormals nicht ausgewähltes Paket python-pip-whl wird gewählt.
Vorbereitung zum Entpacken von .../12-python-pip-whl_9.0.1-2.3~ubuntu1_all.deb ...
Entpacken von python-pip-whl (9.0.1-2.3~ubuntu1) ...
Vormals nicht ausgewähltes Paket python-pip wird gewählt.
Vorbereitung zum Entpacken von .../13-python-pip_9.0.1-2.3~ubuntu1_all.deb ...
Entpacken von python-pip (9.0.1-2.3~ubuntu1) ...
Vormals nicht ausgewähltes Paket python-setuptools wird gewählt.
Vorbereitung zum Entpacken von .../14-python-setuptools_39.0.1-2_all.deb ...
Entpacken von python-setuptools (39.0.1-2) ...
Vormals nicht ausgewähltes Paket python-wheel wird gewählt.
Vorbereitung zum Entpacken von .../15-python-wheel_0.30.0-0.2_all.deb ...
Entpacken von python-wheel (0.30.0-0.2) ...
Vormals nicht ausgewähltes Paket python-xdg wird gewählt.
Vorbereitung zum Entpacken von .../16-python-xdg_0.25-4ubuntu1_all.deb ...
Entpacken von python-xdg (0.25-4ubuntu1) ...
python-secretstorage (2.3.1-2) wird eingerichtet ...
python-pip-whl (9.0.1-2.3~ubuntu1) wird eingerichtet ...
python-setuptools (39.0.1-2) wird eingerichtet ...
python-crypto (2.6.1-8ubuntu2) wird eingerichtet ...
python-keyring (10.6.0-1) wird eingerichtet ...
python-wheel (0.30.0-0.2) wird eingerichtet ...
python-keyrings.alt (3.0-1) wird eingerichtet ...
Trigger für doc-base (0.10.8) werden verarbeitet ...
1 hinzugefügte Doc-base-Datei wird verarbeitet...
Dokumente werden mit scrollkeeper registriert ...
libexpat1-dev:amd64 (2.2.5-3) wird eingerichtet ...
Trigger für man-db (2.8.3-2ubuntu0.1) werden verarbeitet ...
libpython2.7-dev:amd64 (2.7.15~rc1-1) wird eingerichtet ...
python-pip (9.0.1-2.3~ubuntu1) wird eingerichtet ...
python2.7-dev (2.7.15~rc1-1) wird eingerichtet ...
python-all (2.7.15~rc1-1) wird eingerichtet ...
python-xdg (0.25-4ubuntu1) wird eingerichtet ...
libpython-dev:amd64 (2.7.15~rc1-1) wird eingerichtet ...
python-dev (2.7.15~rc1-1) wird eingerichtet ...
libpython-all-dev:amd64 (2.7.15~rc1-1) wird eingerichtet ...
python-all-dev (2.7.15~rc1-1) wird eingerichtet ...
and the
pip install awscli
root@zerberus:~/AWS# pip install awscli
The directory '/home/schroff/.cache/pip/http' or its parent directory is not owned by the current user and the cache has been disabled. Please check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag.
The directory '/home/schroff/.cache/pip' or its parent directory is not owned by the current user and caching wheels has been disabled. check the permissions and owner of that directory. If executing pip with sudo, you may want sudo's -H flag.
Collecting awscli
  Downloading https://files.pythonhosted.org/packages/65/8c/9ac9abe65374653eb65de2fdaecc43e0b6940378e8fccec3a23fbfdc656b/awscli-1.16.53-py2.py3-none-any.whl (1.4MB)
    100% |████████████████████████████████| 1.4MB 798kB/s
Collecting docutils>=0.10 (from awscli)
  Downloading https://files.pythonhosted.org/packages/50/09/c53398e0005b11f7ffb27b7aa720c617aba53be4fb4f4f3f06b9b5c60f28/docutils-0.14-py2-none-any.whl (543kB)
    100% |████████████████████████████████| 552kB 1.6MB/s
Requirement already satisfied: PyYAML<=3.13,>=3.10 in /usr/lib/python2.7/dist-packages (from awscli)
Collecting rsa<=3.5.0,>=3.1.2 (from awscli)
  Downloading https://files.pythonhosted.org/packages/e1/ae/baedc9cb175552e95f3395c43055a6a5e125ae4d48a1d7a924baca83e92e/rsa-3.4.2-py2.py3-none-any.whl (46kB)
    100% |████████████████████████████████| 51kB 4.0MB/s
Collecting colorama<=0.3.9,>=0.2.5 (from awscli)
  Downloading https://files.pythonhosted.org/packages/db/c8/7dcf9dbcb22429512708fe3a547f8b6101c0d02137acbd892505aee57adf/colorama-0.3.9-py2.py3-none-any.whl
Collecting s3transfer<0 .2.0="">=0.1.12 (from awscli)
  Downloading https://files.pythonhosted.org/packages/d7/14/2a0004d487464d120c9fb85313a75cd3d71a7506955be458eebfe19a6b1d/s3transfer-0.1.13-py2.py3-none-any.whl (59kB)
    100% |████████████████████████████████| 61kB 3.6MB/s
Collecting botocore==1.12.43 (from awscli)
  Downloading https://files.pythonhosted.org/packages/c4/d8/242e75a2d9df95510883b65d95c26dab16d2980296c5437782e4f961fbb6/botocore-1.12.43-py2.py3-none-any.whl (4.8MB)
    100% |████████████████████████████████| 4.8MB 303kB/s
Collecting pyasn1>=0.1.3 (from rsa<=3.5.0,>=3.1.2->awscli)
  Downloading https://files.pythonhosted.org/packages/d1/a1/7790cc85db38daa874f6a2e6308131b9953feb1367f2ae2d1123bb93a9f5/pyasn1-0.4.4-py2.py3-none-any.whl (72kB)
    100% |████████████████████████████████| 81kB 5.0MB/s
Collecting futures<4 .0.0="">=2.2.0; python_version == "2.6" or python_version == "2.7" (from s3transfer<0 .2.0="">=0.1.12->awscli)
  Downloading https://files.pythonhosted.org/packages/2d/99/b2c4e9d5a30f6471e410a146232b4118e697fa3ffc06d6a65efde84debd0/futures-3.2.0-py2-none-any.whl
Collecting jmespath<1 .0.0="">=0.7.1 (from botocore==1.12.43->awscli)
  Downloading https://files.pythonhosted.org/packages/b7/31/05c8d001f7f87f0f07289a5fc0fc3832e9a57f2dbd4d3b0fee70e0d51365/jmespath-0.9.3-py2.py3-none-any.whl
Collecting urllib3<1 .25="">=1.20 (from botocore==1.12.43->awscli)
  Downloading https://files.pythonhosted.org/packages/62/00/ee1d7de624db8ba7090d1226aebefab96a2c71cd5cfa7629d6ad3f61b79e/urllib3-1.24.1-py2.py3-none-any.whl (118kB)
    100% |████████████████████████████████| 122kB 4.7MB/s
Requirement already satisfied: python-dateutil<3 .0.0="">=2.1 in /usr/lib/python2.7/dist-packages (from botocore==1.12.43->awscli)
Installing collected packages: docutils, pyasn1, rsa, colorama, futures, jmespath, urllib3, botocore, s3transfer, awscli
Successfully installed awscli-1.16.53 botocore-1.12.43 colorama-0.3.9 docutils-0.14 futures-3.2.0 jmespath-0.9.3 pyasn1-0.4.4 rsa-3.4.2 s3transfer-0.1.13 urllib3-1.24.1
To use the command line you have to create access keys:


and now you can configure your aws cli:

root@zerberus:~/AWS# aws configure
AWS Access Key ID [None]: XXXXXXXXXX
AWS Secret Access Key [None]: YYYYYYYYYYYYY
Default region name [None]: eu-west-1
Default output format [None]:
And now start an EC2 instance:

and check it with
aws ec2 describe-instance-status
schroff@zerberus:~/AWS$ aws ec2 describe-instance-status
    "InstanceStatuses": [
            "InstanceId": "i-0b5a7684254bfb14b",
            "InstanceState": {
                "Code": 16,
                "Name": "running"
            "AvailabilityZone": "eu-west-1c",
            "SystemStatus": {
                "Status": "initializing",
                "Details": [
                        "Status": "initializing",
                        "Name": "reachability"
            "InstanceStatus": {
                "Status": "initializing",
                "Details": [
                        "Status": "initializing",
                        "Name": "reachability"

New OA Framework 12.2.6 Update 16 Now Available

Steven Chan - Wed, 2018-11-14 10:15

Web-based content in Oracle E-Business Suite Release 12 runs on the Oracle Application Framework (also known as OA Framework, OAF, or FWK) user interface libraries and infrastructure.

We periodically release updates to Oracle Application Framework to fix performance, security, and stability issues.

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Packs. In this context, cumulative means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for Oracle E-Business Suite Release 12.2.6 is now available:

Oracle Application Framework (FWK) Release 12.2.6 Bundle 16 (Patch 28649181:R12.FWK.C)

Where is this update documented?

Instructions for installing this OAF Release Update Pack are in the following My Oracle Support knowledge document:

Who should apply this patch?

All Oracle E-Business Suite Release 12.2.6 users should apply this patch. Future OAF patches for EBS Release 12.2.6 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes all fixes released in previous EBS Release 12.2.6 bundle patches.

In addition, this latest bundle patch includes fixes for the following issues:

  • Partial Page Rendering (PPR) event queuing fails intermittently in the IE 11 browser on Windows 7.

  • The table scroll bar cannot be moved up or down on the iProcurement item search page.

  • Text inside a disabled message text area fails to wrap and may therefore not display fully in the IE 11 browser.

  • An advanced table with many columns shows blank on scrolling down in the IE 11 browser on Windows 7.

Related Articles

Categories: APPS Blogs


Subscribe to Oracle FAQ aggregator