Feed aggregator

Tom - what is going to happen to this website?

Tom Kyte - Sun, 2016-12-04 16:26
I wish you much happiness in your upcoming retirement. What will happen to this website? Could one buy an archived copy? It has been such an amazing resource through my career. Even if you are going to hang it up, I'm sure it is still very usefu...
Categories: DBA Blogs

Oracle Developer Cloud Service and ADF Build/Deployment Automation Summary

Andrejus Baranovski - Sun, 2016-12-04 00:12
We are moving our internal development to Oracle Cloud production instance. This weekend I was going through build automation and deployment process with Oracle Developer Cloud Service. I would like to share few hints with you.

There are excellent video tutorials recorded by Shay Schmeltzer, I would not repeat here all the steps, will post only key steps in the process and few extra tips. Watch Shay's videos to get understanding how it works and how to configure Developer Cloud Service (UI was changed since then, but still all config steps are valid):

1. Using Oracle Developer Cloud Service for Git and code review with JDeveloper Applications

2. Oracle ADF Build Automation on the Oracle Developer Cloud Service

Download ADF 12.2.1.1/12.2.1.2 example configured with Ant scripts for build automation on Dev CS - ADFAltaApp.

JDeveloper/ADF version. When I run OJDeploy build in Dev CS (Developer Cloud Service), I can see in the log it prints version 12.2.1.1. This means currently Dev CS supports ADF up to 12.2.1.1:


Locally I prefer working on 12.2.1.2, since JDeveloper is more stable in 12.2.1.2. When I was trying to build code commited from 12.2.12, Dev CS complained it can't open project file. I have solved it by changing 12.2.1.2 to 12.2.1.1 in *.jws and *.jpr files (luckily my local JDeveloper 12.2.1.2 doesn't complain about it and continues to work with the application). With such changes, Dev CS is able to recognize project files and runs build process. Change in the project file:


Development process is centralized around pushing your local changes to Dev CS Git repository branch (this can be done directly from JDeveloper):


In this example, I commit my local changes into fixbugs branch, later changes can be merged into master branch through Dev CS UI:


Build. To merge changes in Dev CS into master branch, we need to register merge request. In the wizard you can specify Git repository name, target branch and review branch (the one from where we are going to get changes and apply to master branch):


You can specify approvers and later when changes are approved, they are merged to master branch.

Next step is build automation (this can invoked on demand or automatically). You can associate build process to Git branch and review past build results:


If you are building with Ant, make sure to add build.properties and build.xml files into ADF application (described in Shay's video). Here is example of build.properties file I'm using in the sample app for ADF 12.2.1.1/12.2.1.2:

Example of build.xml file to run build automation in Dev CS for ADF 12.2.1.1/12.2.1.2:

At first build process was always returning success, even I there were compilation issues left in the code on purpose. In order to force build process to return failure when it should, you need to check "Archive the artifacts" option in Post Build section of build job configuration. This will force build process to produce EAR and if this fails (because of compilation issues), it marks build process failure:


Deploy. Dev CS knows how to deploy EAR into Java Cloud Service. This can be done on demand or automatically, when build is successfully completed. Configuration is simple and straightforward, you need to provide connection details to Java Cloud Service and it works - you can deploy or redeploy:


Application is successfully deployed to Java Cloud Service and visible in EM:


Besides all this, Dev CS offers Wiki's, issue tracking and bunch of other useful features for day to day work in development. So far no complaints, good job Oracle.

Can I do it with PostgreSQL? – 6 – Server programming

Yann Neuhaus - Sat, 2016-12-03 05:43

Today we’ll continue this series with another topic: What does PostgreSQL provide when it comes to server programming, that is: Writing functions and triggers to support your application? In Oracle you can either use PL/SQL or Java, in MariaDB you can use stored procedures written in SQL, MS SQL Server provides Transact SQL and with DB2 you can write stored procedures in a host language or SQL.

We’ll use the same sample data as in the last post:

\c postgres
drop database if exists ddl;
create database ddl;
\c ddl
create table t1 ( a int, b int );
insert into t1 (a,b)
       values (generate_series(1,1000000)
              ,generate_series(1,1000000));
select count(*) from t1;
create index i1 on t1(a);
create unique index i2 on t1(b);
create view v1 as select a from t1;
alter table t1 add constraint con1 check ( a < 2000000 );
\d t1

So, what can you do? To begin with you can create functions containing pure SQL commands. These are called “query language functions”. You can for example do things like this (although this function is not very useful as can you do the same by just selecting the whole table):

CREATE FUNCTION select_all_from_t1() RETURNS SETOF t1 AS '
  SELECT * 
    FROM t1;
' LANGUAGE SQL;

There are two important points here: The “LANGUAGE” part which means that the function is written in pure SQL. The keyword “SETOF” which means that we want to return a whole set of the rows of t1. Once the function is created you can use it in SQL:

(postgres@[local]:5439) [ddl] > select select_all_from_t1();
 select_all_from_t1 
--------------------
 (1,1)
 (2,2)
 (3,3)
...

When you want to do something where it does not make sense to return anything you can do it by using the “VOID” keyword:

CREATE FUNCTION update_t1() RETURNS VOID AS '
  UPDATE t1
     SET a = 5
   WHERE a < 10
' LANGUAGE SQL;

When you execute this you do not get a result:

(postgres@[local]:5439) [ddl] > select update_t1();
 update_t1 
-----------
 NULL
(1 row)
(postgres@[local]:5439) [ddl] > select count(*) from t1 where a = 5;
 count 
-------
     9
(1 row)

What about parameters? You can do this as well:

CREATE FUNCTION do_the_math(anumber1 numeric, anumber2 numeric ) RETURNS numeric AS '
  SELECT do_the_math.anumber1 * do_the_math.anumber2;
' LANGUAGE SQL;

Execute it:

(postgres@[local]:5439) [ddl] > select do_the_math(1.1,1.2);
 do_the_math 
-------------
        1.32

Another great feature is that you can have a variable/dynamic amount of input parameters when you specify the input parameter as an array:

CREATE FUNCTION dynamic_input(VARIADIC arr numeric[]) RETURNS int AS $$
    SELECT array_length($1,1);
$$ LANGUAGE SQL;

(postgres@[local]:5439) [ddl] > select dynamic_input( 1,2,3,4 );
 dynamic_input 
---------------
             4

So far for the SQL functions. What can you do when you need more than SQL? Then you can use the so called “procedural language functions”. One of these which is available by default is PL/pgSQL:

(postgres@[local]:5439) [ddl] > \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

By using PL/pgSQL you can add control structures around your SQL very much as you can do it in PL/SQL (except that you cannot create packages).

CREATE FUNCTION f1(int,int) RETURNS text AS $$
DECLARE
    t_row t1%ROWTYPE;
    result text;
BEGIN
    SELECT * 
      INTO t_row
      FROM t1
     WHERE a = 99;
    IF t_row.b > 0
    THEN
        result := 'aaaaaa';
    ELSE
        result := 'bbbbbb';
    END IF;
    RETURN result;
END;
$$ LANGUAGE plpgsql;
(postgres@[local]:5439) [ddl] > select f1(1,1);
   f1   
--------
 aaaaaa

You can also use anonymous blocks:

(postgres@[local]:5439) [ddl] > DO $$
BEGIN
  FOR i IN 1..10
  LOOP
    raise notice 'blubb';
  END LOOP;
END$$ LANGUAGE plpgsql;
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
DO

Of course there is more than IF-THEN-ELSE which is documented here.

So by now we know two options to write functions in PostgreSQL. Is there more we can do? Of course: You prefer to write your functions in Perl?

(postgres@[local]:5439) [ddl] > create extension plperl;
CREATE EXTENSION
(postgres@[local]:5439) [ddl] > \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plperl  | 1.0     | pg_catalog | PL/Perl procedural language
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language


CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
    my ($x, $y) = @_;
    if (not defined $x) {
        return undef if not defined $y;
        return $y;
    }
    return $x if not defined $y;
    return $x if $x > $y;
    return $y;
$$ LANGUAGE plperl;

(postgres@[local]:5439) [ddl] > select perl_max(1,2);
 perl_max 
----------
        2

You prefer python?

(postgres@[local]:5439) [ddl] > create extension plpythonu;
CREATE EXTENSION
Time: 327.434 ms
(postgres@[local]:5439) [ddl] > \dx
                        List of installed extensions
   Name    | Version |   Schema   |               Description                
-----------+---------+------------+------------------------------------------
 plperl    | 1.0     | pg_catalog | PL/Perl procedural language
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 plpythonu | 1.0     | pg_catalog | PL/PythonU untrusted procedural language

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;

(postgres@[local]:5439) [ddl] > select pymax(1,1);
 pymax 
-------
     1

… or better TcL?

(postgres@[local]:5439) [ddl] > create extension pltclu;
CREATE EXTENSION
Time: 382.982 ms
(postgres@[local]:5439) [ddl] > \dx
                        List of installed extensions
   Name    | Version |   Schema   |               Description                
-----------+---------+------------+------------------------------------------
 plperl    | 1.0     | pg_catalog | PL/Perl procedural language
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 plpythonu | 1.0     | pg_catalog | PL/PythonU untrusted procedural language
 pltclu    | 1.0     | pg_catalog | PL/TclU untrusted procedural language

And these are only the default extensions. There is much more you can do:

  • Java
  • PHP
  • R
  • Ruby
  • Scheme
  • Unix shell

You see: PostgreSQL gives you the maximum flexibility :)

 

Cet article Can I do it with PostgreSQL? – 6 – Server programming est apparu en premier sur Blog dbi services.

Links for 2016-12-02 [del.icio.us]

Categories: DBA Blogs

Front and Center: The User Experience

Usable Apps - Fri, 2016-12-02 19:11
0 0 1 41 237 Oracle America, Inc. 1 1 277 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

I recently returned from the Web Summit in Lisbon, Portugal. This year over 50,000 people joined in the experience. The session tracks were many and chock-full of fascinating tech influencers and thinkers as well as creative technologies.

Web Summit 2016

0 0 1 12 71 Oracle America, Inc. 1 1 82 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

(photo: Karen Scipi (@KarenScipi))

What a difference a year makes! Thinking back to Web Summit 2015 and comparing that experience with Web Summit 2016, I observed a tide shift for the once sidelined majority: the user. This year’s Web Summit left me with a clear sense that no matter how alpha, beta, or established the product design is, the light shines brightly on today’s user.

0 0 1 101 581 Oracle America, Inc. 4 1 681 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

What resonated with me in all of the sessions that I attended was that the user experience in today’s world does indeed matter. Speakers and exhibitors alike tailored their pitches to consumers—users—of their product and spoke about how their product designs reflect and incorporate the unique needs of their users.

Oracle’s own Jeremy Ashley (@jrwashley), Oracle Group Vice President, Applications User Experience and Oracle Thought Leader, focused on one aspect of user experience design in his SaaS Monster debate session: The customer is not always right.

 Karen Scipi)

Jeremy Ashley, Oracle Group Vice President, Applications User Experience (photo: Karen Scipi)

The perspective that Jeremy argued was nicely captured by Oracle Vice President Killian Evers (@keversca):

Jeremy Ashley (Oracle), Andy O’Donoghue (The Gadget Buzz, TV3), and David Gurle (Symphony)

Jeremy Ashley (Oracle), Andy O’Donoghue (The Gadget Buzz, TV3), and David Gurle (Symphony)

0 0 1 13 79 Oracle America, Inc. 1 1 91 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

0 0 1 138 788 Oracle America, Inc. 6 1 925 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

Jeremy presented a key differentiator for Oracle user experience design: “It’s not what the customer says, it’s what the customer wants and needs essentially. The true designer, the true innovator spends as much time in investigation of that than they do in providing the solution.”

And do we. We do ethnographic studies. We also spend a great deal of time with our customers: we listen, we watch, we observe. We follow our customers around and observe where they work—the kinds of buildings they work in, their offices, their cubicles—as well as how they work, and with whom and how they engage with others.

Then we take that knowledge and design user experiences for our solutions—our platform, tools, and applications—that solve customer problems and empower customers to make the solutions their own, to enhance them, and enable them to innovate on their own.

Jeremy Ashley (Oracle), Andy O’Donoghue (The Gadget Buzz, TV3), and David Gurle (Symphony)

Jeremy Ashley (Oracle), Andy O’Donoghue (The Gadget Buzz, TV3), and David Gurle (Symphony)

0 0 1 13 79 Oracle America, Inc. 1 1 91 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

0 0 1 104 594 Oracle America, Inc. 4 1 697 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

Fundamentally each product we deliver should meet a real need. As Jeremy shared: “Every time you design for a large complicated system, there are a number of ways to go about it. . . . . What we [Oracle Applications User Experience] do is we go out to the customer and we identify the essence of the problem.” For example, “Who is the person I can call next that I will make the most money from for the least effort? If it doesn’t address that core essence, it doesn’t matter. Spending that time to understand what the essence is of the problem, that thing that will allow them to participate is a motivation. Identifying not the stories that people tell, but the truth underlying those stories.”

Jeremy Ashley (Oracle), Andy O’Donoghue (The Gadget Buzz, TV3), and David Gurle (Symphony)

Jeremy Ashley (Oracle), Andy O’Donoghue (The Gadget Buzz, TV3), and David Gurle (Symphony)

0 0 1 13 79 Oracle America, Inc. 1 1 91 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

0 0 1 121 690 Oracle America, Inc. 5 1 810 14.0 Normal 0 false false false EN-US JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Cambria; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin;}

We work hard to advocate for our customers and to produce the right solutions for them. We believe that our solutions should simply empower the lives of those who use them. We embed great, consistent user experience throughout our solutions.

For more of Oracle GVP Jeremy Ashley’s sessions at Web Summit:

My Talks/Sessions at UKOUG Tech16

Kuassi Mensah - Fri, 2016-12-02 13:53
Hi guys,

If you plan to attend UKOUG Tech16, please check out my talks.sessions

  1.  Sunday 12/04 16:10  New JDBC & UCP Perf, Scalability & HA features in Oracle Database 12cR2 @
  2. Monday 12/05 17:55 A RESTful MicroService for JSON Processing in Oracle Database 12c R2 @  
  3. Wednesday 12/07 8:50 Hadoop, Spark & Flink Explained to Oracle DBAs & why They Should Care @ https://t.co/7CGLchAYAF
  4. Wednesday 12/07 15:10 Integrate BigData with Master Data: Oracle database table as Hadoop Datasource @
See you there



uninstall java on linux

Learn DB Concepts with me... - Fri, 2016-12-02 12:43
If you are not sure of what the dependent packages that might be blocking java then you can also use yum remove jdk*
This will also take care of dependent rpms.

[root@linux06 usr]# yum remove jdk1.8.0_111-1.8.0_111-fcs.i586

Loaded plugins: refresh-packagekit, security
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package jdk1.8.0_111.i586 2000:1.8.0_111-fcs will be erased
--> Processing Dependency: java for package: jna-3.2.4-2.el6.x86_64
--> Running transaction check
---> Package jna.x86_64 0:3.2.4-2.el6 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================
 Package           Arch        Version                 Repository                                                Size
======================================================================================================================
Removing:
 jdk1.8.0_111      i586        2000:1.8.0_111-fcs      @/jdk-8u111-linux-i586                                   259 M
Removing for dependencies:
 jna               x86_64      3.2.4-2.el6             @anaconda-OracleLinuxServer-201311252058.x86_64/6.5      298 k

Transaction Summary
======================================================================================================================
Remove        2 Package(s)

Installed size: 259 M
Is this ok [y/N]: Y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Erasing    : jna-3.2.4-2.el6.x86_64                                                                             1/2
  Erasing    : 2000:jdk1.8.0_111-1.8.0_111-fcs.i586                                                               2/2
  Verifying  : 2000:jdk1.8.0_111-1.8.0_111-fcs.i586                                                               1/2
  Verifying  : jna-3.2.4-2.el6.x86_64                                                                             2/2

Removed:
  jdk1.8.0_111.i586 2000:1.8.0_111-fcs                                                                               

Dependency Removed:
  jna.x86_64 0:3.2.4-2.el6                                                                                           

Complete!
Categories: DBA Blogs

New York OUG Winter Event

Gerger Consulting - Fri, 2016-12-02 11:55
On December 7th, Gitora founder Yalim Gerger will be at the NYOUG Winter Event to talk about version control of PL/SQL using Git. Click here for the full agenda. We hope to see you there!


Categories: Development

Data Migration

Tom Kyte - Fri, 2016-12-02 09:26
Hi! I have few questions regarding data migration/replication from Oracle: 1. We have a column (of datatype NUMBER) in Oracle table which stores Transaction AMOUNT in the format: 10,52 (instead of 10.52). When we migrate data (or replicate data...
Categories: DBA Blogs

Oracle dynamic database link from variables

Tom Kyte - Fri, 2016-12-02 09:26
hi tom i am facing this query in dynamic sql using dblinks plz simplifie this query and explain me sqlquery := 'select sv.display_value status_id,s.parent_account_no,s.emf_config_id,cm.mkt_code' || ' from service@' ...
Categories: DBA Blogs

Bug in recursive subquery factoring ?

Tom Kyte - Fri, 2016-12-02 09:26
Hi AskTom team. I was playing with recursive subqueries and tried to do some string manipulations, but I noticed something strange: <code>SQL> WITH r(str, outtxt, outtxt2) AS ( 2 SELECT 'aabbba', '', '' FROM dual 3 UNION ALL 4 ...
Categories: DBA Blogs

Pivot Rows into Columns

Tom Kyte - Fri, 2016-12-02 09:26
Hi Tom, I have a Query: SELECT A.SALES_ORDER_COMPANY as A_1, A.SALES_ORDER_NUMBER as A_2, A.SOURCE_SALES_ORDER_TYPE as A_3, A.SALES_LINE_NUMBER as A_4, A.SOURCE_SALESPERSON1_CODE as B, A.COMMISSION_AMT as C FROM SLS_PHI_BROKER_COMM_STG A W...
Categories: DBA Blogs

'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 11.2.0.4 – 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 11.2.0.4 to either 11.2.0.4 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
rem     Script:         histogram_change_11204.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2016
rem     Purpose:
rem

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

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

select
        column_name, endpoint_number, endpoint_value, to_char(endpoint_value,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
from
        user_tab_histograms
where
        table_name = 'T1'
order by
        column_name,
        endpoint_number
;

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

Results 11.1.0.7
----------------
COLUMN_NAME          ENDPOINT_NUMBER                                   ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,'XXXXXXXXX
-------------------- --------------- ------------------------------------------------ ---------------------------------
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

Results 11.2.0.4
----------------
COLUMN_NAME          ENDPOINT_NUMBER                                   ENDPOINT_VALUE TO_CHAR(ENDPOINT_VALUE,'XXXXXXXXX
-------------------- --------------- ------------------------------------------------ ---------------------------------
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 11.1.0.7 you see “4e2020202020”, “592020202020” – that’s ASCII ‘N’ and ‘Y’ respectively, padded to 6 characters with spaces. In 11.2.0.4 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 11.1.0.7 the optimizer will find a histogram entry for c1 = ‘Y’ and produce a cardinality of 900; if you upgrade the database to 11.2.0.4 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).

Footnote

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 12.1.0.2 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 12.1.0.2 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.   

References

Related Articles

Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator