Tom Kyte

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

Hi, this is a general question. I tried to explain a very long a 80Kb of aggregation and pivot query and I get an error "ORA-04031: unable to allocate 40 bytes of shared memory". Particularly when I try to add a parallel hint. Errors in file /tools/list/oracle/product/diag/rdbms/bnkppd/bnkppd/trace/bnkppd_ora_5178102.trc (incident=4708985): ORA-04031: unable to allocate 80 bytes of shared memory ("shared pool","explain plan set statement_i...","qmxqalgDiagDrv","qmxqalgDiag_newElem:qmxqalgDiagStackElem") The bad thing is that the full database hangs when this happened (on a test database). I think that I read somewhere that we can alter the size of the shared pool to accept bigger queries. Can you tell me which parameter I could change for this (even an hidden parameter is OK). Thank you.

Does TLS 1.3 supported in the current OEM 24ai version

DBMS_SCHEDULER has several procedures that operate in bulk on various scheduler objects, such as DBMS_SCHEDULER.STOP_JOB, DBMS_SCHEDULER.DISABLE, etc, and offer a `commit_semantics` parameters. When set to 'ABSORB_ERRORS', it will not rollback when it encounters an error with some of the objects, but will instead report them to `SCHEDULER_BATCH_ERRORS'. May I please request a full example of how to process these errors? I am unable to find one online. A few preliminary questions: Does `SCHEDULER_BATCH_ERRORS` show only the errors from the last call within your specific session? This table doesn't have any kind of session identifiers or call identifiers, so I would assume this is true; however it doesn't explicitly say that in the documentation. If this is not true, how do we disambiguate? The documentation for `ORA-27362` say that the errors can be found in `SCHEDULER_JOB_ERRORS`. As far as I can tell this is not a real view. I assume this is a documentation bug and that it should be `SCHEDULER_BATCH_ERRORS`. This is my current approach for handling bulk errors in DBMS_SCHEDULER. In this case I am using STOP_JOB, and for handling bulk errors I will simply recall STOP_JOB with force => TRUE for those jobs that failed to stop normally. <code> DECLARE l_job_name_csv VARCHAR2(256) := '...'; BEGIN DBMS_SCHEDULER.STOP_JOB( job_name => l_job_name_csv, force => FALSE, commit_semantics => 'ABSORB_ERRORS' ); EXCEPTION WHEN OTHERS THEN IF SQLCODE != -27362 THEN RAISE; END IF; SELECT LISTAGG( scheduler_batch_errors.object_name, ',' ) WITHIN GROUP ( ORDER BY scheduler_batch_errors.object_name ) INTO l_job_name_csv FROM scheduler_batch_errors ; DBMS_SCHEDULER.STOP_JOB( job_name => l_job_name_csv, force => TRUE, commit_semantics => 'ABSORB_ERRORS' ); END; / </code> Is something like the above the correct approach? Thank you.

i am facing issue in fra as space is getting filled and not deleting flashback log filling the mount point on the server causing alert in my envoirnment

Hello, My question basically is this, Is there any scenario where it makes sense to define constraints in the application rather than at the database layer? So I work as a dba and sometimes it feels like a back and forth with the devs in my team. I try to always enforce the use of unique keys, foreign keys(including using same names for columns that mean the same thing) and check constraints as well. More than once I have got a request like this (things are anonymised for the purpose of this question and this is not a generic data model) : Create a table "testtab_3" with columns: testtab_id NUMBER(3) (primary key) object_type VARCHAR2(16) object_value NUMBER(3) now they plan to use object_type and object_value to implement foreign keys inside the application where object_value could be a value that relates back to the primary key of another table in the application say "testtab_1" or "testtab_2", and object_type will tell them what table to point back to. I have gotten requests like this before and pushed back; suggesting two separate columns leaving one null if that value is not required for the row to be able to enforce the integrity inside the database. In this scenario the developer explicitly said "I know you like forcing constraints on us but can you just allow me to do it like this in my application" I try explaining that it's not that "I like" the constraints but that they're important for data integrity. My question, as stated earlier, does this make sense? should constraints be defined in the application layer? is there a scenario where that is better than doing it in the database? am I just making life unnecessarily harder for my teammates ? Thanks for your time and appreciate your response.

Hello Tom, Can you please help me on explaining the difference and what the best approach is. I have a table used for outgoing messages to KAFKA. On the table I have an unique ID filled by a sequence. The KAFKA consumer reads the latest rows, which can be 0 to f/e 50 rows. Like <code>SELECT message FROM <TABLE> WHERE id > :P_ID ORDER BY ID ASC</code> So we need an INDEX on the table. Should I create the INDEX like <i>(ID DESC)</i> <i>(ID ASC)</i> or just <i>(ID)</i> She SQL to create the index could look like: <code>CREATE UNIQUE INDEX <NAME> ON <TABLE> (ID DESC);</code> or <code>CREATE UNIQUE INDEX <NAME> ON <TABLE> (ID);</code> I did not added the Primary Key constraint, while f/e the sequence already adds a number like: <code>CREATE TABLE <TABLE> ( ID NUMBER(10) GENERATED ALWAYS AS IDENTITY ( START WITH 1 MAXVALUE 9999999999 MINVALUE 1 CYCLE NOCACHE ORDER NOKEEP NOSCALE) NOT NULL, MESSAGE CLOB )</code> Thanks Wouter

I have a table. <code>CREATE TABLE QQ_SOME_TABLE ( ID NUMBER(9), GROUP_ID NUMBER(9), FROM_DATE DATE, TO_DATE DATE, VALUE1 VARCHAR2(4000 BYTE), VALUE2 VARCHAR2(4000 BYTE) ); CREATE UNIQUE INDEX QQ_SOME_TABLE_PK ON QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE); ALTER TABLE QQ_SOME_TABLE ADD ( CONSTRAINT QQ_SOME_TABLE_PK PRIMARY KEY (ID, GROUP_ID, FROM_DATE) USING INDEX QQ_SOME_TABLE_PK ENABLE VALIDATE); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('26/10/2025', 'DD/MM/YYYY'), TO_DATE('31/10/2025', 'DD/MM/YYYY'), NULL, '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/11/2025', 'DD/MM/YYYY'), TO_DATE('30/11/2025', 'DD/MM/YYYY'), NULL, '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/12/2025', 'DD/MM/YYYY'), TO_DATE('31/01/2026', 'DD/MM/YYYY'), NULL, '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/02/2026', 'DD/MM/YYYY'), TO_DATE('01/02/2026', 'DD/MM/YYYY'), '1', '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('25/02/2026', 'DD/MM/YYYY'), TO_DATE('28/02/2026', 'DD/MM/YYYY'), NULL, '51'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/03/2026', 'DD/MM/YYYY'), TO_DATE('31/03/2026', 'DD/MM/YYYY'), NULL, '51'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/04/2026', 'DD/MM/YYYY'), TO_DATE('31/12/4712', 'DD/MM/YYYY'), NULL, '51'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('02/02/2026', 'DD/MM/YYYY'), TO_DATE('10/02/2026', 'DD/MM/YYYY'), '1', '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('11/02/2026', 'DD/MM/YYYY'), TO_DATE('14/02/2026', 'DD/MM/YYYY'), '1', '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('15/02/2026', 'DD/MM/YYYY'), TO_DATE('24/02/2026', 'DD/MM/YYYY'), NULL, '52'); COMMIT; select * from qq_some_table order by id, group_id, from_date;</code> There is no gaps in dates periods from_date and to_date for the same id and group_id. I need select that by the same id and group_id finds data diapazon when value1 and value2 are not changed and keeps the minimum of from_date and maximum of to_date. <code>create table qq_result_table as select * from qq_some_table where 1 =2;</code> Here is a result that I need to get. <code> insert into qq_result_table (id, group_i...

Hi! I have a simple subquery that returns the last record by id from table. I want to use this subquery for different queries. What is the difference between using CREATE VIEW AS subquery and CREATE FUNCTION function_name RETURN VARCHAR2 SQL_MACRO with subquery without any parameters? <code>CREATE VIEW v_last_rec AS SELECT id, MAX(col1) KEEP (DENSE_RANK LAST ORDER BY date_column) AS col1, MAX(col2) KEEP (DENSE_RANK LAST ORDER BY date_column) AS col2 FROM table GROUP BY id;</code> <code>CREATE OR REPLACE FUNCTION get_last_recs RETURN VARCHAR2 SQL_MACRO IS BEGIN RETURN q'[ SELECT id, MAX(col1) KEEP (DENSE_RANK LAST ORDER BY date_column) AS col1, MAX(col2) KEEP (DENSE_RANK LAST ORDER BY date_column) AS col2 FROM table GROUP BY id ]'; END; </code> What is the best option for the optimizer for big tables?

Tom, We have a requirement create a report for eg to determine the number of individual performance reports deleted each month for each user. Let us say there is a table report_card which has an individuals performance details for each year. Some inserts, updates and deletes happen on this table. Every month I need to find the number of performance reports deleted for each user. For example let us say the individual performance information is stored in a table like <code> CREATE TABLE test.REPORT_CARD ( USERID VARCHAR2(30 BYTE) CONSTRAINT report_card_pk PRIMARY KEY, EVALUATION_START_DATE DATE constraint report_card_start_dt_nn NOT NULL, EVALUATION_END_DATE DATE constraint report_card_end_dt_nn NOT NULL, PASS_FAIL_IND CHAR(1) ); alter table test.REPORT_CARD add constraint report_card_pk PRIMARY KEY (USERID,EVALUATION_START_DATE,EVALUATION_END_DATE); insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('SMITH',TO_DATE('01/01/2007','MM/DD/YYYY'),TO_DATE('01/01/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('DOE',TO_DATE('01/01/2007','MM/DD/YYYY'),TO_DATE('01/01/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('MARLEY',TO_DATE('01/01/2007','MM/DD/YYYY'),TO_DATE('01/01/2008','MM/DD/YYYY'),'U') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('SMITH',TO_DATE('01/02/2008','MM/DD/YYYY'),TO_DATE('12/31/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('DOE',TO_DATE('01/02/2008','MM/DD/YYYY'),TO_DATE('12/31/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('MARLEY',TO_DATE('01/02/2008','MM/DD/YYYY'),TO_DATE('12/31/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('SMITH',TO_DATE('01/01/2009','MM/DD/YYYY'),TO_DATE('12/31/2009','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('DOE',TO_DATE('01/01/2009','MM/DD/YYYY'),TO_DATE('12/31/2009','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('MARLEY',TO_DATE('01/01/2009','MM/DD/YYYY'),TO_DATE('12/31/2009','MM/DD/YYYY'),'S') DELETE FROM test.REPORT_CARD WHERE TRUNC(EVALUATION_END_DATE) = TR...

Dear Tom, In our ERP, we are actively consuming both EBR & Supplemental Logging. EBR is for upgrades with a near zero downtime while Supplemental Logging is mainly for CDC, LogMiner & GoldenGate. But we encounter errors when ALTER TABLE statements are executed for normal tables in ONLINE mode while Supplemental Logging is enabled. The error we are getting is: <i>ORA-14416: Online DDL's cannot be used with certain types of tables.</i> Quick Test Steps: -- enable minimal supplemental logging (from CDB) <code> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;</code> -- create first table and its constraints <code> CREATE TABLE ORDER_TABLE ( ID VARCHAR2(50), DESCRIPTION VARCHAR2(100), ORDER_DATE DATE, CUSTOMER_ID VARCHAR2(50), CF_ID VARCHAR2(50) ); ALTER TABLE ORDER_TABLE ADD CONSTRAINT ORDER_PK PRIMARY KEY (ID); ALTER TABLE ORDER_TABLE ADD CONSTRAINT ORDER_CFK UNIQUE (CF_ID) USING INDEX; </code> -- create second table and its constraints <code> CREATE TABLE ORDER_CF_TABLE ( CF_ID VARCHAR2(50), AUTH_ID VARCHAR2(100), AUTH_DATE DATE ); ALTER TABLE ORDER_CF_TABLE ADD CONSTRAINT ORDER_CF_PK PRIMARY KEY (CF_ID); ALTER TABLE ORDER_CF_TABLE ADD CONSTRAINT ORDER_CF_RK FOREIGN KEY (CF_ID) REFERENCES ORDER_TABLE (CF_ID) ON DELETE CASCADE; ALTER TABLE ORDER_CF_TABLE ADD CONSTRAINT ORDER_CF_TABLE_CF_ID_NN CHECK ("CF_ID" IS NOT NULL); </code> Now try to execute below: <code>ALTER TABLE ORDER_CF_TABLE DROP CONSTRAINT ORDER_CF_RK KEEP INDEX ONLINE;</code> Error ORA-14416 is raised. Since both ONLINE mode for table DDLs & Supplemental Logging are key functionalities in Oracle database, what we believe is, it should be possible to use them at the same time. Could you please explain this behavior & any possible ways to achieve ONLINE DDLs on tables for upgrades while supplementary logging is enabled? Thanks & Kind Regards, Navinth

Hi Connor, Let me describe the situation. Our client is running a warehouse management system. There are two schemas wh1 and wh2. All the packages and procedures are created in schema wh1 with authid current user. Today I have faced the issue with the SKU master. Both the schemas have the SKU master, which should be ideally identical. When a particular procedure of a package is called from schema wh2 and was looking for an SKU which was present in schema wh2 but was missing from wh1 it flagged an error that the SKU is missing. When I created the SKU in schema wh1 it processed successfully. This is really puzzling. To best of my knowledge when the procedure is being called from schema wh2 it should access schema wh2 tables by default when we are not prefixing the table name with schema name. Am I missing something. Please share your view. Let me try with a sample code: tablename : sku schemas : wh1 and wh2 The said table is created in both the schemas. Lets sku 'SAMPLE1' is in schema wh2. But this sku does not exist in schema wh1. create or replace package wh1.sync_sku is authid current_user; begin upsert_sku(p_sku varchar2); end; create or replace package body wh1.sync_sku is begin procedure upsert_sku(p_sku varchar2) is declare v_found char(1) := 'N'; begin select 'Y' into v_found from sku where sku = p_sku; exception when no_data_found then raise_appliocation_error(-20001, 'SKU does not exist'); when others then raise; end; end; When the procedure upsert_sku is executed from schema wh2 with parameter 'SAMPLE1' its showing the error 'SKU does not exist' although the sku is exist in schema wh2. As soon as we insert the sku in schema wh1 the procedure executes successfully. The schema wh2 have all the required rights to execute the procedure of schema wh1.

Oracle has published following document for MySQL: https://blogs.oracle.com/mysql/why-your-application-should-not-use-one-mysql-user-for-everything. I have not found similar document for Oracle Database: I would like to know if Oracle has documented something similar for Oracle Database ? Thanks.

how to find which field raise the error 6502 or 1408?

hi I have created an interactive report I want to include heading e.g. Amountwise advances as on how can I do this Also I want to include heding while downloading as pdf please help

what is the difference between logging and nologging when creating an index

My question is if they add the column the table will be block during the coluum add because it's not enteprise but standard edition

We are planning to migrate our database from on-premises infrastructure to ODA. The source platform is Solaris sparc, and the target platform is x86. We intend to use the M5 Cross-Endian Platform Migration approach. However, we have a question regarding whether APEX and ORDS will be migrated as part of this process. On the source system, APEX and ORDS are installed in dedicated tablespaces rather than in SYSTEM or SYSAUX. We would like to verify whether this M5 migration is fully compatible for APEX,ORDS and whether any issues are expected. Both Source and target DB's are in 19.25 , APEX IS 22.1

Is there a way to create a On-premise MCP for oracle database without using OCI as a gateway? I want to deploy a real server (vm/container etc) rather than running a local instance of SQLCL on a user desktop that way we are able to configure the agent framework code (multiple chat bots) to talk to the database.