Debug PL/SQL Web Pages

What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.

Here’s a sample web page that fails to run successfully …

1
2
3
4
5
6
7
8
9
10
11
 (more...)

SQL Tuning by Example

My previous post that showed a real life troubleshooting was a hit. I guess people like to hear these kind of things, so I decided to write another post, this time a real life SQL tuning. This happened a while ago, probably about 8-9 years, but it’s an excellent example and it contains many aspects […]

Hotsos 2016 Recap

I wanted to go to Hotsos Symposium for quite some time, having heard so many great topics from there for years. And every time I was a bit lazy to think about what I can talk about. Apparently I thought that everything I know is well covered elsewhere, so why would I be accepted. Plus all these complexities of budget, getting a visa, travel arrangements, jet lag, and personal matters in between. Last year, when (more...)

Cursor_Sharing problem

Here’s a possible bug (though maybe “not a bug”) that came up over the weekend on the OTN database forum. An application generating lots of “literal string” SQL was tested with cursor_sharing set to force. This successfully forced the use of bind variable substitution, but a particular type of simple insert statement started generating very large numbers of child cursors – introducing a lot of mutex waits and library cache contention. Here’s a (substituted) statement (more...)

Identifying when a password was last changed

Security asked to determine when certain passwords were last changed and I used the ptime column in user$ table to find out.

set lines 120 set pages 60
col name form a30
col Last_Changed form a12

   SELECT name,ptime "Last_Changed"
   FROM sys.user$ a, dba_users b
   where a.name=b.username
   order by 1;
NAME                           Last_Changed
 ------------------------------ ------------
 ANONYMOUS                      24-AUG-13
 APEX_030200                    24-AUG-13
 APEX_PUBLIC_USER               24-AUG-13
 APPQOSSYS                      24-AUG-13
 CTXSYS                         24-AUG-13
 DBSNMP                         13-MAY-16**
 DIP                            24-AUG-13
 EXFSYS                          (more...)

Automating DG Broker

I have been applying PSU lately and what’s so hard out it?

Four+ databases running on Primary with DG Broker for standby.

There are no conventions, as some standby databases have dr appended to primary name while others have 2 appended to primary name.

I wanted to view the DG configuration for currently active instances and show_dg_config.sh will show me this.

Next, I want a faster way to shutdown DG by having syntax generated (more...)

Video : JSON Support in Oracle Database 12c

Today’s video is a sprint through some of the JSON support in Oracle Database 12c.

If videos aren’t your thing, you might want to read these instead.

The cameo in this video comes courtesy of Yves Colin, who I’ll see again (more...)

Presenting “Using image copies for Oracle database backups” at ilOUG Tech Days 2016

I’ll be presenting my brand new presentation “Using image copies for Oracle database backups” at ilOUG Tech Days on 30. May in Israel.

More information about the event can be found here

Abstract of my presentation:
When databases get ever larger and larger, backing them up using traditional RMAN backupsets will quickly get unfeasible. Completing a backup requires too much time and resources, but more importantly the same also applies to restores. RMAN has always (more...)

SQL Developer & PL/SQL

While SQL Developer installs with a dbms_output view, some organizations close it before they distribute images or virtual machine (VM) instances. This post shows you how to re-enable the Dbms Output view for SQL Developer.

SQL Developer DBMS_OUTPUT Configuration

SQLDeveloper1

  1. You need to open SQL Developer, which may look like this when the DBMS_OUTPUT view isn’t visible.

SQLDeveloper1

  1. You need to click on the View menu option in SQL Developer and choose the Dbms Output dropdown menu (more...)

Oracle AVG Function with Examples

The Oracle AVG function is a common function in Oracle SQL. I’ll explain how to use it in this article and show you some examples. Purpose of the Oracle AVG Function The AVG function calculates the average of a series of values that you provide to it. Most of the time, this will be a […]

Using External Table on Windows RAC ACFS

One of my customer is using Oracle RAC (11.2.0.3) on Windows 2012. This is might not be the most ideal setup I’ve ever seen, but it works and we’ll leave it by that.

One of the side effects of using Oracle RAC on Windows is that some of the basic things I am used to do when using RAC on Linux (for example) is behaving differently when it comes to windows. Here (more...)

GoldenGate 12.2 Big Data Adapters: part 4 – HBASE

This is the next post in my series about Oracle GoldenGate Big Data adapters. Here is list of all posts in the series:

  1. GoldenGate 12.2 Big Data Adapters: part 1 – HDFS
  2. GoldenGate 12.2 Big Data Adapters: part 2 – Flume
  3. GoldenGate 12.2 Big Data Adapters: part 3 – Kafka
  4. GoldenGate 12.2 Big Data Adapters: part 4 – HBASE

In this post I am going to explore HBASE adapter for GoldenGate. (more...)

Application deployment to Oracle Java Cloud instance using XL Deploy on AWS

This article is about experimenting configuration, connection and deployment from one cloud instance to the other using the basic installation, connections and features like on a local network.

To go further with special cloud facilities instead, consult the XebiaLabs documentation or blog:

XebiaLabs’ EC2 plugin for XL Deploy

XL Scale

XL Deploy and Amazon AWS OpsWorks (© by Andrew Phillips)

The steps we go through during the trial:

  1. Install XL Deploy on EC2 Linux instance as (more...)

Oracle Midlands : Event #15

Don’t forget Oracle Midlands Event #15 next week!

om15

Please show your support and come along. It’s free thanks to the sponsorship by RedStackTech.

Cheers

Tim…


Oracle Midlands : Event #15 was first posted on May 11, 2016 at 2:42 pm.
©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.

dbms_xplan

My favourite format options for dbms_xplan.display_cursor().

This is another of those posts where I tell you about something that I’ve frequently mentioned but never documented explicitly as a good (or, at least, convenient) idea. It also another example of how easy it is to tell half the story most of the time when someone asks a “simple” question.

You’re probably familiar with the idea of “tuning by cardinality feedback” – comparing the predicted data (more...)

Database Comparisons Using AWR Warehouse Part 1- ADDM Comparison

A lot of my ideas for blog posts come from questions emailed to me or asked via Twitter.  Today’s blog is no different, as I was asked by someone in the community what the best method of comparing databases using features within AWR when migrating from one host and OS to another.

Don't_know_wat

There is a  lot of planning that must go into a project to migrate a database (more...)

RI Locks

RI = Referential Integrity: also known informally as parent/child integrity, and primary (or unique) key/foreign key checking.

I’m on a bit of a roll with things that I must have explained dozens or even hundreds of times in different environments without ever formally explaining them on my blog. Here’s a blog item I could have done with to repsone to  a question that came up on the OTN database forum over the weekend.

What happens (more...)

Oracle 12c Caching and In Memory Databases

A few weeks ago, I was asked to give a private session about In-Memory database vs. traditional persistent databases. I created an hour-long session explaining the basics of database systems, how in-memory systems work, and when to use each of the systems.

One of the questions I got (and answered) was about persistent (regular) database cache mechanism – and I felt this is a good opportunity to write about Oracle 12c new feature – the (more...)

Using PL/SQL to run the SQL tuning Advisor in 11gR2

Using PL/SQL to run the SQL tuning Advisor in 11gR2

In the article we will have a look at the steps to invoke and run the SQL Tuning Advisor using the interface of the PL/SQL packages provided by Oracle. The SQL Tuning Advisor can be run in an alternative way using the OEM 12c Cloud Control interface. We will not cover the OEM 12c CC interface.

The approach is simple we run a single SQL (more...)

Advanced Analytics in Oracle Data Visualization Desktop

Oracle Data Visualisation Desktop has the feature of being able to include some advanced analytics. In a previous blog post I showed you how to go about installing Oracle R Distribution on your desktop/client machine. This will allow you to make use of some of the advanced analytics features of Oracle Data Visualization Desktop.

The best way to get started with using the advanced analytics features of Oracle Data Visualization Desktop, is to ignore that (more...)