Hemant's Oracle DBA Blog: October 2020

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)

25 October, 2020

Real Time SQL Monitoring using SQL Developer

Since a previous post with screenshots was very popular

I have created a Video Demo on my Youtube Channel

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

Verify Existing Rows in SQL Server

Insert into Oracle with Select from SQL Server

Verify Rows Inserted into Oracle

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

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"

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}

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)