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

Oracle Database Security Master Class AZORA Meetup Thursday

Bobby Durrett's DBA Blog - Fri, 2019-03-01 16:33

Next Thursday Daniel Morgan from TidalScale will be giving a free Oracle Database Security Master Class for our March Arizona Oracle User Group meeting. See the details in our Meetup link:

#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }
Oracle Database Security Master Class

Thursday, Mar 7, 2019, 12:00 PM

Republic Services – 2nd Floor Conference Rooms
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

13 AZORAS Attending

Oracle Database Security Master Class: Year after year Oracle databases that should be secure are violated. In some cases data is stolen, in some cases data is altered, in some cases the database is not the target but rather is means to an end. In almost all cases there is no excuse. No excuse … but there is an explanation. Oracle professionals d…

Check out this Meetup →

Please RSVP so that we know how many people will be there.

Given the ever-increasing number of security threats to our databases this session on Oracle database security should be valuable to us all.

I hope to see you there!


Categories: DBA Blogs

Installing Oracle GoldenGate 18c Microservices on Windows…. I know, why?

DBASolved - Fri, 2019-03-01 12:00

I’m not a huge fan of the Windows platform although I’ve spent the early part of my career as a Systems Admin working on Windows. As I’ve gotten a bit older, Linux/Unix seem to be more my way of thinking; however, there are still many customers out there who run Oracle Databases and Microsoft SQL Server databases on the Windows platform. They still have a need to move data, right?

In this post, you’ll walk through how to install (yes a boring installation post) Oracle GoldenGate 18c Microservices on the Windows platform. This will include building the ServiceManager and first deployment. First there are a few prerequisites that need to be installed on the Microsoft Windows Platform. These items are:

  • Microsoft Visual C++ 2010 SP1 (64-bit)
  • Microsoft Visual C++ 2012 (64-bit)
  • Microsoft Visual C++ 2017 (64-bit)

You wll also need to confirm with the certification matrix on what platforms are supported. You can find the certification matrix here.


Just like previous releases starting with Oracle GoldenGate 12c (12.1.x), where the Oracle Universal Installer (OUI) was added; Oracle GoldenGate 18c is installed the same way on Windows as on *nix platforms. For this post, you will look at the OUI and the Oracle GoldenGate Configuration Assistance (OGGCA) on Windows.

1. Unzip the downloaded zip file to a directory of your choice. The zip file can either be pulled from Oracle EDelivery or from the Oracle Technology Network. The command below will unzip the file from a Linux prompt, but you can use the Windows Extractor or Winzip to unzip the file.

unzip V980818-01.zip -d ./ogg18c_win

2. Navigate to the directory where the you unzipped the file. You will find a directory called fbo_gg_Windows_x64_services_shiphome. Inside of this directory you will find another called Disk1. You will need to be inside of the Disk1 directory.

3. From the Disk1 directory, you will run the Setup application. You can double-click this application. This will start the Oracle Universal Installer.

4. On the first step of the Oracle GoldenGate 18c installation, you need to specify what version of the Oracle Database you will run Oracle GoldenGate against. Since there is an Oracle Database 18c installed, select the Oracle GoldenGate for Oracle Database 18c.

5. The Software Location is the same at the Oracle GoldenGate Home (OGG_HOME) location. Specify where the software should be installed.

6. Finally, confirm everything you have selected during the installation wizard and click “Install”. At this time, you can also option to save the install steps into a response file by click the “Save Response File” button.

7. At this point, the installation will begin and should complete fairly quickly. Once the installation is done, the Oracle Universal Installer can be closed.

With the Oracle GoldenGate 18c (18.1.0) software installed, you now want to to build your ServiceManager and first deployment. In order to do this, you will need to go to the $OGG_HOME\bin directory and run the batch file called OGGCA.

To run the Oracle GoldenGate Configuration Assistant (OGGCA (batch file)):

1. Navigate to the Oracle GoldenGate Home and enter the $OGG_HOME\bin directory

2. Double-Click on the batch file for the Oracle GoldenGate Configuration Assistanct (oggca). This will start the configuration assistant.

3. Being that this is a new installation, you will provide all the following information:

  • Select the radio button for “Create New Service Manager”
  • Provide the ServiceManager Deployment Home location
  • Leave “Listening hostname/address” as the default
  • Provide a “Listening Port”
  • Select the checkbox for “Registering Service Manager as a system service/daemon”

After you have provide all the needed details, select Next to move through the wizard.

4. Next step, you will add a new GoldenGate Deployment. On this step, the only thing you need to do is click “Next”. The radio button should already be selected for you.

5. Now you will need to provide the deployment details. This consists of the Deployment Name and the Software Home. Keep in mind that the Software Home is the Oracle GoldenGate Home (OGG_HOME). Should be automatically populated as well.

6. After defining the name of the Deployment, you are given the opportunity to provide the deployment home location. On this screen, you can also choose to customize the locations of various deployment files. For now, just provide a deployment home.

7. Next step is to make sure your environment variables are correct.

8. Provide an administrator account. This account is defined at the “Security Role” within the Microservices Deployment framework. This is the same account that will be used to login to the ServiceManager and associated Deployments. If this account is lost, there is no way to recreate the account to date. This same account will need to be use when creating additional deployments under the same ServiceManager.

9. At this point, you have to choose if you want your deployment to be secure or un-secure. To simplify the understanding of the install, lets go with un-secure; however, we will not be able to change the decision unless we recreate the deployment. Use the checkbox for SSL/TLS security very wisely.

10. Now you will assign port numbers to all the services within the deployment. These port numbers will allow you to access each service individually of each other. There is a port number for the following services:

  • Administration Server
  • Distribution Server
  • Receiver Server
  • Performance Metric Server – TCP
  • Performance Metric Server – UDP

Additionally, you need to select the type of NoSQL database that will be used for storing performance metrics information. In the example, you selected Berkely database (BDB). You can also select Light Memory Database (LMDB). Lastly, you will provide a location where the database information will be stored.

11. Now, you will provide the default GoldenGate schema. This is the GoldenGate user that will be inside of the Oracle Database/Pluggable Database. Items like Automatic Heartbeat and Checkpoint tables will go into this schema. In this example, lets call it GGATE.

12. Before creating the deployment, you now have the opportunity to review your selections on the Summary page. At the same time, you can select the “Save Response File” and edit the resulting file for silent installs of the deployments as well. If you are happy with everything you are selecting, then you can click “Finish” to begin building the ServiceManager and associated first deployment.

After clicking “Finish”, the Oracle GoldenGate Configuration Assistant will build the ServiceManaager and the first deployment. At this point, you should be able to access the ServiceManager using the hostname and port number you specified during the configuration.

Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs