[Oracle Cloud] World's first Oracle Autonomous Data Warehouse Cloud

World's First Autonomous Data Warehouse Cloud

Recently weeks back, Oracle just introduced a new and world's first Autonomous Data Warehouse Cloud Service in its Oracle Public Cloud offerings. 

Here in this article, I will be stepping you through how easily you can provision an Oracle Autonomous Data Warehouse Cloud service in Oracle Public Cloud in just less than 2 minutes, yes, it is that fast provisioning in Oracle Public Cloud Stack.


Login to the Oracle Cloud My Services and select/click the "Autonomous Data Warehouse Cloud" in the Platform services(Having navigated to Oracle Database Cloud Service console).

Once you click on the "Autonomous Data Warehouse Cloud, its service page will be opened. Here I have already created one Autonomous Data Warehouse Cloud instance, so I will be creating another one for this blog demonstration.

Click on the "Create Service" option.

On the "Create New Instance" page, enter the "Database Name" for the Autonomous Data Warehouse Cloud Service, its meaningful description, Notification email where you would get updates once your service creation completes(you will received notification for both failed and successful service creation),

Enter the "Admin" user's password in the Configuration, by default 1 CPU and 1TB storage will be provisioned for the service being created.

Click on Next.

A confirmation summary page for the instance will be displayed now, here is the last chance you can review the configuration and go back to edit the configuration if needed, and click on the "Create" at right top to start the creation of service instance.

You would notice here is that Software Release is 18.1.4 as of now.

Service creation is in progress now.

Now can see that Autonomous Data Warehouse Cloud service is created in just 2 minutes, it is that fast to provision this cloud service in Oracle Public Cloud.

[Oracle Cloud] - SQL_ID new feedback option in SQL*PLUS

Here in this article I will be demonstrating one of the new cute feature introduced in Oracle Database 18c,

Earlier we used to spend quite few seconds to get the problematic SQL_ID of a user session to diagnose the performance issue reported, most of the time user calls or shoot out an email saying that there is his one job which is running longer than expected, and we got to get his username and find the things running in there.


In Oracle Database 18c, a recent release by Oracle, there is new feedback option(SQL_ID) added which developers can use in their code that will directly print the SQL_ID of their reporting jobs or something that is being run the current user session.

Let's go through below example to see how this new feature works in Oracle Database 18c.

I am logging to my Oracle Cloud 18c instance.

 [RAJKUMAR@ORA18C ~]sqlplus "/as sysdba"  
 SQL*Plus: Release 18.0.0.0.0 Production on Tue Apr 17 04:45:35 2018  
 Version 18.1.0.0.0  
 Copyright (c) 1982, 2017, Oracle. All rights reserved.  
 Connected to:  
 Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production  
 Version 18.1.0.0.0   

 SQL>  

 SQL> set lines 300  
 SQL>  
 SQL>  
 SQL> select name, open_mode, database_role , cdb from v$database;  
 NAME   OPEN_MODE      DATABASE_ROLE  CDB  
 --------- -------------------- ---------------- ---  
 ORCL18C  READ WRITE      PRIMARY     YES  
 SQL>  
 SQL> show pdbs  
   CON_ID CON_NAME            OPEN MODE RESTRICTED  
 ---------- ------------------------------ ---------- ----------  
      2 PDB$SEED            READ ONLY NO  
      4 PDB1              READ WRITE NO  
 SQL>  

Now I will be logging to my PDB1 pluggable database instance.

 SQL> alter session set container = pdb1;  
 Session altered. 

 SQL> show con_name  
 CON_NAME  
 ------------------------------  
 PDB1  

Now Let's use the new SQL_ID option of feedback prompt of SQL*PLUS utility. and some SQL commands to check its magic of prompting the SQL_ID for the executed SQL statement.

 SQL> set feedback on SQL_ID  
 SQL> 

 SQL> select count(*) from dba_objects;  
  COUNT(*)  
 ----------  
    73564  
 1 row selected.  
 SQL_ID: g4pkmrqrgxg3b 

Yes, you can see above that after enabling the SQL_ID option of feedback at SQL*PLUS, it prints the SQL_ID of the executed SQL statements, now lets verify the SQL Text for the printed SQL_ID.

 SQL> select sql_id ,sql_fulltext from v$sql where sql_id='g4pkmrqrgxg3b';  
 SQL_ID    SQL_FULLTEXT  
 ------------- --------------------------------------------------------------------------------  
 g4pkmrqrgxg3b select count(*) from dba_objects  
 1 row selected.  

Happy Learning....!!