Feed aggregator

Update a rows in a table with data from other table with only table name and primary key.

Tom Kyte - Tue, 2017-09-12 09:46
I'm trying to restore a data from a backup data with some special condition for rows. Example: Today database in DB1: <code>City ID Name location type 1 A [SDO.geom] 1 2 B [SDO.geom] 2 District ID Name At...
Categories: DBA Blogs

ORA-24263: Certificate of the remote server does not match the target address.

Tom Kyte - Tue, 2017-09-12 09:46
I encountered ORA-24263 when migrating from Oracle Database 11g to This error is thrown when using UTL_HTTP to access a site via HTTPS, and that site is a multi-server domain (as is common in today's cloud based world). I no longer have...
Categories: DBA Blogs

PL/SQL - Array collection of Records

Tom Kyte - Tue, 2017-09-12 09:46
Hi Tom, I am a regular visitor of your site and I love it. Every time I visit this site, I learn new things. Here is my problem. I tried using a collection(PL/SQL Tables) of Records in Oracle 8i but later came to know that ...
Categories: DBA Blogs

Pro*C procedure call taking longer time

Tom Kyte - Tue, 2017-09-12 09:46
I have below code in pro*c EXEC SQL CALL SP_INTL_BATCH(:l_wrapper, :l_func, :p_env_data, :l_paramstr, :l_user,:l_error); This simple code taking 12 to 13 second to execute and move to next line , while if i normally execute store procedure from...
Categories: DBA Blogs

DB Link Intermittent Failures

Tom Kyte - Tue, 2017-09-12 09:46
Hi Tom We have a DB Link to a database run by an external institution. Some days we can run our import/export processes just fine through the DB Link to the external database. On other days it will go through periods where it just seems to cut...
Categories: DBA Blogs

#GoldenGate Microservices (1 of 5) … ServiceManager

DBASolved - Mon, 2017-09-11 14:30

This is post 1 of a 5 part post related to Oracle GoldenGate 12.3 Microservices. This series of posts will provide some details over the new graphical user interface (GUI) that has been built into Oracle GoldenGate 12.3.

The first of the Oracle GoldenGate Microservices that you need to understand is that ServiceManager. This service is main interface into the Oracle GoldenGate environment. From the HTML5 page that is provided with ServiceManager, you can quickly get an overview of what is running on the server that Oracle GoldenGate is running on. Additonally the ServiceManager acts as the watch dog process for the environment. This means that if a service was to go down, it can be restarted via the ServiceManager.

The ServiceManager can be configured in three different modes. These modes range from manual to Real Application Cluster (RAC) aware. These modes are:

  1. Manual
  2. Deamon
  3. XAG

These configuration options are selectable during the inital run of Oracel GoldenGate Configuration Assistant (oggca.sh) (Figure 1). If you wanted to use it manually, you woudn’t select anything on this screen. If you want to run it on a single server, than the deamon option is a good choice. For the XAG option, you need to make sure you have the current XAG agent (here).

Figure 1:

After ServiceManage is installed, then you will be able to access the interface via a URL (Figure 2). In the example, you will see that I’m usign port 16000. This is not the default port, but a port that I had configured during the installation using Oracle GoldenGate Configuration Assistant.

Figure 2:

Once the ServerManager page is up, then you just need to login with the administrator account you created during installation of ServiceManager. Keep in mind, if you do not know the userid or password, the consult the people who initally configured the ServiceManager.

After logging in, you are taken directly to the overview page. This page has a few items of importance for the Oracle GoldenGate environment. First you will notice on the left hand side of the page, is a context menu that provides a link to Overview and Diagnostics. This is basically a navigation menu that will be in every microservice (different per service). Then notice at the top of the page, a summary of processes that are running, stopped, or other Lastly, at the middle and bottom of the page, you see the services and deployments that are associated with the installation (Figure 3).

Figure 3:

If you want to know details of the ServiceManager and/or deployments, you can click on the associated deployment under Deployments. This will take you to a summary page, where you can identify items releated to the deployment and change the $OGG_HOME (more on this later).

As you can tell, we have made some great improvments to the Oracle GoldenGate product. Hopefully, this post has you excited to see what else is hiding in the product and my next couple of posts.


Filed under: Golden Gate
Categories: DBA Blogs

Oracle JET List Implementation based on WorkBetter Alta UI

Andrejus Baranovski - Mon, 2017-09-11 14:04
During weekend I was playing with JET WorkBetter sample app. I thought it would be nice to extract People list UI into my own app - so that it will be easy to reuse this UI pattern in our own JET applications. I succeeded to achieve my goal and would like to share WorkBetter Alta UI list sample with you.

This is how my own JET list UI looks like, based on sample app:

Header block is implemented in separate div, this contains title and toolbar for buttons. List content is rendered out of template located in its own div:

Template renders JET list component. List in turn is based based on item template:

Item template defines list row content and structure - through set of JET and custom CSS styles. Data attributes are referenced directly from template structure:

For this sample I'm using basic set of mock data defined in JS module, but obviously it can fetch data from REST service too:

Set of custom styles (created based on WorkBetter example) is located in override.css file within JET application structure:

Sample JET list application can be downloaded from GitHub - jetlistapp.

To build this application, I was using JET CLI commands. Especially useful I found options to strip application leaving only sources and then to restore JET environment - this helps to share code.

Here is the sequence of commands how I was using JET CLI:

ojet create jetlistapp --template=navdrawer (creates JET application)

ojet serve (runs JET application)

ojet strip (removes all files except your sources)

ojet restore (brings back JET environment, after application was stripped)

ojet serve (you can run application again, after it was restored)

Benefit of Dashboard Reporting for Government Operations

Nilesh Jethwa - Mon, 2017-09-11 12:45

Dashboard reporting has never been more important to the government. Considering the humungous streams of data collected through government agencies and programs, the challenge is how to make sense of these data in a way that they will benefit the organization and perhaps save large amounts of money as a result. The use of this type of reporting has been exalted by the US.

In the US

The US administration flaunted the advantages of using dashboards during the presidency of Barack Obama. They said that using a dashboard is an effective way of collating and filtering data so that these data become useful to decision makers. They credited the dashboard for being able to make them see better the implications of data and act on them appropriately.

One example cited by the government is how a dashboard helped them see the status of their investments, particular in pursuing the advance of technology.

The claim from the administration of Obama was that they were able to make better decisions using the data presented by the dashboard. According to them, this led to billions of dollars in savings and cutting delivery time for technological projects.

The US Congress has communicated a positive sentiment on the matter of using a government dashboard for federal decision-making and operations. It expressed interest in increasing the use of transparency tools, such as dashboards, across federal spending programs.

It is expected that more efforts will be exerted towards this goal and the use of dashboards may even be continued to be implemented after Obama relinquishes his office.

Other governments

Read more at http://www.infocaptor.com/dashboard/dashboard-reporting-for-government-and-how-it-can-benefit-government-operations

Sending Emails from Visual Builder Cloud Service

Shay Shmeltzer - Mon, 2017-09-11 12:23

Sending emails as a result of some changes to data is a common requirement we've been hearing from customers of Oracle Visual Builder Cloud Service (VBCS). In previous versions we already added a function that allows you to fire up the client email software from your app. In the new version we rolled out a couple of weeks ago we added a new function that can manage the whole email processing inside VBCS - on the backend/server/cloud side.

When you define a new trigger on a business object you now have the option to add "send email notification" step to your logic flow. 

When you are defining this, you'll be able to pick or define a template for your email. The email template can have parameters to increase reusability. These parameters can then have their value set from expressions that can include values of fields from your objects.

Here is a complete demo video that shows you how to add an email notification to an event on your business object:

Categories: Development

Events to round out 2017 … where I’ll be!

DBASolved - Mon, 2017-09-11 08:23

The first half of 2017 started off with me joining Oracle and getting the first looks at the new Microservices Architecture for Oracle GoldenGate. So where does this leave me for the second half of 2017?

As some of you may know, I’m currently working on a Oracle GoldenGate 12.3 roadshow; where I’m explaining the new features in 12.3 and giving attendees hands-on to the new product. After two stops on the roadshow, so far, we have seen so much excitement for this new architecture. It is going to change the way we, as a community, replicate data both on-premise, in the cloud, and in hybrid architecture.

Check out the new Microservice Architecture by downloading it from OTN (here).

As we continue the roadshow, we will be hitting a few more cities in the US before moving on to EMEA in November. Once we get to EMEA, there will be some cool places we plan on holding events and/or meeting customers. The excitement for Oracle GoldenGate 12.3 continues to grow as we attend more cities.

If you want to know the cities in the US, please see this blog post (here).

On top of the US and EMEA legs of the Oracle GoldenGate 12.3 roadshow, I will be attending two conferences in EMEA. I’m sure you can guess at which two … DOAG and UKOUG. This will my first time at both events and I hope I get to see a lot of great friends and make many new ones.

At DOAG, I will be speaking, on November 22, 2017 @ 08:00, about Oracle GoldenGate Peformance Tuning. Additionally, I will have another product manager in tow (hopefully) so attendees will have direct access to the PMs who are responsible for the core Oracle GoldenGate product.

At UKOUG, I will be speaking, need to confirm date, about the Conflict, Detection and Resolution (CDR) in Oracle GoldenGate 12.3 Both the manual process and the new Automatic CDR. Then if needed, by the UKOUG board, I may have another Oracle GoldenGate 12.3 presentation.

All in all, 2017 is rounding out to be a great first year at Oracle along with many first on top of that. Looking forward to seeing everyone on the roadshow!


Filed under: Golden Gate
Categories: DBA Blogs

Few Steps to use EMC – SAN (LUNs) in Oracle Solaris – Part 02 Enable Device Multipathing

Online Apps DBA - Mon, 2017-09-11 06:24

Note: Enabling Device Multipathing requires a reboot. First, In order to enable Multipathing on OS level, Check Multi Path Utility package is installed. If Not, You need to install it. # pkg install system/storage/multipath-utilities Enable Multipathing Click Here to Continue : Few Steps to use EMC - SAN (LUNs) in Oracle Solaris - Part 03 […]

The post Few Steps to use EMC – SAN (LUNs) in Oracle Solaris – Part 02 Enable Device Multipathing appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Few Steps to use EMC – SAN (LUNs) in Oracle Solaris – Part 01 Unisphere Host Agent?

Online Apps DBA - Mon, 2017-09-11 05:59

Step # 1 : Installation of Unisphere Host Agent. Download and Install the Package, as per OS Version e.g. HostAgent-Solaris-32-sparc-en_US- # cd /var/share/pkg/repositories # pkgadd -d ./HostAgent-Solaris-32-sparc-en_US- Make sure that the required package is installed successfully. Step # 2: Add the VNX Storage Servers in the /etc/hosts file. Step # 3: Add the environment specific […]

The post Few Steps to use EMC – SAN (LUNs) in Oracle Solaris – Part 01 Unisphere Host Agent? appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

fetch the latest total value

Tom Kyte - Sun, 2017-09-10 21:06
Hi, We want to fetch the latest total value for emp id = 2 but its summing total_value for all the rows for emp_id = 2. we want the result(total_value) only for the latest row of emp_id = 2. the statements and query are shown as below, cre...
Categories: DBA Blogs

Creation of Composite sub-partitions on multiple columns using template

Tom Kyte - Sun, 2017-09-10 21:06
Hi, Can we create Composite sub-partitions on multiple columns using template Example CREATE TABLE sgs.part_test ( order_date_time SYSTIMESTAMP, modulo_store_id NUMBER, recurring_flag CHAR (1) ) PARTITION BY RANGE (orde...
Categories: DBA Blogs

Why is there a limit on DBMS_OUTPUT?

Tom Kyte - Sun, 2017-09-10 21:06
I always run into an error that the dbms output exceeds 2000 and I can increase to max of 1000000. My question is, why is there a limit on this? I have gigs of space, why does oracle have to be so stingy with the output log?
Categories: DBA Blogs

12c Access Control Lists

Yann Neuhaus - Sun, 2017-09-10 14:39

There is already enough information about the new simplified 12c way to define Access Control Lists, such as in oracle-base.
I’m just posting my example here to show how it is easy.

If, as a non-SYS user you want to connect to a host with TCP, you get an error:

SQL> connect DEMO1/demo@//localhost/PDB1
SQL> declare
2 c utl_tcp.connection;
3 n number:=0;
4 begin
5 c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23);
6 end;
7 /
Error starting at line : 27 File @ /media/sf_share/122/blogs/12cacl.sql
In command -
c utl_tcp.connection;
n number:=0;
Error report -
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 284
ORA-06512: at line 5
24247. 00000 - "network access denied by access control list (ACL)"
*Cause: No access control list (ACL) has been assigned to the target
host or the privilege necessary to access the target host has not
been granted to the user in the access control list.
*Action: Ensure that an access control list (ACL) has been assigned to
the target host and the privilege necessary to access the target
host has been granted to the user.

Here are the ACLs defined by default:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
SQL> select * from dba_host_acls;
---- ---------- ---------- --- ----- ---------
* NETWORK_ACL_4700D2108291557EE05387E5E50A8899 0000000080002724 SYS
SQL> select * from dba_host_aces;
---- ---------- ---------- --------- ---------- -------- ---------- ------------------ --------- -------------- ---------

So, I add an ACL to access to towel.blinkenlights.nl on telnet port (23) for my user DEMO1:

SQL> exec dbms_network_acl_admin.append_host_ace(host=>'towel.blinkenlights.nl',lower_port=>23,upper_port=>23,ace=>xs$ace_type(privilege_list =>xs$name_list('connect'),principal_name=>'DEMO1',principal_type =>xs_acl.ptype_db));
PL/SQL procedure successfully completed.
SQL> select * from dba_host_acls;
---- ---------- ---------- --- ----- ---------
towel.blinkenlights.nl 23 23 NETWORK_ACL_5876ADC67B6635CEE053684EA8C0F378 000000008000281F SYS
* NETWORK_ACL_4700D2108291557EE05387E5E50A8899 0000000080002724 SYS
SQL> select * from dba_host_aces;
---- ---------- ---------- --------- ---------- -------- ---------- ------------------ --------- -------------- ---------
towel.blinkenlights.nl 23 23 1 GRANT NO DEMO1 DATABASE CONNECT

Now I can connect from my user:

SQL> connect DEMO1/demo@//localhost/PDB1
SQL> declare
2 c utl_tcp.connection;
3 n number:=0;
4 begin
5 c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23);
6 end;
7 /
PL/SQL procedure successfully completed.

If you don’t know why I used towel.blinkenlights.nl, then just try to telnet to it and have fun…


Cet article 12c Access Control Lists est apparu en premier sur Blog dbi services.

Thoughts on RMAN Backup Strategy

Michael Dinh - Sun, 2017-09-10 12:56

Do you label your backupset to provide meaningful name?


For Level 0 backup, do you perform database backup separately from archivelog and tag them differently, e.g. LEVEL0, ARCHIVELOG?

How do you tag your backup?

Do you backup controlfile to trace or backup init parameter or backup password file?

Just some thoughts after have been through hell with restore and lucky came out of hell.

Of course, there are pros and cons to everything.
With too much customization, cannot simply use RMAN configure to set it and forget it.

FYI: 12c syntax for SQL

connect target;
set echo on 
set command id to "LEVEL0";
show all;
run {
set controlfile autobackup format for device type disk to '/oradata/backup/%d_%I_%F.ctl';
allocate channel d1 device type disk format '/oradata/backup/%d_%I_%T_%U.LEVEL0' maxopenfiles 1;
backup as compressed backupset 
incremental level 0 check logical database 
#filesperset 1 tag="LEVEL0"
include current controlfile
plus archivelog not backed up 1 times
#filesperset 8 tag="LEVEL0"
alter database backup controlfile to trace as '/oradata/backup/cf_@.sql' REUSE RESETLOGS;
create pfile='/oradata/backup/init@.ora' from spfile;
create pfile from spfile;
report schema;
list backup summary;


Reporting Tools and Dashboard for Operations Management

Nilesh Jethwa - Sun, 2017-09-10 12:44

As an operations manager, you play the role of an overseer. You are monitoring the day-to-day operations of your store or business, keeping track of factory efficiency, optimizing logistics and safety. You are practically keeping watch of everything that happens in the business and you find yourself just everywhere.

Being a highly functional entity in your business requires that you have a continuous feed of important information about your business. You need data to tell you everything is running smoothly, or tell you something is not going well. This is where a dashboard can help.

What can a dashboard give you?

With the use of reporting tools, such as dashboards, you can create a data reporting system that can help you have an overall view of your operations whenever you need it.

With a dashboard, you can drill down into important details and identify issues before they spin out of control. Once issues have been identified, you can quickly alert stakeholders about them and help them determine necessary measures to take.

Most dashboards systems are customizable and can be operated through a self-service platform. You can design yours in such a way that you can combine multiple data from different sources and look for correlations among data to help you come up with better decisions for your business.

Operational Dashboards

There are three types of dashboards: strategic, operational and analytic dashboards. The kind of dashboard for people running business’s daily operations is the operational type.

Whereas strategic dashboards are used to monitor key performance indicators and analytic dashboards are used for analyzing huge volumes of data to identify trends and predict outcomes, operational dashboards are used to monitor day-to-day processes.

These processes obviously change minute by minute and the dashboard is designed to capture KPIs and metrics associated with these processes. You expect this type of dashboard to be able to give you very frequent updates. Used to monitor short-time progress towards identified goals, dashboards like this are engineered in such a way that they can be viewed several times during the day giving users different data for each view.


Read more at http://www.infocaptor.com/dashboard/reporting-tools-and-dashboard-for-operations-management

Partner Webcast - Oracle Identity Management: Journey to the Cloud

From large organizations to small organizations, a journey to using more cloud-based applications is under way. Oracle extends its leadership in IAM by introducing the next generation...

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

Hey Mum, I am a Citizen Data Scientist with Oracle Data Visualization Cloud (and you can be one too)

Amis Blog - Sun, 2017-09-10 03:42

One of the Oracle Public Cloud Services I have seen mouthwatering demos with but have not actually tried out myself is Oracle Data Visualization Cloud. I had several triggers to at last give it a try – and I am glad I did. In this article a brief report of my first experiences with this cloud service that aims to provide the business user – aka citizen data scientist – with the means that explore data and come up with insights and meaningful visualizations that can easily be shared across the team, department or enterprise.

I got myself a 30-day trial to the cloud service, uploaded a simple Excel document with a publicly available dataset on the countries of the world and started to play around. It turned out to be quite simple – and a lot of fun – to come up with some interesting findings and visualizations. No technical skills required – certainly not any beyond an average Excel user.


  • get a trial account – it took about 5 hours from my initial request for a trial to the moment the trial was confirmed and the service had been provisioned
  • enter the Data Viz CS and add a new Data Source (from the Excel file with country data)
  • do a little data preparation (assign attributes to be used as measures) on the data source
  • create a new project; try out Smart Insights for initial data exploration, to get a feel for attributes
  • create some visualizations – get a feel for the data and for what DVCS can do ; many different types of visualizations, various options for filtering, embellishing, highlighting; many dimensions to be included in a visualization
  • try a narrative – a dossier with multiple visualizations, to tell my story

In a few hours, you get a very good feel for what can be done.


Create Countries as my new data source

First steps: download countries.csv from https://www.laenderdaten.info/downloads/  . Create Excel workbook from this data. Note: I first tried to upload the raw csv file format, but that ended with an internal error.




The data that was imported is shown. Now is a good moment to set the record straight – any meta data defined at this point is inherited in projects and visualizations that use this data. For example: if we want to calculate with attributes and use them as values – for the size of bubbles and stacks and to plot a line – we have to identify those attributes as measures.


Once the data source is set up – we can create our first project based on that data source by simply clicking on it. Note: the PROPOSED_ACTS and ACT_ALBUMS data sources are based on database table in an Oracle DBaaS instance to which I have first created a connection (simply with host, port, service name and username & password).



My First Project – Data Preparation & Initial Exploration

Here is the Data Preparation page in the project. We can review the data, see what is there, modify the definition of attributes, prescribe a certain treatment (conversion) of data, add (derived) attributes etc.


If we click on the Visuals icon in the upper right hand corner, we get a first stab at visualization of some of the data in this data source. Out of the box – just based on how DVCS interprets the data and the various attributes:


For example the number of countries per continent. Note how we can select different measures from the dropdownlist – for example area:


This tells us that Asia is the largest continent in landmass, followed by Africa, Russia is the largest country and the size of all countries using a currency called Dollar put together is the largest, with Rubel using countries (probably just one) as a runner up. Note: all of this – out of the box. I am 10 minutes into my exploration of DVCS!


First Visualizations – Let’s try a Few Things

Go to the second tab – Visualize.

Drag an attribute – or multiple attributes – to the canvas.



The default visualization is a pivot table that presents the data for the selected attribute. Drag two more attributes to the canvas:



The result is a matrix of data – with the measure (area) in the cells:


In order to prepare the visualization for presentation and sharing, we can do several things – such as removing columns or rows of data that is not relevant, setting a color to highlight a cell:



Define range filters on selected attributes – for example filter on countries with at least a 45M population or



When the filter has been set, the matrix adapts:


We can try out different styles of visualization – what about a map?



DVCS recognizes the names of continents and countries as geographical indications and can represent them on a map, using color for total area. Let’s remove continent from the Category dimensions, and let’s set bubble size for population:


If we are interested in population density, we can add a calculated value:


Some more examples:

Select countries by size per continent – in a horizontal stack chart:


and a treemap – with population added in as additional attribute represented through color:



Any visualization we like and want to include in our final narrative can be saved as an insight:


On the Narrate tab – we can include these insights in a meaningful order to tell our story through the data visualizations. Also see Building Stories.



Oracle Data Visualization Cloud Service: https://cloud.oracle.com/en_US/data-visualization  (at $75.00 / Named User / Month with a minimum of 5 users a fairly friendly priced offering)

The country data is downloaded from https://www.laenderdaten.info/downloads/

Documentation on Oracle Data Visualization Cloud Service: http://docs.oracle.com/en/cloud/paas/data-visualization-cloud/bidvc/getting-started-oracle-data-visualization.html 

Documentation on Building Stories: http://docs.oracle.com/en/cloud/paas/data-visualization-cloud/bidvc/building-stories.html#GUID-9D6282AA-C7B7-4F7E-9B9E-873EF8F1FB5D

The post Hey Mum, I am a Citizen Data Scientist with Oracle Data Visualization Cloud (and you can be one too) appeared first on AMIS Oracle and Java Blog.


Subscribe to Oracle FAQ aggregator