Feed aggregator

PLS_INTEGER versus NUMBER versus "dynamic types"

Tom Kyte - Thu, 2019-06-06 10:06
It has been suggested to me that I use PL/SQL declarations like PROCEDURE foo ( p_id IN PLS_INTEGER )... instead of PROCEDURE foo (p_id IN NUMBER ) ... or PROCEDURE foo (p_id IN mytable.my_id%TYPE ) I've always preferred the last option, si...
Categories: DBA Blogs

Using MERGE to update data 2 times

Tom Kyte - Thu, 2019-06-06 10:06
I am having a Full dump of 1m records arriving every day.I need to obtain this set into STG. There is soft delete records thus when I compared between Full dump(source) and STG(target), the records in STG always larger than source. I have researc...
Categories: DBA Blogs

Function comparing dates in a range

Tom Kyte - Thu, 2019-06-06 10:06
I have a function that will extract records from a databaase with specific dates and times. One of the parameters passed is a date. This functions runs twice a day On of the parameters passed is AM or PM). My functions works for morning (times are ...
Categories: DBA Blogs

Pivot with dynamic dates column

Tom Kyte - Thu, 2019-06-06 10:06
Hi Tom, I want to show the absence of my people in an APEX form / editable grid. The absence is shown for every day and the day column should be generated automatically. Current SQL code: <code>with rws as ( select person.name, absence....
Categories: DBA Blogs

Synchronizing database sequences during manual data replication

Tom Kyte - Thu, 2019-06-06 10:06
Hi Tom, Experts, I am in a need to replicate manually all objects from one schema to another (manually, because my schemas, both source and target, reside in the cloud and I have no access to any OS level utility, nor sql*net access to the databas...
Categories: DBA Blogs

Filter Incremental Data in Oracle DB

Tom Kyte - Thu, 2019-06-06 10:06
Hi TOM, I have requirement to filter differential/Incremental data in Oracle Database. Primary key that I use is composite key (combination of 2 columns). They are in such a shape that I am unable to prepare 'where' condition that can help me to id...
Categories: DBA Blogs

Connection Storm with Inactive sessions on ORACLE RAC

Tom Kyte - Thu, 2019-06-06 10:06
Hi All, We recently migrated from Solaris to AIX and moved to Oracle RAC : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for...
Categories: DBA Blogs

Using Oracle best way

Tom Kyte - Thu, 2019-06-06 10:06
Hi, We have set of Mainframe programs which connects to Oracle database and queries set of tables. Our application and DB servers are in 2 different locations (which we cannot change at the moment), which i believe is causing considerable delays w...
Categories: DBA Blogs

PFCLATK - Audit Trail Toolkit - Checksums

Pete Finnigan - Thu, 2019-06-06 09:46
We have a toolkit called PFCLATK that is used in customer engagements to assist our customers to create comprehensive and useful audit trails for their databases. The toolkit is used in consulting engagements at the moment but will be adding....[Read More]

Posted by Pete On 06/06/19 At 03:08 PM

Categories: Security Blogs

Discover target database 18c with EM12c

Yann Neuhaus - Thu, 2019-06-06 08:34

Working on Enterprise Manager 12.1.0.4 version at a client’s site, we would like to know if oracle database target in 18c version could be discovered and monitored, even if Enterprise Manager 12.1.0.4 does not support Oracle 18c database targets.

Installing the agent 12c on the target host did not cause any problem, the oracle database 18c discovery ran successfully, but the database was seen as down in the Enterprise Manager 12.1.0.4 console.

We tried several tricks without any positive results, but running the following command shows us that this was a connection problem:


oracle@em12c:/home/oracle/:> emctl getmetric agent DB18,oracle_database,Response
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.
All rights reserved.
Status,State,oraerr,Archiver,DatabaseStatus,ActiveState0,UNKNOWN,
Failed to connect: java.sql.SQLException: 
ORA-28040: No matching authentication protocol,UNKNOWN,UNKNOWN,UNKNOWN

With Oracle 18c, the default value for SQLNET.ALLOWED_LOGON_VERSION parameter is 12, it means that database clients using pre-12c jdbc thin drivers cannot authenticate to 18c database servers.

The workaround is to add in the database server sqlnet.ora the following lines:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11

We restart the database and the agent, and the Oracle database 18c is displayed up and running in Enterprise Manager 12.1.0.4:

Some more tests showed us that everything is working fine: incident management, performance, top activity ..aso

Nevertheless do not forget that oracle database target version 18c are not supported with Enterprise Manager 12.1.0.4. I will recommand to use the last Enterprise Manager 13.3 last version in order to administer and monitor Oracle database 18c.

Cet article Discover target database 18c with EM12c est apparu en premier sur Blog dbi services.

Get Up to Speed with Oracle ACEs on the Kscope Database Track

OTN TechBlog - Thu, 2019-06-06 08:03
All Aboard for Database Expertise...

This second post in a series on Kscope 2019 sessions presented by members of the Oracle ACE program focuses on the database track. Kscope 2019 arrives on time, June 23-27 in Seattle. Click here for information and registration.

Click the session titles below for time, date, and location information for each session.

We'll cover sessions in the other tracks in upcoming posts. Stay tuned!

 

Oracle ACE Directors

Oracle ACE Director Alex NuijtenAlex Nuijten
Director, Senior Oracle Developer, allAPEX
Oosterhout, Netherlands

 

Oracle ACE Director Debra LilleyDebra Lilley
Associate Director, Accenture
Belfast, United Kingdom

 

Oracle ACE Director Dimitri GielisDimitri Gielis
Director, APEX R&D
Leuven, Belgium

 

Oracle ACE Director Francisco AlvarezFrancisco Munoz Alvarez
CEO, CloudDB
Sydney, Australia

 

Oracle ACE Director Heli HelskyahoHeli Helskyaho
CEO, Miracle Finland Oy
Finland

 

Oracle ACE Director Jim CzuprynskiJim Czuprynski
Senior Enterprise Data Architect, Viscosity North America
Bartlett, Illinois

 

Oracle ACE Director Kim Berg HansenKim Berg Hansen
Senior Consultant, Trivadis
Funen, Denmark

 

Oracle ACE Director Martin Giffy D'SouzaMartin Giffy D’Souza
Director of Innovation, Insum Solutions
Calgary, Alberta, Canada

 

Oracle ACE Director Mia UrmanMia Urman
CEO, AuraPlayer Ltd
Brookline, Massachusetts

 

Oracle ACE Director Patrick BarelPatrick Barel
Sr. Oracle Developer, Alliander via Qualogy
Haarlem, Netherlands

 

Oracle ACE Director Peter KoletzkePeter Koletzke
Technical Director, Principal Instructor
Independent Consultant

 

Oracle ACE Director Richard NiemiecRichard Niemiec
Chief Innovation Officer, Viscosity North America
Chicago, Illinois

 
Oracle ACEs

Oracle ACE Dani SchniderDani Schnider
Senior Principal Consultant, Trivadis AG
Zurich, Switzerland

 

Oracle ACE Holger FriedrichHolger Friedrich
CTO, sumIT AG
Zurich, Switzerland

 

Oracle ACE Liron AmitziLiron Amitzi
Senior Database Consultant, Self Employed
Vancouver, Canada

 

Oracle ACE Philipp SalvisbergPhilipp Salvisberg
Senior Principal Consultant, Trivadis AG
Zurich, Switzerland

 

Oracle ACE Robert MarzRobert Marz
Principal Technical Architect, its-people GmbH
Frankfurt, Germany

 
Oracle ACE Associates

Oracle ACE Associate Alfredo AbateAlfredo Abate
Senior Oracle Systems Architect, Brake Parts Inc LLC
McHenry, Illinois

 

Oracle ACE Associate Eugene FedorenkoEugene Fedorenko
Senior Architect, Flexagon
De Pere, Wisconsin

 
Additional Resources

PostgreSQL partitioning (6): Attaching and detaching partitions

Yann Neuhaus - Thu, 2019-06-06 01:00

Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL here they are:

  1. PostgreSQL partitioning (1): Preparing the data set
  2. PostgreSQL partitioning (2): Range partitioning
  3. PostgreSQL partitioning (3): List partitioning
  4. PostgreSQL partitioning (4) : Hash partitioning
  5. PostgreSQL partitioning (5): Partition pruning

From time to time it might be required that you attach a partition to an existing partitioned table. A reason for that might be that you partitioned by year and suddenly you get data for the next year because your data is based on imported data from an external source. Of course you could just create a new partition for that and then load the data but there is a more elegant way of doing that.

Our range partitioned table currently has partitions for the years 2012 to 2020:

postgres=# \d+ traffic_violations_p
                                      Partitioned table "public.traffic_violations_p"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2012 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01'),
            traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            traffic_violations_p_default DEFAULT

Lets assume we suddenly get data for the year 2021 and we want that data to be part of the range partitioned table. If we keep the partitioned table as it is, new data would go to the default partition and this is not what we want. What we can do is to create a regular table with exactly the same structure as the partitioned table. With PostgreSQL’s create table command you have the “like_option” which is very handy for this use case:

postgres=# create table traffic_violations_p_2021 ( like traffic_violations_p including all );
CREATE TABLE
postgres=# \d traffic_violations_p_2021
                     Table "public.traffic_violations_p_2021"
         Column          |          Type          | Collation | Nullable | Default 
-------------------------+------------------------+-----------+----------+---------
 seqid                   | text                   |           |          | 
 date_of_stop            | date                   |           |          | 
 time_of_stop            | time without time zone |           |          | 
 agency                  | text                   |           |          | 
 subagency               | text                   |           |          | 
 description             | text                   |           |          | 
 location                | text                   |           |          | 
 latitude                | numeric                |           |          | 
 longitude               | numeric                |           |          | 
 accident                | text                   |           |          | 
 belts                   | boolean                |           |          | 
 personal_injury         | boolean                |           |          | 
 property_damage         | boolean                |           |          | 
 fatal                   | boolean                |           |          | 
 commercial_license      | boolean                |           |          | 
 hazmat                  | boolean                |           |          | 
 commercial_vehicle      | boolean                |           |          | 
 alcohol                 | boolean                |           |          | 
 workzone                | boolean                |           |          | 
 state                   | text                   |           |          | 
 vehicletype             | text                   |           |          | 
 year                    | smallint               |           |          | 
 make                    | text                   |           |          | 
 model                   | text                   |           |          | 
 color                   | text                   |           |          | 
 violation_type          | text                   |           |          | 
 charge                  | text                   |           |          | 
 article                 | text                   |           |          | 
 contributed_to_accident | boolean                |           |          | 
 race                    | text                   |           |          | 
 gender                  | text                   |           |          | 
 driver_city             | text                   |           |          | 
 driver_state            | text                   |           |          | 
 dl_state                | text                   |           |          | 
 arrest_type             | text                   |           |          | 
 geolocation             | point                  |           |          | 
 council_districts       | smallint               |           |          | 
 councils                | smallint               |           |          | 
 communities             | smallint               |           |          | 
 zip_codes               | smallint               |           |          | 
 municipalities          | smallint               |           |          | 

For now this table has no relational at all to the partitioned table, it just has the very same structure, that’s it. Lets populate the new table with some data for 2021:

insert into traffic_violations_p_2021 (date_of_stop)
       select * from generate_series ( date('01.01.2021')
                                     , date('12.31.2021')
                                     , interval '1 day');

Now that we have 365 rows in the new table we can attach it to the partitioned table (of course you could already attach it before loading the data as well):

alter table traffic_violations_p
      attach partition traffic_violations_p_2021
      for values from ('2021-01-01') to ('2022-01-01');

So you can prepare the whole new data without touching the partitioned table. Once you are happy with it a simple attach command is enough and the partitioned table contains the new partition holding the data:

postgres=# \d+ traffic_violations_p
                                      Partitioned table "public.traffic_violations_p"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2012 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01'),
            traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
            traffic_violations_p_default DEFAULT

Having the data for 2021 it might not be required anymore to have the data for 2012. Without partitioning you would need to delete from the table for getting rid of those rows. With partitioning you can detach the partition from the partitioned table:

postgres=# alter table traffic_violations_p detach partition traffic_violations_p_2012;
ALTER TABLE
postgres=# \d+ traffic_violations_p
                                      Partitioned table "public.traffic_violations_p"
         Column          |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 seqid                   | text                   |           |          |         | extended |              | 
 date_of_stop            | date                   |           |          |         | plain    |              | 
 time_of_stop            | time without time zone |           |          |         | plain    |              | 
 agency                  | text                   |           |          |         | extended |              | 
 subagency               | text                   |           |          |         | extended |              | 
 description             | text                   |           |          |         | extended |              | 
 location                | text                   |           |          |         | extended |              | 
 latitude                | numeric                |           |          |         | main     |              | 
 longitude               | numeric                |           |          |         | main     |              | 
 accident                | text                   |           |          |         | extended |              | 
 belts                   | boolean                |           |          |         | plain    |              | 
 personal_injury         | boolean                |           |          |         | plain    |              | 
 property_damage         | boolean                |           |          |         | plain    |              | 
 fatal                   | boolean                |           |          |         | plain    |              | 
 commercial_license      | boolean                |           |          |         | plain    |              | 
 hazmat                  | boolean                |           |          |         | plain    |              | 
 commercial_vehicle      | boolean                |           |          |         | plain    |              | 
 alcohol                 | boolean                |           |          |         | plain    |              | 
 workzone                | boolean                |           |          |         | plain    |              | 
 state                   | text                   |           |          |         | extended |              | 
 vehicletype             | text                   |           |          |         | extended |              | 
 year                    | smallint               |           |          |         | plain    |              | 
 make                    | text                   |           |          |         | extended |              | 
 model                   | text                   |           |          |         | extended |              | 
 color                   | text                   |           |          |         | extended |              | 
 violation_type          | text                   |           |          |         | extended |              | 
 charge                  | text                   |           |          |         | extended |              | 
 article                 | text                   |           |          |         | extended |              | 
 contributed_to_accident | boolean                |           |          |         | plain    |              | 
 race                    | text                   |           |          |         | extended |              | 
 gender                  | text                   |           |          |         | extended |              | 
 driver_city             | text                   |           |          |         | extended |              | 
 driver_state            | text                   |           |          |         | extended |              | 
 dl_state                | text                   |           |          |         | extended |              | 
 arrest_type             | text                   |           |          |         | extended |              | 
 geolocation             | point                  |           |          |         | plain    |              | 
 council_districts       | smallint               |           |          |         | plain    |              | 
 councils                | smallint               |           |          |         | plain    |              | 
 communities             | smallint               |           |          |         | plain    |              | 
 zip_codes               | smallint               |           |          |         | plain    |              | 
 municipalities          | smallint               |           |          |         | plain    |              | 
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
            traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
            traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
            traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
            traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
            traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
            traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
            traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
            traffic_violations_p_default DEFAULT

Whatever you want to do with the data from now on: The old partition became a regular table and you can either drop, truncate or move it to low cost storage for archiving.
This was attaching and detaching of partitions. In the next post we’ll look at indexing and constraints when it comes to partitioned tables.

Cet article PostgreSQL partitioning (6): Attaching and detaching partitions est apparu en premier sur Blog dbi services.

Local Install rlwrap for OEL 7.6

Michael Dinh - Wed, 2019-06-05 19:13

Installing rlwrap 7.6, requires python34 local install

yum install rlwrap

[root@SLC02PNY ~]# yum install rlwrap
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package rlwrap.x86_64 0:0.43-2.el7 will be installed
--> Processing Dependency: perl(Data::Dumper) for package: rlwrap-0.43-2.el7.x86_64
--> Processing Dependency: /usr/bin/python3.4 for package: rlwrap-0.43-2.el7.x86_64

****************************************************************************************************
Package python34 is obsoleted by python36, but obsoleting package does not provide for requirements
****************************************************************************************************

--> Running transaction check
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
---> Package rlwrap.x86_64 0:0.43-2.el7 will be installed
--> Processing Dependency: /usr/bin/python3.4 for package: rlwrap-0.43-2.el7.x86_64
Package python34 is obsoleted by python36, but obsoleting package does not provide for requirements
--> Processing Dependency: /usr/bin/python3.4 for package: rlwrap-0.43-2.el7.x86_64
Package python34 is obsoleted by python36, but obsoleting package does not provide for requirements
--> Finished Dependency Resolution

yum install python34

root@SLC02PNY ~]# yum install python34
Loaded plugins: ulninfo

****************************************************************************************************
Package python34 is obsoleted by python36, trying to install python36-3.6.8-1.el7.x86_64 instead
****************************************************************************************************

Resolving Dependencies
--> Running transaction check
---> Package python36.x86_64 0:3.6.8-1.el7 will be installed
--> Processing Dependency: python36-libs(x86-64) = 3.6.8-1.el7 for package: python36-3.6.8-1.el7.x86_64
--> Processing Dependency: libpython3.6m.so.1.0()(64bit) for package: python36-3.6.8-1.el7.x86_64
--> Running transaction check
---> Package python36-libs.x86_64 0:3.6.8-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                         Arch                     Version                        Repository                            Size
====================================================================================================================================
Installing:
 python36                        x86_64                   3.6.8-1.el7                    ol7_developer_EPEL                    66 k
Installing for dependencies:
 python36-libs                   x86_64                   3.6.8-1.el7                    ol7_developer_EPEL                   8.6 M

Transaction Summary
====================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 8.6 M
Installed size: 36 M
Is this ok [y/d/N]: n

cat /etc/system-release

[root@ADC6160274 ~]# cat /etc/system-release
Oracle Linux Server release 7.6
[root@ADC6160274 ~]#

yumdownloader python34-3.4.5-4.el7.x86_64

[root@ADC6160274 ~]# yumdownloader python34-3.4.5-4.el7.x86_64
python34-3.4.5-4.el7.x86_64.rpm                                                                              |  50 kB  00:00:00

yumdownloader python34-libs-3.4.5-4.el7.x86_64
[root@ADC6160274 ~]# yumdownloader python34-libs-3.4.5-4.el7.x86_64
python34-libs-3.4.5-4.el7.x86_64.rpm                                                                         | 8.2 MB  00:00:01

yum localinstall python34-libs-3.4.5-4.el7.x86_64.rpm python34-3.4.5-4.el7.x86_64.rpm

[root@ADC6160274 ~]# yum localinstall python34-libs-3.4.5-4.el7.x86_64.rpm python34-3.4.5-4.el7.x86_64.rpm
Loaded plugins: ulninfo
Examining python34-libs-3.4.5-4.el7.x86_64.rpm: python34-libs-3.4.5-4.el7.x86_64
Marking python34-libs-3.4.5-4.el7.x86_64.rpm to be installed
Examining python34-3.4.5-4.el7.x86_64.rpm: python34-3.4.5-4.el7.x86_64
Marking python34-3.4.5-4.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package python34.x86_64 0:3.4.5-4.el7 will be installed
---> Package python34-libs.x86_64 0:3.4.5-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                     Arch                 Version                     Repository                                       Size
====================================================================================================================================
Installing:
 python34                    x86_64               3.4.5-4.el7                 /python34-3.4.5-4.el7.x86_64                     36 k
 python34-libs               x86_64               3.4.5-4.el7                 /python34-libs-3.4.5-4.el7.x86_64                29 M

Transaction Summary
====================================================================================================================================
Install  2 Packages

Total size: 29 M
Installed size: 29 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python34-libs-3.4.5-4.el7.x86_64                                                                                 1/2
  Installing : python34-3.4.5-4.el7.x86_64                                                                                      2/2
  Verifying  : python34-3.4.5-4.el7.x86_64                                                                                      1/2
  Verifying  : python34-libs-3.4.5-4.el7.x86_64                                                                                 2/2

Installed:
  python34.x86_64 0:3.4.5-4.el7                                  python34-libs.x86_64 0:3.4.5-4.el7

Complete!

yum install rlwrap

[root@ADC6160274 ~]# yum install rlwrap
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package rlwrap.x86_64 0:0.43-2.el7 will be installed
--> Processing Dependency: perl(Data::Dumper) for package: rlwrap-0.43-2.el7.x86_64
--> Running transaction check
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                           Arch                    Version                        Repository                           Size
====================================================================================================================================
Installing:
 rlwrap                            x86_64                  0.43-2.el7                     ol7_developer_EPEL                  118 k
Installing for dependencies:
 perl-Data-Dumper                  x86_64                  2.145-3.el7                    ol7_latest                           47 k

Transaction Summary
====================================================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 165 k
Installed size: 378 k
Is this ok [y/d/N]: y
Downloading packages:
(1/2): perl-Data-Dumper-2.145-3.el7.x86_64.rpm                                                               |  47 kB  00:00:00
(2/2): rlwrap-0.43-2.el7.x86_64.rpm                                                                          | 118 kB  00:00:00
------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                               311 kB/s | 165 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-Data-Dumper-2.145-3.el7.x86_64                                                                              1/2
  Installing : rlwrap-0.43-2.el7.x86_64                                                                                         2/2
  Verifying  : perl-Data-Dumper-2.145-3.el7.x86_64                                                                              1/2
  Verifying  : rlwrap-0.43-2.el7.x86_64                                                                                         2/2

Installed:
  rlwrap.x86_64 0:0.43-2.el7

Dependency Installed:
  perl-Data-Dumper.x86_64 0:2.145-3.el7

Complete!
[root@ADC6160274 ~]#

Simplifying Troubleshooting Process for Oracle Service Cloud Admins and Agents (BUI Version)

OTN TechBlog - Wed, 2019-06-05 11:39
What is Troubleshoot Extension?

If you are an Oracle Service Cloud administrator, you are likely the first person in your company your agents will go to when they are experiencing some error with their Oracle Service Cloud. Troubleshooting errors can quickly spawn into a bigger investigative effort to identify the root cause (e.g., network issue, configuration issue, training, defect), but the troubleshooting process usually starts with getting the right details about the error from your agents. Your agents are already frustrated because an error could be impeding their work and impacting their metrics. And now you’re asking them to provide detailed information on the error: steps to reproduce this issue, workstation information or data traffic, much of which they aren’t familiar with.

Recognizing the dynamics of this common scenario, we decided to walk a mile in both your shoes, as the administrator, and your agents’ shoes. As a result of this experience, we came up with the idea of automating the process for gathering the needed information from agents instead of requiring the administrators and agents to try and overcome the current challenges. We developed a sample code named as “Troubleshoot Extension” for the Oracle Service Cloud Browser UI (Similar what we did before in Agent Desktop) to address this need.

The “Troubleshoot Extension Sample Code” was created to automatically capture information, such as browser navigator, agent basic information and console log, error, warn and debug information in one fell swoop instead of requiring agents to install or use different tools outside of Oracle Service Cloud. All the agents need to do is push the "start" button (located in the status bar) and push the “stop” when the agents have completed all steps to reproduce the error.

The sample code is available to download in the bottom of the article, but before going further, I'd like to encourage you to understand the importance of web console to troubleshoot a web-based application so that the approach used here makes more sense.

Why we are sharing this sample code?
  • First, because we believe that by automating this process, we can simplify your communication with agents experiencing errors, accelerate the potential solution for the process.
  • Second, Agents don't need to understand how to gather technical information for troubleshooting.
  • Lastly, because through this sample code, we can achieve:
    • sharing a complex sample code where you can apply and reuse for other needs;
    • sharing a start point where you can enhance this troubleshooting tool to adapt to your requirement.
How does Troubleshoot Extension work?

Let’s take a more in-depth look at what the sample code delivers. The sample code is implementing an Extension StatusBar with a start and stop button, plus a timer that provides the duration of how long your agent is capturing the steps to reproduce.

By clicking on the start button, the troubleshoot extension will automatically set the log level (see developer tools logs for more information). Everything that is registered in the web console will be captured.

Once you have finished capturing your steps to reproduce click on the stop button and the Troubleshoot Extension takes care of compile all web console information to present to you in a window modal.

**This extension is not compatible with IE11. 

The window modal presents a friendly version of the result captured, but you can save the result by clicking on the download button on top. The download button will create a text file with the information that was captured in and can share with your technical team to help them on the investigation.

The information capture is read-only, but there is an additional field on top where your agent can add more information such as steps to reproduce or any other comment.

How to install?

1. Upload the extension code as a BUI extension.

  1. Download the extension file.
  2. Open your Admin console and upload the zip file as Agent Browser UI Extensions.
  3. Name your extension. E.g.: Troubleshoot Extension. 
  4. Select "Console" as an extension type.
  5. Select  TsExnteion/ViewModel/js/ts-statusbar.js as the Init File.
  6. Go to profile and assign this extension to the profile you'd like to use this extension.

2. Create custom configuration settings.

  1. Go to Site Configuration > Configuration Settings.
  2. Click on New > Text to create a custom configuration setting.
  3. The Key name is "CUSTOM_CFG_TS".
  4. The site value is the following JSON: {"debugLevel":3, "performance": true}

Ultimately, this solution should simplify your communication with agents experiencing errors, accelerate troubleshooting by having the required information in one easy step, and save everyone time and frustration that surrounds these issues.

The source code is available here for download, and you take advantage to build a better troubleshoot model integrated into your Oracle Service Cloud. 

If you are a developer and want to contribute to this sample code, you are welcome to join the Github.

Simplifying Troubleshooting Process for Oracle Service Cloud Admins and Agents (.NET Add-In Version)

OTN TechBlog - Wed, 2019-06-05 11:31

If you are an Oracle Service Cloud administrator, you are likely the first person in your company your agents will go to when they are experiencing some error with their agent desktop. Troubleshooting errors can quickly spawn into a bigger investigative effort to identify the root cause (e.g., network issue, configuration issue, training, defect), but the troubleshooting process usually starts with getting the right details about the error from your agents. Your agents are already frustrated because an error could be impeding their work and impacting their metrics. And now you’re asking them to provide detailed information on the error: steps to reproduce this issue, workstation information or data traffic, much of which they aren’t familiar with.

Recognizing the dynamics of this common scenario, we decided to walk a mile in both your shoes, as the administrator, and your agents’ shoes. As a result of this experience, we came up with the idea of automating the process for gathering the needed information from agents instead of requiring the administrators and agents to try and overcome the current challenges. We developed a sample code named as “Troubleshoot Add-In” for the Oracle Service Cloud Agent Desktop (For the Browser UI version) to address this need.

The “Troubleshoot Add-In Sample Code” was created to automatically capture information, such as steps-to-reproduce, and workstation information in one fell swoop instead of requiring agents to install or use different tools outside of Oracle Service Cloud. All that agents need to do is push the "start" button (located in the status bar) and push the “stop” when the agents have completed all steps to reproduce the error.

Take a look at these two scenarios and see how the “Troubleshoot Add-In” can improve communication between administrators and agents who are encountering an issue.

Let’s take a more in-depth look at what the sample code delivers. The sample code is implementing an Extension StatusBar with a start and stop button, plus a timer that provides the duration of how long your agent is capturing the steps to reproduce.

How Troubleshoot Add-In Sample Code Works

By clicking on the start button, a friendly loading form shows up. You can personalize your message by changing a ServerConfigProperty used in this sample code. At this moment, the sample code will start a standard windows application called PSR (Problem Steps to Reproduce). As a sample code, this code is limited to use a windows standard application and from here, I would encourage you as a developer to expand this sample code for your needs.

For instance, If you want to get rid of Fiddler installation needs, you can use Fiddler Core and embed their .dll to capture data traffic plus steps to reproduce. Check out for Fiddler Demo Code and try yourself, I am pretty sure it will be useful.

Once you have finished capturing your steps to reproduce click on the stop button and the sample code closes PSR and starts to capture workstation information such as .NET version, windows version, capacity. Also, you can run the OSCinfo.bat as described in the answer 2412. This sample code is providing this option if you need to capture more information as ping and traces. See for the ServerConfigProperty options.

Lastly, a message pops up to inform the agent where the results were saved. The sample code takes care of compile and compresses all files resulted from PSR and Workstation Information in a local folder or wherever folder you have specified in a ServerConfigProperty.

Okay, this is true, I like to use ServerConfigProperty and there is more fo them. With that, you can set up your add-in without changing your code. 

Ultimately, this solution should simplify your communication with agents experiencing errors, accelerate troubleshooting by having the required information in one easy step, and save everyone time and frustration that surrounds these issues.

The source code is available here for download, and you take advantage to build a better troubleshoot model integrated into your Oracle Service Cloud. 

If you are a developer and want to contribute to this sample code, you are welcome to join the Github.

Microsoft and Oracle to Interconnect Microsoft Azure and Oracle Cloud

Oracle Press Releases - Wed, 2019-06-05 07:00
Press Release
Microsoft and Oracle to Interconnect Microsoft Azure and Oracle Cloud

REDMOND, Wash., and REDWOOD SHORES, Calif.—Jun 5, 2019

Microsoft Corp. and Oracle Corp. on Wednesday announced a cloud interoperability partnership enabling customers to migrate and run mission-critical enterprise workloads across Microsoft Azure and Oracle Cloud. Enterprises can now seamlessly connect Azure services, like Analytics and AI, to Oracle Cloud services, like Autonomous Database. By enabling customers to run one part of a workload within Azure and another part of the same workload within the Oracle Cloud, the partnership delivers a highly optimized, best-of-both-clouds experience. Taken together, Azure and Oracle Cloud offer customers a one-stop shop for all the cloud services and applications they need to run their entire business.

Connecting Azure and Oracle Cloud through network and identity interoperability makes lift-and-improve migrations seamless. This partnership delivers direct, fast and highly reliable network connectivity between two clouds, while continuing to provide first-class customer service and support that enterprises have come to expect from the two companies. In addition to providing interoperability for customers running Oracle software on Oracle Cloud and Microsoft software on Azure, it enables new and innovative scenarios like running Oracle E-Business Suite or Oracle JD Edwards on Azure against an Oracle Autonomous Database running on Exadata infrastructure in the Oracle Cloud.

“As the cloud of choice for the enterprise, with over 95% of the Fortune 500 using Azure, we have always been first and foremost focused on helping our customers thrive on their digital transformation journeys,” said Scott Guthrie, executive vice president of Microsoft’s Cloud and AI division. “With Oracle’s enterprise expertise, this alliance is a natural choice for us as we help our joint customers accelerate the migration of enterprise applications and databases to the public cloud.”

“The Oracle Cloud offers a complete suite of integrated applications for sales, service, marketing, human resources, finance, supply chain and manufacturing, plus highly automated and secure Generation 2 infrastructure featuring the Oracle Autonomous Database,” said Don Johnson, executive vice president, Oracle Cloud Infrastructure (OCI). “Oracle and Microsoft have served enterprise customer needs for decades. With this partnership, our joint customers can migrate their entire set of existing applications to the cloud without having to re-architect anything, preserving the large investments they have already made.”

As a result of this expanded partnership, the companies are today making available a new set of capabilities:

  • Connect Azure and Oracle Cloud seamlessly, allowing customers to extend their on-premises datacenters to both clouds. This direct interconnect is available starting today in Ashburn (North America) and Azure US East, with plans to expand additional regions in the future.
  • Unified identity and access management, via a unified single sign-on experience and automated user provisioning, to manage resources across Azure and Oracle Cloud. Also available in early preview today, Oracle applications can use Azure Active Directory as the identity provider and for conditional access.
  • Supported deployment of custom applications and packaged Oracle applications (JD Edwards EnterpriseOne, E-Business Suite, PeopleSoft, Oracle Retail, Hyperion) on Azure with Oracle databases (RAC, Exadata, Autonomous Database) deployed in Oracle Cloud. The same Oracle applications will also be certified to run on Azure with Oracle databases in Oracle Cloud.
  • A collaborative support model to help IT organizations deploy these new capabilities while enabling them to leverage existing customer support relationships and processes.
  • Oracle Database will continue to be certified to run in Azure on various operating systems, including Windows Server and Oracle Linux.
 

“The alliance between Microsoft and Oracle is welcome news as we accelerate Albertsons’ digital transformation and leverage the full value of the public cloud,” said Anuj Dhanda, executive vice president and chief information officer at Albertsons Companies. “This will allow us to create cross-cloud solutions that optimize many of our current investments while maximizing the agility, scalability and efficiency of the public cloud.”

“As we look to bring our omnichannel experience closer together and transform the technology platform that powers the Gap Inc. brands, the collaboration between Oracle and Microsoft will make it easier for us to scale and deliver capabilities across channels,” said Sally Gilligan, chief information officer at Gap. “The interoperability between Azure and Oracle Cloud allows us to deploy Oracle or custom-built applications on Azure and Oracle databases on Oracle Cloud.”

“At Halliburton, we have a long history of running both Oracle and Microsoft technologies for our most critical applications. Our deep experience with these two strategic vendors has yielded consistently stable and performant application deployments,” said Ken Braud, senior vice president and CIO at Halliburton. “This alliance gives us the flexibility and ongoing support to continue leveraging our standard architectures, while allowing us to focus on generating business outcomes that maximize returns for our shareholders.”

Those wanting to learn more should visit https://aka.ms/OracleOnAzure or https://www.oracle.com/cloud/oci-azure.html.

Contact Info
Celina Bertallee
Oracle
(650) 506-6542
celina.bertallee@oracle.com
Microsoft Media Relations
WE Communications for Microsoft
(425) 638-7777
rrt@we-worldwide.com
About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

About Microsoft

Microsoft (Nasdaq “MSFT” @microsoft) enables digital transformation for the era of an intelligent cloud and an intelligent edge. Its mission is to empower every person and every organization on the planet to achieve more.

Future Product Disclaimer

The preceding 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, timing and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation.

Trademarks

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

Note to editors: For more information, news and perspectives from Microsoft, please visit the Microsoft News Center at http://news.microsoft.com. Web links, telephone numbers and titles were correct at time of publication, but may have changed. For additional assistance, journalists and analysts may contact Microsoft’s Rapid Response Team or other appropriate contacts listed at http://news.microsoft.com/microsoft-public-relations-contacts.

Talk to a Press Contact

Celina Bertallee

  • (650) 506-6542

Microsoft Media Relations

  • (425) 638-7777

PostgreSQL partitioning (5): Partition pruning

Yann Neuhaus - Wed, 2019-06-05 01:00

This is the next post in the PostgreSQL partitioning series. If you missed the previous ones here they are:

  1. PostgreSQL partitioning (1): Preparing the data set
  2. PostgreSQL partitioning (2): Range partitioning
  3. PostgreSQL partitioning (3): List partitioning
  4. PostgreSQL partitioning (4): Hash partitioning

This time we will have a look at partition pruning. Never heard of that? Don’t worry, hopefully it will be clear at the end of this blog post.

When you ask Wikipedia for pruning you get this: “Pruning is a horticultural and silvicultural practice involving the selective removal of certain parts of a plant, such as branches, buds, or roots. Reasons to prune plants include deadwood removal, shaping (by controlling or redirecting growth), improving or sustaining health, reducing risk from falling branches, preparing nursery specimens for transplanting, and both harvesting and increasing the yield or quality of flowers and fruits.”

Although this is about plants it almost exactly describes partition pruning as well: “selective removal of certain parts of a plant”. In our case it is removal of partitions when it is known that the partition(s) can not contain data we are asking for.

Lets come back to our range partitioned table. We partitioned the table by the “time_of_stop” column:

postgres=# select * from pg_partition_tree('traffic_violations_p');
            relid             |     parentrelid      | isleaf | level 
------------------------------+----------------------+--------+-------
 traffic_violations_p         |                      | f      |     0
 traffic_violations_p_default | traffic_violations_p | t      |     1
 traffic_violations_p_2012    | traffic_violations_p | t      |     1
 traffic_violations_p_2013    | traffic_violations_p | t      |     1
 traffic_violations_p_2014    | traffic_violations_p | t      |     1
 traffic_violations_p_2015    | traffic_violations_p | t      |     1
 traffic_violations_p_2016    | traffic_violations_p | t      |     1
 traffic_violations_p_2017    | traffic_violations_p | t      |     1
 traffic_violations_p_2018    | traffic_violations_p | t      |     1
 traffic_violations_p_2019    | traffic_violations_p | t      |     1
 traffic_violations_p_2020    | traffic_violations_p | t      |     1

Each partition contains data from one year. If we ask for data from 2013 PostgreSQL should only read that partition and just ignore the others.

postgres=# explain select count(*) from traffic_violations_p where date_of_stop = date('02-FEB-2013');
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11393.96..11393.97 rows=1 width=8)
   ->  Gather  (cost=11393.75..11393.96 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10393.75..10393.76 rows=1 width=8)
               ->  Parallel Seq Scan on traffic_violations_p_2013  (cost=0.00..10393.29 rows=185 width=0)
                     Filter: (date_of_stop = '2013-02-02'::date)
(6 rows)

Indeed that is happening and only the traffic_violations_p_2013 is considered. All other partitions will just be ignored and that of course is a performance improvement. This is the simple case and it is partition pruning at planning time. Because we have a literal in the where clause PostgreSQL can already decide at planning time which partitions it needs to read and which can be skipped.

Consider this example:

select count(*) 
  from traffic_violations_p 
 where date_of_stop = select date('01-FEB-2013');

In this case PostgreSQL can not know what will be the actual values coming back from the sub-select at planning time. If you are on PostgreSQL 10 there is not much you can do as partition pruning at execution time made it into PostgreSQL 11. As I am on PostgreSQL 12 Beta 1 it should work:

postgres=# postgres=# explain         
select count(*) 
  from traffic_violations_p 
 where date_of_stop = ( select to_date('01.01.2014','DD.MM.YYYY'));
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=85047.50..85047.51 rows=1 width=8)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=4)
   ->  Gather  (cost=85047.28..85047.49 rows=2 width=8)
         Workers Planned: 2
         Params Evaluated: $0
         ->  Partial Aggregate  (cost=84047.28..84047.29 rows=1 width=8)
               ->  Parallel Append  (cost=0.00..84042.52 rows=1901 width=0)
                     ->  Parallel Seq Scan on traffic_violations_p_2015  (cost=0.00..12924.89 rows=269 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2014  (cost=0.00..12235.54 rows=255 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2016  (cost=0.00..12097.57 rows=252 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2018  (cost=0.00..12051.87 rows=249 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2017  (cost=0.00..10996.34 rows=228 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2013  (cost=0.00..10393.29 rows=218 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2012  (cost=0.00..8351.41 rows=172 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2019  (cost=0.00..4959.83 rows=246 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_default  (cost=0.00..11.54 rows=15 width=0)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2020  (cost=0.00..10.74 rows=1 width=0)
                           Filter: (date_of_stop = $0)
(28 rows)

The execution plan shows that all partitions will be scanned and that is no surprise. When you want to see partition pruning at execution time you actually have to execute the statement, so explain(analyze):

postgres=# explain(analyze)
select count(*) 
  from traffic_violations_p 
 where date_of_stop = ( select to_date('01.01.2014','DD.MM.YYYY'));
                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=85047.50..85047.51 rows=1 width=8) (actual time=149.747..149.747 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)
   ->  Gather  (cost=85047.28..85047.49 rows=2 width=8) (actual time=145.730..150.004 rows=3 loops=1)
         Workers Planned: 2
         Params Evaluated: $0
         Workers Launched: 2
         ->  Partial Aggregate  (cost=84047.28..84047.29 rows=1 width=8) (actual time=119.148..119.149 rows=1 loops=3)
               ->  Parallel Append  (cost=0.00..84042.52 rows=1901 width=0) (actual time=119.052..119.127 rows=189 loops=3)
                     ->  Parallel Seq Scan on traffic_violations_p_2015  (cost=0.00..12924.89 rows=269 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2014  (cost=0.00..12235.54 rows=255 width=0) (actual time=119.024..119.077 rows=189 loops=3)
                           Filter: (date_of_stop = $0)
                           Rows Removed by Filter: 74405
                     ->  Parallel Seq Scan on traffic_violations_p_2016  (cost=0.00..12097.57 rows=252 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2018  (cost=0.00..12051.87 rows=249 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2017  (cost=0.00..10996.34 rows=228 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2013  (cost=0.00..10393.29 rows=218 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2012  (cost=0.00..8351.41 rows=172 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2019  (cost=0.00..4959.83 rows=246 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_default  (cost=0.00..11.54 rows=15 width=0) (never executed)
                           Filter: (date_of_stop = $0)
                     ->  Parallel Seq Scan on traffic_violations_p_2020  (cost=0.00..10.74 rows=1 width=0) (never executed)
                           Filter: (date_of_stop = $0)
 Planning Time: 0.604 ms
 Execution Time: 150.120 ms
(32 rows)

The keywords here are “(never executed)” and this is partition pruning at execution time. There are some limitations with this but this will be covered in the last post of this series when we will look at corner cases when it comes to partitioning in PostgreSQL.

The next post will cover attaching and detaching of partitions.

Cet article PostgreSQL partitioning (5): Partition pruning est apparu en premier sur Blog dbi services.

Geoffrey Moore to Keynote Oracle Retail Cross Talk 2019

Oracle Press Releases - Tue, 2019-06-04 12:46
Press Release
Geoffrey Moore to Keynote Oracle Retail Cross Talk 2019 Over 200 global retailers will gather to discuss the state of retail and how brands are using technology to innovate and ignite customer success

Redwood Shores, Calif.—Jun 4, 2019

Oracle Retail announced today that Geoffrey Moore, Bestselling Author, Consultant and Disruptive Technology Specialist, will deliver the keynote address at Oracle Retail Cross Talk. The event brings together a global community of over 200 retail executives to share deep domain expertise and insights on technology that is core to their retail businesses. Cross Talk provides a unique and authentic networking forum where over 35 global retailers including Bata, Best Buy, the Estée Lauder companies, Kendra Scott, and more will share their success stories and lessons learned. The event will take place June 10-12, 2019 at the JW Marriott in Minneapolis, MN. 

“‘How can retailers succeed and thrive in a fast-paced and continually evolving business environment?’ Cross Talk focuses on collectively addressing this very question and enabling retailers to hear directly from their peers about how they are rising above today’s most pressing industry challenges to deliver results and delight customers,” said Mike Webster, senior vice president and general manager, Oracle Retail.

Attendees will gain insight from talented retail executives representing the world’s leading brands including:

  • Bata will discuss its digital transformation and how it is unlocking the business potential of omnichannel
  • Best Buy will share critical insights into how it is optimizing supply chain strategies
  • Estée Lauder will highlight its approach to delivering a seamless shopping experience
  • International Shoppes will demonstrate how it is modernizing the customer experience
  • Gap, Inc. will talk about its latest brand to go live with Oracle Retail cloud offerings
  • James Avery will illustrate how it is upgrading the customer experience
  • Kendra Scott will discuss how it built a platform for growth with cloud 
  • Maui and Sons will share key learning as it rides the wave to operational agility
  • Stein Mart will talk about its migration of planning to the cloud

Oracle PartnerNetwork members and strategic solution integrators are critical in helping deliver customer success. A number of key collaborators are sponsoring the show, including Accenture and Deloitte as platinum sponsors; gold sponsors BTM Retail and Retail Consult; and Aspire, CAI, Ignitiv, Logic, Quickborn, and SkillNet as silver sponsors. Solution level sponsors, Adyen and OneDoor, will also showcase their integrations into Oracle’s platform for modern retail.

“Our community is thrilled for the return of Oracle Retail Cross Talk,” said Rose Spicer, senior director of retail marketing, Oracle Corporation. “The response has been amazing and we cannot wait to assemble some of the biggest brands in the industry with new faces to discuss the issues and opportunities that are impacting them most today. We have also added some compelling networking elements to connect the retailers to one another.”

For more information about how Oracle delivers a modern platform for retail organizations through best-in-class, industry-specific business solutions, visit www.oracle.com/retail.

To learn more about the Oracle Retail Cross Talk 2019, visit: https://www.oracle.com/industries/retail/cross-talk/.

Contact Info
Kris Reeves
Oracle PR
+1.925.787.6744
kris.reeves@oracle.com
About Oracle Retail

Oracle is the modern platform for retail. Oracle provides retailers with a complete, open, and integrated platform for best-of-breed business applications, cloud services, and hardware that are engineered to work together. Leading fashion, grocery, and specialty retailers use Oracle solutions to accelerate from best practice to next practice, drive operational agility and refine the customer experience. For more information, visit our website www.oracle.com/retail.

About Oracle

The Oracle Cloud offers a complete suite of integrated applications for Sales, Service, Marketing, Human Resources, Finance, Supply Chain and Manufacturing, plus Highly Automated and Secure Generation 2 Infrastructure featuring the Oracle Autonomous Database. For more information about Oracle (NYSE: ORCL), please visit us at www.oracle.com.

Trademarks

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

Kris Reeves

  • +1.925.787.6744

ADF Faces and Client Side Value with innerHTML

Andrejus Baranovski - Tue, 2019-06-04 07:24
In ADF Faces you can leverage the full power of JavaScript. I will explain how you could assign a value from ADF Faces component to the plain HTML div.

The sample app is available on GitHub repo. It doesn't require DB connection, you can run it straight away in Oracle JDeveloper.

Look into JSF page. I have implemented ADF Faces input component with value change listener. Below this component, there is HTML div with ID ot1. We will assign a text value to this div programmatically from JS function passClientSideValue:


JavaScript function reads the value by client ID from ADF Faces component and assigns it to the innerHTML property of HTML div:


When ADF Faces value is changed, value change listener is invoked through ADF auto-submit event. In value change listener, we extract client ID of the input component and pass it to JS function through JavaScript call from Java:


This is how the end result looks like:


In particular, this approach can be useful, when you want to bypass ADF Faces validation lifecycle and display updated value despite current validation errors in the form.

Kscope 2019 APEX Track Sessions by Oracle ACE Program Members

OTN TechBlog - Tue, 2019-06-04 05:00
A Towering Achievement...

On June 23rd ODTUG Kscope 2019 kicks off in Seattle. Among the vast army of session presenters for the event you'll find more than 50 active members of the Oracle ACE Program, and they'll be presenting more than 100 sessions across all the various tracks. This post is the first of a series that will highlight those ACE sessions. This post focuses on sessions on the APEX track.

For the details on each session, including time, location, and session descriptions, just click the title link.

Oracle ACE Directors

Oracle ACE Director Dimitri GielisDimitri Gielis
Director, APEX R&D
Leuven, Belgium

 

Oracle ACE Director Francis MignaultFrancis Mignault
CTO/VP Technologies, Insum Solutions
Montreal, Canada

 

Oracle ACE Director John ScottJohn Scott
Founder/Director, APEX Evangelists
Leeds, United Kingdom

 

Oracle ACE Director Martin Giffy D'SouzaMartin Giffy D’Souza
Director of Innovation, Insum Solutions
Calgary, Alberta, Canada

 

Oracle ACE Director Niels de BruijnNiels de Bruijn
Business Unit Manager, MT AG
Cologne, Germany

 

Oracle ACE Director Peter RaganitschPeter Raganitsch
CEO, FOEX GmbH
Austria

 

Oracle ACE Director Roel HartmanRoel Hartman
Director/Senior APEX Developer, APEX Consulting
Apeldoorn, Netherlands

 

Oracle ACE Director Scott SpendoliniScott Spendolini
Vice President, Viscosity North America
Austin, Texas

 
Oracle ACEs

Oracle ACE Alan ArentsenAlan Arentsen
Senior Oracle Developer, Arentsen Database Consultancy
Breda, Netherlands

 

Oracle ACE Christian RokittaChristian Rokitta
Managing Partner, iAdvise
Breda Area, Netherlands

 

Oracle ACE Jorge RimblasJorge Rimblas
Senior APEX Consultant, Insum
Minneapolis-St. Paul, Minnesota

 

Oracle ACE Scott WesleyScott Wesley
Systems Consultant/Trainer, SAGE Computing Services
Perth, Australia

 

Oracle ACE Vincent MorneauVincent Morneau
Front-End Lead, Insum
Montreal, Canada

 

Oracle ACE Niall Mc PhillipsNiall Mc Phillips
Owner/CEO, Long Acre sàrl

 
Oracle ACE Associates

Oracle ACE Associate Kai DonatoKai Donato
Senior Consultant, MT AG
Cologne, Germany

 

Oracle ACE Associate Lino SchildenfeldLino Schildenfeld
NZ/AU Director, APEX R&D
Australia/New Zealand

 

Oracle ACE Associate Moritz KleinMoritz Klein
Principal APEX Consultant, MT AG
Frankfurt, Germany

 

Oracle ACE Associate Adrian PngAdrian Png
Senior Consultant/Database Administrator, Insum
Canada

 
Additional Resources

Pages

Subscribe to Oracle FAQ aggregator