Tom Kyte

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

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