DBA Blogs

(Not so serious) question is in the subject :-)

Hello, I know that in single instance I can use DBA_BLOCKERS and DBA_WAITERS to find blocking sessions and waiting sessions. But this does not work in RAC if blocking and waiting sessions run on different instances. How should I write the query using GV$LOCK in RAC ? Could you also explain what does GV$LOCK.BLOCK = 2 really mean and how this can be used; 2 - The lock is not blocking any blocked processes on the local node, but it may or may not be blocking processes on remote nodes. This value is used only in Oracle Real Application Clusters (Oracle RAC) configurations (not in single instance configurations). Thanks.

Hi, just learned about DBMS_DST.get_latest_timezone_version today ( https://oracle-base.com/articles/misc/update-database-time-zone-file ) <code> SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_40.dat 40 0 SQL> SELECT DBMS_DST.get_latest_timezone_version from dual; GET_LATEST_TIMEZONE_VERSION --------------------------- 44 SQL> select banner_full from v$version; BANNER_FULL ---------------------------------------------------------------------------------------------------- Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.28.0.0.0 </code>, want to know more about it (what does it do ? how does it work ?, what are prerequisities>?, which exceptions can it raise ?) however, it does not seem to be documented. ( neither https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_DST.html nor on https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_DST.html )

Hi, I have a requirement where multiple users need to create Oracle Scheduler jobs in a dedicated target schema. For example, suppose I have two users: USER1 ? the user who creates the job USER2 ? the target schema and intended owner of the job If USER1 creates a Scheduler job for USER2, for example: BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'USER2.MY_JOB', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN NULL; END;', enabled => TRUE ); END; / the resulting job has: OWNER = USER2 JOB_CREATOR = USER1 This is expected behavior. However, there is an issue with this approach in our environment. If USER1 is later dropped from the database, the job still exists and its JOB_CREATOR points to a user that no longer exists. Our requirement is that when USER1 creates a job for USER2, both of the following should be USER2: OWNER = USER2 JOB_CREATOR = USER2 We tried creating a procedure in USER2 that calls DBMS_SCHEDULER.CREATE_JOB, and then calling that procedure from USER1. However, JOB_CREATOR is still recorded as USER1. Is there a supported way in Oracle to create a Scheduler job from USER1 for USER2 while having JOB_CREATOR recorded as USER2? Alternatively, is there a recommended Oracle architecture for this use case, especially when USER1 may be dropped later but the Scheduler jobs must remain valid and associated with USER2? Would Proxy Authentication, definer-rights procedures, or another mechanism be appropriate for this? Thank you.

I have an application root with several applications ( from testing out the functionality) Example: Application A create user XXX Application B create table XXX.T1 ... Now I decide to uninstall application A <code>alter pluggable database application A begin uninstall;</code> but can't drop the user XXX <code>drop user XXX cascade;</code> Error report - ORA-00604: error occurred at recursive SQL level 1 ORA-65311: cannot modify the common object of another application so I realise I need to first drop the table XXX.T1 which was installed by application B, so I need to uninstall application B to do this but I can't end the uninstall of application A <code>alter pluggable database application A end uninstall;</code> Error report - ORA-65344: cannot uninstall or purge an application that has objects, users, roles, and profiles and I can't begin the uninstall of application B <code>ALTER PLUGGABLE DATABASE APPLICATION B BEGIN UNINSTALL;</code> Error report - ORA-65213: existing application action in progress If I try to drop the user outside of the application action <code>drop user XXX cascade</code> Error report - ORA-00604: error occurred at recursive SQL level 1 ORA-65311: cannot modify the common object of another application I'm stuck. When I check the objects in Application A there is none ( from dba_objects ) because the only thing in application A is the create user statement ( seen from dba_app_statements). How can I abort the uninstall of application A as this is preventing me from beginning an uninstall of application B ? Ideally I'd like to remove all the applications I created while testing this functionality. Thanks

HI Tom, My question is I have 9+ years of experience working in oracle as a developer with sql,pl/sql, forms,reports and Apex.. Do you think considering my experience it is required for me to go for developer with DBA or stay as Developer itself understanding the latest features in every new version of sql and pl/sql? Is it practically possible for me to be a developer and a DBA. Which is better for career opportunities and long stand in the industry. Your sincere suggestion on this is of great help fro my future career also. Thanks Prashanth R

We have an application developed using Oracle Forms and Oracle Reports, where application user IDs and passwords are maintained and authenticated within our database. As part of ongoing business requirements, we frequently receive requests for data extraction and reporting. We are currently evaluating Oracle APEX as a platform for developing these reports. We would appreciate your guidance on the following questions: 1. Can reports developed in Oracle APEX be launched directly from Oracle Forms? a) If this is supported, what is the recommended integration approach? 2.What is the launch mechanism for invoking an Oracle APEX report from Oracle Forms? Could you provide a sample URL or reference architecture demonstrating this integration? 3.How should user authentication be handled between Oracle Forms and Oracle APEX? a) Is a separate login to Oracle APEX required when a user launches an APEX report from Oracle Forms? b) Alternatively, can Oracle APEX leverage the existing authenticated user session from Oracle Forms, enabling a seamless Single Sign-On (SSO) experience without prompting the user to log in again?

1. The table is partitioned by list ex: week1_user1, week2_user1 2. Each partition contains around 900M records 3. Every week, we are truncating the week2_user1 partition and moving the data from week1_user1 to week2_user1 <code>insert /*+ parallel(8) enable_parallel_dml */ into t1 select /*+ FULL(t1) */'week2_user1' col1,col2,col3 from t1 where col1='week1_user1';</code> col1 is the partition by list column 4. Plan looks like <code> -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 78705 (100)| | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 682M| 165G| 78705 (15)| 00:00:07 | | | Q1,02 | P->S | QC (RAND) | | 3 | INDEX MAINTENANCE | T1 | | | | | | | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 682M| 165G| 78705 (15)| 00:00:07 | | | Q1,02 | PCWP | | | 5 | PX SEND RANGE | :TQ10001 | 682M| 165G| 78705 (15)| 00:00:07 | | | Q1,01 | P->P | RANGE | | 6 | LOAD AS SELECT (HIGH WATER MARK BROKERED)| T1| | | | | | | Q1,01 | PCWP | | | 7 | OPTIMIZER STATISTICS GATHERING | | 682M| 165G| 78705 (15)| 00:00:07 | | | Q1,01 | PCWP | | | 8 | PX RECEIVE | | 682M| 165G| 78705 (15)| 00:00:07 | | | Q1,01 | PCWP | | | 9 | PX SEND RANDOM LOCAL | :TQ10000 | 682M| 165G| 78705 (15)| 00:00:07 | | | Q1,00 | P->P | RANDOM LOCA| | 10 | PX BLOCK ITERATOR | | 682M| 165G| 78705 (15)| 00:00:07 | KEY | KEY | Q1,00 | PCWC | | |* 11 | TABLE ACCESS FULL | T1| 682M| 165G| 78705 (15)| 00:00:07 | KEY | KEY | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------------------------------...

This is a follow up to my July post. I have had almost two months to work with my new Selectloop Python script and I want to tell people what I learned so it will be helpful to them. Also it will help me to write this out and think about what I want to say.

First, I want to say that even though this post is about AI it is not generated by AI. This is all my words. I’m not even going to use spell or grammar checkers. I tend to write without commas so unless I get motivated to go back later and figure out where they go this may be comma free. I wrote my previous post but I did go to Copilot for advice and I feel like it makes what I write more generic. With all the “AI slop” out there you don’t need me adding to it.

Second, I was very suprised at how little interest my previous post generated. I documented Selectloop both on this blog and internally. My own DBA team had zero interest and no one commented on the blog post and it did not get many hits. That’s fine but what is funny is how excited I am about Selectloop and what I learned through it. Somehow I have not connected with people about how significant this is. Maybe it is because I have been on this AI journey and many of my readers and coworkers have not. Maybe people have been burned out on too much AI hype. To finally find a valuable use for AI has been such a joy. Maybe this post will help. I’ve learned some practical things:

• Run Selectloop for only a few minutes and look for quick results
• The generated queries are often more useful than the final report
• If you ask the same question twice it might give a good answer once and a bad the other time

So, I’ve simplified the command line to look like this:

python selectloop.py MYDATABASE question.txt

It runs with these settings:

• 30 select statements
• 120 second timeout for SQL query and AI inference
• 5000 line max rows fetched from query

This typically runs in a few minutes. I like to set it running while doing something else. You can also run several runs at the same time. 30 queries is enough to see if it is going down the right path. This is just from my experience. Sometimes the queries have syntax errors so you want to run enough so you get a fair number that actually run.

Here is an example of a simple question.txt file:

Find the top query for the past week and
describe its plan and purpose if you can.

Here is the console output from the run:

$ python selectloop.py MYDATABASE question.txt

Database: MYDATABASE
Question file: question.txt
Report file: selectloop_report_07482b1e.txt
Number of loops: 30
Query and Bedrock timeout in seconds: 120
Maximum number of rows fetched from query: 5000

Processing select statement 1 prompt length = 355
Processing select statement 2 prompt length = 1321
Processing select statement 3 prompt length = 1789
Processing select statement 4 prompt length = 2958
Processing select statement 5 prompt length = 5237
Processing select statement 6 prompt length = 6721
Processing select statement 7 prompt length = 8025
Processing select statement 8 prompt length = 9642
Processing select statement 9 prompt length = 10419
Processing select statement 10 prompt length = 10853
Processing select statement 11 prompt length = 11463
Processing select statement 12 prompt length = 14170
Processing select statement 13 prompt length = 14706
Processing select statement 14 prompt length = 15017
Processing select statement 15 prompt length = 15485
Processing select statement 16 prompt length = 15935
Processing select statement 17 prompt length = 17948
Processing select statement 18 prompt length = 21775
Processing select statement 19 prompt length = 23310
Processing select statement 20 prompt length = 24142
Processing select statement 21 prompt length = 26078
Processing select statement 22 prompt length = 34193
Processing select statement 23 prompt length = 36095
Processing select statement 24 prompt length = 36933
Processing select statement 25 prompt length = 37713
Processing select statement 26 prompt length = 43045
Processing select statement 27 prompt length = 49334
Processing select statement 28 prompt length = 51252
Processing select statement 29 prompt length = 54094
Processing select statement 30 prompt length = 55196

Generating final report

Run time in seconds: 179

Here is the final report.

*** Report generated by AI. It may contain errors. ***

TOP QUERY REPORT: PAST WEEK
===========================================================================

MOST EXECUTED QUERY (SELECT 3)
-------------------------------
SQL ID : d7bgf84kwj3s7
Executions : 2,816,949
Last Active : 2026-09-29 22:02:38

SQL Text:
  SELECT /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */
  P.VALCHAR FROM SYS.OPTSTAT_USER_PREFS$ P
  WHERE P.OBJ#=:B2 AND P.PNAME=:B1

PURPOSE:
This is an internal Oracle optimizer statistics query. It reads
per-object optimizer preferences from the SYS.OPTSTAT_USER_PREFS$
table. Oracle calls it during statistics gathering to check whether
any object-level overrides (e.g. degree, method_opt) have been set
via DBMS_STATS.SET_TABLE_PREFS.

EXECUTION PLAN (SELECT 4):
  Step 0: SELECT STATEMENT (cost=1)
  Step 1: TABLE ACCESS BY INDEX ROWID on SYS.OPTSTAT_USER_PREFS$
  Step 2: INDEX UNIQUE SCAN on SYS.I_USER_PREFS$
          Access: P.OBJ#=:B2 AND P.PNAME=:B1

The plan is optimal: a unique index lookup with no disk reads
(SELECT 5: disk_reads=0) and exactly 1 buffer get per execution.
Avg elapsed and CPU times are near zero microseconds per call.

CONTEXT (SELECT 6, 7, 11):
The single ASH sample ties this SQL to SYS running DBMS_SCHEDULER
job ORA$AT_OS_OPT_SY_13592, the Oracle auto optimizer stats task.
SELECT 11 confirms "auto optimizer stats collection" is ENABLED and
completing successfully across daily maintenance windows (SELECT 16).

No action is required. This is expected Oracle background activity.
===========================================================================

This was not an active database so the top query was a system generated one.

Notice how it refers to the select numbers like this:

EXECUTION PLAN (SELECT 4):
  Step 0: SELECT STATEMENT (cost=1)
  Step 1: TABLE ACCESS BY INDEX ROWID on SYS.OPTSTAT_USER_PREFS$
  Step 2: INDEX UNIQUE SCAN on SYS.I_USER_PREFS$
          Access: P.OBJ#=:B2 AND P.PNAME=:B1

Here is select number 4:

-- SELECT number 4

-- Retrieves execution plan details for SQL ID 'd7bgf84kwj3s7' from v$sql_plan.

SELECT
    p.plan_hash_value,
    p.operation,
    p.options,
    p.object_owner,
    p.object_name,
    p.object_type,
    p.cost,
    p.cardinality,
    p.bytes,
    p.cpu_cost,
    p.io_cost,
    p.access_predicates,
    p.filter_predicates,
    p.id,
    p.parent_id,
    p.depth,
    p.position
FROM v$sql_plan p
WHERE p.sql_id = 'd7bgf84kwj3s7'
ORDER BY p.plan_hash_value, p.id;

PLAN_HASH_VALUE        OPERATION        OPTIONS OBJECT_OWNER         OBJECT_NAME    OBJECT_TYPE COST CARDINALITY BYTES CPU_COST IO_COST                  ACCESS_PREDICATES FILTER_PREDICATES ID PARENT_ID DEPTH POSITION 
--------------- ---------------- -------------- ------------ ------------------- -------------- ---- ----------- ----- -------- ------- ---------------------------------- ----------------- -- --------- ----- -------- 
      324713838 SELECT STATEMENT           None         None                None           None    1        None  None     None    None                               None              None  0      None     0        1 
      324713838 SELECT STATEMENT           None         None                None           None    1        None  None     None    None                               None              None  0      None     0        1 
      324713838     TABLE ACCESS BY INDEX ROWID          SYS OPTSTAT_USER_PREFS$          TABLE    1           1    42     8381       1                               None              None  1         0     1        1 
      324713838     TABLE ACCESS BY INDEX ROWID          SYS OPTSTAT_USER_PREFS$          TABLE    1           1    42     8381       1                               None              None  1         0     1        1 
      324713838            INDEX    UNIQUE SCAN          SYS       I_USER_PREFS$ INDEX (UNIQUE)    0           1  None     1050       0 "P"."OBJ#"=:B2 AND "P"."PNAME"=:B1              None  2         1     2        1 
      324713838            INDEX    UNIQUE SCAN          SYS       I_USER_PREFS$ INDEX (UNIQUE)    0           1  None     1050       0 "P"."OBJ#"=:B2 AND "P"."PNAME"=:B1              None  2         1     2        1 

6 rows selected.

Elapsed time: 0.00 seconds

It’s really easy to refer back to the select statements from the final report using the number.

I’ve updated the repository to contain this updated version: https://github.com/bobbydurrett/SelectLoop

The only hassle with the repository is that you have to do your own setup of your AWS account and database credentials. But the main code is all there in selectloop.py.

So, the point of this is just that Selectloop will run 30 queries for you against an Oracle database. You can ask it whatever you want. It runs for a few minutes. It may or may not be useful. But there have been times when it has been very useful in my work. You really have nothing to lose cutting one or more executions of Selectloop loose while you continue to work in other ways. Chances are if it is useful you will find not only good information in the final report, but also in the list of select statements and their output. I don’t know. I think it is extremely cool. If you are up for it, give it a try.

Bobby

Striim 5.4.2 gives AI agents pipeline access via MCP and Clara, without bypassing your existing controls.

The post AI Agents at the Data Pipeline Layer: What Striim 5.4.2 Actually Hands Over (and What It Doesn’t) appeared first on DBASolved.

A Derby duplicate-key error can block Striim from restarting right after you apply a license key — here's the fix.

The post When Your Demo Derby Database Blocks a Striim Restart appeared first on DBASolved.

 My video demonstration on using python-oracledb to extract data from an Oracle Database and read it into a Pandas Dataframe 

The main documentation page for Python-oracledb

Fetching to a DataFrame

Moving your Striim metadata repository off the default Derby instance and onto PostgreSQL, step by step, with real SQL.

The post So, You Want to Move Your Striim MDR to PostgreSQL? appeared first on DBASolved.

After six great years, I'm closing RheoData and joining Striim as Senior Director of Product Marketing. Here's why.

The post Same River, New Current: Joining Striim appeared first on DBASolved.

I have a task to modify PDB parameters, for example, setting `RECYCLEBIN` to `ON`. I changed the parameter on the primary PDB, but the change is not reflected on the standby database. The Parmeter is dynamic even its not reflected on standby side.

Hello, I was messing around with Application Containers, Roots, and PDBs and ran into something very interesting that I do not fully understand. It appears that the Application Root creates clones of itself when performing application sync - more specifically an uninstall. Please see below example: <code> create pluggable database app_root2 as application container admin user pdbadmin identified by "c0nn0r_h3lp!" -- use only if you're not using oracle managed files -- ensure you set the directory to where your datafiles are stored file_name_convert=('/u03/datafiles/ORCL/pdbseed/','/u03/datafiles/ORCL/APP_ROOT2/'); alter pluggable database APP_ROOT2 open; alter session set container = APP_ROOT2; alter pluggable database application test1 begin install '1.0'; create table test1 sharing=data (column1 number, column2 varchar2(120)); alter pluggable database application test1 end install; create pluggable database cust3 admin user pdbadmin identified by "cust1_pwd!" file_name_convert=('/u03/datafiles/ORCL/pdbseed/','/u03/datafiles/ORCL/APP_ROOT2/CUST3/'); alter pluggable database cust3 open; -- look at the list of PDBs prior: alter session set container = CDB$ROOT; show pdbs; -- go back into the root and sync: alter session set container = APP_ROOT2; alter session set container = CUST3; alter pluggable database application test1 sync; alter session set container = APP_ROOT2; alter pluggable database application test1 begin uninstall; drop table test1; alter pluggable database application test1 end uninstall; alter session set container = cust3; alter pluggable database application test1 sync; alter session set container = CDB$ROOT; select con_id, name, application_root, application_pdb, application_root_con_id from v$pdbs where name like 'F%'; select con_id, name, application_root, application_pdb, application_root_con_id from v$pdbs -- be sure to put YOUR application root con id returned from the above query here in this where clause where con_id = 12; </code> Any attempt to close, alter, etc. this "fake" PDB is rejected with an "ORA-65266 - application root clone may not be dropped, unplugged or altered" Another interesting note it is classified as an application root and an application PDB - which if we look at the root PDB it is an Application Root but not an application PDB. My two questions: 1. What exactly is this PDB? 2. Why is it created during application synchronization/uninstallation? Thank you

Hello Tom, I have just upgraded to APEX v26.1 and enabled Natural Language Support for Interactive Report on one of my IRs, as a new feature of this upgrade. I have also created a Generative AI provider using OpenAI using the gpt-5.6-terra model. Using this AI provider for other AI features of APEX works fine. When I then try to search or ask a question on the said IR, it throws an error: ?<i>ORA-20954: The HTTP request to Generative AI Service at https://api.openai.com/v1/chat/completions failed with HTTP-400: Function tools with reasoning_effort are not supported for gpt-5.6-terra in /v1/chat/completions. To use function tools, use /v1/responses or set reasoning_effort to 'none?.</i>" Upon reading the latest APEX documentation further, it clearly says: <i>OpenAI - Uses /chat/completions for chat and /embeddings for vector generation. OpenAI expects JSON with messages[] and model parameters, and authenticates using a Bearer token. APEX maps its internal format to the OpenAI schema, injects credentials, and normalizes responses so applications see a consistent structure across models and providers.</i> That explains the problem. OpenAI have also made it clear that they?re migration their tools to use /v1/responses, though /v1/chat/completions is till supported for some things. Is there a workaround for this, other than using a different AI provider? Personally, the limitation as far as I can tell is that Oracle APEX does not seem to be providing the flexibility, at least on the Natural Language Support for Interactive Report feature, to select a REST API to an AI provider tool. The Natural Language Support for Interactive Report feature relies on APEX?s preconfigured internal mapping to the supported AI providers. Am I wrong?

Hi, If I?m reading the documentation (picture of timing_point_section in https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/CREATE-TRIGGER-statement.html) correctly, variable declarations are not allowed within the timing-point-section. However, the compiler accepts the declarations and the code works as well. The question is: Is the documentation incomplete or is my code wrong? Here?s a meaningless but working example: <code> CREATE OR REPLACE TRIGGER cmptrg_test FOR UPDATE OR INSERT OR DELETE ON my_table COMPOUND TRIGGER l_var number; AFTER STATEMENT IS l_cnt NUMBER; --possible here? The documentation doesn?t mention this or am I wrong? BEGIN NULL; --do something with l_cnt... END AFTER STATEMENT; END cmptrg_test; /</code> Thank you for clarifying, Florian.

I am looking for feedback from DBAs and performance experts who have experience managing highly volatile tables in Oracle Database 19.31. In our environment, we have work tables, processing tables, and staging tables that can grow from a few thousand rows to several million rows during the same processing cycle. Managing optimizer statistics on these objects has become a challenge. If statistics are unlocked, Oracle may automatically gather statistics while business processes are running. In some situations, this introduces additional overhead and can increase execution times. On the other hand, when statistics become stale or do not accurately reflect the current data volume, the optimizer may choose inefficient execution plans, resulting in significant performance regressions. I would like to understand the most effective strategy for this type of workload: ?Do you regularly gather statistics on highly volatile tables, or do you prefer to lock them? ?How do you handle tables whose row counts change dramatically within a single batch process? ?What role do Dynamic Statistics (Dynamic Sampling) and Real-Time Statistics play in this scenario? ?Can these features provide sufficiently accurate cardinality estimates to help the optimizer consistently choose good execution plans without frequent statistics gathering? ?Have you observed plan instability or regressions when relying mainly on Dynamic Statistics or Real-Time Statistics? ?Are there any Oracle 19c (specifically 19.31) best practices that you would recommend for this type of environment? My main objective is to maintain stable and efficient execution plans while minimizing the overhead of statistics collection on large volatile tables. I would be very interested in hearing about real-world experiences, lessons learned, and practical recommendations from those who have faced similar challenges. Thank you in advance for your insights.