Long Running Query by OCI Performance Hub Mustafa Kalaycı, 2026-01-27 Hi, We discovered that one of the query run by OCI database management is taking too long and consume a lot of resources. Query is basically getting tablespace information via AWR data but due to some optimization issues it chooses very interesting execution plan. Here is the query: Oracle PL/SQL WITH latest_snap_id AS ( SELECT snap_id, dbid FROM ( SELECT MAX(snap_id) snap_id, dbid FROM dba_hist_snapshot WHERE ( end_interval_time - snap_timezone ) > ( sys_extract_utc(systimestamp) - INTERVAL '1' DAY ) GROUP BY dbid ) WHERE ROWNUM = 1 ), tb_usage as ( SELECT tablespace_name, contents, round((tablespace_size * blocksize) /(1024 * 1024 ), 4) as allocated_space, round((tablespace_usedsize * blocksize) /(1024 * 1024 ), 4) as used_space FROM dba_hist_tbspc_space_usage tb_usage, sys.ts$ all_tbs, dba_tablespaces dba_tbs, latest_snap_id WHERE ts# = tablespace_id AND tb_usage.snap_id = latest_snap_id.snap_id and tb_usage.dbid = latest_snap_id.dbid AND dba_tbs.tablespace_name = all_tbs.name ) SELECT round(SUM(t.allocated_space)/1024, 2) AS "StorageAllocated", round(SUM(t.used_space)/1024, 2) AS "StorageUsed" from tb_usage t having count(*) > 0 123456789101112131415161718192021222324252627282930313233343536373839404142 WITH latest_snap_id AS ( SELECT snap_id, dbid FROM ( SELECT MAX(snap_id) snap_id, dbid FROM dba_hist_snapshot WHERE ( end_interval_time - snap_timezone ) > ( sys_extract_utc(systimestamp) - INTERVAL '1' DAY ) GROUP BY dbid ) WHERE ROWNUM = 1), tb_usage as ( SELECT tablespace_name, contents, round((tablespace_size * blocksize) /(1024 * 1024 ), 4) as allocated_space, round((tablespace_usedsize * blocksize) /(1024 * 1024 ), 4) as used_space FROM dba_hist_tbspc_space_usage tb_usage, sys.ts$ all_tbs, dba_tablespaces dba_tbs, latest_snap_id WHERE ts# = tablespace_id AND tb_usage.snap_id = latest_snap_id.snap_id and tb_usage.dbid = latest_snap_id.dbid AND dba_tbs.tablespace_name = all_tbs.name )SELECT round(SUM(t.allocated_space)/1024, 2) AS "StorageAllocated", round(SUM(t.used_space)/1024, 2) AS "StorageUsed"from tb_usage thaving count(*) > 0 What… Continue Reading
What Should Change After Base DB System Clone Mustafa Kalaycı, 2025-11-12 Hi, if you are working on Oracle Cloud (which you should), you might copy your database to create a new environment. For example you can copy your DEV database as TEST. to achieve that there are many options but I will focus specific one. “Clone DB System”. In Oracle Cloud … Continue Reading
How to call HTTPS Url Without SSL Wallet in 19c Mustafa Kalaycı, 2025-04-06 Hi, as you know for over the years, if you want to call a secure url (https) you must add the certificates to your ssl wallet first. this operation, most probably, requires also DBA action. Finally with 23ai changed this and now you can call a https url without wallet…. Continue Reading
Is Table Unnecessary for Pipelined Function Mustafa Kalaycı, 2025-04-05 Hello everyone, What a busy year. I couldn’t find time to write something and so much is happening, AI models, Quantum etc. So I want to write more now. I know that many people think that blogging is dead but I don’t share this thought because AI is still not… Continue Reading
PDB Syncronization Issue Mustafa Kalaycı, 2024-11-19 Hello, 2024 was hell of a year for me (still is) I couldn’t even check my blog. I just want to add a post, something small. From time to time I got pdb_plug_in_violations records about sync pdb comments like grants/revokes. I use a common user to manage my pdbs. Lets… Continue Reading
How to limit DB Link Connection Timeout Mustafa Kalaycı, 2024-07-03 Hi, I just want to share something small that I dug to learn. I didn’t work with db links for years and now all of a sudden I have tens of them. This comes with new challenges of course. When you tried to run a query or function/procedures over a… Continue Reading
Cloud Base Database Service Mustafa Kalaycı, 2024-04-252025-12-08 Hi, Since I relocated to the Netherlands, I cannot find much time to write but finally I have some spare time. Recently, we migrated a client database environment to the cloud and to achieve that I started to learn cloud at the end of 2023 (I know I am “a… Continue Reading
29th of February and interval Mustafa Kalaycı, 2024-02-29 Hi, it has been a long time since I post something. I just relocated to a different country, the Netherlands, and I was dealing with so much paperwork and stuff. So far so good. Dutch people are so kind and warm, they help about everything. My first impression is amazing… Continue Reading
Guid vs Sequences Mustafa Kalaycı, 2023-12-29 Hi Everyone, I haven’t written any posts lately because there is so much going on in my life and biggest change is I am moving to the Netherlands. it is a big change for me and my family but I hope everything will be fine (if we can find a… Continue Reading
How to Compare Two Tables Mustafa Kalaycı, 2023-10-262025-12-08 Hi, Few days ago, I joined Chris Saxon’s webinar about “how to compare two tables”. Chris is one of the amazing guy in Oracle community and he shared different methods about how to compare two tables and also, strengths and weakness of the methods. before reading my post, I strongly… Continue Reading