DBA Blogs

Query Performance Large SQL

Tom Kyte - Wed, 2017-09-20 19:26
Hi Tom, Thanks for taking time to read my question. I have a query that joins about 36 tables. Driving table has about 43 million records. 5 other tables have about 9 million records. Rest of the tables are small. Its a combination of inner and l...
Categories: DBA Blogs

Quick Python script to backup remote directory using ftp

Bobby Durrett's DBA Blog - Wed, 2017-09-20 18:47

I looked around for some other ways to do this but decided to just code this up in Python. It connects to a remote Linux server using ftp and recursively copies all the files and directories back to a Windows machine.

Here is the source:

.gist table { margin-bottom: 0; }

This is an example of the ease of use of Python for quick scripting. It uses a low-level ftp library called ftplib.


Categories: DBA Blogs

Partner Webcast – Data Management Platform for Innovation

The Oracle Cloud Platform enables customers to accelerate innovation while reducing costs and modernizing their infrastructure leveraging Oracle’s Data Management Cloud Solutions. They can...

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

Golden Gate, Streams

Tom Kyte - Wed, 2017-09-20 01:06
Hi Chris/Connor, Can you please help to clarify below query: We have a Archival requirement, where we need to : 1. Copy certain tables data (older than 7 years) from Primary DB to secondary DB 2. Delete those copied data from Primary DB. S...
Categories: DBA Blogs

need to extract numbers from a varchar upto a non-numeric character

Tom Kyte - Wed, 2017-09-20 01:06
Hi, I have a column called house number which is a varchar2, and it has all kinds of combinations of data entered (incorrect format) as shown below: House_Number ------------- 416-A 416-A 1573A 131# A23 133 A-21 133 A22 13320A 133A-21 1...
Categories: DBA Blogs

transfert files,delete&loadData

Tom Kyte - Wed, 2017-09-20 01:06
Hello, I would like to do these 3 steps automatically one time per day with oracle: 1) transfert .csv files. 2) Delete data from table. 3) Load table again with sqlldr from .csv files transferred. So if you could please let me know what would ...
Categories: DBA Blogs

Database is very slow....

Tom Kyte - Wed, 2017-09-20 01:06
Hi, I have a database in Oracle My database is being used by JDE application and Block size set to 16KB. We are using FATA disk to store the datafiles of the database. We are maintaining separate tablespaces for data and indexes. We are us...
Categories: DBA Blogs

XMLQuery ORA-19114: XPST0003 - error during parsing the XQuery expression:

Tom Kyte - Wed, 2017-09-20 01:06
I'm doing examples from workbook. I created table and insert couple of records. Below is my code: Create table: <code>CREATE TABLE test_Pracownicy (IDPracownika NUMBER(3), Dane XMLTYPE); Insert record to the table: INSERT INTO test_Pracow...
Categories: DBA Blogs

Generating large json in 12.2 using json_object and json_arrayagg

Tom Kyte - Wed, 2017-09-20 01:06
Is it possible to get a result from the following query? <code> select JSON_OBJECT( KEY 'objects' VALUE (SELECT JSON_ARRAYAGG( JSON_OBJECT( KEY 'object_type' VA...
Categories: DBA Blogs

Database redaction with PL/SQL invoker rights

Tom Kyte - Tue, 2017-09-19 06:46
Dear Oracle Masters, I am trying to create a proof of concept application architecture based upon the thick-database paradigm incorporating invoker rights, code based access control and redaction in Oracle 12cR2. Background =========== I h...
Categories: DBA Blogs

Client Result Cache not supported by sqlplus ?

Tom Kyte - Tue, 2017-09-19 06:46
<code> sokrates > select distinct sokrates > client_connection, client_oci_library, client_version, client_driver sokrates > from v$session_connect_info sokrates > where sid = (select sid from v$mystat where rownum=1) sokrates > / CLIENT_CONN...
Categories: DBA Blogs


Tom Kyte - Tue, 2017-09-19 06:46
I have an MS ACCESS application that will be developed with an Oracle table (back-end) Weekly, a user will get Spreadsheets that are imported (via VBA scripting) into Access. In the temporary Access tables, other data is added. The plan is then ...
Categories: DBA Blogs

DB Cloning

Tom Kyte - Tue, 2017-09-19 06:46
Team: We have two oracle databases Source database - DB1 running on HP Unix platform, version is having 20+ TB of data. Target database - DB2 running on Linux platform, version is having no data for now. Our goal is to clo...
Categories: 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


Subscribe to Oracle FAQ aggregator - DBA Blogs