DBA Blogs

Evolving SQL Plan Baselines

Tom Kyte - Tue, 2017-09-19 06:46
We have recently upgraded one of our main databases from to, and we used SQL Plan Baselines to try to minimize the impact of many plan changes. We are running a job to evaluate and evolve new plans captured into the baseline (calls ...
Categories: DBA Blogs


Tom Kyte - Tue, 2017-09-19 06:46
Hi TOM, We have ASM implemented in lot of databases and below are the values of auto_start attribute in our crs config: NAME=ora.asm AUTO_START=never NAME=ora.DG_GRID_CL.dg --> This is the disk group which we use for keeping OCR & Voting di...
Categories: DBA Blogs

Idempotent and Nullipotent in Cloud

Pakistan's First Oracle Blog - Tue, 2017-09-19 04:50
I was going through the documentation of Oracle Cloud IaaS, when I came across the vaguely familiar term Idempotent.

One great thing which I have felt very strongly with all this Cloud-mania is the recall of various theoretical computing concepts which we learned/read in university courses way back. From networking through web concepts to operating system; there are plethora of concepts which are coming back to be in practice very actively in everyday life of cloud professionals.

Two such mouthful words were Idempotent and Nullipotent. These are types of actions. Difference between Idempotent and Nullipotent action is the result they return when performed.

In simple terms;

    When executed an Idempotent action would provide a result first time and then this result would remain same, no matter how many times the action is repeated after that first time.
    An Nullipotent action would always provide same result whether executed several times or not executed at all.
So in terms of Cloud where REST (Representational State Transfer) APIs and HTTP (Hyper Text Transfer Protocol) are norm, these 2 concepts of Idempotent and Nullipotent are very important. In order to manage resources in cloud (through URI), there are various HTTP actions which could be performed. Some of these actions are Idempotent and some are Nullipotent.

Like GET action of HTTP is nullipotent. No matter how many times you execute this, it doesn't affect state of the resource and would return same result. And Put is Idempotent action of HTTP which would change the state of resource first time its executed and all subsequent executions of same PUT action would be like as first time.
Categories: DBA Blogs

SRVCTL Status Doesn't Show RAC instances Running Unlike SQLPLUS

Pakistan's First Oracle Blog - Mon, 2017-09-18 18:34
Yesterday, I converted a single instance physical standby database to a cluster database with 2 nodes.

After converting that to RAC database, I brought both instances up in mount state on both nodes and they came up fine and I started managed recovery on one node and it started working perfectly fine and got in sync with the primary.

Then I added them as a cluster resource by srvctl like this:

$ srvctl add database -d mystb -o /d01/app/oracle/product/ -r PHYSICAL_STANDBY -s MOUNT
$ srvctl add instance -d mystb -i mystb1 -n node1
$ srvctl add instance -d mystb -i mystb2 -n node2

But srvctl status didnt show it running:

$ srvctl status database -d mystb -v
Instance mystb1 is not running on node node1
Instance mystb2 is not running on node node2

While from SQLPLUS, I could see both instances mounted:

SQL> select instance_name,status,host_name from gv$instance;

---------------- ------------ ----------------------------------------------------------------
mystb1             MOUNTED      node1
mystb2              MOUNTED      node2

So I needed to start database in srvctl (thought it was already started and mounted) just to please srvctl:

So I ran this:

$ srvctl start database -d mystb

The command didn't do anything but change the status of resource on the cluster. After running above, it worked:

$ srvctl status database -d mystb -v
 Instance mystb1 is running on node node1
 Instance mystb2 is running on node node2
Categories: DBA Blogs

Added save and restore data function to PythonDBAGraphs

Bobby Durrett's DBA Blog - Mon, 2017-09-18 18:30

I pushed out a quick change to PythonDBAGraphs to automatically save the data for any graph that you make so that you can redraw the graph later. This is better than saving an image file because the redrawn graph lets you see details about the points on the graph when you hover the mouse over the points.

Now when you generate a graph you get a line like this:

Saving data in C:\temp\ASH active session count for MYDB database.txt

When you want to see the graph again you run show_saved.py like this:

python show_saved.py
Enter name of data file to be restored: C:\temp\ASH active session count for MYDB database.txt


Categories: DBA Blogs

“Oracle Indexing Internals & Best Practices” Seminar: Australia 2017 Tour (Stage)

Richard Foote - Mon, 2017-09-18 17:10
I’ll be running a fully revised and updated version of my acclaimed “Oracle Indexing Internals and Best Practices” seminar throughout Australia in Oct/Nov 2017. Previous versions of this seminar have been hugely popular and run in some 18 countries. This updated version will feature lots of new material including 12c related indexing capabilities and recommendations. […]
Categories: DBA Blogs

Evolute from a Developer to DBA

Tom Kyte - Mon, 2017-09-18 12:26
Hi Tom, I found this website by accident one year ago and get addicted to it in just a couple of hours. Thank you for all the interesting and useful instructions/suggestions. I am a database developer who uses PL/SQL to code everyday. I always ho...
Categories: DBA Blogs

Delete a record at the at end when others stage

Tom Kyte - Mon, 2017-09-18 12:26
I have a procedure which writes a record each in two tables. Want to delete the record inserted into the first table if the second table insert fails. However, the delete does not happen. The problem is that the before insert trigger in the second t...
Categories: DBA Blogs

capture old and new values of the fields using Stored Procedure

Tom Kyte - Mon, 2017-09-18 12:26
Hi, We have a requirement to store old and new values of the fields and insert into another table. For example, If we have Table A and Table B and in both the tables columns are exactly same but we need to write a procedure like whenever an...
Categories: DBA Blogs

How to pass a list of values from one procedure to another

Tom Kyte - Mon, 2017-09-18 12:26
The first step is to fetch the data. Second is to pass the list of values fetched in step 1 to another procedure. This is the table <code>CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), ...
Categories: DBA Blogs

Refreshing Materialized view in a remote database

Tom Kyte - Mon, 2017-09-18 12:26
Categories: DBA Blogs

Splitting and storing Json string from file to table

Tom Kyte - Mon, 2017-09-18 12:26
Hi Team..Please help me on below scenario. I have data in a file as below. It has two records Id and JsonString DELIMITED By "| 1001"|{"id":"1234","code":"test1","Version":v1,"time":"2017-08-31","rank":2,"device":"TEST1234TEST","response":"appr...
Categories: DBA Blogs

Flashlogs not getting deleted

Tom Kyte - Mon, 2017-09-18 12:26
Hi Tom, I see FRA size is Filling up and when checked Flashback Logs are occupying most of it as seen below, SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------...
Categories: DBA Blogs

Log Buffer #522: A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2017-09-18 10:22

This edition of Log Buffer covers Cloud, Oracle, SQL Server and MySQL and much more.


Introducing managed SSL for Google App Engine

Using Cloud Foundry CUPS to inject Spring Security credentials into a Spring Boot Application

ClusterControl in the Cloud – All Our Resources

Monitoring Amazon Aurora Audit Events with Amazon CloudWatch

Integrating Teradata with Amazon Redshift Using the AWS Schema Conversion Tool


Demo App for REST enabled SQL

The Insurance of Oracle APEX – Can Your Low Code Framework Do This?

Oracle GoldenGate is now avaliable on Docker! All the details you need to get up and running on an image is included in Oracle’s public github.

Assign Pseudo Random Password for Oracle DB Users

Simple regexp to check that string contains word1 and word2 and doesn’t contain word3

SQL Server:

Automate MSBI deployments Using PowerShell

NEW – Machine Learning with SQL Server 2017 and R Tutorial

Optional Parameters and Missing Index Requests

Moving Dynamic Data Masked Data to New Locations

Indexing for Windowing Functions: WHERE vs. OVER


Lock, Stock and MySQL Backups: Data Guaranteed Webinar Follow Up Questions

gh-ost 1.0.42 released: JSON support, optimizations

Massive Parallel Log Processing with ClickHouse

cscope: Searching Code Efficiently

Updating InnoDB Table Statistics Manually

Categories: DBA Blogs

Richard Foote Consulting Has Arrived (New Angels Of Promise)

Richard Foote - Sun, 2017-09-17 17:32
Today is the official launch of Richard Foote Consulting, my new independent company in which I’ll be providing specialist Oracle Database consulting and training services, focusing on database performance tuning, database problem resolutions and database health check assessments (at least until everyone moves across to the new self-tuning Oracle Cloud database). Based on all the […]
Categories: DBA Blogs

connect by rownum produce ORA-30009

Tom Kyte - Sat, 2017-09-16 05:26
SQL> select * from scott.t0909_1; ID S E ---------- ---------- ---------- 1 10 11 2 1 5 3 88 92 -----Scene 1 select * from scott.t0909_1 connect by rownum<=3; ID S E ---------- ---------- ---------- 1 10 11 1 10 11 1 10 ...
Categories: DBA Blogs

Vendor Code 17002 Unable to Connect to DB Windows 10

Tom Kyte - Sat, 2017-09-16 05:26
IO Error: The Network Adapter could not establish the connection. My laptop is client/server using Windows 10. I added Port 1521 as exception to Firewall, but this did not resolve the problem. I am knowledgeable in DB design/build using SQL Server, b...
Categories: DBA Blogs

TO_DATE with no month

Tom Kyte - Sat, 2017-09-16 05:26
If you use to_date without specifying a day, Oracle assumes the 1st of the month. However, if you don't specify a month, Oracle uses the current month. Is this by design, and if so do you know the rationale for it? eg on 15 September: <code> ...
Categories: DBA Blogs

Want to retrive numbers in words

Tom Kyte - Sat, 2017-09-16 05:26
I want output as: numbers string 101 One zero one 102 One zero two 851 eight five one 9856 nine eight five six 356 three five six 748 seven four eight 254 two five four ...
Categories: DBA Blogs

How to split rows into balanced sets based on a running total limited to 2000

Tom Kyte - Fri, 2017-09-15 11:06
Hi, my question fits in to your "Balanced sets in SQL" collection of questions. I have a table as follows (in reality 26 million rows): <code> CREATE TABLE T ( "PI" VARCHAR2(120), "S" NUMBER, "L" NUMBER ); Insert into T (PI,S,L...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs