Skip navigation.

Feed aggregator

PeopleTools Mobile Book Now Shipping

Jim Marion - Wed, 2015-09-30 09:02

I received notice yesterday that our latest book, PeopleSoft PeopleTools: Mobile Applications Development (Oracle Press) 1st Edition, is now shipping. Probably the most exciting news is that Amazon has the book listed as the #1 New Release in the Oracle Databases category.

Oracle 12c – Adaptive Query Optimization

Pythian Group - Wed, 2015-09-30 07:51

Scenario: A user complains that a batch job that is critical for the month end business processing is taking more time than expected. As a DBA you quickly check and identify the sql statement that is running, compare the current execution plan with the previous plans and come to the conclusion that it has picked up a wrong plan. Instead of a Hash Join, it is doing a Nested Loop or did not pick the parallel threads and instead does a single threaded full table scan.

At this juncture, you do not have control to change the execution plan unless you cancel the job, fix it and rerun it. A simple analogy is like getting stuck in traffic and cannot do anything about it, even though you know that there are better and faster alternative routes to reach your destination.

Luckily, the above scenario was the case with 11g, though with 12c a new feature called – Adaptive Query Optimization was introduced and this feature helps the optimizer adjust the plans based on the real time data.

12c Optimizer:- With Oracle 11g, an optimizer decides an optimal execution plan for a query based on the conditions in the query, statistical information of the underlying objects and initialization parameters that influence the optimizer. With 12c, a new adaptive approach to query optimization is introduced by adjusting execution plans based on information collected during run time. This new approach is extremely helpful when the existing statistics are not sufficient to generate an optimal plan. There are two aspects in Adaptive Query Optimization:

  1. Improving the initial execution of a query during runtime.
  2. Adaptive statistics, that provide additional information for subsequent executions.

I will be focusing on the first aspect in this article.

The two features that optimizer adjusts based on the real time data are:

Hash Join vs Nested Loops – Optimizer may pick nested loop for a join operation based on the existing table statistics information, but during run time, if it realizes that more data is being processed, it will switch to Hash Join. This adaptive optimizer feature not only changes the plan on run time, but stores the adaptive statistics in the database. This additional information will be useful for future executions.

Parallel Distribution – Hybrid Hash – When a SQL statement is executed in parallel mode, optimizer decides whether to perform certain operations like sorts, joins and aggregations in parallel or as a single threaded operation based on the statistics. With the new adaptive feature, optimizer differs this decision till run time and based on the run time data it decides whether to pick the parallel mode or not.

Good resource about this new feature can be found here and here.

Check some of our other Oracle 12c blogs


Discover more about our Oracle expertise. 

Categories: DBA Blogs

[Video] Getting Started with Formspider

Gerger Consulting - Wed, 2015-09-30 07:28
Yesterday we hosted a webinar for people who are interested in learning Formspider but could not find the time. Don’t worry if you missed it though! Below is a recording of the event.

The webinar consists of seven parts:

1) About Gerger, the company behind Formspider.

2) Brief history of Formspider and a short overview of its references and success stories.

3) Formspider Benefits & Features.

4) Formspider Architecture

5) Overview of the Formspider IDE

6) Introduction to Formspider Objects and API’s.

7) Let’s build an application with Formspider

During the webinar, we also answered a lot of questions coming from the audience.

You can watch the webinar below:


And here are the slides we used during the presentation:

Getting started with Formspider from Yalim K. Gerger

You can download the assets of the sample application we built from this Dropbox link.

Many thanks to everyone who attended to the webinar.

Categories: Development

SQL Profile and Plan Baseline Notes

Michael Dinh - Wed, 2015-09-30 07:09

Optimizer with Oracle Database 12c
Oracle White Paper – June 2013

What is the difference between SQL Profiles and SQL Plan Baselines?

The difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can’t.

By Maria Colgan-Oracle on May 08, 2012

Plan Stability using Sql Profiles and SQL Plan Management
Amit Bansal / 20 December, 2011

3 OCT/10

Comments from Kerry Osborne September 9th, 2011 – 18:31
You didn’t mention the two biggest differences (in my opinion).

Baselines know what plan they are trying recreate and SQL Profiles do not.
SQL Profiles will blindly apply any hints it has and what you get is what you get.
Baselines will apply the hints and if the optimizer gets the plan it was expecting, it uses the plan.
If it doesn’t come up with the expected plan, the hints are thrown away and the optimizer tries again (possibly with the hints from another accepted Baseline).

Profiles have a “force matching” capability that allows them to be applied to multiple statements that differ only in the values of literals.
Think of it as a just in time cursor sharing feature. Baselines do not have this ability to act on multiple statements.

Comments from Kerry Osborne January 25th, 2012 – 16:38

I have seen Baselines be disregarded, even without such extreme conditions as a specified index having been removed.

The reason for this is that Baselines attempt to apply enough hints to limit the choices the optimizer has to a single plan,
but there are situations where the set of hints is not sufficient to actually force the desired plan.

What I mean is that the hints will eliminate virtually all possibility but there still may be a few that are valid and so it’s possible to get a different plan.

In fact, I have even seen situations where the act of creating a Baseline causes the plan to change.

This is clearly not intended behavior but it can happen.

I think I blogged about that once. At any rate, in these cases where the optimizer arrives at a different plan than was expected,
the optimizer uses a plan generated completely without the hints (or possible picks another accepted Baseline and applies it’s hints).

This behavior is very different from SQL Profiles which blindly apply the hints.

I should also note that SQL Profiles can also be created manually with the same set of hints that would be used in a Baseline and there is a MOS note regarding how to do this.

I have blogged about that as well here:

Around the Clock Database Support: The Database Night Watch

Chris Foot - Wed, 2015-09-30 07:00

It’s late, you’re tired, and all you want is your head to hit the pillow – all DBAs know the feeling.  It was a long day to begin with, and just as you drifted off to sleep, your cell phone rings.  All of a sudden you need to log back into your datacenter and troubleshoot a critical database that became unresponsive.  End users need access to this data before they sit down at their desk in the morning – so sleep isn’t an option.

There’s no such thing as time off in Database Administration, meaning DBA managers and their teams never truly rest.  You’re tasked with database security, monitoring, capacity planning, hardware/software installation and configuration, and numerous other day-to-day responsibilities, all while juggling the critical issues that arise around the clock.  But as your company’s “go-to” IT professional, the data and its surrounding environment are your responsibility 24×7.

Common Problems Facing Modern DBA Teams

In-house DBA managers and their teams face a variety issues while trying to properly manage the databases they’re responsible for.  The planned structure of a workday can be derailed in an instant when dealing with:

  • Understaffing / Limited Resources
  • Break/Fix Problems & Critical Issues (Maintained 24×7)
  • Gaps in Technical Knowledge
    • No DBA knows every platform and application
  • Rapid Updates & Development of Applications

New Call-to-action

Internal DBA teams often feel overwhelmed with the responsibilities they carry around, and rightfully so with those responsibilities weighing on their shoulders 24×7.  Weekends, holidays and late nights are all fair game to be maintaining these environments.  But the most frustrating part for a DBA is when these recurring issues keep them from fully using their skillset to design, build and deploy projects for the long-term benefit of the database environment and business.  It’s these reasons that often leave DBA managers looking for remote DBA support.


100% US-Based Supplemental Database Support

With internal resources overwhelmed, remote database support is an option DBA managers consider when looking to supplement their in-house team.  Based on the needs of your business, remote DBA support from RDX gives businesses the entire collective knowledge of a team of DBAs with expertise across a wide range of platforms and applications.  RDX DBA support frees up time for your team to focus on the high priority / high ROI activities that have been neglected due to the current workload.

With database monitoring services and access to RDX expertise, we’re able to help your DBAs focus on revenue-generating initiatives and provide emergency support for critical issues – but most importantly, you can finally get some sleep.  Even more importantly, these tools and best practices insure fewer emergency database issues in the long run.

Customized Monitoring Strategy that Meets Your Unique Needs

RDX works with you to custom tailor an event notification and resolution strategy for every component of your application architecture including databases, middle tier servers and other hardware devices.  From monitoring a database buffer cache to a long running job or third-party application error log, the RDX DOC team is able to quickly deploy a customized monitoring solution that meets each, individual customer’s monitoring requirements.

24×7 Emergency Remote DBA Support

Rest easy – RDX’s emergency remote database support has your environments covered 24×7. Our Database Operations Center (DOC) keeps a watchful eye on your databases around the clock; and all technicians are dedicated IT professionals trained in problem-resolution, oftentimes correcting problems before anyone even notices.

The DOC is also your contact for database support in the early hours of the morning – and they will escalate any critical issues that arise to our on-call DBAs who will work tirelessly until all issues are corrected.  Even in the early hours of the morning, if a critical database error has occurred on a host server, the DOC and RDX remote DBAs are available and working to correct the issue – finally giving you the support needed to become proactive with your database administration.

The post Around the Clock Database Support: The Database Night Watch appeared first on Remote DBA Experts.

Integrating PFCLScan and Creating SQL Reports

Pete Finnigan - Wed, 2015-09-30 05:50

We were asked by a customer whether PFCLScan can generate SQL reports instead of the normal HTML, PDF, MS Word reports so that they could potentially scan all of the databases in their estate and then insert either high level....[Read More]

Posted by Pete On 25/06/14 At 09:41 AM

Categories: Security Blogs

Oracle Database Developer Choice Awards : Spread the Love!

Tim Hall - Wed, 2015-09-30 01:57

You’ve probably already heard about the Oracle Database Developer Choice Awards, Steven Feuerstein wrote about them here, but this is just a quick reminder to get your votes in.

This year there are five categories to vote for:

I am always a little nervous about “popularity contests”, but I would suggest you look through the finalists for each category and vote for someone if they have made an impact on you personally. IMHO it shouldn’t be a, “vote for your mate”, thing.

You’ve got until 15th October to get your votes in! :)



Oracle Database Developer Choice Awards : Spread the Love! was first posted on September 30, 2015 at 8:57 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Links for 2015-09-29 []

Categories: DBA Blogs

Another Home for Our Stuff

Oracle AppsLab - Tue, 2015-09-29 14:43

I’m very pleased to announce that our colleague and friend Kathy recently pushed live a page on under the OAUX section, dedicated to our Emerging Technologies team and our work.

Our new home, circa 2015.

Our new home, circa 2015.

Big moment for us. I feel like a made guy.

Don’t worry. This little blog will continue to be our home and stream of consciousness, but now, we have another, more structured home, focused on our projects and thinking.

Like that’s our home, all neat and organized, and this is our garage, where we tinker and talk about interesting stuff, all disorganized, with exposed wires and cracked open cases.

Anyway, the content there won’t be as fluid as what you read here, but it won’t be a static collection. We’ll keep it updated as we progress on our projects and find new shiny objects to chase.

Huzzah for us. Thanks our GVP Jeremy (@jrwashley) Misha (@mishavaughan), head of the OAUX Communications and Outreach team, and everyone who has made this possible. Enjoy.Possibly Related Posts:

Oracle ERP Cloud Customers Share Their Journeys at OpenWorld 2015

Linda Fishman Hoyle - Tue, 2015-09-29 12:45
A Guest Post by Kazim Isfahani, Director, Oracle ERP Cloud Strategy (pictured left)

The excitement is rising around OpenWorld 2015. CFO’s, project leaders, procurement officers, along with finance and operations leaders, will gather in San Francisco October 25 - 29 to learn more about how to shift their businesses to the cloud. The 40+ conference sessions, conveniently located in Moscone West, are designed for ERP Cloud customers and prospects to hear from their peers, learn from Oracle’s experts, and maximize their time at OpenWorld.

Why Attend?

The ERP Cloud team has a plethora of customers, each willing to share their journey to the cloud. Attendees will have the opportunity to meet, network, and have one-on-one discussions with senior executives from companies including Solairus Aviation, Total Terminals International, MyEyeDr., Transit Wireless, Macy’s, Irving Materials, Alex Lee, Pandora, and GE. These speakers bring a treasure trove of vital information and proven success recipes that will guide any customer considering an ERP Cloud journey of their own.

Key Oracle ERP executives will be on-hand including Rondy Ng, SVP Applications Development; Tom Anthony, VP Procurement Product Strategy; Colleen Baumbach, VP PPM Product Strategy; and Terrance Wampler, VP Financials Product Strategy. Each will be showcasing the latest product releases and associated roadmaps across the ERP family. There are a number of sessions that are not to be missed:

  • Modernize Your Business with Oracle ERP Cloud, October 26, 1:30 p.m. PT | Room 2008 [GEN6885]
  • Empowering Modern Procurement, October 26, 4:00 p.m. PT | Room 3003 [GEN6881]
  • The Future of PPM Is in the Cloud, October 26, 4:00 p.m. PT | Room 3005 [GEN9475]
  • Oracle Revenue Management Cloud Service: Updates for IFRS15/ASC606, Highlights, Customer Panel, October 27, 11:00 a.m. PT | Room 3006 [CON9214]
  • Around the Cloud in Less Than 80 Days: An Oracle Financials Cloud Success Story, October 27, 5:15 p.m. PT | Room 3006 [CON6009]
  • Shift Happens—Manage the Change to Cloud Applications, October 28, 9:15 a.m. PT | Room 2008 [GEN8421]
  • Managing ERP Cloud Service After Go-Live, October 29, 9:30 a.m. PT | Room 3006 [CON9218]

Meet the Experts

These popular sessions give customers  an opportunity to meet the people who design and build the applications. These interactive sessions allow for discussions on Oracle Applications strategy and customer-specific business and IT strategy. The experts are available to discuss the latest releases and share insights into the best path for an enterprise. Space is limited, so pre-registration is recommended. There are four sessions scheduled.

  • Meet the Experts: Oracle Procurement Cloud, October 27, 11:00 a.m. PT | Room 3001A [MTE10274]
  • Meet the Experts: Oracle Financials Cloud, October 27, 4:00 p.m. PT | Room 3001A [MTE10271]
  • Meet the Experts: Oracle Project Portfolio Management Cloud, October 28, 12:15 p.m. PT | Room 3001A [MTE10273]
  • Meet the Experts: Oracle Fusion Accounting Hub Reporting Cloud Service, October 28, 4:15 p.m. PT | Room 3001A [MTE10272]


Located in Moscone West, on the Main Level, the ERP Showcase will be the place to see the latest and greatest ERP Cloud releases firsthand. Oracle’s top product experts will be on site to highlight the benefits and answer customer questions.

What About After Hours?

After a long day of learning, networking, and sharing, OpenWorld has plenty of fun planned. It all starts with the Welcome Reception on Sunday, October 25 on the Oracle Plaza @ Howard Street.

World-renowned performers always headline the Oracle Appreciation Event and this year is no different. On Wednesday, October 28, join us on Treasure Island in an exclusive, private event.

That’s not all. OpenWorld will be the venue for numerous partner events, including those from our ERP sponsors Deloitte Consulting and Frontera Consulting.

So … What Now?

These are just some of the many ERP Cloud specific activities that you will not want to miss. Discover all that Oracle OpenWorld 2015 has to offer on Oracle ERP Cloud via our FocusOn ERP Cloud Document.

If you have not already, be sure to make your plans now to learn everything there is to know about the Oracle ERP Cloud at OpenWorld. The depth and breadth of the ERP Cloud agenda and the customer stories will leave you with a guided roadmap to begin or accelerate your own journey to the ERP Cloud. See the full OpenWorld agenda for more.

We look forward to seeing you there! Register now!

User Experience and the Competitive Wedge

Linda Fishman Hoyle - Tue, 2015-09-29 11:55

A Guest Post by Kathy Miedema, Senior Oracle Market Research Analyst (pictured left)

With the advent of the simplified user interface (UI) two Oracle OpenWorlds ago, Group Vice President Jeremy Ashley changed the way Oracle customers experience the cloud.

Now the cloud is the way forward, and the simplified UI is the competitive wedge. Jeremy (pictured right), who leads the Oracle Applications User Experience team, has spent the last year looking ahead at how mobile devices are changing the way we work, and where the Internet of Things fits in.

Catch up with Jeremy and see how mobile devices and emerging technology are influencing the Oracle user experience on Oracle Voice, an Oracle blog on the platform. Think you’re one among billions who is shackled to your phone? You might be, but Jeremy says that mobile devices also free you and allow you to be more human in Why Oracle Had To Rethink UX For Cloud And Mobile.

And maybe the Internet of Things, or IoT, strikes you as something just for technology geeks and the movies. Jeremy’s mid-September post, Internet Of Things Is Already Here, So Start Building A Better IoT Experience, points out that IoT has been around for longer than you think, and a better user experience on any device is yours for the taking.

Jeremy and his team will be at OpenWorld, talking about the Oracle user experience roadmap and more. In addition to several sessions on UX strategy, the OAUX team will be showing demos of current, nearly-there, and future user experiences at the OAUX Exchange on Monday, October 26. You don’t need an OpenWorld badge to get in, but you do need to register. Hope to see you in San Francisco, October 25-29.

Master Notes

Jonathan Lewis - Tue, 2015-09-29 11:52

MoS has a number of “Master Note” documents which pop up from time to time while I’m checking for known problems or solutions; they get extended from time to time (and, conversely, link to some articles which are clearly no longer relevant to current versions). This is just a tidied up list of a few of the master notes that I’ve jotted down over time. The Data Dictionary overview appeared in my daily “Hot Topics” report today, and that prompted me to publish the list I’d got so far.

  • Oracle Learning Library (home page)
  • Oracle Online Learning advanced search (hundreds of free short vidoes)
  • Overview of Data Dictionary (1500058.1)
  • Privileges And Roles (1347470.1)
  • Overview of Oracle Segment Storage (1491960.1)
  • Overview of Oracle Temporary Tablespaces (1498442.1)
  • Troubleshooting Oracle Temporary Tablespaces (1524594.1)
  • Overview of Oracle Background Processes (1503146.1)
  • Troubleshooting Oracle Background Processes (1509616.1)
  • Overview for SCN issues (1503937.1)
  • Transaction management
  • Troubleshooting Database Startup/Shutdown Problems (851057.1)
  • Overview of Oracle Data Definition Language (DDL) (1501399.1)
  • Partitioning (1312352.1)
  • Parallel Execution (203238.1)
  • Table Compression 11g (1223705.1)
  • Resource Manager and DBMS_RESOURCE_MANAGER (1484302.1)
  • Materialized Views (1353040.1)
  • Collections (1147234.1)
  • Plan Stability Features (Including SQL Plan Management (SPM)) (1359841.1)
  • Flashback Technologies (1138253.1)
  • Database Vault (1195205.1)
  • Oracle Text Overview of New Features in Release 10g (Doc ID 249991.1)
  • Top 5 RAC issues (1373500.1)
  • Diagnosing ORA-600 (1092832.1)
  • Database Corruption Issues (1088018.1)

Quick Tip: If you search MoS with either of the expressions “overview of” or “Master Note” and a word describing the feature you’re researching then there’s a fair chance that you’ll find a document that catalogues a number of articles about the topic you’re interested in.

Smart Combo Box LOV Based on Description Attribute

Andrejus Baranovski - Tue, 2015-09-29 11:35
I had a question from blog reader for my previous post about smart Combo Box LOV - Smart Combo Box LOV with Filter. Reader was asking if it is possible to define Combo Box LOV on description rather than ID attribute. ADF BC out of the box allows to define LOV on readonly description attribute. I had a post about it - Defining the LOV on a Reference Attribute in Oracle ADF 11g. Today I will test and describe how the same can be applied for Combo Box LOV in ADF 12c.

If you want to define Combo Box LOV on description attribute, first thing to do is to create Association between main EO and EO from where description attribute should come:

Based on association we can define left outer join and bring description attribute to the main VO:

Description attribute is readonly, since it comes from join:

Combo Box LOV should be defined directly for this readonly description attribute. We should return description and key - both attributes. ADF BC would display description on UI, but it would return key attribute in the background:

Combo Box is set to filter initial list based on criteria, same as in previous blog mentioned above:

UI implementation contains LOV Combo Box mapped with JobTitle attribute:

This is how it looks on UI. LOV Combo Box displays description attribute and in the background it updates JobId. Same works in both table and form UI:

Combo Box LOV displays multiple attributes and rows are filtered based on initial View Criteria:

Download sample application -

Oracle 12c RAC on Oracle Linux 6 and 7 using NFS

Tim Hall - Tue, 2015-09-29 09:39

linux-tuxFollowing on from the last post, I’ve brought my NFS RAC stuff up to date also.

I noticed I had not done a RAC install using NFS on Oracle Linux 6, so I threw that in for good measure too. :)

Just as a little history to this… I was doing the desktop Oracle RAC thing (using VMware then VirtualBox) for a while, when I started reading some blog posts by Kevin Closson about NFS. At the time, NFS filers were considered the poor relation to SANs, which was obvious or they wouldn’t be so cheap in comparison right? In those articles Kevin pointed out that most people’s systems at the time probably weren’t capable of maxing out a decent filer if it were set up correctly. Since NFS is a cluster file system, that got me thinking I should try RAC on it to see how easy it was. That was in the Oracle 10g days. How time flies when you are having fun… :)



Oracle 12c RAC on Oracle Linux 6 and 7 using NFS was first posted on September 29, 2015 at 4:39 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

delete all data

Laurent Schneider - Tue, 2015-09-29 08:53

How do you delete all data? The simplistic approach would be to truncate all tables

SQL> select table_name from user_tables;
SQL> truncate table t1;
Table truncated.

You cannot truncate if you have referential integrity constraints.

SQL> truncate table t2;
ORA-02266: unique/primary keys in table 
  referenced by enabled foreign keys

Ok, let’s disable the RIC

SQL> select table_name, constraint_name
  from user_constraints
  where constraint_type='R';
--- ----------
T3  SYS_C00107
SQL> alter table t3 disable constraint SYS_C00107;
Table altered.
SQL> truncate table t2;
Table truncated.
SQL> truncate table t3;
Table truncated.

You cannot truncate cluster tables

SQL> truncate table t4;
ORA-03292: Table to be truncated is part of a cluster

Cluster tables could be dropped with TRUNCATE CLUSTER.

SQL> select cluster_name from user_clusters;
SQL> truncate cluster c;
Cluster truncated.

The code above doesn’t work with Partitioned cluster ( because it was not properly implemented at the time of the writing.

For reference partitioning, it is not possible to disable the foreign key

SQL> alter table t6 disable constraint fk;
ORA-14650: operation not supported for 
  reference-partitioned tables

In 12c, if the foreign key is defined with ON DELETE CASCADE, you can truncate cascade the parent.

SQL> select table_name, REF_PTN_CONSTRAINT_NAME
  from user_part_tables 
  where partitioning_type='REFERENCE';
--- ---
T6  FK 
SQL> select r_constraint_name, delete_rule 
  from user_constraints 
  where constraint_name='FK';
----- -----------
SQL> select table_name
  from user_constraints 
  where constraint_name='PK';
SQL> truncate table t5 cascade;
Table truncated.

But if one of child or child-child table is using reference partitioning without the ON DELETE CASCADE, then the parent or grand-parent could not be truncated. And truncate cascade for reference partitioning is not documented (yet).

But there is very nice alternative to TRUNCATE called is DELETE :)

SQL> select table_name, REF_PTN_CONSTRAINT_NAME
  from user_part_tables 
  where partitioning_type='REFERENCE';
--- ---
T8  FK 
SQL> select r_constraint_name, delete_rule 
  from user_constraints 
  where constraint_name='FK';
----- -----------
SQL> select table_name
from user_constraints 
where constraint_name='PK'
SQL> truncate table t7 cascade;
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SCOTT"."T8"
SQL> truncate table t8;
Table truncated.
SQL> delete from t7;
2 rows deleted

To get the tables in the right order, parent tables after children, you can do some hierarchical query and then order by rownum desc, a construct I’m using for the first time I confess. Note the leaf tables are truncable.

select c_owner owner, child table_name   
      p_OWNER, parent, nvl(c_owner, a.owner) c_owner,
      nvl(child, a.table_name ) child
        PT.OWNER P_owner, pt.table_name parent, 
        pt2.owner c_owner, pt2.table_name child
      FROM all_part_tables pt
      JOIN all_constraints c
      ON pt.OWNER = c.owner
        AND PT.TABLE_NAME = c.table_name
        AND c.constraint_type = 'P'
        AND c.status = 'ENABLED'
      JOIN all_constraints r
      ON r.r_owner = c.owner
        AND r.r_constraint_name = c.constraint_name
        AND r.constraint_type = 'R'
        AND r.status = 'ENABLED'
      JOIN all_part_tables pt2
      ON r.owner = pt2.owner
        AND r.constraint_name = pt2.REF_PTN_CONSTRAINT_NAME
        AND pt2.partitioning_type = 'REFERENCE'
    ) t
    RIGHT JOIN all_tables a 
    ON child = table_name and a.owner = T.c_OWNER
where connect_by_isleaf=0  
CONNECT BY parent = PRIOR child and p_owner=PRIOR c_owner
start with parent is null 
order by rownum desc;

----- ---

Note the query above is very slow. If dictionary-performance is an issue, maybe we could delete all tables and catch exceptions and loop until all tables are empty

SQL> delete from t9;
ORA-02292: integrity constraint (SCOTT.F10) violated - child record found
SQL> delete from t10;
ORA-02292: integrity constraint (SCOTT.F11) violated - child record found
SQL> delete from t11;
1 row deleted.
SQL> delete from t9;
ORA-02292: integrity constraint (SCOTT.F10) violated - child record found
SQL> delete from t10;
1 row deleted.
SQL> delete from t11;
0 row deleted.
SQL> delete from t9;
1 row deleted.
SQL> delete from t10;
0 row deleted.
SQL> delete from t11;
0 row deleted.
SQL> delete from t9;
0 row deleted.
SQL> delete from t10;
0 row deleted.
SQL> delete from t11;
0 row deleted.

If you have close to zero reference-partitioning table, this approach will be more efficient.

OPN: #28daysofcloud for Partners

Starting this week, watch for our 28 Days of Cloud series of informative content that will prepare your partners to better sell Oracle cloud offerings. 28 Days of Cloud is an immersive,...

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

Saving XML documents from an Oracle database

Marco Gralike - Tue, 2015-09-29 06:46
A colleague of mine asked about the several ways of saving XML content, I knew,…

Column Group Headings in an Interactive Report (APEX 5.0)

Dimitri Gielis - Tue, 2015-09-29 05:12
In 2008 I blogged about how to add Group Headings into an Interactive Report in Oracle Application Express (APEX 3.x!).

Here's an example what I mean with Group Headings, you want to logically group certain columns (e.g. Empno, Ename) and have a group name above them (e.g. Group 1):

The approach I used at that time worked, but it required a lot of manual work. In 2009 Martin D'Souza improved it and wrote a blog post about that. In 2010, when plugins were introduced in APEX 4, Martin wrapped it all in a nice APEX plugin which made adding Column Group Headings almost declarative. The plugin was further improved and put on GitHub.

Today, having APEX 5, and the Interactive Reports being changed a lot behind the scenes, the plugin was broken. As we're in the middle of restructuring how to share code, plugins etc., I forked Martin's git repo and made the plugin compatible with APEX 5 and Universal Theme (see version 1.0.4), so when you need it, you can grap it from the forked repo. At a later time the plugin will be moved to OraOpenSource, so depending when you read this post, check out OraOpenSource as Martin, I and many other developers will start putting our open source code on OraOpenSource.

Here's a quick how to use the plugin.

Step 1: Download the file dynamic_action_plugin_com_clarifit_apexplugin_ir_column_grouping.sql
Step 2: Import the plugin in your APEX application
Step 3: Define the Groups in the Interactive Report (you can right click on Column Groups in the  Attributes of your Interactive Report and add the Groups you need):

Step 4: Assign the group to the columns of your report (you can multi select the columns and in the right pane you can select the group):

Important: define for every column that has a group defined, a static id equal to the name of the column (e.g. ENAME).

Step 5: Add the dynamic action plugin to the page
- Event: After Refresh
- Selection Type: Region
- Region:
- IR Column Grouping [Plug-In]

That's it... note that when you scroll down and you have sticky headers, the group will stay with it.

Categories: Development

Focus on Digital Experience, Cloud Collaboration, Content and Business Process Management

WebCenter Team - Tue, 2015-09-29 05:00
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Each year at OOW, we drive the best and most innovative sessions for our attendees. This year is no different. With over 30 speaking sessions reflecting strategy and perspectives from our executives, technology experts, customers, partners, user groups and industry thought leaders; 10 live product demos; and 7 hands-on-labs devoted to Oracle WebCenter, Oracle Business Process Management (BPM) and our Cloud Content and Collaboration solutions, we are excited to showcase our product innovations, customer successes and vision at OOW 2015.

This Year’s Highlights:

  • Over 30 sessions featuring customers and partners like Lincoln Financial Group, the Sherwin Williams Company, Nextel, South California Gas Company, TekStream Solutions, Capgemini, Booz Allen Hamilton, Mythics and many more
  • A Meet the Experts session on Tuesday, October 27 from 7:00 p.m. – 8:00 p.m. on “Oracle’s Digital Experience Strategy: Oracle WebCenter, Oracle BPM, and Oracle Cloud Services”
  • 7 live product demo stations; 6 hands-on labs sessions, including Cloud labs focusing on Documents Cloud, Process Cloud and offering sneak previews of upcoming cloud services.
  • Innovation Awards ceremony with winners and representation from Digital Experience, BPM, Documents Cloud Service and Process Cloud Service

Must Attend Sessions:

Don’t miss the Strategy and Vision sessions for the overall portfolio and each of the respective solution and cloud technologies. We invite all customers to attend these not-to-be-missed sessions that can help organizations plan their roadmaps. Attendees will also get an opportunity to hear from customer organizations on implementation successes.

Monday, October 26, 2015

Digital Experience and Collaboration Roadmap—Oracle WebCenter, BPM, and Cloud Services [CON9207]
11:00 am - 11:45 am | Moscone West—2024

Oracle Documents Cloud Service—Vision, Strategy, and Roadmap [CON9183]
12:15 pm - 1:00 pm | Moscone West—2024

Oracle Process Cloud Service and the Oracle BPM Suite—Vision, Strategy, and Roadmap [CON9181]
1:30 pm - 2:15 pm | Moscone West—2024

Oracle WebCenter Sites Strategy and Vision [CON9191]
2:45 pm - 3:30 pm | Moscone West—2024

Tuesday, October 27, 2015

Oracle WebCenter Portal Strategy and Vision [CON9187]
11:00 am - 11:45 am | Moscone West—2024

Oracle Sites Cloud Service: Rapid Building of Mobile-First Digital Experiences [CON9199]
4:00 pm - 4:45 pm | Moscone West—2024

Oracle WebCenter Content and Other Oracle WebCenter Solutions: Vision and Strategy [CON9182]
12:15 pm - 1:00 pm | Moscone West—2024

Oracle’s Digital Experience Strategy: Oracle WebCenter, Oracle BPM, and Oracle Cloud Services [MTE10051]
7:15 pm - 8:00 pm | Moscone South—301

There are many more sessions highlighting customer successes, partner discussions, persona and/or industry based discussions, Cloud/PaaS lessons, live product demonstrations and Hands-On-Labs (HOL) sessions so do bookmark the following links for a complete listing:

Focus on: Digital Experience, Content and Business Process Management

Focus on: Content and Collaboration in the Cloud

Note that My Schedule is live for Oracle OpenWorld now. Customer and partner attendees can use My Schedule to plan and optimize time during the conferences by building personalized conference schedule beforehand. We recommend adding the sessions mentioned above and the others of interest from the Focus On documents.

WebCenter & BPM Auxiliary Events at OOW:

In addition to regularly scheduled programs of sessions, hands-on labs and demos, we have planned additional events for our customers and partners to actively engage with the Oracle and partner teams, and network with their peers. These auxiliary events include:

· Customer Appreciation Reception – Monday 10/26 – 6:30pm - 8:30pm (Invite and RSVP | OOW Registration NOT Required)

· Innovation Awards Ceremony (CON10374) – Tuesday 10/27– 4:00pm - 5:30pm, YBCA Theater (OOW Registration Required)

· Oracle Appreciation Event at OOW 15 with Elton John, featuring Beck – Wednesday 10/28 – 6:30 pm – 12:00 am (Event Information)

Your Conference on Mobile:

Attendees with Apple iOS or Android devices can access Oracle conference information via the Oracle OpenWorld mobile app (available mid-October). Download the app from the Apple iTunes store or Google Play, and get news and updates, access your schedule and the Oracle OpenWorld Content Catalog, explore interactive maps, find exhibitors and sponsors, and discover social channels.

Social Media Communications:

We will be highlighting our key session on Oracle WebCenter blog periodically. In addition, please use the following hashtags to discuss OOW on your channels and follow along on @OracleWebCenter and @OracleBPM

#OOW15 | #OracleDOCS | #OraclePCS | #OracleSCS | #WebCenter | #OracleBPM | #OraclePaaS

Don’t forget to RSVP for the Customer Appreciation Event and bookmark:

Focus on: Digital Experience, Content and Business Process Management

Focus on: Content and Collaboration in the Cloud

Looking forward to a great #OOW15 together!

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

Come and Enjoy – OakTable World 2015

Marco Gralike - Tue, 2015-09-29 02:31
Yep, its free, its cool and a lot of fun. During Oracle OpenWorld 2015, the…