DBA Blogs

Stop/Start all RAC databases at once

DBA Scripts and Articles - Wed, 2018-09-26 12:33

Introduction Stopping all RAC databases running on an ORACLE_HOME at once and saving the state before shutting them down is really helpfull when you are doing patching on a server. In my case I have databases running on some nodes and not the others, it’s getting complicated to keep track of which database is running … Continue reading Stop/Start all RAC databases at once

The post Stop/Start all RAC databases at once appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Run class Java in Oracle from other databases

Tom Kyte - Tue, 2018-09-25 18:26
Hello Is it possible to create a Java class using JDBC and run a job, I have seen that it has how to execute Java classes in SQL Developer, I would like it to work in Oracle itself, not in SQL Developer
Categories: DBA Blogs

Overlapping of dates

Tom Kyte - Tue, 2018-09-25 18:26
I have two scenarios explained below: 1. Suppose say, I have an organization with start date as 01/JAN/2017 and end date as 31/DEC/2017. The speciality of my organization has the start date as 01/OCT/2017 and end date as 10/OCT/2018. So when the qu...
Categories: DBA Blogs

Simulation of long time for query parsing

Tom Kyte - Tue, 2018-09-25 00:06
Hi Tom, How can I force a long query parsing time for Oracle or how to prepare such a complicated query? I want to simulate a situation where Oracle parses a query for 30 seconds before execution. Within 30 seconds I want to modify the synonym ...
Categories: DBA Blogs

Unified Audit on 12.2 - partitioning

Tom Kyte - Tue, 2018-09-25 00:06
Hi there We have a customer on 12.2 and is about use Unified Audit, since we have alot of FGA audit due to GDPR (450 G in 3? mth) - we chose UA because of its partitioning capabilites, thus beeing able to delete offload. Questions: 1 I know...
Categories: DBA Blogs

Convert clob to char to get distinct

Tom Kyte - Tue, 2018-09-25 00:06
Hii ASKTOM, select distinct clob_column_name from table_name; I get following error:- ora-00932: inconsistent datatypes;expected-got CLOB While i use to_char function select distinct to_char(clob_column_name) from table_name; then i get follo...
Categories: DBA Blogs

Export table with exclude columns

Tom Kyte - Mon, 2018-09-24 05:46
Hi, I have large partitioned table now i want to export and give dump to someone but not all columns some specific columns needs to trim.. My approach i am importing table on dev setup then make specific columns unused and then try to drop then...
Categories: DBA Blogs

Connection from Data Center to Cisco Switch issue on Exadata X7

Alejandro Vargas - Mon, 2018-09-24 04:25

It may happen that when trying to reach the Exadata machine using the management network, the data center switch that connects to the Exadata Cisco switch get a 'connection rejected' error.

On X7-2 port 48 of the Cisco switch is setup by default as ‘spanning tree network’ that set the BPDU* filter to disable, and communications between customer switch and the Cisco are rejected.

To solve run this command on the Cisco:

  • spanning tree BPDU filter en

Enabling BPDU filtering in the interface level stops sending or receiving BPDU on this interface; this is the same as disabling spanning tree on the interface.

See on My Oracle SupportCisco 93108 switch is getting Error Disabled on the Uplink Switch (Doc ID 2360702.1)

* BPUD stands for: Bridge Protocol Data Unit. BPDUs are data messages that are exchanged across the switches within an extended LAN that uses a spanning tree protocol topology


Categories: DBA Blogs

Connect Power BI to GCP BigQuery using Simba Drivers

Ittichai Chammavanijakul - Fri, 2018-09-21 21:56

Power BI can connect to GCP BigQuery through its provided connector. However, some reported that they’ve encountered the refresh failure as seen below. Even though the error message suggests that the quota for the API requests per user per minute may be exceeded, some reported that the error still occurs even if with a small dataset is being fetched.

In my case, by simply disabling parallel loading table (Options and settings > Options > Data Load), I no longer see this issue. However, some still said it did not help.

An alternative option is to use another supported ODBC or JDBC driver from Simba Technologies Inc. which is partnered with Google.


  • Download the latest 64-bit ODBC driver from here.
  • Install it on the local desktop where Power BI Desktop is installed. We will have to install the same driver on the Power BI Gateway Server if the published report needs to be refreshed on Power BI Service.


  • From Control Panel > Administrator > ODBC Data Source Administrator > System DSN, click Configure on the Google BigQuery.
  • Follow the instructions from the screens below.

When connecting on Power BI, Get Data > choose ODBC.

Categories: DBA Blogs

Clob data type error out when crosses the varchar2 limit

Tom Kyte - Fri, 2018-09-21 04:26
Clob datatype in PL/SQL program going to exception when it crosses the varchar2 limit and giving the "Error:ORA-06502: PL/SQL: numeric or value error" , Why Clob datatype is behaving like varchar2 datatype. I think clob can hold upto 4 GB of data. Pl...
Categories: DBA Blogs

Migrating Oracle 10g on Solaris Sparc to Linux RHEL 5 VM

Tom Kyte - Fri, 2018-09-21 04:26
Hi, if i will rate my oracle expertise i would give it 3/10. i just started learning oracle, solaris and linux 2months ago and was given this task to migrate. yes our oracle version is quite old and might not be supported anymore. Both platforms ...
Categories: DBA Blogs

"secure" in securefile

Tom Kyte - Fri, 2018-09-21 04:26
Good Afternoon, My question is a simple one. I've wondered why Oracle decided to give the new data type the name "securefile". Is it because we can encrypt it while before with basicfile, we couldn't encrypt the LOB? Also, why not call it "se...
Categories: DBA Blogs

Pre-allocating table columns for fast customer demands

Tom Kyte - Fri, 2018-09-21 04:26
Hello team, I have come across a strange business requirement that has caused an application team I support to submit a design that is pretty bad. The problem is I have difficulty quantifying this, so I'm going you can help me all the reasons why ...
Categories: DBA Blogs

move system datafiles

Tom Kyte - Fri, 2018-09-21 04:26
Hi Tom, When we install oracle and create the database by default (not manually) ...the system datafiles are located at a specific location .. Is is possible to move these (system tablespace datafiles) datafiles from the original location to...
Categories: DBA Blogs

how does SKIPEMPTYTRANS work?

Tom Kyte - Fri, 2018-09-21 04:26
I am wondering how does SKIPEMPTYTRANS work? when does ogg judge a transaction empty or not? if it does the judgement in the middle transction? how does ogg know it's a empty transaction? provided that it did not update mapped tables before the jud...
Categories: DBA Blogs

In-Database Archiving

Tom Kyte - Wed, 2018-09-19 15:46
Hi, Currently i am using list partitioning based on a status column to classify the data as ACTIVE and EXPIRED. And then the corresponding partitions are exported and then dropped from Prod. The problem with this approach is the internal data m...
Categories: DBA Blogs

TDE Column vs TDE tablespace when to use

Tom Kyte - Wed, 2018-09-19 15:46
Hi, I have gone through the TDE column and TDE tablespace encryption. Most cases TDE tablespace option is found to be better compared to TDE column option. Wanted to know what advantage TDE column encryption gives or rather the use cases for TD...
Categories: DBA Blogs

In explain plan one of the tables in the query is not even scanned for joining , Do you know why ?

Tom Kyte - Wed, 2018-09-19 15:46
I am running a query like below explain plan for select count(1) from A , B where A.column1=1 and A.column2=3 and A.column3=b.column3(+) When I check the explain plan I can see the sort aggregate and other things ,However I do not see...
Categories: DBA Blogs

How to allow private connectivity across organizations(GCP)?

Surachart Opun - Wed, 2018-09-19 02:41
It's interesting, when you would like to allow private connectivity across two VPC networks that they belong to the different project/organization on Google Cloud Platform (GCP).

As google document that we can use VPC Network Peering? VPC Network Peering is a decentralized or distributed approach to multi-project networking. Additional, it works with Compute Engine, Kubernetes Engine,and App Engine flexible environments.

I did a lab about Virtual Private Cloud (VPC) Network Peering. There shows to do VPC Network Peering between VPC networks in the same project. So, I would like to see how it works on across organizations.

Example: On My Organization (my project), I would like to connect server (Private IP Address) on another Organization (another project).

As a subnet CIDR prefix in one peered VPC network cannot overlap with a subnet CIDR prefix in another peered network. So, both VPC networks must have the different CIDR prefix.

My Organization [myproject] {ubuntu-test, default/} <======> No organization [qwiklabs-gcp***]{privatenet-us-vm/}

On No organization [qwiklabs-gcp***]: VPC network name is "privatenet".


My Organization [myproject]: I used "default" VPC and default firewall.

Then, starting to create "VPC Network Peering".

- To create "VPC Network Peering" on myproject:
Networking => "VPC network" => "VPC network peering".
Click "Create Peering Connection".  name = "peering-to-lab".
Note: you must know Project ID and VPC network name for network destination.

It should show "Waiting for peer network to connect".

-  To create "VPC Network Peering" on another Project: On No organization [qwiklabs-gcp***], "Create Peering Connection".  name = "peering-to-mygcp".

After clicking "Create". It should show "Connected" on both projects (if configuration corrects) like.

On myproject:

- Finally, test connection: ssh to my vm and test (ssh) connection to {privatenet-us-vm/}.
Note: (as firewall allow icmp/ssh). No need to do on firewall.

opun@ubuntu-test:~$ ssh
opun@'s password:
Linux privatenet-us-vm 4.9.0-8-amd64 #1 SMP Debian 4.9.110-3+deb9u4 (2018-08-21) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Wed Sep 19 06:22:05 2018 from
Could not chdir to home directory /home/opun: No such file or directory
$ w
 06:22:55 up 43 min,  2 users,  load average: 0.00, 0.00, 0.00
USER     TTY      FROM             LOGIN@   IDLE   JCPU   PCPU WHAT
opun     pts/1       06:22    1.00s  0.00s  0.00s w ************Reference: https://cloud.google.com/vpc/docs/vpc-peering
Categories: DBA Blogs

#Exasol Database whoami

The Oracle Instructor - Wed, 2018-09-19 02:03

This little script displays some useful meta-information:

SQL_EXA> create schema myschema;
EXA: create schema myschema;

Rows affected: 0

SQL_EXA> create or replace script whoami as
 output('Current User: '.. tostring(exa.meta.current_user))
 output('Current Schema: '.. tostring(exa.meta.current_schema))
 output('Session ID: '.. tostring(exa.meta.session_id))
 output('Database Version: '.. tostring(exa.meta.database_version))
 output('Number of Nodes: '.. tostring(exa.meta.node_count))
EXA:create or replace script whoami as...

Rows affected: 0
SQL_EXA> col output for a40;
COLUMN   output ON
FORMAT   a40
SQL_EXA> execute script whoami with output;
EXA: execute script whoami with output;

Current User: SYS
Current Schema: MYSCHEMA
Session ID: 1612024483893367379
Database Version: 6.1.0-alpha1
Number of Nodes: 1

5 rows in resultset.

All available metadata is documented here (Chapter 3. Concepts -> 3.6 UDF scripts).

Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs