Tom Kyte

We are in the process of purging/deleting data from some large tables ex. Our F42199 table had 63 million rows from which we deleted 10 million rows. Since deleting the 10 million rows we have inserted 4 millions new rows Prior to purging the 10 million rows the size of the F42199 table was 158GB derived by querying the dba_extents view. Today the table has grown to 260 GB, much to our surprise as we thought the empty blocks from the deleted rows would be reused. So the question is why are the empty blocks not being reused or why is the table size increasing?

Can an end user create spatial data in Oracle Spatial Studio by digitizing as line or polygon on an apex created front- end map screen and if so, can the user digitized feature be stored as sdo geometry in the Oracle database? For example, a technician entering a road project text data would be able to digitize the road within the Oracle cloud infrastructure using oracle spatial studio like you can with qgis and postgresql.

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

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.

I am interested in learning about the best practices for ensuring that the Oracle optimizer consistently chooses efficient execution plans without relying on hints, SQL patches, SQL profiles, or forced execution plans that may become outdated over time. In your experience, what database features, parameters, and maintenance practices should be implemented to help the optimizer make the right decisions and minimize plan regressions? For example: Which optimizer-related parameters are most important to review? What are the recommended statistics gathering strategies for Oracle 19c? How effective are Real-Time Statistics, Dynamic Statistics, SQL Plan Management (SPM), and Automatic Indexing in preventing regressions? What role do histograms, extended statistics, and accurate object statistics play? How do you manage volatile tables and changing data distributions? What monitoring and validation processes do you use to detect and prevent plan changes before they impact production? I am particularly interested in feedback from teams running large Oracle 19c environments and the lessons they have learned in maintaining stable performance while allowing the optimizer to adapt naturally to data changes. Thank you in advance for sharing your recommendations and real-world experiences.

We use Os authentication for management access control and automating backups and scripts on windows rdbms servers to avoid using any passwords. This works fine on PDBs, but on the cdb level, if we want to access the database as a user with no sys_dba for automating clonning of pdbs we cannot use that as the common_user_prefix and os_authentication_prefix does not allow for a user to be both... Is there a solution around this? I can set the prefix to be the same, but Then Os authentication will not be available on the pdbs!

Hello, I would like to ask for technical guidance on the following scenario. I have a Node.js 24 backend running on Linux, deployed either on AWS EC2 or in an Amazon ECS container. It uses node-oracledb 6.8, also tested with 6.9, in Thin mode with a connection pool to an Amazon RDS for Oracle 19c Single-AZ instance. During planned Oracle/RDS maintenance, the database becomes temporarily unavailable while the Node.js process remains running. After maintenance has completed and RDS is available again, the application does not correctly re-establish database connectivity. The service only starts working again after restarting the Node.js process or container. This has occurred several times with two independent Node.js applications in two different AWS accounts, both based on the same Node.js and RDS for Oracle architecture and configuration. I have already searched Ask TOM and reviewed these related questions: https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:45249405283766 https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:16008986393518 https://asktom.oracle.com/ords/asktom.search?tag=node-reliability-issues However, I could not find an answer covering this complete configuration and planned Amazon RDS maintenance scenario. Is this combination of AWS, Linux, Node.js, node-oracledb Thin mode, and RDS for Oracle 19c commonly used in production? According to current best practices, what is the correct configuration across AWS, RDS for Oracle, Linux, Node.js, and the node-oracledb parameters to handle this scenario reliably and safely? At the application level, how should the JavaScript code behave while the database is unavailable and after it becomes available again? How should connection-pool management, in-flight requests, and the return to normal operation be structured so that no restart is required? Thank you in advance for any technical guidance or direct experience you can share.

Hello All, I encounter an error trying to use a SQL MACRO (TABLE) in freesql.com, in both versions 26ai and 23ai. The function top_n compiles successfully, but trying to call it fails: <code>create or replace function top_n (p_table DBMS_TF.TABLE_T, p_rows NUMBER) return varchar2 SQL_Macro is l_sql varchar2(200) := 'select * from top_n.p_table fetch first top_n.p_rows rows only'; begin dbms_output.put_line('sql='||l_sql); return l_sql; end; / Function TOP_N compiled -- test in SQL select * from top_n (scott.dept, 3) / ORA-00942: table or view "TOP_N"."P_TABLE" does not exist</code> The same example works ok in 19c <code>SQL> select * from top_n (scott.dept, 3) DEPTNO DNAME LOC ------ ------------ ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO sql=select * from top_n.p_table fetch first top_n.p_rows rows only 3 rows selected. </code> It looks like this is a database bug, as I also encounter the same problem in a different 26ai environment. Thanks a lot in advance & Best Regards, Iudith Mentzel

I am running Oracle Database 19c (RU 19.22) in a 4-node RAC extended cluster. During a recent incident, we experienced ORA-32701 ?Possible hangs detected? with Hang IDs (915, 916, 917). The Hang Manager detected a global hang situation where instance 1 was identified as the final blocker (CKPT process / DBRM session), and Oracle eventually evicted the instance due to a ?LOCAL, HIGH confidence hang?. We observed that _hang_resolution_scope is set to INSTANCE in our environment, along with _hang_detection_enabled = TRUE and _hang_resolution_policy = HIGH. My questions are: What is the recommended best practice for _hang_resolution_scope in Oracle 19c RAC 4-node clusters (especially extended RAC)? Should it remain PROCESS in production environments? Under what conditions does Oracle recommend switching to INSTANCE scope, and what are the risks (e.g. cascading instance evictions)? In global hang scenarios (ORA-32701), how does Hang Manager decide between process termination vs instance eviction in modern 19c releases? Are there known best practices to avoid false positives or unnecessary instance evictions in large RAC clusters under heavy batch workloads (PL/SQL, DBMS_SCHEDULER, materialized view refresh)? Any guidance or references to official Oracle best practices would be appreciated. more info for incident trace file for instance 1 *** 2026-06-01T11:36:12.118125+02:00 HM: Hang Statistics - only statistics with non-zero values are listed current number of local active sessions 78 current number of local hung sessions 38 instance health (in terms of hung sessions) 51.29% number of cluster-wide active sessions 224 number of cluster-wide hung sessions 104 cluster health (in terms of hung sessions) 53.58% -------------------------------------- 2297510041,1,0,2297510041,65128162,"06-01-2026 11:36:43.948768000",2502,36203,2,0,"",0,0,0,"",0,0,0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,33792,3,0,0,30070,0,4294967291,0,1,4294967295,0,0,0,0,0,,0,0,165959219,"oracle@ffcrac41.francefrais.local (CKPT)","","","","ffcrac41",0,"" -------------------------------------- log file for instace 3 DIA0 Critical Database Process As Root: Hang ID 915 blocks 7 sessions Final blocker is session ID 2502 serial# 36203 OSPID 32174 on Instance 1 If resolvable, instance eviction will be attempted by Hang Manager