Feed aggregator

How true is the DBMS_UTILITY.GET_TIME function

Tom Kyte - Wed, 2017-06-28 19:26
I was trying to verify how true would be the values returned by the function DBMS_UTILITY.GET_TIME using the below code. <code>DECLARE curr_time1 DATE := SYSDATE; curr_time2 NUMBER := dbms_utility.get_time; BEGIN LOOP EXIT WHEN SYSD...
Categories: DBA Blogs

Oracle GoldenGate Cloud Service

Pakistan's First Oracle Blog - Wed, 2017-06-28 18:37
Even on Amazon AWS, for the migration of Oracle databases from on-prem to Cloud, my tool of choice is GoldenGate. The general steps I took for this migration was to create extract on source in on-prem, which sent data to replicat running in AWS Cloud in EC2 server, which in turn applied data to cloud database in RDS.

I was intrigued to see this new product from Oracle which is Oracle GoldenGate Cloud Service (GGCS).

So in this GGCS, we have extract, extract trail, and data pump running in the on-prem, which sends data to a Replication VM node in Oracle Cloud. This Replication VM node has a process called as Collector which collects incoming data from the on-prem. Collector then writes this data to a trail file from which data is consumed by a Replicat process and then applied to the cloud database.

This product looks good as it leverages existing robust technologies and should become default way to migrate or replicate data between oracle databases between on-prem and cloud.
Categories: DBA Blogs

ODA X6 installation: re-image

Yann Neuhaus - Wed, 2017-06-28 15:44

The Oracle Database Appliance is shipped with a bare-metal installation which may not be the latest version. You may want to have it virtualized, or get the latest version to avoid further upgrade, or install an earlier version to be in the same configuration as another ODA already in production. The easiest for all cases is to start with a re-image as soon as the ODA is plugged. This post is not a documentation, just a quick cheat sheet.

I don’t want to spend hours in the data center, so the first step, once the ODA is racked, cabled and plugged, is to get it accessible from the management network. Then all tasks can be done from a laptop, accessing the ILOM interface through a browser (Java required, and preferably 32-bits) before the public network is setup.


Here is the back of the server where you find the management network port.
This was an X5-2 but very similar to X6-2. You can look at the 3D view of X6-2 to get a better view.
There is also VGA and USB to plug a monitor and keyboard just for the time to setup the management network.

You can also use the serial port which is just next to it but I usually don’t have a serial adapter for my laptop.

First checks

You can plug a keyboard and monitor and log on the server: root password is welcome1 (no need to change it for the moment as we will re-image the machine)

In ODA X6-HA you have two nodes, numbered 0 and 1 and called oak1 and oak2 … be careful. You may wonder which server is node 0 and which one is node 1, because the servers are the same. Yes, that’s true. The nodes are identified from the storage port they are connected to. The node 0 is the one connected to the blue mini-SAS and the red ones are for node 1.

  • Blue cable – Node 0 – oak1
  • Red cable – Node 0 – oak2

Here I’m on node 0 and check hardware version

[root@oak1 ~]# /opt/oracle/oak/bin/oakcli show env_hw

This means Bare Metal X6-2 HA, a virtualized one would show VM-ODA_BASE ODA X6-2, and ODA X6-2S would show BM ODA_Lite X6-2 Small.

From storage topology, we confirm that this node has been recognized as node 0:

[root@oak1 ~]# /opt/oracle/oak/bin/oakcli validate -c StorageTopology
It may take a while. Please wait...
INFO : ODA Topology Verification
INFO : Running on Node0
INFO : Check hardware type
SUCCESS : Type of hardware found : X5-2
INFO : Check for Environment (Bare Metal or Virtual Machine)

And finally check the version because you may be lucky to get the version you want and then don’t need to re-image (I switched to node 1 here):


Of course, you don’t need to re-image when you want an higher version. You can upgrade it, but re-image is simple.

BMC Network

The management network interface can get an address from DHCP. But who runs DHCP on the management network? There are two ways to assign a static IP to the management network.

You may use the IPMI Tool commands:
ipmitool -I open sunoem cli
cd SP
cd Network
set pendingipadress=
set pendingnetmask=
set pendingipgateway=
set commitpending=true

Here is an example:

Or you can go to BIOS. It goes fast but filming in slow motion shows that the key is F2:
Once you are in the BIOS interface, go to Advanced, then choose ‘BMC Network configuration’ to configure IPMI and enter the following BMC Lan information:
IPv4 IP Assignment [Static] IPv4 address
IPv4 Subnet Mask
IPv4 Default Gateway

Once you are in the BIOS you can choose to boot on the CD-ROM first because I sometimes have problems to set that from the ILOM Web interface.

Once you have validated that the ILOM IP address can be reached from your office, you can remove the keyboard and monitor and leave the datacenter. the hardware is ok. Now the software can be deployed from the ILOM Web interface. The password to connect to ILOM from the Web browser is ‘changeme’ and you should change it.


The latest ISO image for Bare Metal or Virtualized ODA can be found from Note 88888.1
You can also find the oldest versions:

From the ILOM you you get to the console of node 0:


From there, you can attach the ISO image: ILOM Remote Control / Devices / CD-ROM Images

and then reboot on the CD-ROM:

Reboot (Host Control / Next Boot Device / CD-ROM )


Do that for both nodes (you can run them at the same time) and prepare the information for the deployment and download the ‘End-User RDBMS Clone files’ for the database version you want.

You can download the Oracle Appliance Manager Configurator and take your time to setup and verify the configuration.

configure firstnet

Here is part of the information to prepare. First, you will configure the network to be able to scp the software (Grid Infrastructure and Database):

[root@oak1 ~]# /opt/oracle/oak/bin/oakcli configure firstnet
Configure the network for node(s) (local,global) [global]:
The network configuration for both nodes:
hostname: Hostname lookup failure
Domain Name: pachot.net
DNS Servers: Primary DNS Server:
Node Name Host Name
0 myserver1
1 myserver2
Choose the network interface to configure (net1,net2) [net1]:
Enter the IP address for net1 on Node 0:
Enter the IP address for net1 on Node 1:
Netmask for net1:
Gateway address for net1 []:

Note that this is a funny example. I hope you don’t use the 192.168.16/24 as you public network because this is used for the private interconnect where IP addresses and are hardcoded. But thanks to that the configure-network can be run fron one node only.


Now that you have access through the public network, you can copy (scp) the Oracle Home clones and the configuration file to /tmp, unpack the .zip (for i in *.zip ; do /opt/oracle/oak/bin/oakcli unpack -package $i ; done) and run deploy (ssh -X /opt/oracle/oak/bin/oakcli deploy), loading the configuration from your file or entering all information from there. Crossing the fingers, this should go to the end without any problem. On the opposite, My Oracle Support notes may help. The nice thing with ODA is that most of the configurations are similar so there a good chances that a problem was already encountered and documented.


Cet article ODA X6 installation: re-image est apparu en premier sur Blog dbi services.

DOAG Red Stack Magazin Artikelreihe "Oracle Database Cloud Performance" (German)

Randolf Geist - Wed, 2017-06-28 11:03
In der aktuellen Ausgabe des für Mitglieder der DOAG, SOUG und AOUG kostenlosen DOAG Red Stack Magazins wurde der erste Teil meiner zweiteiligen Artikelserie "Oracle Database Cloud Performance" veröffentlicht.

Die Artikelserie basiert auf den hier bereits publizierten Erkenntnissen in diesem Bereich und führt diese weiter fort.

Der erste Teil geht auf die verschiedenen Aspekte der maximal erreichbare Performance ein (CPU, Storage etc.), der zweite Teil wird in der nächsten Ausgabe zu lesen sein und legt den Schwerpunkt auf die Konsistenz der Performance, also wie konsistent sich die Datenbanken in der Cloud in Bezug auf Performance während der Tests verhalten haben.

Oracle ERP Cloud Named Leader in 2017 Gartner Magic Quadrant for Cloud Core Financial Management Suites

Oracle Press Releases - Wed, 2017-06-28 10:00
Press Release
Oracle ERP Cloud Named Leader in 2017 Gartner Magic Quadrant for Cloud Core Financial Management Suites

Redwood Shores, Calif.—Jun 28, 2017

News Summary

Oracle today announced it has been named a Leader in Gartner’s 2017 “Magic Quadrant for Cloud Core Financial Management Suites for Midsize, Large and Global Enterprises” research report1. Out of 11 products evaluated, Oracle ERP Cloud is positioned the highest for both completeness of vision and ability to execute.

According to the report, “Leaders demonstrate a market-defining vision of how core financial management systems and processes can be supported and improved by moving them to the cloud. They couple this with a clear ability to execute this vision through products, services and go-to-market strategies. They have a strong presence in the market and are growing their revenues and market shares. In the cloud core financial management suites market, Leaders show a consistent ability to win deals in organizations of different sizes, and have a good depth of functionality across all areas of core financial management. They have multiple proof points of successful deployments by customers based in their home region and in other geographies. Their offerings are frequently used by system integrator partners to support finance transformation initiatives.”

“Transformation and innovation are top of mind for finance organizations. Customers are looking for best-in-class cloud solutions to help drive deep real time business insights, increase productivity, and lower costs,” said Rondy Ng, senior vice president, applications development at Oracle. “Oracle ERP Cloud is the only enterprise-grade solution on the market with the breadth, depth, security, scalability, and cutting-edge innovations to meet these demanding business needs. We consider our leader positioning by Gartner as validation of our product and service driven strategy and recognition of our commitment to our customers’ success.”

“When we started our finance transformation effort to modernize our systems infrastructure, we needed a flexible solution that reduced our cost profile, mandated standardized best practice processes, and accelerated our ability to uptake innovative technology,” said Matthew Trager, Head of Finance Business Architecture, Lloyds Banking Group. “Oracle ERP Cloud met these and all of our functional and security requirements, giving us the agility to react and grow in a hyper-competitive business environment.”

Oracle ERP Cloud is the industry’s most complete, modern, and proven financial platform delivered seamlessly through the Oracle Cloud. A modern user interface driven by the latest design innovations delivers embedded analytics, contextual social collaboration, and a device-independent mobile experience to make Oracle ERP Cloud familiar and easy to use.

Oracle ERP Cloud includes complete ERP capabilities across Financials, Procurement, and Project Portfolio Management (PPM), as well as Enterprise Performance Management (EPM), Governance Risk and Compliance (GRC) and Supply Chain Management (SCM). Native integration with the broader Oracle SaaS portfolio for Human Capital Management (HCM), and Customer Experience (CX) offer customers a practical, business-driven, rapid adoption path forward.

Download Gartner’s 2017 “Magic Quadrant for Cloud Core Financial Management Suites for Midsize, Large and Global Enterprises” research report”1.

For additional information on Oracle Cloud solutions for Finance, visit Oracle Enterprise Resource Planning (ERP) Cloud’s Facebook, Twitter, or the Modern Finance Leader blog.

Gartner Disclaimer
Gartner does not endorse any vendor, product or service depicted in its research publications, and does not advise technology users to select only those vendors with the highest ratings or other designation. Gartner research publications consist of the opinions of Gartner's research organization and should not be construed as statements of fact. Gartner disclaims all warranties, expressed or implied, with respect to this research, including any warranties of merchantability or fitness for a particular purpose.

1.Gartner, “Magic Quadrant for Cloud Core Financial Management Suites for Midsize, Large and Global Enterprises,” by Nigel Rayner, Robert P. Anderson, John E. Van Decker, June 2017.

Contact Info
Joann Wardrip
About Oracle

The Oracle Cloud delivers hundreds of SaaS applications and enterprise-class PaaS and IaaS services to customers in more than 195 countries and territories while processing 55 billion transactions a day. For more information about Oracle (NYSE:ORCL), please visit us at http://cloud.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Joann Wardrip

  • +1.650.607.1343

Benefits of Dashboards for IT

Nilesh Jethwa - Wed, 2017-06-28 09:54

The process of examining sets of data using specialized computer systems is called data analytics or data analysis. These computer systems make data easier to interpret by transforming, arranging, and modeling it to form patterns.

Data analytics tools guide businesspeople in coming up with better business decisions. Researchers and scientists also use these tools to evaluate scientific hypotheses, theories, and models.

Real-time report of data about the performance of a company, a specific department, a project, or group of employees can be accessed through a so-called data analytics dashboard. This is an information management tool, which monitors a group or a company’s metrics, key data points, and key performance indicators (KPI).

A dashboard can be customized according to a department or a company’s needs. It connects to your attachments, services, files, and Application Program Interface (API).

IT Management Dashboards

Some of the available dashboards for IT include the following:


Read more at http://www.infocaptor.com/dashboard/data-analytics-and-benefits-of-dashboards-for-it

gnTel Selects Oracle Communications to Scale Cloud Based Hosted Services

Oracle Press Releases - Wed, 2017-06-28 09:00
Press Release
gnTel Selects Oracle Communications to Scale Cloud Based Hosted Services gnTel deploys Oracle Communications Session Border Controller to expand their SIP trunking and hosted unified communications services and to establish a clear path for evolution to NFV

Redwood Shores, Calif.—Jun 28, 2017

gnTel, a fast growing cloud-based telecom service provider to small and medium sized businesses in the Netherlands, has selected Oracle Communications technology to better scale their operations and lay the groundwork for the evolution of their network to NFV. Replacing their existing solution, gnTel will use Oracle Communications Session Border Controller (OCSBC) running on Acme Packet 4600 purpose-built hardware platforms to immediately create a new scalable access and peering infrastructure for their customers. This solution will allow gnTel to not only jumpstart their services but because of the close interworking between physical and virtualized OCSBC instances, and a flexible licensing scheme, also preserve this investment while they migrate to NFV.

Oracle Communications Session Border Controller is one of the industry’s leading border control solution that operates with most major IP-PBX, unified communications, and application server providers - reducing risks associated with commercial production level installations. Available on both purpose-built physical and commercial-off-the-shelf virtualized platforms, and coupled with a perpetual network-wide licensing scheme, OCSBC provides maximum flexibility to operators.

gnTel also chose NetAxis Solutions, an Oracle professional services partner, with deep technology competencies and experience, to design and implement their new service infrastructure.

“At gnTel, we recognize the fast pace of technology evolution in the telecom industry. We wanted to invest in a proven solution from a company that brings together expertise in cloud, virtualization, IT, and telecommunications,” said Onno Speekenbrink, gnTel. “Oracle’s solution and NetAxis Solution’s implementation will position us well for future growth.”

“Oracle’s cloud-ready session delivery solutions coupled with their extensive interoperability with third-party equipment and with our network monitoring software makes it simple for us to size, configure, deploy, and maintain the complex multi-vendor solution that gnTel requested,” said Bart van de Kar, NetAxis. “This project will further cement our expertise and relationship with Oracle.”

“In today’s marketplace, operators are looking for long-term, cost-effective and verified solutions that can improve their agility, streamline operations and accelerate time to market,” said Doug Suriano, senior vice president and general manager, Oracle Communications. “Oracle Communications SBC provides CSPs, such as gnTel, the unconstrained flexibility that supports their efforts now and in the future.”

To learn more about Oracle Communications solutions, please connect on Twitter @OracleComms and at www.youtube.com/user/OracleCommunications, or visit oracle.com/communications.

To learn more about NetAxis Solutions, please visit http://www.netaxis.be/.

For further information about gnTel please visit http://www.gntel.nl/en.

Contact Info
Katie Barron
Kristin Reeves
Blanc & Otus
About Oracle

The Oracle Cloud delivers hundreds of SaaS applications and enterprise-class PaaS and IaaS services to customers in more than 195 countries while processing 55 billion transactions a day. For more information about Oracle (NYSE:ORCL), please visit us at http://cloud.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Katie Barron

  • +1.202.904.1138

Kristin Reeves

  • +1.415.856.5145

State University of New York Selects Oracle Cloud at Customer to Modernize the Learning Experience for Students and Staff

Oracle Press Releases - Wed, 2017-06-28 09:00
Press Release
State University of New York Selects Oracle Cloud at Customer to Modernize the Learning Experience for Students and Staff Largest University System in the U.S. Looks to Oracle Cloud to Fuel Innovation, While Managing Stringent Regulatory Mandates

Redwood Shores, Calif.—Jun 28, 2017

News Summary

Oracle today announced that State University of New York is modernizing its infrastructure with Oracle Cloud. The State University of New York (SUNY) is the largest comprehensive university system in the United States, with 64 institutions serving more than 1.3 million students. Leveraging Oracle Cloud at Customer, SUNY aims to digitally transform its IT infrastructure, while meeting stringent regulatory data requirements and improving services to its constituents. The solution was selected in conjunction with Mythics, a Platinum-level partner in the Oracle PartnerNetwork.

Because of regulatory concerns and state mandates, SUNY needed a cloud solution that would sit behind their firewall, while still offering all the modern capabilities of a public cloud offering. After a competitive review, SUNY chose the Oracle Cloud Machine and Oracle to lead its data center transformation. SUNY expects its individual institutions to adopt the Oracle Cloud Machine to run their public cloud initiatives while keeping sensitive data within their premises. Individual schools can leverage the Oracle Cloud Machine to provision virtual machines, replicate mission critical data for disaster recovery, and quickly stand-up development and test environments, which can help those schools to reduce their individual data center footprints and reduce operating costs.  

“SUNY is dedicated to continually innovating to deliver the best learning experiences for our students and modern, efficient services for our staff,” said, Mike Notarius, CIO of the State University of New York Information Technology Exchange Center (ITEC). “We are excited to stand up the Oracle Cloud at the State University of New York (SUNY) Information Technology Center (ITEC) and provide a public cloud experience within the SUNY Private Cloud.”

Leveraging Oracle’s Cloud PaaS and IaaS capabilities, Oracle Cloud Machine enables customers to innovate in the cloud, while keeping their data behind their firewall. With a comprehensive, integrated portfolio of platform services, the offering empowers users to innovate faster, increase productivity, and lower costs.

Contact Info
Katie Barron
Oracle PR
Kristin Reeves
Blanc & Otus
About Oracle

The Oracle Cloud delivers hundreds of SaaS applications and enterprise-class PaaS and IaaS services to customers in more than 195 countries while processing 55 billion transactions a day. For more information about Oracle (NYSE:ORCL), please visit us at http://cloud.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Katie Barron

  • +1.202.904.1138

Kristin Reeves

  • +1.415.856.5145

New CPADMIN Utility for Managing Concurrent Processing Now Available

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

In Oracle E-Business Suite, concurrent processing simultaneously executes programs running in the background with online operations. When a user runs a report, a request to run the report is generated. The command to run the report is a concurrent request. The program that generates the report is a concurrent program. Concurrent programs are started by a concurrent manager.

A new CPADMIN command line utility for E-Business Suite 12.2 consolidates various utilities for concurrent processing into a single menu-based utility. This ADADMIN-style utility can be used for multiple tasks, including:

  • View Concurrent Manager status
  • Clean CP tables
  • Set Concurrent Manager diagnostics
  • Start, stop, or verify an individual Concurrent Manager
  • Rebuild Concurrent Manager views
  • Move request files
  • Analyze requests
  • Configure request log/out file directory locations

Complete details for running the new CPADMIN utility are published here:

Related Articles


Categories: APPS Blogs

Effects if WWV_FLOW_FILES table has the data cleared

Tom Kyte - Wed, 2017-06-28 01:06
Hello, This might be a simple question, but I wanted to be 100% sure that nothing will happen if the data is cleared from the WWV_FLOW_FILES table. Does this have any affect on anything major within Apex?
Categories: DBA Blogs

Autorestart a singleton database on Grid Infrastructure?

Tom Kyte - Wed, 2017-06-28 01:06
Good Afternoon, We have a grid infrastructure clustered environment that holds only singleton databases, so the instances are only active on one node. The instances are able to be started on other nodes, obviously. We have setup failover rules; ...
Categories: DBA Blogs

Delete a table from another schema after fetch cursor

Tom Kyte - Wed, 2017-06-28 01:06
Hi Guys, Iam fairly new to oracle and was asked a question in an interview about what happens when a table from another schema gets deleted by an user while the same table was used in a cursor by another user and the deletion happens before fet...
Categories: DBA Blogs

SQL Tuning Advisor- not really sure what it does

Tom Kyte - Wed, 2017-06-28 01:06
Good Morning, I am trying to understand what each of the three settings for the CBO really do. 1) There is the "normal", which allows the CBO to run as usual to create execution plans within a short time. 2) There is the Tuning Mode- Limit...
Categories: DBA Blogs

Find name of reference partitioned child tables

Tom Kyte - Wed, 2017-06-28 01:06
We want to delete the records from ?child tables (for e.g. `child1', `child2?, etc?)? before start deleting the records from parent table in a specific partition we are interested in for e.g. 'P_COMPLETED_20160519'. Child tables are partitioned using...
Categories: DBA Blogs

Create Insert Statements Dynamically

Tom Kyte - Wed, 2017-06-28 01:06
Tom,How do you create insert statments dynamically if I give a table name? in TOAD tool, they have this option for each table [Create insert statements] and I was wondering what kind of logic they might have used to create them. I was trying it my...
Categories: DBA Blogs

escape semicolon ?

Tom Kyte - Wed, 2017-06-28 01:06
hi Tom, I have a big script file that when executed gives an error: "ORA-01756: quoted string not properly terminated" I've reduced it to identifying as the semicolon being the problem: update table_x set x_sql_statement= 'DECLARE l_stat...
Categories: DBA Blogs

Updates to Oracle Utilities Testing solution

Anthony Shorten - Tue, 2017-06-27 18:49

We are pleased to announce the availability of new content for the Oracle Functional Testing Advanced Pack for Oracle Utilities. This pack allows customers of supported Oracle Utilities products to adopt automated testing quickly and easily by providing the testing components used by Product Development for use in the Oracle Application Testing Suite.

We have released, as patches available from My Oracle Support, the following content patches:

  • Oracle Utilities Customer Care And Billing v2. (available as patch 26075747).
  • Oracle Utilities Customer To Meter v2. (available as patch 26075823).
  • Oracle Utilities Meter Data Management/ Oracle Utilities Smart Grid Gateway v2.2.0.1 (available as patch 26075799).

This means the current release of the pack, v5.0.1.0, supports the following products and versions:

  • Oracle Utilities Customer Care And Billing,, &
  • Oracle Utilities Mobile Workforce Management, &
  • Oracle Real Time Scheduler, &
  • Oracle Utilities Application Framework,,, &
  • Oracle Utilities Meter Data Management, &
  • Oracle Utilities Smart Grid Gateway (all adapters), &      
  • Oracle Utilities Work And Asset Management 2.1.1, & 2.2.0
  • Oracle Utilities Operational Device Management 2.1.1 & 2.2.0
  • Oracle Utilities Customer To Meter

The pack continues to support the ability to build flows for these products, including flows across multiple products, packaged integration and supports all channels of access including online, web services and batch. We also support mobile testing for the Oracle Utilities Mobile Workforce Management and Oracle Real Time Scheduler products running on Android and iOS devices.

The pack also includes sanity flows used by the Oracle Utilities cloud deployments that test the installation of the products are complete and operational.

Web Services: REST vs SOAP

Floyd Teter - Tue, 2017-06-27 14:54
I was in the middle of a discussion earlier today about service-based integration for Oracle HCM Cloud.  In that conversation, someone asked me why anyone would ever use SOAP over REST.  My answer was pretty lengthy but, when I was done, someone said I ought to share it on my blog. 

Well, here's the thing:  my thinking on the subject is not original.  I simply communicate fundamentals I learned elsewhere.  And I'm big on giving credit where credit is due.  So when asked about REST vs SOAP, I simply regurgitate the very best summary of REST vs SOAP that I've ever read.  That summary can be found here.  It's a little dated now, having been written in 2010, but it's still accurate.  Feel free to use it yourself.  But do take a moment every now and then to give credit where credit is due.

Summer, autumn and winter: a lot of conferences ahead

Yann Neuhaus - Tue, 2017-06-27 14:50

It is hot in Europe, it is summer, enjoy, but technology moves fast so you have the chance to already prepare for the next conferences. The IT Tage 2017 will happen the 11th to 14th of December this year and we are happy to be there again.
This event covers a wide range of topics and we will be there again covering and talking about:

In addition you will have the chance to join Jan from EnterpriseDB speaking about the differences between Oracle and PostgreSQL. If you want to hear more about that, you might want to join the PostgreSQL Conference Europe this year.


Maybe Jan and me get the chance to talk there as well, we submitted an interesting session, stay tuned.

Chances are high that you may find us at the ukoug, too:


… and we’ll be at the #DOAG2017 for sure this year again.

Hope to see you soon… Cheers, discussions ahead …


Cet article Summer, autumn and winter: a lot of conferences ahead est apparu en premier sur Blog dbi services.

12c Multitenant Internals: VPD for V$ views

Yann Neuhaus - Tue, 2017-06-27 13:49

I described in an earlier post on AWR views how the dictionary views were using metadata and object links to show information from other containers. But this mechanism cannot work for fixed views (aka V$) because they don’t have their definition in the dictionary.

The big difference is that most of V$ views are available long before the dictionary is opened or even created. Just start an instance in NOMOUNT and you can query the V$ views. Even in multitenant, you can switch to different containers in MOUNT, and query V$ views, when no dictionary is opened.

SQL> alter database mount;
Database altered.
SQL> show pdbs
---------- ------------------------------ ---------- ----------
SQL> alter session set container=pdb1;
Session altered.
SQL> show pdbs;
---------- ------------------------------ ---------- ----------

V$ views query information from the instance and this information pertain to one container:

  • CON_ID=0 for the CDB itself
  • CON_ID=1 for CDB$ROOT
  • CON_ID=2 for PDB$SEED
  • CON_ID=3 for the first PDB you have created

When you are in root, the V$ views are queried as normal and show all information – from all containers – with their related CON_ID

When you are in a PDB, you must see the objects that belong to your PDB, but not those that belong to other PDBS. But this is not sufficient. For example, you may query the version, and the version is related to the CDB itself, with CON_ID=0:

SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> select * from v$version;
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release - 64bit Production 0
PL/SQL Release - Production 0
CORE Production 0
TNS for Linux: Version - Production 0
NLSRTL Version - Production 0

Then, in a PDB you should see your PDB objects and the CON_ID=0 ones. Oracle needs a new mecanism for that. One way would be to switch to root, query the V$ and filter on CON_ID. We don’t need that. Context switch is there to access data from a different container tablespace, because tablepaces are not shared. But V$ views expose data from the instance, and the instance is shared. Any container can see all rows, and we just want to filter some rows.

Here is the execution plan when querying V$VERSION from a PDB:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
SQL> explain plan for select * from v$version;
SQL> select * from table(dbms_xplan.display);
Plan hash value: 1078166315
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 68 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$VERSION | 1 | 68 | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter(("CON_ID"=0 OR "CON_ID"=3) AND

An additional predicate (“CON_ID”=0 OR “CON_ID”=3) is added to the view. How is it done? Oracle has a security feature for that: Virtual Private Database – aka Row Level Security – which adds a where clause dynamically.

One way to get more information about virtual private databases is to have an error on its execution and I know that a user with only select privilege cannot EXPLAIN PLAN (see MOS Note 1029064.6).

I connect to a PDB with a low privileged user:
SQL> connect scott/tiger@//localhost/PDB1

I explain plan the V$VERSION fixed view.
SQL> explain plan for select * from v$version;
Error starting at line : 10 File @ /media/sf_share/122/blogs/multitenant-vpd.sql
In command -
explain plan for select * from v$version
Error report -
ORA-28113: policy predicate has error
28113. 00000 - "policy predicate has error"
*Cause: Policy function generates invalid predicate.
*Action: Review the trace file for detailed error information.

Interesting error which confirms the guess: this is a VPD error and it generates a trace:
*** 2017-06-26T22:45:17.838507+02:00 (PDB1(3))
*** SESSION ID:(141.17865) 2017-06-26T22:45:17.838537+02:00
*** CLIENT ID:() 2017-06-26T22:45:17.838541+02:00
*** SERVICE NAME:(pdb1) 2017-06-26T22:45:17.838545+02:00
*** MODULE NAME:(java@VM104 (TNS V1-V3)) 2017-06-26T22:45:17.838548+02:00
*** ACTION NAME:() 2017-06-26T22:45:17.838552+02:00
*** CLIENT DRIVER:(jdbcoci : 2017-06-26T22:45:17.838555+02:00
*** CONTAINER ID:(3) 2017-06-26T22:45:17.838558+02:00
Error information for ORA-28113:
Logon user : SCOTT
Table/View : SYS.V_$VERSION
VPD Policy name : CON_ID
Policy function: SYS.CON_ID
RLS view :
ORA-01039: insufficient privileges on underlying objects of the view

There’s no container switch here, all is running in PDB1 with CON_ID=3 and the internal VPD has added a where clause to filter rows with CON_ID=0 and CON_ID=3

Do not search for the VPD policy name ‘CON_ID’ and function ‘CON_ID’ in the dictionary views because this happens even when the dictionary is not accessible. This is an internal policy used when querying fixed views in multitenant and which probably use some of the VPD code only.


Cet article 12c Multitenant Internals: VPD for V$ views est apparu en premier sur Blog dbi services.


Subscribe to Oracle FAQ aggregator