Feed aggregator

IRR Calculations in SQL or PLSQL

Tom Kyte - Tue, 2017-08-08 07:06
Hello Tom, I would like to if there is any way to calculate IRR (internal rate of return) with the help of stored function/ procedure. IRR is normal function available in Excel which allow user to calculate the IRR on base of cashflows. For E...
Categories: DBA Blogs

Prefixing a packaged procedure call with the package name from inside said package ?

Tom Kyte - Tue, 2017-08-08 07:06
Within a single package two procedures call a function in the package. One prefixes with the package name and the other does not. Is there any difference? Thank you. <code> CREATE OR REPLACE PACKAGE BODY demo AS FUNCTION f1 RETURN NUMBE...
Categories: DBA Blogs

Oracle Scheduler job continue to run though is set to stop after 240 minutes/4 hours

Tom Kyte - Tue, 2017-08-08 07:06
Hi, Although they had set the max job duration to 240 minutes/4 hours, the Oracle Scheduler job continue to run for more than 15 hours until they killed it. How could we get this job to stop once past the max job duration? I had already set tha...
Categories: DBA Blogs

SQL Baseline impact

Tom Kyte - Tue, 2017-08-08 07:06
I got user reported a SQL had been run slow about a month. so I checked the AWR snapshots and generated a baseline from AWR snapshot because the good plan was recorded 20 days ago. The related SQL ran faster the next day but the baseline didn't be us...
Categories: DBA Blogs

Rebuilding the same index in multiple sessions(parallel)

Tom Kyte - Tue, 2017-08-08 07:06
Tried rebuilding the index in multiple sessions,but got the error intiated rebuild in two sessions using below query ALTER INDEX indexname REBUILD One of the session is completed fine but other one failed with below error ORA-08104: this ...
Categories: DBA Blogs

Oracle database Libraries (External Procedures Using C)

Tom Kyte - Tue, 2017-08-08 07:06
Dear Tom, I have an Oracle 12c( installation in Cent OS Linux. I was learning the Oracle Libraries and I created a library to find the Reverse of a String and I have published the function as given below. CREATE OR REPLACE FUNCTION...
Categories: DBA Blogs

Oracle Empowers Organizations to Meet Accelerating Market Changes with Major Innovations to Supply Chain Management Cloud

Oracle Press Releases - Tue, 2017-08-08 07:00
Press Release
Oracle Empowers Organizations to Meet Accelerating Market Changes with Major Innovations to Supply Chain Management Cloud Six new applications and hundreds of new capabilities enhance the user experience and support demand-driven business models with modern, end-to-end supply chain best practices

Redwood Shores, Calif.—Aug 8, 2017

To help organizations meet rapidly changing business demands, Oracle today announced significant expansions to Oracle Supply Chain Management (SCM) Cloud. Part of Oracle Cloud Applications Release 13, the latest release of Oracle SCM Cloud delivers a comprehensive, next-generation supply chain platform that enables organizations to embrace outside-in demand driven processes and capabilities without the complexities and constraints of traditional supply chain architectures. The new release includes a number of innovative solutions including: supplier collaboration, quality management, maintenance, sales and operations planning, demand management, and supply planning, as well as expansions across all existing solutions.

To successfully manage accelerating technology, business and industry changes, organizations must rethink traditional supply chain systems to make them connected, agile and customer-oriented. The latest release of Oracle SCM Cloud empowers organizations to meet these changing market needs and customer service requirements by supporting demand-driven, aligned and digitally connected business models. As a result, organizations can improve customer service and customer-direct channels, while facilitating increased margins, reduced inventory costs and minimized supply disruptions.

“Business leaders recognize the undeniable benefits of digitizing core business functions like the supply chain, yet many still struggle to maintain outdated supply chain systems that are designed for the problems of the past—not the challenges of the future,” said Jon S. Chorley, chief sustainability officer and group vice president of supply chain management product strategy at Oracle. “Oracle SCM Cloud is built from the ground up for the cloud in order to support current and future business challenges. These enhancements go broader and deeper, demonstrating our commitment to supply chain excellence, which includes delivering the visibility, insights and capabilities organizations need to successfully balance supply and demand across increasingly complex global supply chains.”

With the latest release of Oracle SCM Cloud, Oracle is significantly expanding the industry’s broadest cloud-based supply chain management suite. This release includes the introduction of six new applications and hundreds of new capabilities that support demand-driven business models with modern end-to-end supply chain best practices, as well as user experience enhancements, including infolets. The six new products included in the latest release of Oracle SCM Cloud are:

  • Oracle Sales and Operations Planning (S&OP) Cloud: Executes strategy consistently with enterprise-wide alignment and visibility for growth and profitability, helping organizations achieve business goals by quickly evaluating and acting on plans. Organizations can benefit from aggregate planning with social collaboration and S&OP process management by aligning sales and operations to enhance decision making using embedded analytics.
  • Oracle Demand Management Cloud: Enables organizations to achieve business objectives by optimizing new product introductions, improving decision making and synchronizing cross-functional demand plans. Organizations can improve demand visibility, manage demand variability and accurately sense, predict and shape customer demand.
  • Oracle Supply Planning Cloud: Adapts quickly to fluctuating demand with the ability to plan for multiple manufacturing and fulfillment strategies in real time, including planning global supply chain activities, monitoring and prioritizing problem areas, and evaluating and executing the actions that will have the greatest impact. Enables greater agility with a comprehensive Genealogy and Installed base system that tracks as-built details and is leveraged by Maintenance Cloud and the new Service Contracts Cloud.
  • Oracle Supply Chain Collaboration Cloud: Supports increased business velocity by enabling organizations to plan, manage and collaborate with suppliers and contract manufacturers more efficiently. It enables organizations to benefit from integrated planning, orchestration, visibility and execution to better transact with trading partners, collaborate on order forecasts and manage exceptions, track production processes more effectively, and gain control over outsourced operations.
  • Oracle Quality Management Cloud: Delivers exceptional product quality to protect organizations’ brands with the combination of collection, analysis and action. Enables organizations to drive best practices from issue to resolution by providing a comprehensive and integrated solution for auditable, closed-loop quality management.
  • Oracle Maintenance Cloud: Enables improved return on capital and lower costs with the ability to better maintain enterprise assets. Helps organizations to achieve efficient and productive maintenance operations. Organizations can leverage complete integration of maintenance, supply chain and financial applications to transform asset maintenance from a complex and expensive process into one that can open up the ability to gain a competitive advantage.

In addition to these new products, existing Oracle SCM Cloud modules have been significantly enhanced. Highlights include:

  • Information-Driven Navigation: All user functions are now accessible through role-specific dashboards and landing pages composed of analytic infolets that highlight issues and direct users to relevant actions.
  • Inventory: Ability to source all requisitions from internal warehouses.
  • Manufacturing: Full support of outside processing.
  • Mobility: A new mobile application to collect and vote on product innovation ideas.
  • Order Management: Integration with Oracle Configure, Price and Quote (CPQ) Cloud and the ability to use the SCM Configurator within CPQ.
  • User Experience: Completely redesigned self-service procurement constructed around the familiar shopping cart experience.

Additional Information

For additional information on Oracle Supply Chain Management (SCM) Cloud, visit Facebook, Twitter or the Oracle SCM blog.

Contact Info
Joann Wardrip
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at oracle.com.


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

Safe Harbor

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

Talk to a Press Contact

Joann Wardrip

  • +1.650.607.1343

Getting started with MapR-DB Table Replication

Tugdual Grall - Tue, 2017-08-08 03:15
Read & comment this article on my new blog Introduction MapR-DB Table Replication allows data to be replicated to another table that could be on on the same cluster or in another cluster. This is different from the automatic and intra-cluster replication that copies the data into different physical nodes for high availability and prevent data loss. This tutorial focuses on the Tugdual Grallhttps://plus.google.com/103667961621022786141noreply@blogger.com0

Partner Webcast – Moving Oracle Workloads to Cloud: ISV & Bespoke Applications with ...

Moving & deploying ISV & bespoke applications on the Oracle Cloud enables customers to accelerate innovation and drive digital transformation initiatives while reducing costs. Only Oracle...

We share our skills to maximize your revenue!
Categories: DBA Blogs

What Employers Want : Make Yourself Stand Out

Tim Hall - Tue, 2017-08-08 01:42

If a job is worth applying for, lots of other people will be applying for it as well. What makes you stand out?

If you’ve taken on board some of the previous posts you already know about qualifications, enthusiasm, self-sufficiency, communication skills, confidence etc. If you’ve got those things down, you are on the right path, but what else can you do? Here are some of my opinions…

Blog : Over the course of your career you should keep writing about your subject. It will improve you as an individual and it’s public evidence that you take an interest in your subject. Over time it will improve your confidence when it comes to written work. If you do it for long enough you may even be able to build a profile for yourself, which may open doors for you. Just remember that not every company is populated by people who follow what’s happening in the community. There are no guarantees.

Present : Similar to blogging, this will improve you are a person and make you more confident in group situations, like job interviews and meetings. This could be something you do for colleagues as part of knowledge spreading, or you could venture out into the wider Oracle community, which can be helpful from a networking perspective.

User Groups and Local Meetups : Whether you are a student or already in work, try getting involved with a user group or local meetup in your area. You will learn a lot and you will meet people. Make the effort to interact. Especially at the local meetups, you will hear people speaking about their companies and vacancies etc. I’m not suggesting you turn up and ask for a job, but if you’ve built up a relationship with the people in your local meetup, you may find new opportunities. Networking is important!

Followers of the blog will know I’m rather biased where blogging and public speaking are concerned, but I can’t emphasise enough how this will change you as a person. Invest in yourself and the rest will fall into place!

Remember, if it is a good job you will be in competition with a lot of people for it. What makes you stand out from the crowd? If the answer is nothing, then you have some work to do before you will be able to get a job like that!

Check out the rest of this series here.



What Employers Want : Make Yourself Stand Out was first posted on August 8, 2017 at 7:42 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Two configuration changes with upgrade

Bobby Durrett's DBA Blog - Mon, 2017-08-07 17:58

A couple of weeks ago I upgraded a major production database from to Our developers kept hitting one ugly bug after another on the minimally patched so I wanted to get them on the most patched up version of Oracle 11. This is on HP-UX Itanium 11.31. I made two key configuration changes that I want to discuss here. I changed the database to use direct I/O and I set a parameter so that the database would not use direct path reads for most table and partition scans. The main point of this blog post is that one change required the other. I moved to direct I/O to improve checkpoint performance but that slowed down queries that repeatedly scanned segments using direct path reads. I set an underscore parameter that prevented direct path reads except on very large table scans and that sped the problem queries back up to normal.

In our testing a coworker ran a massive number of updating jobs at the same time. A truncate took about an hour during this excessive load. I ran a simple test script to truncate a small table during this load and it took over a minute. The truncate spent almost all of its time on these two waits:

  1. local write wait – 61%
  2. enq: RO – fast object reuse – 38%

This was on a Delphix clone of production so the NFS file systems were already forcing the database to use direct I/O. But, I had to max out the database writer processes to get the needed checkpoint performance. I based this decision on my experience with checkpoint performance on another database which I documented in this post: url. I set these parameters:

  1. filesystemio_options=directIO
  2. db_writer_processes=36
  3. dbwr_io_slaves=0

We use these settings on a large data warehouse staging database on and HP-UX 11.31 to tune checkpoint performance there. So, we have tested this configuration in production for several years.

After changing these parameters the truncates ran fast under the same heavy updating load.

We remounted the database file systems with mincache=direct, convosync=direct options on our non-Delphix physical databases when we moved them to direct I/O.

For a long time I have seen issues with slowness of full partition scans on our Delphix clones of the production database that this post is about. Here is a post about this issue: url. I remember hearing about an undocumented underscore parameter that you could use to prevent direct path reads and always meant to look into it but kept holding back. I didn’t want to set the underscore parameter on my Delphix copies of production and not set it on my physical production database. That would make our test database different from production and that could lead to invalid testing. But, an upgrade was a great time to put the parameter in both on Delphix and in production. This was the parameter that I put in:


I read several good blog posts about this parameter and other ways to deal with direct path reads in Oracle 11. Evidently some behavior changed in Oracle 11 that caused full scans to bypass the buffer cache in more situations, using direct path reads. Some post talked about DBA’s just bumping up the value for _small_table_threshold when they upgraded to Oracle 11 so I tried it and testing proved it out. Here is an earlier post about my work with this parameter: url.

So, the upgrade has given me a chance to not only move our database to a more patched up, and hopefully stable, release but it also was a chance to make two key configuration changes. Without direct I/O our production database was using the Unix file system cache to cache the blocks that were being repeatedly scanned using direct path reads. But, to switch to direct I/O I had to end the direct path reads so that the database buffer cache would be used to cache the scanned blocks. Direct I/O and the 36 database writer processes gave us great checkpoint performance. Disabling direct path read kept the queries that had depended on the Unix filesystem cache running just as fast by allowing them to use the database buffer cache.


Categories: DBA Blogs

List of the tools I use and why I use them

Dimitri Gielis - Mon, 2017-08-07 16:33
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

I initially thought to only list the software tools I use, but tools are more than software alone. As a developer I find it important you have everything you need to be successful. For me that means a clean desk, a whiteboard, paper and some writing material (as explained in my previous post) and top notch hardware. Here's a picture of my desk:

So lets move on to the software part now, but before reading further, lets start with a quote I came up with ;)

The tools don’t make the developer - Dimitri Gielis
or another quote I like a lot, sent in by Alan Rintoul:

The single most important component of a camera is the twelve inches behind it – Ansel Adams
When doing development, it's not about the tools, it's about mindset. Tools can help achieve a goal, but which tool to use depends on you and how you work with them. I love to hear what and how other people are using tools to get the job done. Martin D'Souza showed in this podcast how he works with Atom (text editor) and why you should use it. I loved the podcast, Martin is a very smart guy and good developer, but you can look at this podcast in two different ways. You can look at it and say, I'm going to use Atom now, and work with Atom the same as you did in Notepad, or you can look at the principles he explains why he went with Atom and apply it to your editor of choice (which might be Atom, Sublime or another). I recently switched to Visual Studio Code as my main editor, because for me it worked better in the job I have to do a lot (read large JSON files, work with Markdown and Git). Does it mean everybody should switch? Not at all, whatever works for you.

So having said that, I thought it would be nice to list the (software) tools I use and order them by how frequently I use them. There're apps I use multiple times a day (daily), some I use multiple times a week (weekly) or just a few times a month (monthly). While compiling this list I also saw I still have apps installed I don't use that frequently at all. The below list contains only desktop applications I installed on my laptop, next to those programs I also use some webapps like Bitbucket and Trello to name a few, but I'll cover those in future posts as part of different sections. Same applies for plugins or command line apps.

Note: I've a Mac laptop with macOS, so the below applications might not exist for Windows.
  • Mail: for my emails I use Mail that is included in OSX. I also use Google Mail for other mailboxes.
  • Safari (Chrome, Firefox): I typically develop in Safari or Chrome. Safari is a bit more battery friendly, but Chrome has beter developer tools and plugins. Firefox I use when I need just another browser to see if something behaves the same.
  • Visual Studio Code: My favorite editor, I plan to do a quick video how I work with Visual Studio Code and will update this post when done placeholder for video
  • Oracle APEX: the obvious development tool of choice :) (technically not a desktop app, but belongs here anyway)
  • SQL Developer: My favorite editor to have a window in my Oracle databases. I plan to record a quick video how I work with SQL Developer and will update this post when done placeholder for video
  • TweetBot: a few times a day I check my Twitter account or the news of #orclapex
  • Slack: at my company or with friends we use Slack to communicate with each other when we are remote. There's also an orclapex team where many people of the Oracle APEX community are in
  • 1Password: with this little tool I can have all different and secure passwords, accessible with a click
  • Dropbox, Google Drive, iCloud: most important documents are in the cloud with one of those services
  • Pushbullet: sents me notifications of the server on all my devices
  • iTerm2: my window to the server or whenever I need a terminal
  • SourceTree (Git): here I've all my connections to Git repositories and I can quickly see when and what was changed
  • SQLcl: mostly used when I want to run scripts, or used behind the scenes with automation
  • Node.js: mostly used by other programs like Visual Studio Code or APEX Office Print during development
  • Gulp: I use it to automate some things, for example when I save a Markdown file in Visual Studio Code it will automatically build an html file for me
  • VMware: when I need to have a Windows machine or want to test something in my OXAR VM
  • MS Office 365: Word, Excel, Powerpoint: I use to create or edit documents for example with track changes or I'm using it to create templates for APEX Office Print
  • GotoMeeting, Skype, Zoom, Webex, TeamViewer, Google: when connecting to customers, friends, we use any of those meeting tools
  • Moom: a little window manager for OSX, which allows me to quickly see two windows next to each other
MonthlyInstalled, but not using that often
  • TextExpander: snippets manager, this one I actually plan to use more
  • XCode: used when building native iOS apps or compiling Cordova apps
  • VirtualBox: when testing Oracle Developer VMs
  • Paw: a REST manager, but I'm using Visual Studio Code plugin for that now
  • The Unarchiver: when getting files from customers to unpack them
  • Letter Opener for macOS Mail: some people send windows mails, without this tool I can't read it on OSX
  • Duet: enables my iPad as second screen, only used when travelling
  • Kaleidoscope: to compare two files (if they are not in Git)
  • Classeur: used when writing in Markdown for my Blog, but replaced it with Visual Studio Code
  • BBEdit, Atom, Sublime Text: replaced with Visual Studio Code
  • OmniPlan: used for planning
  • Patterns: to try regular expressions
  • MacDown: used to create Markdown files, for example most of the AOP documentation was written in here, but using Visual Studio Code now
  • LibreOffice: used in combination with AOP
  • Pages, Numbers: to exchange or read older files I wrote in Pages or Numbers
  • MJML: to write responsive emails
The above are just some tools that help me doing my job. Over time I changed tools and will most likely use others in the future. So depending when you read this (after 2017) things might have changed.

Feel free to share your favorite tools in the comments section.
Categories: Development

composite hash - list partitioning

Tom Kyte - Mon, 2017-08-07 12:46
Tom, does Oracle support hash partitioning and range or list partitioning on each hash partition. i.e hash-range composite partition and hash-list composite partition ? "vldb and partitioning manual" does not list this combination - wasnt sure ...
Categories: DBA Blogs

Identifying whether an entry is generated "by default" in a trigger

Tom Kyte - Mon, 2017-08-07 12:46
Dear Tom, When having a table with an autogenerated ID like this: <code>create table ids ( id number generated by default as identity, t varchar2(300));</code> And creating a trigger like this: <code>create or replace trigger ids_trig be...
Categories: DBA Blogs

How to export 44 lakhs of records from a table in oracle?

Tom Kyte - Mon, 2017-08-07 12:46
How to export 44 lakhs of records from a table in oracle?
Categories: DBA Blogs

Rebuilding Indexes

Tom Kyte - Mon, 2017-08-07 12:46
I know this question has been asked earlier and I am sorry to take up this question slot but I am confused regarding rebuilding indexes. If I am interpreting it correctly, you don't recommend rebuilding indexes at all. I have talked to two se...
Categories: DBA Blogs


Tom Kyte - Mon, 2017-08-07 12:46
Tom, Happy New Year. Thanks for all your many contributions to the Oracle community. I want to find out column dependencies on views. Basically if I have a view MYVIEW with columns A,B,C,D I'd like to write a query to show the source table/colu...
Categories: DBA Blogs

Synthesize rows based on column values

Tom Kyte - Mon, 2017-08-07 12:46
I have one database table test.The structure of the table is: Col1(varchar) Col2(number) The table has 2 rows: Abc 5 Def 6 I desire the output to be: Col1 Abc Abc Abc Abc Abc Def Def Def Def Def Def I need to write a single q...
Categories: DBA Blogs

How to load repetitive similar kind of structure of data format from a plain text file to DB tables?

Tom Kyte - Mon, 2017-08-07 12:46
Hi Oracle Masters I have been assigned a requirement to load data from a text file say myreport.txt to the Oracle Tables. text file contains data set of marks of every subject for students. file myreport.txt -------- 10th standard results de...
Categories: DBA Blogs

New Oracle Security On-Line Training Dates Added

Pete Finnigan - Mon, 2017-08-07 12:46
We have finally added new on-line training dates for some of our classes; the very popular two days "How to perform a security audit of an Oracle Database" is first followed by the one day class "Hardening and Securing Oracle....[Read More]

Posted by Pete On 07/08/17 At 06:30 PM

Categories: Security Blogs


Subscribe to Oracle FAQ aggregator