Feed aggregator

direct connect Database links in the exadata environment

Tom Kyte - Wed, 2017-06-07 10:06
Can Dblinks be utilized in the Exadata environment. If not, how are direct connects to external databases done?
Categories: DBA Blogs

Convert loop to bulk collect forall

Tom Kyte - Wed, 2017-06-07 10:06
Here is my code: <code>procedure load_cmpnt_history is cursor c1 is select --+rule h.ndc_emp_id , nvl(upper(h.cmpnt_id),'FAA00000') cmpnt_id , h.compl_dt , nvl(nvl(h.crs_grade,h.cmpl_stat),'OTHER...
Categories: DBA Blogs

Partition Maintenance Induced Concurrency

Tom Kyte - Wed, 2017-06-07 10:06
Hi TOMs, A little back story: Our application incurs many "cursor: pin s wait on x" and "library cache lock" waits during our nightly partition maintenance job. It has been expected behavior and one that we've been living happily with for quit...
Categories: DBA Blogs

Abnormally RAM consuption by sqlldr

Tom Kyte - Wed, 2017-06-07 10:06
Hello, We have problem with sqlldr, we have server with 32 GB RAM and 24 GB swap. While loading big table ( 40 mil records with blobs ), free RAM decreases continuostly, and next swap decreases continuostly too. After some while loading finsih...
Categories: DBA Blogs

Is there a mailing list for APEX release announcements?

Joel Kallman - Wed, 2017-06-07 09:49
There was an interesting question today from Oracle Support, on behalf of a customer.  They asked:
"The customer would like to know if there is any mailing list or other type of subscription to be notified whenever there is a new or upgrade release of APEX."
And I answered with:

As far as mailing lists, they could always sign up for the Oracle Database Insider Newsletter.  Announcements about Oracle Application Express, SQL Developer, Oracle REST Data Services and many other database-related technologies are typically included in this newsletter.

I can also suggest that your customer:
  1. Follow Oracle Application Express on Facebook:  https://www.facebook.com/orclapex/
  2. Join the LinkedIn group:  https://www.linkedin.com/groups/8263065
  3. Follow @oracleapexnews on Twitter:  https://twitter.com/oracleapexnews
  4. Visit the Web site:  https://otn.oracle.com/apex
The APEX releases and patch sets are always announced through these social media channels.

And let's not forget https://apex.world, which is the clearinghouse of information for everything APEX.

OAC: Essbase and DVCS

Rittman Mead Consulting - Wed, 2017-06-07 09:00

Finally managed to get around to having a proper look at Essbase within Oracle Analytics Cloud Service (OAC) after a busy couple of months. This post focusses mainly on initial impressions on the ‘out of the box’ the Essbase side of this - which we will explore in more detail in future posts, as well as more detail on the use of Essbase with DVCS.

Using Essbase with DVCS

One of the features we are keen to explore more in this context is the integration of Essbase and the Data Visualisation Cloud Service (DVCS). One point that we found that we do not think is being expressed clearly anywhere else we have seen is how to configure this: In setting up our OAC instance, we were having difficulty coming up with a combination of configuration selections that enables Essbase and DV to work at the same time.

Oracle documentation (such as the price list) suggest that both should be available within Standard Edition OAC:

But Doc ID 2265410.1 on MoS suggests, by needing to add a security rule to the Essbase OAC, that two OAC instances are required. We could not find any reference to this requirement in Oracle documentation or blogs on the subject, but it transpires after checking with Oracle that this is indeed the case – Essbase and DV need to be on separate OAC instances.


Looking purely at Essbase, my initial reaction is very positive…whilst the interface is different (I am sure tears will be shed for EAS & Studio in the foreseeable future…although given the way some stalwarts are still clinging on the last surviving copies of the Excel Add In, maybe not too imminently), once the surface of the new interface is scratched more...ahem…’seasoned’ developers will take comfort from being able to do a lot of the same things as they currently can. I am also confident it will fulfil one of the stated objectives in making it easier for non-experts to be able quickly and easily deploy cubes for analysis purposes.

Whilst the manual application and cube maintenance tools through the OAC front-end seem resilient and work effectively, I think some aspects will be difficult to use as the primary maintenance method in a production system - the ‘breadcrumb’ method afforded to dimension maintenance in particular will start to get fiddly to use with a dimension of any sort of volume. The application and cube Import (from a formatted Excel spreadsheet) facility is great - to my mind, a bit like a supercharged and easier-to-use Outline Load Utility in Hyperion Planning - and the ability to refresh the spreadsheet from a deployed cube is a good feature that shouldn’t have been taken for granted. I know Excel is regarded as the Devil’s work in some BI quarters…I personally don’t feel that way until it is being used as a database (or as some form of primary data storage)…but in this context, it is quick & easy to use, on most people’s desktops straightway, and is intuitive.

Still in the Excel corner, on the Smartview side, the addition of the Cube Designer extension (requiring Smartview to be able to consider & change the more generic aspects (not members) of the ‘cube maintenance’ spreadsheets is a nice touch that makes this more straightforward and removes the need to pay strict attention to the spreadsheet layout. The ‘treeview’ style hierarchy viewer also helps make sense of the parent-child members that need to be detailed on the individual dimension tabs.

One issue that has flitted across my mind at this early stage is that of rules files. Whilst the Import facility creates these for you (as with creating a cube from Essbase Studio) which is welcome, and rules files created in an on-prem system can be uploaded (again, welcome), the on-board rules file editor is text based:

I’m not too sure how many people have created or edited rules files like this before (although I’d hazard a guess), but whilst the presence of any means to create, amend, or even tweak a file is good, it remains to be seen how usable this approach is. The alternative is to resubmit from the maintenance spreadsheet thus getting it created / amended for you or to maintain in on-prem system…but seeing as this platform is an alternative to (rather than an augmentation of) on prem for a lot of people, I’m not sure how practical this is.

Whilst the existing tools look really promising, I can’t help but think there will be occasions going forwards where it might be advantageous to be able to create a rules file to run an uploaded file outside of them: time will tell.

The Command Line Tool (downloadable from OAC-Essbase / Utilities) is a little limited at the moment, but goes some way towards filling the potential gap left by the absence of client-side EssMsh and can only grow with further releases: from the Oracle OAC documentation...

In conclusion, first impressions are very favourable. There are changes (eg Security), new features (eg Sandboxing), and I am sure there will be gaps for those considering moving from existing on-prem applications - for example, as I have seen someone else reference, there does not seem to be any reference to partitions in the front end or the import spreadsheet layout - so whilst there is a lot with which we will quite quickly feel familiar, there are also going to be new areas and new practices for us to get into step with: as above, we will look to explore some of these in future posts.

Categories: BI & Warehousing

12c MultiTenant Posts -- 3 : Restore Individual PB

Hemant K Chitale - Wed, 2017-06-07 05:36
Restoring the single PDB in a Container Database.

$rman target /

Recovery Manager: Release - Production on Wed Jun 7 06:34:47 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL12C (DBID=768045447)

RMAN> restore pluggable database newpdb;

Starting restore at 07-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00016 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
channel ORA_DISK_1: restoring datafile 00018 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
channel ORA_DISK_1: restoring datafile 00019 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp tag=TAG20170605T061347
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 07-JUN-17

RMAN> recover pluggable database newpdb;

Starting recover at 07-JUN-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_06/o1_mf_1_16_dmgyksjy_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_17_dmhj6nbk_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp tag=TAG20170605T061438
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc RECID=4 STAMP=946017330
media recovery complete, elapsed time: 00:00:11
Finished recover at 07-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed


I had connected to the root to restore the PDB.

Categories: DBA Blogs

Where Can I Find the Latest Patch Wizard Updates for EBS 12.2?

Steven Chan - Wed, 2017-06-07 02:00

Patch Wizard helps you identify useful or critical missing patches for your EBS environment.

This tool is updated as needed, with patches that are cumulative: they include new updates as well as all fixes released previously.  The latest updates are always documented here:

The last updates to the EBS 12.2 Patch Wizard were released in September 2016 via this patch:

This patch fixed the following issues:

  • 19412224 - FLAGGED FILE IMPORTS HANG AFTER 18598941
  • 20318695 - ADPAENGB.PLS 120.13.12020000.8 CAUSES ORA-06512 IN EBS R12.2.3

Related Articles

Categories: APPS Blogs

Schedule a job to migrate the data from SQL Server to Oracle

Tom Kyte - Tue, 2017-06-06 15:46
Hello Experts, Is there any way with which I can schedule a job to migrate a data from MS SQL server to Oracle database on daily basis? Please suggest. Thanks, Ankit
Categories: DBA Blogs

Asynchronous Global Indexing does not work as expected in 12c in a Database upgraded from 11g to 12c

Tom Kyte - Tue, 2017-06-06 15:46
We observed following. 1) 11g database is upgraded to 12c. Set the COMPATIBLE parameter to 12c version. Restart the DB. Created a table, partitioned the table and insert data into that table. Then dropped the partition. This works as expected in ...
Categories: DBA Blogs

Timestamp with ff3 and ff7 issue

Tom Kyte - Tue, 2017-06-06 15:46
I have string field '2017/06/02 17:51:12.0000000' which get converted into timestamp with formatting 'YYYY/MM/DD HH24:MI:SS.FF3' correctly in 11g I am having issue of "ora-01830: date format picture ends before converting entire input string" on ...
Categories: DBA Blogs

Adding Dinamic Nulls to perform Union

Tom Kyte - Tue, 2017-06-06 15:46
Hello Sir, it's the first time I write to you! I've the necessity of listing per table the age of data (oldest data thet we have in the table). i'm trying to generate a dinamic sqls for each table of the schema like: <code>SELECT'ALL_OBJECT...
Categories: DBA Blogs

excel2collection functionality of ORDS will not work with APEX 5.1 or later

Joel Kallman - Tue, 2017-06-06 11:43
If you're using the functionality of Oracle REST Data Services (ORDS) with Oracle Application Express to parse a Microsoft Excel file and store the parsed results in an APEX collection, I have good news and bad news.

Bad News:  This functionality will no longer work in Application Express 5.1 or later.  Due to architectural changes in the way that pages are submitted in APEX 5.1, this functionality was unfortunately (but necessarily) broken with no practical way to restore it.  And due to other necessary architectural changes, the bundled library which does the Excel parsing in ORDS is being removed in a future version of ORDS.

Good News:  Some customers have reported that they have successfully replaced this functionality using Anton Scheffer's EXCEL2COLLECTIONS plug-in.

We take very seriously our desire to always preserve existing functionality for customers, and to try to always avoid any change in user interface or functionality upon upgrade.  But in this case, it was simply not practical.

Thanks to Denes Kubicek for prominently raising this issue on the OTN discussion forum.

Webcast: Standards-based Desktop Integration in Oracle E-Business Suite

Steven Chan - Tue, 2017-06-06 02:00

Oracle University has a large number of free recorded webcasts for Oracle E-Business Suite.  Here's a useful one on Web Applications Desktop Integrator (Web ADI) and Oracle Report Manager:

Padmabrabodh Ambale covers the latest standards support in Oracle Web Applications Desktop Integrator and Oracle Report Manager. The session includes new features in Release 12.2 and other proposed design changes that result in vastly improved performance and spreadsheet experience. In addition, it offers information on how you can use Desktop Integration Framework to build your own custom desktop integrations between Oracle E-Business Suite and Microsoft Excel for enhanced end user productivity for mass upload/download of spreadsheet data. This material was presented at Oracle OpenWorld 2015.

Related Articles


Categories: APPS Blogs

Solaris x86 cold Backup opened on Solaris Sparc with all DB versions same.

Tom Kyte - Mon, 2017-06-05 21:26
I am trying to minimize downtime for the move of a physical standby server to a new location across country and was hoping to set up an x86, Virtual server and storage at the receiving location as another standby database and then just physi...
Categories: DBA Blogs

How to parse an array to update a table of records

Tom Kyte - Mon, 2017-06-05 21:26
I am going to receive an array of account numbers from a .NET program, together with another array of statuses (1 for each account number) and I need to parse these arrays and update the status of those accounts in a table on the database: Eg: ...
Categories: DBA Blogs

Parallel union all, rollup, cube degradation while insert (

Tom Kyte - Mon, 2017-06-05 21:26
the problem is that query like this:(table creation script at the end) <code> insert /*+no_append parallel(8)*/ into tmp_px0 select count(distinct rn) from tmp_px1 union all select count(distinct rn) from tmp_px2 union all select ...
Categories: DBA Blogs

Global Temporary Table - Commit issue over database link

Tom Kyte - Mon, 2017-06-05 21:26
<code>Below SQL gives 696 rows which I am processing in BULK collect 300 SELECT ROWID FROM TB_EMPLOYEE_TEST WHERE DT <= TO_DATE('01/01/2008', 'MM/DD/YYYY'); Global temporary table is created with ON COMMIT DELETE ROWS; still data in global tab...
Categories: DBA Blogs

How to create dynamic table type and variable to insert bulk data

Tom Kyte - Mon, 2017-06-05 21:26
Hi Tom, I have a scenario in which I need to pass the table name as input variable and create a table type based on that table and insert the data into input table using bulk collect dynamically.I have tried creating the table type and insertion ...
Categories: DBA Blogs

Business Intelligence for the Finance Industry

Nilesh Jethwa - Mon, 2017-06-05 15:20

The banking and finance industry is experiencing continuous changes brought about by advances in technology. The changes are welcome – however not without the accompanying challenges.

As a result, the kind of market we now have is dynamic and Wall Street is making the necessary adjustments, not to dampen vibrancy in the market, but to improve strategies in line with the changing technology. That requires developing new tools and implementing them correctly.

Business Intelligence (BI) Tools for Financial Institutions

There are a lot of tools that have been developed and ones that are worth discussing about are business intelligence tools that help managers identify better management practices and make smarter business decisions. A finance management dashboard is one of these tools.

Here’s a quick look on how this kind of instruments can help in those areas and in other areas such as profitability, reduction of risks and creating competitive advantage.

  • Operational Efficiencies

Improving efficiency is a key to winning in the competition. BI tools can analyze how efficient are the operational processes leading to maximization of resources and expertise and to reduction of operational cost.

How good are you at customer experience? A dashboard can help you improve this at the point where your employees and customers come face to face.

  • Products and Services

BI tools do not only allow managers to track which products and services are performing well but also give them insights on the needs and experience of their customers.

  • Marketing

Do you want to know the best market segment to target? A simple dashboard is all you need to have a good picture of it. The tool can also show you which customer profile gives you the highest revenue and the one that should be put lowest in the list of priorities.

  • Customer Retention

There are customers who are loyal to you. You need to know the reasons why they are staying with your business, but you also need to identify why others are leaving.

Business analytic tools can help you determine the reasons why and lead you to taking steps to improve your retention rates.

  • Risk Reduction

This is an ever-changing world where nothing is 100% certain. How would you keep track of your customers and internal organizations so that you can early detect possibilities of fraud?

You can use BI tools for this. There are also lots of benefits from knowing your clients’ financial capacities, which a dashboard can provide you 24/7.

  • Investment

At some points, managers run out of invest ideas and strategies. The good thing about BI tools is that they can bring in “new” results from real-time data.

New results mean new things that can be explored further for better decision making and development of new approaches to investing. That’s the kind of data and analysis only a dashboard can bring to your organization.

As an example, the number of cars on a shopping mall’s parking lot can be useful in triangulating consumer research. Recently, images from satellites are used to gather data of the global supply of fuel.

These and more examples clearly show how indispensable BI tools can be in the conduct of modern business.

Read more at http://www.infocaptor.com/dashboard/business-intelligence-for-the-finance-industry


Subscribe to Oracle FAQ aggregator