Feed aggregator

'out blob' vs 'out nocopy blob'

Tom Kyte - Fri, 2016-12-02 09:26
Say I have a table: create table emp( empid number(5), name varchar2(30), photo blob ); create or replace procedure get_photo(p_empid in number, p_name out varchar2, p_photo OUT BLOB) as begin select name, photo into p_name, p_ph...
Categories: DBA Blogs

list agg function - Custom defined

Tom Kyte - Fri, 2016-12-02 09:26
Hello, I am fairly new to Oracle and quite frankly a noob at writing code. I am writing a query to pull in data. One of the fields needs to be concatenated. However there a multiple duplicate records in the field resulting in many rows of data....
Categories: DBA Blogs

DBMS_JOB deprecated in 12c Release 2 in favor of DBMS_SCHEDULER

Tom Kyte - Fri, 2016-12-02 09:26
The 12c2 database upgrade guide (http://docs.oracle.com/database/122/UPGRD/deprecated-features-oracle-database-12c-r2.htm#UPGRD-GUID-C34B4093-97BE-4237-9BE4-F45450F23BA3) identifies DBMS_JOB as deprecated and notes that it may be desupported in a fut...
Categories: DBA Blogs

Database performance problem

Tom Kyte - Fri, 2016-12-02 09:26
Hi team, I want to ask you some questions. 1. My office using oracle database. We have 9 schemas in one database. Each schemas having branches. Sometimes the database application running heavy so much. When I see the CPU usage of server database i...
Categories: DBA Blogs

Histogram Upgrade

Jonathan Lewis - Fri, 2016-12-02 09:02

I’ve written notes in the past about the improvements 12c introduces for histograms – particularly the frequency and top-N histograms which can be generated “free of charge” while the basic “approximate NDV” scans are taking place to gather stats. Gathering histograms in 12c is much safer than it used to be in earlier versions of Oracle even in the case of the new hybrid histograms (which are still sampled on a very small sample and therefore still a stability risk).

There is a threat, though, recently highlighted by Franck Pachot, that sneaked in at – the way in which the calculation of endpoint values for histograms on char() and nchar() columns has changed. When you upgrade from anything prior to to either or 12c you need to recreate any historgams on those columns; note that this does not apply to varchar2() and nvarchar2() columns, just the fixed length character types. If you fail to do this then you may find that Oracle produces some very silly estimates of cardinality, which could result in some very inefficient tablescans – in particular you are likely to find (as Franck showed) cases where you “know” that a particular value is IN the histogram but the optimizer behaves as if it isn’t – which means it uses the “half the least popular” estimate for the cardinality.

Here’s a little demo to show the underlying difference:

rem     Script:         histogram_change_11204.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2016
rem     Purpose:

create table t1 (v1 varchar2(32), c1 char(32));
insert into t1
        case when rownum <= 100 then 'N' else 'Y' end,
        case when rownum <= 100 then 'N' else 'Y' end
        rownum <= 1000
        dbms_stats.gather_table_stats( ownname => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 10'

column endpoint_value format 999,999,999,999,999,999,999,999,999,999,999,999
break on column_name skip 1

        column_name, endpoint_number, endpoint_value, to_char(endpoint_value,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
        table_name = 'T1'
order by

Here are the results from an instance of (though anything up to should produce the same), and (and later – including 12.2):

-------------------- --------------- ------------------------------------------------ ---------------------------------
C1                               100  405,650,737,316,592,000,000,000,000,000,000,000    4e20202020203a7bb119d5f6000000
                                1000  462,766,002,760,475,000,000,000,000,000,000,000    59202020202034d998ff0b5ae00000

V1                               100  404,999,154,965,717,000,000,000,000,000,000,000    4e0000000000181f436c7bbb200000
                                1000  462,114,420,409,600,000,000,000,000,000,000,000    590000000000127d2b51b120000000

-------------------- --------------- ------------------------------------------------ ---------------------------------
C1                               100  404,999,154,965,717,000,000,000,000,000,000,000    4e0000000000181f436c7bbb200000
                                1000  462,114,420,409,600,000,000,000,000,000,000,000    590000000000127d2b51b120000000

V1                               100  404,999,154,965,717,000,000,000,000,000,000,000    4e0000000000181f436c7bbb200000
                                1000  462,114,420,409,600,000,000,000,000,000,000,000    590000000000127d2b51b120000000

Look particularly at the first 6 bytes of the Hex version of the endpoint values for the char() column c1. In you see “4e2020202020”, “592020202020” – that’s ASCII ‘N’ and ‘Y’ respectively, padded to 6 characters with spaces. In the spaces have disappeared – the char() columns are now padded to 6 characters with zeros (which is how varchar2() columns have always been treated).

In the optimizer will find a histogram entry for c1 = ‘Y’ and produce a cardinality of 900; if you upgrade the database to without recreating the histograms the optimizer won’t find a histogram entry for the predicate and will produce a cardinality of 50 (i.e. 100 / 2).


There’s a brief summary of the algorithm Oracle uses to generate values for character-based histograms at this URL.


Oracle SIN Function with Examples

Complete IT Professional - Fri, 2016-12-02 05:00
In this article, I’ll explain what the Oracle SIN function is and show you some examples. Purpose of the Oracle SIN Function The purpose of the SIN function is to calculate the sine of a number. The sine is the ratio of the length of the side of the triangle opposite the angle to the […]
Categories: Development

Database Vault Certified with EBS 12.1 and 12.2

Steven Chan - Fri, 2016-12-02 02:05

Oracle Database Vault allows security administrators to protect a database from privileged account access to application data.  Database objects can be placed in protected realms, which can be accessed only if a specific set of conditions are met.

We are pleased to announce that Oracle Database Vault 12c is now certified with Oracle E-Business Suite Release 12.2 and 12.1. To integrate Oracle Database Vault 12c with Oracle E-Business Suite Release 12.2 or 12.1, follow the instructions in the corresponding My Oracle Support Knowledge Document:

Note:  We deliver a default realm via two different patches for Oracle E-Business Suite 12.2 and 12.1 .  You may use the default realm as-is or choose a subset.

Pending Certifications with External Integrations

The following two configurations are still underway and pending:

  • Single Sign-On Integration with Oracle Access Manager
  • Oracle Discoverer

Oracle's Revenue Recognition rules prohibit us from discussing certification and release dates, but you're welcome to monitor or subscribe to this blog. I'll post updates here as soon as soon as they're available.   


Related Articles

Categories: APPS Blogs

Micro segmenting using Adobe Target leading to personalization

Arvind Jain - Thu, 2016-12-01 20:34
Using DMP and DSP tools like Adobe Target, Adobe Audience Manager, LiveRamp.. you can practically (not legally) deliver real-time personalization. What they really do is micro targeting / micro segmentation. But today it has reached that fine level of granularity where ads / messaging shown to end users have started looking creepy. We are being watched all the time.

Realtime micro targeting is possible, in the same way as it is with any other type of CRM data - data is pre-loaded into Adobe Audience manager (AAM) and as soon as AAM sees that particular user in realtime, it can show specific segment messaging based on their segment qualification. They key here is how finely you define your segments. The more micro targeting you do, the more personal the message will appear.

The thing that is not realtime is sending CRM data to AAM, but once the data is dropped to AAM it is available later for real-time use after some sync duration.

Thanks for reading


Compare two tables

Tom Kyte - Thu, 2016-12-01 15:06
I want to compare two tables which have exactly same columns. Tables contains 200 columns each and have million of records in them. My query is I want to compare these two tables and find out which columns have different data and then I need to po...
Categories: DBA Blogs

Converting a GUID to specific number of characters.

Tom Kyte - Thu, 2016-12-01 15:06
Hi Experts, I have major experience in SQL Server and I am quite new to Oracle and trying to implement something . I need a help from you guys. I have converted a varchar value into a GUID which looks like this 7171D6557B34C0BF47F166092EE93E06...
Categories: DBA Blogs

Bad Performance of the PLSQL block when it is being executed by Execute Immediate.

Tom Kyte - Thu, 2016-12-01 15:06
I have 50 tables, each with millions of data rows in the Staging Table, that need to be validate by Business Rules. each record and each field have n number of validation, Example Validation Description- 1. (STAGING_FACT_ORG_ID IS NULL) To va...
Categories: DBA Blogs

About IN keyword in Oracle

Tom Kyte - Thu, 2016-12-01 15:06
Dear Tom, Can i ask question. If i have a array of varchar2 with name is arr_val in oracle. How can i use "where in arr_val" instead of "where in (select column_name from table_name)".And if it can be done. Can y explain which way is better? Thank...
Categories: DBA Blogs

Truncate Load gather stats

Tom Kyte - Thu, 2016-12-01 15:06
Hi, I have a DW activity. <b>Steps include</b>. Drop index on Table A Truncate Table A. Load table A with millions of records. Create indexes back on table A. Drop index on Table B Truncate Table B. Load table B with millions of records....
Categories: DBA Blogs

Partitioned Sequence

Tom Kyte - Thu, 2016-12-01 15:06
Is sequence partitioning available in Oracle Database version ? When I query dba_sequences, I see the column partition_count but I was not able to find any documentation related to it, is this feature available for production use ?
Categories: DBA Blogs

Better Oracle JET Code Structuring with Your Own Modules - Part II

Andrejus Baranovski - Thu, 2016-12-01 10:14
You can end up into long lines of JavaScript code when implementing more complex use cases in JET. This will complicate maintenance and make code hardly readable. To prevent this - plan code structure carefully and use your own modules. Structure code into different modules - to reuse common code across multiple use cases.

Check my JET/ADF BC sample available on GitHub - jetcrud. This sample implements one common module - Customer Controller. Module is responsible to define ADF BC REST service connection. It contains REST service URL, JET model definition with ID attribute and JET collection created based on JET model and assigned with fetch size. This allows not to repeat same definitions again and again in each of JET modules implementing use cases (edit, add, etc.):

Such module is created as any other JET module with define header. We can use it in any other JET module, by specifying module name in define block. For example customers.js module imports Customer Controller through define block:

Within customer.js module we access function from Customer Controller to obtain collection.

Same Customer Controller module is reused inside another module editCustomer.js. Here we are accessing function from Customer Controller to obtain model:

Such approach allows to simplify JavaScript code and render different UIs based on one module. Readonly table is based on customerController.js:

Edit form is based on same customerController.js:

Read previous post - Oracle JET CRUD - Search and Edit Form - Part I.

CQLSH – Unable to connect to any servers Tried connecting to [(‘’, 9042)

VitalSoftTech - Thu, 2016-12-01 09:30
When trying to connect to a node in the Cassandra Cluster using cqlsh, I get the "Unable to connect to any servers. Tried connecting to [('', 9042)" message. Learn how to modify the configuration so that the hostname is not required.
Categories: DBA Blogs

Oracle Pledges $1.4 Billion to Support Computer Science Education in the European Union

Oracle Press Releases - Thu, 2016-12-01 08:00
Press Release
Oracle Pledges $1.4 Billion to Support Computer Science Education in the European Union Part of Oracle’s Greater $3.3 Billion Annual Investment to Accelerate Digital Literacy Worldwide

Redwood Shores, Calif.—Dec 1, 2016

Today, the European Commission and DIGITALEUROPE launched the Digital Skills and Jobs Coalition, a multi-stakeholder partnership to bolster computer science (CS) and coding proficiency at all levels within Europe’s workforce pipeline. In conjunction, Oracle announced a three-year investment totaling $1.4 billion in direct and in-kind support of CS education throughout the European Union (EU) member states.

Nearly 1,000 EU educational institutions currently collaborate with Oracle Academy, Oracle’s flagship program in education philanthropy that brings computer science education to nearly 3.1 million students in 110 countries. As part of today’s pledge, Oracle Academy aims to train 1,000 additional EU educators in CS, Java and Database instruction, as well as reach students across an added 1,000 EU educational institutions over the next three years.

“Digitally skilled professionals are critical to Europe’s competitiveness and capacity for innovation,” said John Higgins, Director General, DIGITALEUROPE. “Over the last ten years, we’ve seen the demand for workers with computer science and coding skills grow by four percent each year. Oracle’s efforts to bring computer science into classrooms across the European Union will help strengthen our digital economy.”

“At Oracle, we are thrilled to inspire and engage students from all different parts of the world in computer science,” said Alison Derbenwick Miller, Oracle Academy Vice President. “Earlier this year, Oracle made significant commitments to the White House’s CS for All and Let Girls Learn initiatives. Today’s announcement continues our momentum to advance digital education and increase diversity in technology fields globally.”

Additionally, Oracle Academy will drive several programs during the next three years, including:

  • Invite National Coalitions in the EU member states to partner with Oracle Academy in delivering computer science education to teachers and students
  • Provide new higher education curriculum in emerging technologies
  • Level Oracle Academy’s entire curriculum to the European Qualifications Framework (EQF)

Today’s commitment is an extension of Oracle’s pledge in 2013, which trained and empowered 1,246 EU educators to teach computer science, as well as added 203 EU educational institutions to Oracle Academy.

Contact Info
Julie Sugishita
Oracle Corporate Communications
About Oracle Academy

As Oracle’s flagship philanthropic educational program, Oracle Academy advances computer science education globally to drive knowledge, innovation, skills development, and diversity in technology fields, offering a free and complete portfolio of software, curriculum, hosted technology, faculty trainings, support, and certification resources. Supporting more than 3.1 million students annually in 110 countries, the program works with public and private partners to provide the tools educators need to engage, inspire and prepare students to become innovators and leaders of the future. Through Oracle Academy, students receive hands-on experience with the latest technologies, helping to make them college and career ready in the era of big data, cloud computing, the Internet of Things, and beyond.

About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.


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

Safe Harbor

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Julie Sugishita

  • 1.650.506.0076

Note to myself: when handling large payloads

Darwin IT - Thu, 2016-12-01 07:36
Today I stumbled on a question in the communities about handling large payloads in BPEL/XSLT. Although I know that SOASuite from 11g onwards can do paging of XML to disk, I never had the need. However, you could need it from time to time. And it's good to know how to do it.

It's noted on My Oracle Support with Doc ID 1327970.1. Which refers to the 11g documentation on Managing Large Documents and Large Numbers of Instances.

Learning all the time....

ANNOUNCEMENT: 12cR2 documentation is available

Laurent Schneider - Thu, 2016-12-01 05:42

The doc docs.oracle.com/database/122 is available. Check new features and stay tuned.

Okay, just 12 for today : HIGH compressed index, case insensitive database with bound collation, partitioned external tables, AL32UTF8 default for new database, listagg overflow, VALIDATE_CONVERSION, approx_percentile, json_exists, flashback pluggable database, the SHARING clause, RAC streched clusters site definition and INACTIVE_ACCOUNT_TIME in profile.

Some I like more than others &#x1f642;

Okay, one more : SQLPLUS HISTORY !

Oracle COS Function with Examples

Complete IT Professional - Thu, 2016-12-01 05:00
In this article, we look at the Oracle COS function and see some examples. Purpose of the Oracle COS Function The COS function will return the cosine of a number. If your knowledge of cosine is a bit rusty (like mine), then you might find this definition helpful.   Syntax The syntax of the COS […]
Categories: Development


Subscribe to Oracle FAQ aggregator