Feed aggregator

Hi Team, This is the first time I am asking question on asktom, while I am very much thankful to the solutions you have provided and kept helping people always. We want to add 60 CLOB columns to the table which is around 17TB in size. This is a datawarehouse DB and data loading to this table is completely stopped now, as the source table of MS SQL Server has 60 additional columns to it. Kindly suggest the best and efficient way to add the column to the table. The table is partitioned one. Regards Ojas

Dear Tom I have Oracle EBS 12.8 with Oracle 12C. I upgraded my database to 19C with the latest patches, and everything is working fine, except my DBlink with another MS SQL server. Note that it was working before the upgrade. I dropped the DBlink and re-created it, but I have no luck connecting. The Oracle gateway I installed in my SQL server is 11G. When I connect, I get the following error: <code>ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wapps24)(PORT=1521))(CONNECT_DATA=(SID=VestioOracleFinIntegration))) ORA-02063: preceding line from VESTIOORACLEFININTEGRATION Process ID: 42099</code> My DBlink setup is <code>OWNER PUBLIC DB_LINK VESTIOORACLEFININTEGRATION.WAFRA.LOCAL USERNAME OracleFin HOST VESTIOORACLEFININTEGRATION CREATED 16-APR-25 HIDDEN NO SHARD_INTERNAL NO VALID YES INTRA_CDB NO</code>

Hi, I see Oracle document shows an example of insert statement for fast ingest as INSERT /*+ MEMOPTIMIZE_WRITE */ INTO test_fast_ingest VALUES (1,'test'); Can fast ingest using memoptimize be used for insert statement, that is INSERT as select... INSERT /*+ MEMOPTIMIZE_WRITE */ INTO test_fast_ingest select col1,col2,col2 from another_table; Thanks, Girish

I?m having trouble with the parallel execution of an Oracle SQL query for creating indexes on <b>separate table (assume each table have single index)</b>. The performance boost isn?t even close to what I was hoping for. I therefore need your help in determining what to check for in the Oracle DB host in order to troubleshoot the issue. Although, I found all SQL queries having WAIT_CLASS as ?USER I/O?. I also did a check on IOPS through NetData UI on parallel index creation env, its max observed near ~24%. Just to notice, we already aware that PARALLEL hint may help here, but we are looking further improvement on top of that. Please let me know, whether any tunning on Oracle DB Side required or We can assume there won't be any benefit by executing index creation in parallel (even on different table) and we can only specify PARALLEL degree to enhance the performance.

I created a new project in Visual Studio 2019 with Entity Framework v6.5.1, Oracle.ManagedDataAccess v23.8, and Oracle.ManagedDataAccess.EntityFramework v23.8. When I add an ADO.NET Entity Data Model, I select EF Designer from the database. It then prompts me for the connection string. I add it, test it, and everything works. When I click Next, a window appears asking me to select the EntityFramework version. The wizard automatically exits and returns me to the first screen to reselect the ADO.NET element. Therefore, it doesn't create the model or the EDMX file. This doesn't happen when I use Oracle.ManagedDataAccess v19.27 and Oracle.ManagedDataAccess.EntityFramework v19.27. I can't update to the latest version? Please help.

Hi, While listening through the video "Developer Coaching - Oracle Database 23ai - Zero Slides, Zero Marketing, 100% Live Demo" there mentioned that we can set the oracle wallet path to system: to access the windows level certificates, but when I tried the same using below PLSQL code, I couldn't succeed, I'm getting the error "ORA-29248: an unrecognized WRL was used to open a wallet". Kindly let me know where I went wrong with the code? and how to use the system: as mentioned? Oracle Database: 19.3.0.0.0 Error: ORA-29248: an unrecognized WRL was used to open a wallet Command Used: utl_http.set_wallet('system:',null); full code is given below: <code> declare p_url varchar2(200) := 'https://www.oracle.com'; l_http_request utl_http.req; l_http_response utl_http.resp; l_text varchar2(32767); begin utl_http.set_wallet('system:',null); l_http_request := utl_http.begin_request(p_url); l_http_response := utl_http.get_response(l_http_request); utl_http.read_text(l_http_response,l_text,32766); dbms_output.put_line(substr(l_text,1,100)); exception when utl_http.end_of_body then utl_http.end_response(l_http_response); end; </code> Thanks for your support in advance.

hello I have a table FCM PARTITIONED ON (Q) Column BY RANGE INTERVAL( NUMTODSINTERVAL(7, 'DAY')) i have an issue that "current/active" partition becomes stale several times a day vast majority of DMLs on the table are INSERTS (basic inserts , with values , something like insert into table values :1 :2 ) while we do have a maintenance window , it is scheduled at night time , so during day we often have Stale statistics for current/active partition here are the questions : 1. is there any optimal way (something like trigger ) so that i can gather statistics on the partition AS SOON as it becomes stale , while it is possible to schedule a manual job to check for stale partitions , it still leaves the window between jobs , where partition can become stale , i dont really like the idea of a job running every 20 minutes , but as a last resort it can also be done .

One of the goals of creating an interpreter written in PL/SQL to execute a custom language was for our use in our tools. We wanted to be able to ship PL/SQL and customise it after its deployed without re-compiling the....[Read More]

Posted by Pete On 14/04/25 At 12:11 PM

Artificial Intelligence (AI) is rapidly transforming the business landscape, offering unprecedented opportunities for innovation and growth. However, successful AI implementation […]

The post Five Enterprise Strategies for implementing AI appeared first on DBASolved.

Hi Team Please find re-producible data available in LiveSQL shared link -> https://livesql.oracle.com/ords/livesql/s/c9r56fcrqizrcrc5aiep4hrpn Requirement is to fetch last record for "t_id" column value for each "code" + "status" column values respectively. Looking for options on how to fetch below data w/ SQL. <code> ID T_ID CODE STATUS ---------- ---------- ------------- ------------ <id_val> t1 Lookup Enable -- fetch last record <id_val> t1 Lookup Disable -- fetch last record <id_val> t1 Sync Enable -- fetch last record <id_val> t1 Sync Disable -- fetch last record <id_val> t2 Lookup Enable -- fetch last record <id_val> t2 Lookup Disable -- fetch last record <id_val> t2 Sync Enable -- fetch last record <id_val> t2 Sync Disable -- fetch last record <id_val> t2 Search Enable -- fetch last record <id_val> t2 Search Disable -- fetch last record </code> Appreciate all the help provided in Asktom. Thanks..

<b>Use Case:</b> We are implementing partitioning in the AQ to enable batch processing with array dequeue while ensuring: 1. Ordering maintained per partition (FIFO). 2. Possibility to Array Deqeue on Application side. ( Unless provided a more performant solution ). <b>Approach & Observation:</b> 1. We partition messages using a sort of partition_id, ( User defined in queue table) assigning each message a hash (eg: 1-5 ) at enqueue time. <code> PROCEDURE Enqueu_Event_( partition_id_ NUMBER, message_content_ JSON_OBJECT_T ) IS queue_payload_ TEST_PAYLOAD_TYPE; r_enqueue_options_ DBMS_AQ.ENQUEUE_OPTIONS_T; r_message_properties_ DBMS_AQ.MESSAGE_PROPERTIES_T; v_message_handle_ RAW(16); BEGIN -- Construct payload with provided partition_id queue_payload_ := TEST_PAYLOAD_TYPE(partition_id_, message_content_.to_blob()); -- Enqueue message DBMS_AQ.ENQUEUE( queue_name => OUT_QUEUE_NAME, enqueue_options => r_enqueue_options_, message_properties => r_message_properties_, payload => queue_payload_, msgid => v_message_handle_ ); COMMIT; END Publish_Event_; / </code> 2. On the consumer side ( Polling ), we use AQDequeueOptions with: <code> dequeueOpts.navigation := DBMS_AQ.FIRST_MESSAGE; to fetch the first message for ordering. deqeueOpts.deq_condition := ("partition_id = 1") to filter messages per partition. PROCEDURE Dequeue_Events_( partition_id_ NUMBER ) IS r_dequeue_options_ DBMS_AQ.DEQUEUE_OPTIONS_T; r_message_properties_ DBMS_AQ.MESSAGE_PROPERTIES_ARRAY_T; v_message_handle_ DBMS_AQ.MSGID_ARRAY_T; queue_payload_ TEST_PAYLOAD_TYPE; batch_size_ CONSTANT PLS_INTEGER := 10; -- Adjust batch size as needed BEGIN r_dequeue_options_.navigation := DBMS_AQ.FIRST_MESSAGE; r_dequeue_options_.dequeue_mode := DBMS_AQ.REMOVE; -- Condition to filter by partition_id r_dequeue_options_.condition := 'tab.partition_id = ' || TO_CHAR(partition_id_); -- Array dequeue DBMS_AQ.DEQUEUE_ARRAY( queue_name => IN_QUEUE_NAME, dequeue_options => r_dequeue_options_, message_properties => r_message_properties_, payload => queue_payload_, num_msgs => batch_size_, msgid => v_message_handle_ ); COMMIT; END Dequeue_Events_; / </code> <b>Questions & Clarification:</b> Does setting<b> DBMS_AQ.FIRST_MESSAGE</b>; override the lack of ordering guarantee in deq_condition? Can we reliably expect FIFO ordering per partition even when using deq_condition? If not, what is the best approach to ensure parallel processing per partition while preserving order? Is there a more efficient way to implement partitioned parallel dequeuing without sharded queues? <b>Links: </b> deq_condition ordering not guaranteed - https://docs.oracle.com/en/database...

Hi,Tom: The recycle bin is empty, and no DDL statements similar to drop BIN$MmWdiJOqKt7gY0eQFAotmw==$0 were executed. However, the following error was reported: Caused by: java.sql.BatchUpdateException: ORA-00604: error occurred at recursive SQL level 1 ORA-38301: can not perform DDL/DML over objects in Recycle Bin After performing the following operations, the error no longer occurred: I disabled the SPACE ADVISOR. BEGIN DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto space advisor',operation => NULL,window_name => NULL); END; / I dropped the table that caused the error and then recreated it. I don't know the reason for this issue. Could you provide some guidance? Thank you very much! Best Regards

how can i import a file version 24.2.2 into 24.1.7

Hello, Please advise on how to create List Partition with Range Sub-Partition. Also on the Range Sub-Partition like to have name custom defined based on each day like DEL_AUG_082019. <code>CREATE TABLE "DBB_USER"."STG_ES_STS" ( "CS_ID" CHAR(7), "CWIN" NUMBER(9), "PGM_TYP_CD" CHAR(2), "ES_STS_ID" NUMBER(9), "BGN_DT" DATE, "END_DT" DATE, "CRT_USR_ID" VARCHAR2(15), "CRT_DTM" DATE, "UPD_USR_ID" VARCHAR2(15), "UPD_DTM" DATE, "HIST_IND" CHAR(1), "LAST_RTRV_DT" DATE, "SRC_CITY_CD" VARCHAR2(2), CONSTRAINT "STG_XPK_ES_STS_RSN" PRIMARY KEY ("CS_ID", "CWIN", "PGM_TYP_CD", "ES_STS_ID", "ES_RSN_ID", "SRC_CITY_CD", "OP_TIME") VALIDATE ) TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) PARTITION BY LIST ("SRC_CITY_CD") (PARTITION "DEL" VALUES ('34') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) , PARTITION "MUM" VALUES ('07') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) , PARTITION "CAL" VALUES ('57') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) , PARTITION "CHN" VALUES ('42') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) , PARTITION "UNK" VALUES ('-1') TABLESPACE "DBB_USER" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) ) PARALLEL 4 ENABLE ROW MOVEMENT </code> Sample Data: TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME -------------------- ----------------------------------- -------------------- ------------------------------ DBB_USER STG_ES_STS DEL DEL_AUG_012019 DBB_USER STG_ES_STS DEL DEL_AUG_022019 DBB_USER STG_ES_STS DEL DEL_AUG_032019 DBB_USER STG_ES_STS DEL DEL_AUG_042019 DBB_USER STG_ES_STS DEL DEL_AUG_052019 DBB_USER STG_ES_STS DEL DEL_AUG_092019 DBB_USER STG_ES_STS MUM DEL_AUG_012019 DBB_USER STG_ES_STS MUM DEL_AUG_022019 DBB_USER STG_ES_STS MUM DEL_AUG_032019 DBB_USER STG_ES_STS MUM DEL_AUG_042019 DBB_USER STG_ES_STS MUM DEL_AUG_052019 DBB_USER STG_ES_STS MUM DEL_AUG_092019