Feed aggregator

What privilege to view package body

Tom Kyte - Mon, 2018-09-17 08:46
Hi Tom: I have a problem when i grant the package privilege to the other user. A is a normal user which used in factory environment. user B is for app team which can not create anything. First I grant create any procedure ,execute any procedure...
Categories: DBA Blogs

Hardware resource planning

Tom Kyte - Mon, 2018-09-17 08:46
Hello, Thanks for taking up this question. I am interested in understanding how to optimize the hardware resources (cores, memory, disk space) required for Oracle without impacting performance. There are multiple virtual machines in a VMwa...
Categories: DBA Blogs

system user could login without password or incorrect password

Tom Kyte - Mon, 2018-09-17 08:46
hi all, recently i had an incident.. i just logged into the database as system using sqlplus when sqlplus prompted for username i put 'SYS AS SYSDBA' and when prompted for password,instead of entering my password i just hit the ENTER key and s...
Categories: DBA Blogs

Returning count of rows deleted using execute immediate

Tom Kyte - Sun, 2018-09-16 14:46
How to I get the number of rows deleted within PL/SQL using the EXECUTE IMMEDIATE command?
Categories: DBA Blogs

Partitioning -- 5 : List Partitioning

Hemant K Chitale - Sun, 2018-09-16 10:14
List Partitioning allows you to specify a value (or a set of values) for the Partition Key to map to each Partition.

This example shows List Partitioning.

SQL> create table request_queue
2 (request_id number primary key,
3 request_submision_time timestamp,
4 requestor number,
5 request_arg_1 varchar2(255),
6 request_arg_2 varchar2(255),
7 request_arg_3 varchar2(255),
8 request_status varchar2(10),
9 request_completion_time timestamp)
10 partition by list (request_status)
11 (partition p_submitted values ('SUBMITTED'),
12 partition p_running values ('RUNNING'),
13 partition p_errored values ('ERRORED'),
14 partition p_completed values ('COMPLETED'),
15 partition p_miscell values ('RECHECK','FLAGGED','UNKNOWN'),
16 partition p_default values (DEFAULT)
17 )
18 /

Table created.


Note how the P_MISCELL Partition can host multiple values for the REQUEST_STATUS column.
The last Partition, has is specified as a DEFAULT Partition (note that DEFAULT is a keyword, not a value like the others) to hold rows for REQUEST_STATUS for values not mapped to any of the other Partitions.  With List Partitioning, you should always have a DEFAULT Partition (it can have any name, e.g. P_UNKNOWN) so that unmapped rows can be captured.

If you go back to my previous post on Row Movement, you should realise the danger of capturing changing values (e.g. from "SUBMITTED" to "RUNNING" to "COMPLETED") in different Partitions.  What is the impact of updating a Request from the "SUBMITTED" status to the "RUNNING" status and then to the "COMPLETED" status ?  It is not simply an update of the REQUEST_STATUS column alone but a physical reinsertion of the entire row (with the consequent update to all indexes) at each change of status.

SQL> insert into request_queue
2 values (request_id_seq.nextval,systimestamp,101,
3 'FAC1','NOTE',null,'SUBMITTED',null)
4 /

1 row created.

SQL> commit;

Commit complete.

.... sometime later ....

SQL> update request_queue
2 set request_status = 'RUNNING'
3 where request_id=1001
4 /
update request_queue
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


So, although now we know that we must ENABLE ROW MOVEMENT, we must suffer the impact of the physical reinsertion of the entire row into a new Partition.

SQL> alter table request_queue enable row movement;

Table altered.

SQL> update request_queue
2 set request_status = 'RUNNING'
3 where request_id=1001
4 /

1 row updated.

SQL> commit;

Commit complete.

.... sometime later ....

SQL> update request_queue
2 set request_status = 'COMPLETED',
3 request_completion_time=systimestamp
4 where request_id=1001
5 /

1 row updated.

SQL> commit;

Commit complete.


(Note that all the previous "Partitioning 3a to 3d" posts about Indexing apply to List Partitioning as well)

Categories: DBA Blogs

Oracle Core Audit - Do you Audit your Core database engine for breach?

Pete Finnigan - Sat, 2018-09-15 20:26
Oracles core database audit is a useful tool to monitor activity of the core database engine or applications and detect potential abuses. It seems to be a sad fact that with a lot of companies that i visit and from....[Read More]

Posted by Pete On 15/09/18 At 08:28 AM

Categories: Security Blogs

Updating Exadata Software summary

Syed Jaffar - Sat, 2018-09-15 07:06
Updating an Exadata software is one of the crucial tasks for any Database Machine Administrator (DMA). Though not necessarily one has to patch the environments whenever there is a new patch released by Oracle, but, it is highly recommended to patch the systems at least twice a year to fix any known &unknown bugs, security vulnerabilities and other issues.

This blog post summarizes the overall overview of software updates on an Exadata Database Machine. The post explains what components are needed the updates, the update order of components, pre-requisites and etc.

Typically, Exadata database machine updates are divided in the following categories:

  • Exadata Infrastructure Software 
  • Grid Infrastructure and Oracle Database Software

Updating the Exadata Software comprises of following components:

  • Storage Servers
  • Database Servers
  • InfiniBand Switches
Software upgrade for Cell and DB nodes typically contains the updates for the following:
  • OLE OS
  • Exadata Software
  • Firmware (Disk, Flash, RAID Controller, HCA, ILOM etc)


The following pre-upgrade activities are highly recommended before upgrading the Exadata software in any environment:

  • Review MOS Doc 888828.1 and download the target version software
  • Download observer.patch.zip from MOS Doc 1553103.1
  • Review MOS Doc 1270094.1 for any critical issues
  • Run the latest version of ExaCHK utility. Fix any FAIL and WARNINGS issues reported in the ExaCHK report. Also, review version recommendations in the MAA scoreboard section
  • Ensure you have latest upgrade/patching utilities, such as, patchmgr, opatch etc. (MOS Doc 1070954.1)
  • Perform prerequisites checks
  • Backup the Exadata database servers before the update 
Rolling vs Non-rolling upgrades

Software updates can be performed online or offline (rolling or non-rolling) fashion. For online updates, it is highly recommended ASM high level disk group redundancy to avoid any data or service loss.

As part of best practices, the following is update order is recommended and treated as safe:
  1. GI and Oracle Database home
  2. Database Servers
  3. Storage Servers
  4. IB Switches
patchmgr update utiity

patchmgr update utility is used to patch the Exadata infrastructure components.  Following are the capabilities of patchmgr:
  • Single invocation for Database servers, storage servers and IB Switches
  • updates firmware, OS and Exadata softwares
  • Online update advantage
Conclusion: Though the procedure looks pretty straight forward & simply when reading, with my past experience, patching each environments comes up with surprises and we need to be ready, unless we are very lucky on the particular day to have a smooth patching experience.

In the upcoming posts, I will talk about how to use patchmgr and other update utilizes to update Exadata software, Database, Storage servers and IB Switches.

[Blog] Oracle Database System Deployment Options On Cloud

Online Apps DBA - Sat, 2018-09-15 05:26

Oracle Database can be deployed on Cloud under OCI Classic (OCI) and OCI. Deploying Database on OCI is much better because of all the features in OCI. Database on OCI can be on top of Bare Metal (BM) , Virtual Machine (VM), Exadata Cloud , Autonomous Transaction Process (ATP), or Autonomous Dataware House Cloud) ADWC. […]

The post [Blog] Oracle Database System Deployment Options On Cloud appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

[BLOG] BigData Hadoop Developer: Job Responsibilities & Skills

Online Apps DBA - Sat, 2018-09-15 01:26

Are you a Beginner who is just starting to learn Hadoop/Big Data? Visit: https://k21academy.com/hadoopdev11 and learn about: ✔What is Hadoop ✔Job Roles for Hadoop ✔Hadoop Developer Skills and much more… Are you a Beginner who is just starting to learn Hadoop/Big Data? Visit: https://k21academy.com/hadoopdev11 and learn about: ✔What is Hadoop ✔Job Roles for Hadoop ✔Hadoop […]

The post [BLOG] BigData Hadoop Developer: Job Responsibilities & Skills appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Business Logic for Business Object in Visual Builder - Triggers, Object Functions, Groovy and More

Shay Shmeltzer - Fri, 2018-09-14 18:15

The business objects that you create in Visual Builder Cloud Service (VBCS) are quite powerful. Not only can they store data, manage relationships, and give you a rich REST interface for interacting with them, they can also execute dedicated business logic that deals with the data.

If you click on the Business Rules section of a business object you'll see that you can create:

  • Triggers - allow you to react to data events such as insert, update, and delete on records.
  • Object and field Validators - allowing you to make sure that data at the field or record level is correct.
  • Object Functions - A way to define "service methods" that encapsulate logic related to a business object. These functions can be invoked from various points in your application, and also from outside your app.

To code logic in any of these location you will leverage the Groovy language.

I wanted to show the power of some of the functionality you can achieve with these hook points for logic. The demo scenario below is based on a requirement we got from a customer to be able to send an email with the details of all the children records that belong to a specific master record. Imagine a scenario where we have travel requests associated with specific airlines. When we go to delete an airline we want to send an email that will notify someoe about the travel requests that are going to be impacted by this change.

To achieve this I used an accessor - an object that helps you traverse relationships between the two objects - to loop over the records and collect them.

In the video below you'll see a couple of important points:

  • Business object relationship and how to locate the name of an accessor
  • Using a Trigger Event to send an email
  • Passing an object function as a parameter to an email template
  • Coding groovy in a business object

For those interested the specific Groovy code I used is:

def children = TravelRequests; // Accessor name to child collection def ret_val = "List of travel requests "; if (!children.hasNext()) { return "no impact"; } while (children.hasNext()) { def emprec = children.next(); def name = emprec.name; ret_val=ret_val+" " +name; } return ret_val;


By the way - if, like me, you come from a background of using Oracle ADF Business Components you might find many of the things we did here quite familiar. That's because we are leveraging Oracle ADF Business Components in this layer of Visual Builder Cloud Service. So looking up old Groovy tutorial and blogs about ADF BC might prove to be useful here too :-)



Categories: Development

Oracle Linux on Arm (aarch64) update

Wim Coekaerts - Fri, 2018-09-14 10:44

Nothing new to announce but I wanted to take a few minutes to give a little update on where we are with Oracle Linux for Arm. Just a quick summary:

- We have a full version of Oracle Linux 7 (update 5) for Arm. This is freely downloadable from edelivery. The ISO is free download, you can freely use it, you can redistribute it. Just like Oracle Linux x86. No authorization codes, no activation keys. Just download, install and use. Of course, this includes all source code.

- OL7 on Arm uses UEKR5 (4.14.x Linux) including DTrace support (Sometimes I hear people say that UEK is a proprietary kernel. It is not! It is fully open. All the changes, so you actually get to see every single commit of every single change we or others made, not a tar file. it's OPEN)

- there are a ton of packages built for OL/Arm:

ol7_MySQL80/aarch64 MySQL 8.0 for Oracle Linux 7 (aarch64) 32 ol7_developer/aarch64 Oracle Linux 7Server Packages for Develo 15 ol7_developer_EPEL/aarch64 Oracle Linux 7Server EPEL Packages for D 12,410 ol7_developer_UEKR5/aarch64 Oracle Linux 7Server Unbreakable Enterpr 183 ol7_latest/aarch64 Oracle Linux 7Server Latest (aarch64) 8,881 ol7_optional_latest/aarch64 Oracle Linux 7Server Optional Latest (aa 7,246 ol7_software_collections/aarch64 Software Collection Library for Oracle L 136 repolist: 28,903

This includes a ton of EPEL stuff, as you can see above. We have a devtoolset containing gcc 7.3.1 we have support for other languages :golang 1.10, nodejs, python php,...  docker is there... lots of goodies to have a good easy full-fledged development environment.

As a reminder:  if you have an Arm box and you want to use docker -> we have images on docker hub for Arm as well.

you can simply do:

# docker pull oraclelinux:latest

and it pulls in the Arm docker image for Oracle Linux.


# docker pull oraclelinux:latest latest: Pulling from library/oraclelinux cd165b3abf95: Download complete [6329822.343702] XFS (dm-3): Mounting V4 Filesystem cd165b3abf95: Extracting 86.45MB/86.45MB cd165b3abf95: Pull complete Digest: sha256:d60084c2aea5fa6cb8ed20c04ea5a8cd39c176c82a9015cc59ad6e860855c27f Status: Downloaded newer image for oraclelinux:latest



We are proud to announce:

Yann Neuhaus - Fri, 2018-09-14 09:42


(no words required for this post, the image says it all)


Cet article We are proud to announce: est apparu en premier sur Blog dbi services.

Parse string then flatten into columns

Tom Kyte - Fri, 2018-09-14 07:46
Hi, LiveSQL link not accepted by the form: https://livesql.oracle.com/apex/livesql/s/g88hb5van1r4ctc65yp4lq9gb I have this situation (see link): <b>ID String</b> Id1 Thing1: Sub1, <br>Thing2: Sub7 ,Sub8 , Sub9 <br>Thing3: Sub12 Id1 Thing...
Categories: DBA Blogs

Oracle View object - performance Issue with Outer Join including a WITH clause

Tom Kyte - Fri, 2018-09-14 07:46
Hi Tom ; Thank you , I've been using your site for 2 years now, resolved many issues based on your answers. Case Scenario : Customer having multiple addresses , only one is active ; some cases ALL the addresses of a customer could be inactive....
Categories: DBA Blogs


Tom Kyte - Fri, 2018-09-14 07:46
Error starting at line : 12 in command - CREATE MATERIALIZED VIEW EMP_MV BUILD IMMEDIATE REFRESH FORCE ON COMMIT AS SELECT EMPID,EMPNAME FROM EMP Error report - ORA-12054: cannot set the ON COMMIT refresh attribute for the materializ...
Categories: DBA Blogs

Recommended partition size

Tom Kyte - Fri, 2018-09-14 07:46
We want to partition some tables using interval partitioning on the creation date. Partitioning is for manageability - we want to drop older partitions eventually - and partition pruning for improving performance. What is the Recommended partition si...
Categories: DBA Blogs

ODPI-C 3.0 Introduces SODA Document Storage

Christopher Jones - Fri, 2018-09-14 06:59
ODPI-C logo

Release 3.0 of Oracle Database Programming Interface for C (ODPI-C) is now available on GitHub

ODPI-C is an open source library of C code that simplifies access to Oracle Database for applications written in C or C++.



This release introduces support for Simple Oracle Document Access (SODA) when using Oracle Database 18c. SODA provides a non-SQL API for storing and accessing documents. Commonly documents are JSON, but other types can also be used. In this release the SODA API is a Preview. More details about SODA are in the companion release announcement for Python cx_Oracle 7.0.

Also introduced is a call timeout feature for Oracle Client 18c users. This allows applications direct control over how long database operations are allowed to run, making it easier for applications to control outcomes and keep control of user interaction. Again, details are in the cx_Oracle announcement.

If you're creating Windows applications for distribution, a change to how Oracle client libraries are located will be helpful. ODPI-C will try to load the Oracle client from the same directory as the ODPI-C binary, before defaulting to the standard search, i.e. using PATH. This means you can bundle a specific version of Instant Client with your application and know that it will be used in preference to any other Oracle libraries on the system.

There are a raft of other tweaks and improvements which can be found in the release notes.

ODPI-C References

Home page: https://oracle.github.io/odpi/

Code: https://github.com/oracle/odpi

Documentation: https://oracle.github.io/odpi/doc/index.html

Release Notes: https://oracle.github.io/odpi/doc/releasenotes.html

Installation Instructions: oracle.github.io/odpi/doc/installation.html

Report issues and discuss: https://github.com/oracle/odpi/issues

Exadata and Capacity on Demand (CoD)

Syed Jaffar - Fri, 2018-09-14 06:10
As most of us knew that the Exadata Database Machine comes in different sizes with different resource capacity. Not sure how many of you aware that Capacity on Demand (CoD) option can enable customers to start with limited active cores processors and increase them dynamically on demand. If CoD option is not enabled during the initial EDM configuration, then, all active cores are enabled by default and can't be reduced any further.

With X4-2 or higher, number of active cores can be reduced during the installation and can be increased based on the demand.  For X4-2, cores are increased in two (2) core increment, where as X4-8 increased in eight (8) core factor, see the table below.

Below example demonstrates the procedure to increase the active core processors:

Using DBMCLI utility:

DBMCLI> LIST DBSERVER attributes coreCount

DBMCLI> ALTER DBSERVER pendingCoreCount = new_core_count

DBMCLI> LIST DBSERVER attributes coreCount

Note: Once active cores are enabled (increased), there is no procedure to reduce them again.

Restart the database servers after increasing the core count.

Below table depicts the capacity-on-demand core configuration for various EDM types and releases:

Python cx_Oracle 7 Introduces SODA Document Storage

Christopher Jones - Thu, 2018-09-13 22:48

cx_Oracle logo

cx_Oracle 7.0, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

cx_Oracle is an open source package that covers the Python Database API specification with many additions to support Oracle advanced features.



Anthony Tuininga has just released cx_Oracle 7.0. This release brings some key technologies and new features to the Python developer:

  • Oracle Simple Document Access (SODA) support - an exciting addition to the standard relational access model is the new set of APIs for Oracle SODA. See below.

  • Added Connection.callTimeout to support call timeouts when cx_Oracle is using Oracle Client 18.1 and higher. This is a useful backstop to prevent out-of-control SQL and PL/SQL statement execution.

    The main code layer beneath cx_Oracle's implementation is Oracle Call Interface. This API handles all the network connectivity to Oracle Database. For each OCI function executed by cx_Oracle, zero or more 'round-trips' to the database can occur - calling the database and getting a response back.

    The callTimeout value applies to each round-trip individually, not to the sum of all round-trips. Time spent processing in cx_Oracle before or after the completion of each round-trip is not counted.

    • If the time from the start of any one round-trip to the completion of that same round-trip exceeds callTimeout milliseconds, then the operation is halted and error "DPI-1067: call timeout of N ms exceeded with ORA-XXX" is returned.

    • In the case where a cx_Oracle operation requires more than one round-trip and each round-trip takes less than callTimeout milliseconds, then no timeout will occur, even if the sum of all round-trip calls exceeds callTimeout.

    • If no round-trip is required, the operation will never be interrupted.

    After a timeout occurs, cx_Oracle attempts to clean up the internal connection state. The cleanup is allowed to take another callTimeout milliseconds.

    If the cleanup was successful, the DPI-1067 error will be returned and the application can continue to use the connection.

    For small values of callTimeout, the connection cleanup may not complete successfully within the additional callTimeout period. In this case an ORA-3114 is returned and the connection will no longer be usable. It should be closed.

  • Added support for closing a session pool via the function SessionPool.close(). This is useful for being 'nice' to the database and making sure that database sessions are not left dangling until the database cleans them up. In particular the optional 'force' argument is handy when you need to suddenly halt a Python application and immediately free all the sessions in the database.

  • Added support for getting the contents of a SQL collection object as a dictionary, where the keys are the indices of the collection and the values are the elements of the collection. See function Object.asdict().

  • On Windows, cx_Oracle will now attempt to load the Oracle client libraries from the same directory as the cx_Oracle module before doing the standard Windows library location search, e.g. in the directories in the PATH environment variable. This new feature could be useful if you are bundling up applications and want to include the Oracle Instant Client. By putting the client in the same directory as the cx_Oracle library there is no need to set PATH, no need to worry about users changing PATH, and no need to worry about having multiple versions of Oracle client libraries in PATH.

  • A change in cx_Oracle 7 is that when a DML RETURNING statement is executed, variables bound to it will return an array when calling Variable.getvalue(). Attempts to set cx_Oracle.__future__.dml_ret_array_val are now ignored.

  • When a connection is used as a context manager, the connection is now closed when the block ends. Attempts to set cx_Oracle.__future__.ctx_mgr_close are now ignored.

The full release notes show the other new features and changes. Review this list before you upgrade:

python -m pip install cx_Oracle --upgrade SODA in Python cx_Oracle

Oracle Simple Document Access (SODA) support was originally introduced in Java and recently exposed to C. Python support for SODA is now available in cx_Oracle 7 when using Oracle client 18.3 libraries and connecting to Oracle Database 18.1 or higher. SODA is all hot and new and under rapid development. For this cx_Oracle release we're labelling SODA support as a 'preview'. With a future version of the Oracle Client libraries this will change.

SODA is typically used to store JSON documents in Oracle Database, but has flexibility to let you store other types of content.

Once a DBA has granted you the SODA_APP privilege, you can simply create collections and store documents in them. Some basic examples are:

# Create the parent object for SODA soda = connection.getSodaDatabase() # Create a new SODA collection # This will open an existing collection, if the name is already in use. collection = soda.createCollection("mycollection") # Insert a document # A system generated key is created by default. content = {'name': 'Matilda', 'address': {'city': 'Melbourne'}} doc = collection.insertOneAndGet(content) key = doc.key print('The key of the new SODA document is: ', key)

You can then get documents back via a key look up, or by a search. A key lookup is straightforward:

# Fetch the document back doc = collection.find().key(key).getOne() # A SodaDocument content = doc.getContent() # A JavaScript object print('Retrieved SODA document dictionary is:') print(content)

For documents that can be converted to JSON you can alternatively get them as string:

content = doc.getContentAsString() # A JSON string print('Retrieved SODA document string is:') print(content)

The find() method is an operation builder, with methods that allow progressive filtering criteria to be set, limiting the set of documents that are then operated on by a terminal method such as getOne(), getDocuments() and count().

With JSON documents, a complete filtering specification language can be used to pattern match documents. A brief example is:

# Find all documents with names like 'Ma%' print("Names matching 'Ma%'") documents = collection.find().filter({'name': {'$like': 'Ma%'}}).getDocuments() for d in documents: content = d.getContent() print(content["name"])

A runnable example is in SodaBasic.py

Check out the cx_Oracle SODA manual and the Introduction to Simple Oracle Document Access (SODA) manual to see its power and simplicity.

Check it out!

PS the photo is one I took last weekend on a beach in Australia, which is coming into Spring. I thought you'd like it better than corporate clip art.

cx_Oracle References

Home page: oracle.github.io/python-cx_Oracle/index.html

Installation instructions: cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: cx-oracle.readthedocs.io/en/latest/index.html

Release Notes: cx-oracle.readthedocs.io/en/latest/releasenotes.html

Source Code Repository: github.com/oracle/python-cx_Oracle

Identify the missing object in an ora-08103 error

Tom Kyte - Thu, 2018-09-13 13:26
We have a large cursor that runs nightly and on a random night we get the following error from the job that calls the cursor: 180911 180019 (APPS.GAINS_COMMON,1194) Begin export_data 180911 194348 (APPS.GAINS_COMMON,4735) BEGIN export_sla 1809...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator