Keep Learning Keep Living – Let's talk about Oracle.

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:

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

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

What…

26ai Alert Log Size

Mustafa Kalaycı, 2025-11-28

Hi, Oracle database writes alert logs into 2 different location with different file formats. To find diag alert location:

select * from v$diag_info where name ='Diag Alert';

1	select * from v$diag_info where name ='Diag Alert';

in this directory, you will find XML files for alert logs. Oracle always writes into log.xml file. Whenever it exceeds a certain threshold, log.xml file is renamed as log_2.xml. on…

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 …

Using MFA While Logging into the Oracle Database

Mustafa Kalaycı, 2025-08-142025-12-08

Hello Everyone, There is another great development on security side of Oracle database users. With latest release updates (I think 19.28 and 23.9) we can now enable Multi Factor Authentication (MFA) while a user is logging into the database. That is a fantastic security feature especially for production environments. Your…

2 of Most Practical New Features on 23.9

Mustafa Kalaycı, 2025-08-09

Hi, with 23.9 I saw 2 beautiful enhancement in SQL. These are not “exceptional” things but their practical usage is a lot to me as a both developer and dba. 23.9 is already available on cloud base database systems. First, INSERT SET is here! one of my biggest complains in…

Milliseconds are lost after date to tz conversion on 19c

Mustafa Kalaycı, 2025-07-15

Hi, yesterday, I found (maybe) a small bug in 19c. Whenever I deal with timestamp with time zones, I always learn something new. so, the request is as this: there is a DATE column which should be converted into a timestamp with tz but if it is null then result…

DDL Generation Failed with ORA-65047: Object SYS.KUPUTIL is invalid

Mustafa Kalaycı, 2025-06-19

Hello everyone, recently I copied a PDB into another CDB and after all the actions are done, developers got error during ddl generation:

ORA-65047: Object SYS.KUPUTIL is invalid or compiled with errors in root.
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPUTIL"
ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 1730
ORA-06512: at "SYS.DBMS_METADATA", line 1216
ORA-06512: at "SYS.DBMS_METADATA", line 8546
ORA-06512: at line 1

ORA-65047: Object SYS.KUPUTIL is invalid or compiled with errors in root.

ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPUTIL"

ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 1730

ORA-06512: at "SYS.DBMS_METADATA", line 1216

ORA-06512: at "SYS.DBMS_METADATA", line 8546

ORA-06512: at line 1

This is basically because and invalid synonym “KUPUTIL”. to overcome the issue, I recreate the synonym and compile invalid objects one by one. it must be…

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….

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…

Password Rollover Time in Seconds

Mustafa Kalaycı, 2024-11-26

Hello, Today, I was working on a new client database security issues. one of the first thing I checked is profile of DBA or highly privilged users. While setting PASSWORD_ROLLOVER_TIME limit in profile, I realized that even if I set it to 1 day, in DBA_PROFILES view it is listed…