This is the second blogpost on using PL/SQL inside SQL. If you landed on this page and have not read the first part, click this link and read that first. I gotten some reactions on the first article, of which one was: how does this look like with ‘pragma udf’ in the function?
Pragma udf is a way to speed up using PL/SQL functions in (user defined function), starting from version 12. If you want (more...)
iAdvise presented the APEX 5.0 roadshows in the first half of 2015. Besides the presentation about the new features, we provided 3 tutorials which helped the visitors to explore Oracle Application Express 5.0. Now we offer these tutorials through our blog. In this article the second tutorial is presented.
This tutorial helps you step by step through the process of migrating an application to the Universal Theme of Oracle Application Express 5.0. In this tutorial we talk about (more...)
I had a requirement the other day to understand when some data had been changed, but there was no column on the table which showed this.
So how do I find out? Well I could go off mining redo and lots of other time consuming and exotic things, but you can use the Oracle Pseudocolumn ORA_ROWSCN. This gives the SCN assocaited with each row. Well, actually it usually doesn’t. It does not show when the (more...)
Here’s one of those odd little tricks that (a) may help in a couple of very special cases and (b) may show up at some future date – or maybe it already does – in the optimizer if it is recognised as a solution to a more popular problem. It’s about an apparent restriction on how the optimizer uses the BITMAP MERGE operation, and to demonstrate a very simple case I’ll start with a data (more...)
Whenever you use PL/SQL in SQL statements, the Oracle engine needs to switch from doing SQL to doing PL/SQL, and switch back after it is done. Generally, this is called a “context switch”. This is an example of that:
-- A function that uses PL/SQL
create or replace function add_one( value number ) return number is
l_value number(10):= value;
-- A SQL statement that uses the PL/SQL function
select sum(add_one(id)) (more...)
In Enterprise Manager 12c, there was a little known functionality where you could check what management packs you needed to be licensed for to use a particular page in Enterprise Manager. I referred to it many times at conferences I was presenting at, and almost always people did not realize the functionality existed. Let’s see how this works.
To start with, I’m going to go to the Performance Home page for a particular database (accessed (more...)
I published a note on AllthingsOracle a few days ago discussing the options for dropping a column from an existing table. In a little teaser to a future article I pointed out that dropping columns DOESN’T reclaim space; or rather, probably doesn’t, and even if it did you probably won’t like the way it does it.
I will be writing about “massive deletes” for AllthingsOracle in the near future, but I thought I’d expand on (more...)
Primary and Standby databases are running on the same server using OMF with listening on port 1530/1531
Note I have – TraceLevel = ‘SUPPORT’
+++ Check listener for DGMGRL service from PRIMARY and STANDBY.
$ lsnrctl status listener_las|grep DG -A 1
Service "hawklas_DGB" has 1 instance(s).
Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGMGRL" has 1 instance(s).
Instance "hawklas", status UNKNOWN, has 1 handler(s) for this service...
$ lsnrctl (more...)
In the previous article, I covered the basics of how to remove database passwords (credentials) from Oracle monitoring or backup scripts and how to instead secure them using a “Secure External Password Store” (SEPS) and Oracle Wallet.
While this mechanism is far better than putting a plain text credential in a script file, one of the more advanced options, specifically tying the files to the local host with the “-auto_login_local” (more...)
Almost every DBA writes and uses various custom scripts to monitor and backup their Oracle databases. However, finding the optimal and most secure way to connect to the database is often not prioritized.
The short summary is that having your script put the username/password (credentials) in any sort of variables or command arguments is the “bad way”. Using an “Oracle External Password Store” (SEPS) or Oracle Wallet is the “better way”. Yet this technology (more...)
About 6 years ago I wanted to know which instance parameters are derived from cpu_count. So it tested a 126.96.36.199 DB - in that version there 21 parameters changed based on the value of cpu_count. Some DB versions passed by so I decided it's time for another check. This time it's 188.8.131.52 without any PSUs/patches. The machine is the same class as previous, so it took some time.
I’m currently involved in a project where we are replacing one company’s entire hardware platform. They also have Oracle database 10.2.0.5 (that we cannot upgrade right now) and what is really unusual for me, is that this Oracle database runs under Windows (and we cannot migrate to another platform). We also decided to use Oracle Grid Infrastructure (aka Oracle Clusterware) 184.108.40.206 to implement active-passive standby server for this database. (more...)
For those of you that have been wondering why I’ve been so quiet of recent times, it’s because my wife and I took advantage of a trip to England for UKOUG in December to spend nearly 5 weeks touring around Europe (2 weeks of which was spent river cruising from Amsterdam to Budapest with the fabulous APT river cruises, something I can HIGHLY recommend if you want to see Europe!). But while I (more...)
Sending notification emails shouldn’t require any licenses right? Unfortunately it might be more complicated than it seems
As you know Enterprise Manager Cloud Control provides a web interface called web console, and (naturally) it consists of web pages that enables you to access the features of Enterprise Manager Framework. Some of these features (mostly the basic ones) are free but some of them require you to buy management pack licenses. When you’re not sure if (more...)