29 October, 2020

Getting your SQL Statement's SQL_ID

 SQL*Plus now can provide you the SQL_ID of the last statement executed in your own session with SET FEEDBACK SQL_ID.

A quick demo :


SQL> set feedback on sql_id
SQL> select count(*) from my_target where factory='SYS';

  COUNT(*)
----------
     52217

1 row selected.

SQL_ID: g1mk14hdxc1ww
SQL> select * from table(dbms_xplan.display_cursor('g1mk14hdxc1ww'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g1mk14hdxc1ww, child number 0
-------------------------------------
select count(*) from my_target where factory='SYS'

Plan hash value: 1690349505

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| MY_TARGET_NDX |  2683 | 13415 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FACTORY"='SYS')


19 rows selected.

SQL_ID: 5dyyqqwuyu01v
SQL>


After I executed my query against the "my_target" table, SQL*Plus provided my the SQL_ID ("g1mk14hdxc1ww").  I could then immediately get the Execution Plan for the statement, without having to query for the SQL_ID in V$SQL or V$SQLAREA.

This demonstration is with SQL*Plus 19.0 against a 19c Database.  (I think "set feedback on sql_id" was introduced in SQL*Plus 18)




11 October, 2020

On-Premises SQL Server to Oracle ADB on the Cloud -- 2

 Having configured connectivity between an On-Premises SQL Server Database to an Oracle ADB Database in the Oracle Cloud in the previous post, I will now copy data from SQL Server to Oracle


  • Create the Target Table in Oracle ADB 

SQL Developer


  • Verify Existing Rows in SQL Server


SQL Server Management Studio

  • Insert into Oracle with Select from SQL Server

SQL Server Management Studio


  • Verify Rows Inserted into Oracle

SQL Developer


  • Create New Row in SQL Server and Verify that it is NOT present in Oracle
SQL Server Management Studio


The last screenshot is literally a NOT IN query between SQL Server On-Premises and Oracle in the Cloud.












10 October, 2020

On-Premises SQL Server to Oracle ADB on the Cloud - 1

Setting up a connection between an On-Premises SQL Server Database Instance to an Oracle ADB on the Oracle Cloud

Strong Caveat :  This is only a POC.  Most organisations would NOT allow an open direct connection between an On-Premises Database and and External Site (whether a Database or any other Service).  

1. On my Free-Tier Oracle ADB, I login as the ADMIN user and configure a new database account "ss_user"

SQL Developer to Oracle ADB


2. I install Oracle Client and configure connectivity to the ADB database with the Wallet information

(for detailed instructions see the Oracle Cloud documentation here).  {I used an 18c Client on Windows to connect to 19c ADB, simply because I already and 18c client and didn't want to wait to download the 19c client}

sqlnet.ora



tnsnames.ora



3.  Optionally re-register the OraOLEDB18.DLL file  (using CMD as Administrator)  (you might also need to reboot your Windows or restart the SQL Server Instance)

CMD as Administrator




4.  Define the Linked Server in SQL Server (using SSMS)


Oracle OLEDB Provider




Linked Server Configuration Pag


Linked Server Security Configuration


5.  Test Connectivity

Test Connectivity Option for Linked Server



6  Run queries against from SSMS


SSMS Query and Results Pane


See the next blog post on actual transaction and query (insert and select from On-Premises SQL Server to Oracle ADB in the cloud)

03 October, 2020

Extracting DDL using SQL Developer

 The "DDL" command in SQL Developer 20.2 new mimics the command in sqlcl


Here I extract the definition my table OBJECTS_LIST and it's index



Here I extract the code for a Stored Procedure