DBA Blogs

TO_DATE() on SYSDATE failed in insert all query.

Tom Kyte - Mon, 2019-03-11 21:06
Hello, Yesterday I used insert all query to enter 8 rows in two tables directly from toad. At that time I used TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI') for inserting sysdate in my table (I know sysdate was enough but 'coz of since last four m...
Categories: DBA Blogs

Database operations monitor 12c

Tom Kyte - Mon, 2019-03-11 02:46
Team, Was reading about Real time operations monitor in Oracle 12c @ <u>https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/monitoring-database-operations.html#GUID-DC573FB7-40C5-4D6D-BE81-EF356900B444 </u> and here is a quick...
Categories: DBA Blogs

SQL fetch phase need library cache lock/pin

Tom Kyte - Mon, 2019-03-11 02:46
Hi , thks for all your works here,i've learnd much a lot! now, i have some question on <b>SQL fetch phase</b> and <b>library cache lock/pin </b>which stuck me for days. here is something i find from some website, <i> SELECT statement follow...
Categories: DBA Blogs

Need to find the Oracle EBS Version 12 Data Model

Tom Kyte - Mon, 2019-03-11 02:46
Need to find the Oracle EBS Version 12 Data Model
Categories: DBA Blogs

Information related fro AWR/ASH

Tom Kyte - Mon, 2019-03-11 02:46
Dear Sir's, Please help me with good link or book for AWR/ASH report analyzer and various wait events including RAC. Also want to seen behind rollback segment header contention in oracle. Please help me to sort this. Oracle version: 12.2....
Categories: DBA Blogs

How to create an sosreport on Oracle Linux

Zed DBA's Oracle Blog - Sun, 2019-03-10 13:18

When creating a SR for an issues on Oracle Linux, for example in an Exadata environment, you are quite often enough asked to run an sosreport.

What is sosreport?

“The “sosreport” is a tool to collect troubleshooting data on an Oracle Linux system. It generates a compressed tarball of debugging information that gives an overview of the most important logs and configuration of a Linux system, to be sent to Oracle Support.

Among other things, the sosreport includes information about the installed rpm versions, syslog, network configuration, mounted filesystems, disk partition details, loaded kernel modules and status of all services

It has a plugin-based architecture that enables features to be enabled or disabled, and additional functionality added.”

How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)

Why support needs sosreport?

“The sosreport collects system information from an Oracle Linux system by capturing various log files, configuration files and command outputs that helps in diagnosing a problem faster.

Since this collects most of the commonly sort information while troubleshooting problems, collecting a sosreport helps in reducing the number of iterations of data request from the customer.

The logs, configuration files and related command outputs provides a better picture about the system environment and thus it is very helpful for cases about Root cause analysis and on going issues.

The sosreport helps the support to identify configuration errors and make proactive recommendations too.”

How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)

How to use

To use, simple run “sosreport”:

[root@v1ex1dbadm01 ~]# sosreport

sosreport (version 3.2)

This command will collect diagnostic and configuration information from
this Oracle Linux system and installed applications.

An archive containing the collected information will be generated in
/tmp/sos.9gvK0N and may be provided to a Oracle USA support

Any information provided to Oracle USA will be treated in accordance
with the published support policies at:


The generated archive may contain data considered sensitive and its
content should be reviewed by the originating organization before being
passed to any third party.

No changes will be made to system configuration.

Press ENTER to continue, or CTRL-C to quit.

Please enter your first initial and last name [v1ex1dbadm01.v1.com]: Z Anwar
Please enter the case id that you are generating this report for []: 3-XXXXXXX1234

Setting up archive ...
Setting up plugins ...
Running plugins. Please wait ...

Running 70/70: xfs...
Creating compressed archive...

Your sosreport has been generated and saved in:

The checksum is: 04d1a2b728216ba79df6cc38f801de6d

Please send this file to your support representative.

[root@v1ex1dbadm01 ~]#

You will then have a tar file at the end, which you can upload to your SR for your support engineer to analysis.

If you don’t have sosreport installed, then install the sos package:

[root@v1ex1dbadm01 ~]# yum install sos
More info, can be found in the following MOS note:
  • How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)
  • SRDC – How To Collect Sosreport on Oracle Linux and Oracle VM (Doc ID 1928183.1)
Related Posts


If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.


Zed DBA (Zahid Anwar)

Categories: DBA Blogs

Comparison between #Oracle and #Exasol

The Oracle Instructor - Fri, 2019-03-08 04:41

After having worked with both databases for quite some time, this is what I consider to be the key differences between Oracle and Exasol. Of course the two have much in common: Both are relational databases with a transaction management system that supports the ACID model and both follow the ANSI SQL standard – both with some enhancements. Coming from Oracle as I do, much in Exasol looks quite familiar. But let’s focus on the differences:


Oracle is leading technology for Online Transaction Processing (OLTP). If you have a high data volume with many users doing concurrent changes, this is where Oracle shines particularly.

Exasol is leading technology for analytical workloads. If you want to do real-time ad hoc reporting on high data volume, this is where Exasol shines particularly.

Architecture Data Format & In-Memory processing

Oracle uses a row-oriented data format, which is well suited for OLTP but not so much for analytical workloads. That’s why Hybrid Columnar Compression (only available on Engineered Systems respectively on Oracle proprietary storage) and the In-Memory Column Store (extra charged option) have been added in recent years.

Exasol uses natively a compressed columnar data format and processes this format in memory. That is very good for analytical queries but bad for OLTP because one session that does DML on a table locks that table against DML from other sessions. Read Consistent SELECT is possible for these other sessions, though.

Oracle was designed for OLTP at times when memory was scarce and expensive. Exasol was designed to process analytical workloads in memory.


Oracle started as a non-clustered (single instance) system. Real Application Clusters (RAC) have been added much later. The majority of Oracle installations is still non-clustered. RAC (extra charged option) is rather an exception than the rule. Most RAC installations are 2-node clusters with availability as the prime reason, scalability being rather a side aspect.

Exasol was designed from the start to run on clustered commodity Intel servers. Prime reasons were MPP performance and scalability with availability being rather a side aspect.

Data Distribution

This doesn’t matter for most Oracle installations, only for RAC. Here, Oracle uses a shared disk architecture while Exasol uses a shared nothing architecture, which is optimal for performance because every Exasol cluster node can operate on a different part of the data in parallel. Drawback is that after adding nodes to an Exasol cluster, the data has to be re-distributed.

With Exadata, Oracle tries to compensate the performance disadvantage of the shared disk architecture by enabling the storage servers to filter data locally for analytical workloads. This approach leads to better performance than Oracle can deliver on other (non-proprietary) platforms.

Availability & Recoverability

Clearly, Oracle is better in this area. A non-clustered Oracle database running in archive log mode will enable you to recover every single committed transaction you did since you took the last backup. With Exasol, you can only restore the last backup and all changes since then are lost. You can safeguard an Oracle database against site failure with a standby database at large distance without performance impact. Exasol doesn’t have that. With RAC, you can protect an Oracle database against node failure. The database stays up (the Global Resource Directory is frozen for a couple of seconds, though) upon node failure with no data loss.

If an Exasol cluster node fails, this leads to a database restart. Means no availability for a couple of seconds and all sessions get disconnected. But also no data loss. Optionally, Exasol can be configured as Synchronous Dual Data Center – similar to Oracle’s Extended RAC.

Complexity & Manageability

I realized that there’s a big difference between Exasol and Oracle in this area when I was teaching an Exasol Admin class recently: Some seasoned Oracle DBAs in the audience kept asking questions like “We can do this and that in Oracle, how does that work with Exasol?” (e.g. creating Materialized Views or Bitmap Indexes or an extra Keep Cache) and my answer was always like “We don’t need that with Exasol to get good performance”.

Let’s face it, an Oracle database is probably one of the most complex commercial software products ever developed. You need years of experience to administer an Oracle database with confidence. See this recent Oracle Database Administration manual to get an impression. It has 1690 pages! And that’s not yet Real Application Clusters, which is additionally 492 pages. Over 2100 pages of documentation to dig through, and after having worked with Oracle for over 20 years, I can proudly say that I actually know most of it.

In comparison, Exasol is very easy to use and to manage, because the system takes care of itself largely. Which is why our Admin class can have a duration of only two days and attendees feel empowered to manage Exasol afterwards.

That was intentionally so from the start: Exasol customers are not supposed to study the database for years (or pay someone who did) in order to get great performance. Oracle realized that being complex and difficult to manage is an obstacle and came out with the Autonomous Database – but that is only available in the proprietary Oracle Cloud.


Using comparable hardware and processing the same (analytical) workload, Exasol outperforms any competitor. That includes Oracle on Exadata. Our Presales consultants regard Exadata as a sitting duck, waiting to get shot on a POC. I was personally shocked to learn that, after drinking the Oracle Kool-Aid myself for years.

In my opinion, these two points are most important: Exasol is faster and at the same time much easier to manage! I mean anything useless could be easy to manage, so that’s not an asset on its own. But together with delivering striking performance, that’s really a big deal.


This is and has always been a painpoint for Oracle customers: The licensing of an Oracle database is so complex and fine granular that you always wonder “Am I allowed to do this without violating my license? Do we really need these features that we paid for? Are we safe if Oracle does a License Audit?” With Exasol, all features are always included and the two most popular license types are totally easy to understand: You pay either for the data volume loaded into the cluster or for the amount of memory assigned to the database. No sleepless nights because of that!


This topic becomes increasingly important as many of our new customers want to deploy Exasol in the cloud. And you may have noticed that Oracle pushes going cloud seriously over the last years.

Exasol runs with all features enabled in the cloud: You can choose between Amazon Web Services, (AWS), Microsoft Azure and ExaCloud


This is presently the most popular way our customers run Exasol in the cloud. See here for more details.

MS Azure

Microsoft’s cloud can also be used to run Exasol, which gives you the option to choose between two major public cloud platforms. See here for more details.


Hosted and managed by Exasol, ExaCloud is a full database-as-a-service offering. See here for more details.

Hybrid Exasol deployments that combine cloud with on-prem can also be used, just depending on customer requirements.

Oracle offers RAC only on the Oracle Cloud platform, not on public clouds. Various other features are also restricted to be available only in Oracle’s own cloud. The licensing model has been tweaked to favor the usage of Oracle’s own cloud over other public clouds generally.

Customer Experience

Customers love Exasol, as the recent Dresner report confirms. We get a perfect recommendation score. I can also tell that from personal encounters: Literally every customer I met is pleased with our product and our services!


Oracle is great for OLTP and okay for analytical workloads – especially if you pay extra for things like Partitioning, RAC, In-Memory Column Store and Exadata. Then the performance you get for your analytical workload might suit your present demand.

Exasol is totally bad for OLTP but best in the world for analytical workloads. Do you think your data volume and your analytic demands will grow?

Categories: DBA Blogs

Index skip scan with high NDV leading column

Tom Kyte - Fri, 2019-03-08 00:26
Hello Tom, I have the situation: A table tbl (220 Mio recs) the following query <code>select col_a, col_b, col_c from tbl t where Mod(Dbms_Rowid.Rowid_Row_Number(t.rowid, 2) = 0 --pred 1 and col_d =<lit>; --pred 2</code> col_d is in...
Categories: DBA Blogs

ORA-29279: SMTP permanent error: 502 Command not implemented while executing a mail proc

Tom Kyte - Fri, 2019-03-08 00:26
Hi Tom, Very Good Morning ! <b>First let me make sure, here no sample table data is needed . </b> Usually for all of the scheduled procedures, we come with some kind of mail procedures, where in we get either success/Failure mail once the j...
Categories: DBA Blogs

Estimate database size from archive

Tom Kyte - Thu, 2019-03-07 06:06
I am new to Oracle...and am being asked by a friend. He has a Remedy (Oracle dB) system and an archive that is 37 MB. He wants to move the archive to a new system... How large will the database be from this archive instance? What are your recom...
Categories: DBA Blogs

How can I show special characters in ascii values

Tom Kyte - Thu, 2019-03-07 06:06
Hi Tom, I would like to show many characters ascii values and I use this sql below <code>set serveroutput on declare c varchar2 (100); a number (3); begin for i in ( select 'abcde' from dual ) loop ...
Categories: DBA Blogs


Tom Kyte - Thu, 2019-03-07 06:06
Hi Beda, What is the added value of JSON_SERIALIZE(expr) compared to JSON_QUERY(expr, '$')? Are there significant differences in the actual code executed underneath? Thanks in advance, Stew Ashton
Categories: DBA Blogs

Migration to 18c from version 10g

Tom Kyte - Wed, 2019-03-06 11:46
Hey folks, We have been stuck on 10g for a while (couldn't migrate off forms and reports apps) and now find we are positioned to drop our forms & reports. We're looking to migrate the db from 10g to 18. Has anyone done this kind of a jump before...
Categories: DBA Blogs

Unwanted results in SQL query

Tom Kyte - Wed, 2019-03-06 11:46
Hello, Ask Tom Team. I have a SQL query but I'm getting unwanted results. <code>SELECT t1.invoice_sender,t1.einvoice,t3.modified_einvoice,SUBSTR(t3.modified_einvoice,2,2),t2.LOADED_606,t4.APPROVALS FROM table1 t1 INNER JOIN table2 t2 on ...
Categories: DBA Blogs

Unable to install 18c Expression Edition

Tom Kyte - Wed, 2019-03-06 11:46
We are unable to install 18c Expression Edition in Windows 10 it is extracting and in middle, it is start rollback. Finally below the below message. Below is the message. The wizard was interrupted before Oracle Database 18c Express Edition c...
Categories: DBA Blogs

Different Plans in SQL and PL/SQL

Tom Kyte - Wed, 2019-03-06 11:46
Hi, in a PLSQL package I generate and execute dynamic SQL Statements. I wrote a litte procedure, which returns the output of dbms_xplan.display into a log table: <code> FUNCTION logPlan(p_statament VARCHAR2) RETURN CLOB IS PRAGMA autonomous_t...
Categories: DBA Blogs

Caching for PLSQL packages over ORDS

Tom Kyte - Tue, 2019-03-05 17:26
I need to cache few values when the plsql procedure is called through a rest service multiple times i.e. when it is executed from a same user multiple times for optimization. I am calling below package procedure through ORDS rest service. Belo...
Categories: DBA Blogs

Replacing card number with *

Tom Kyte - Tue, 2019-03-05 17:26
I have a 16 digit card number where I need to replace the 3rd digit of the card number to 9th digit card number with *.Tried regular expressions,replace and translate nothing worked.Need guidance in this tom!!
Categories: DBA Blogs

Declare a dynamic table type

Tom Kyte - Tue, 2019-03-05 17:26
Hello, I have a stored procedure which takes 2 input paramaters - owner and table_name. I would like to create a TYPE variable based on what is passed. This is the code: <code> create or replace PROCEDURE proc ( in_owner IN VARCHA...
Categories: DBA Blogs

Why are Index Organized tables (IOTs) not supported by interval partitioning

Tom Kyte - Mon, 2019-03-04 23:06
Hi tom, Could you please let me know why we can not use interval partitioning on index organized tables. Thanks in advance
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs