Questions

Database Version 19.16.0.0.0I have a JSON document with "address", "address_name", and "state" keys among others. I'm searching for a user input in either "address" OR "address_name" in the JSON document. I also have an AND for the "state" key. Depending on th...

Hi team,i take Level-0 backup on Sunday, then from monday to saturday i take differential incremental backups.i want to restore backup on another machine, i have control file backup of Sunday. i can restore level-0 backup easily using control file of Sunday be...

We have around 50 custom tables, which are using for the traces in db objects. Each table contains more than billion records.most of the tables don't have indexes. I want to delete data >sysdare-365 days. Which is not working it is taking very very long time. ...

<code>declare v_list varchar2(4000);begin begin v_list := :P4_STATES; exception when no_data_found then v_list :='0'; end; for y in (select * from(table(DBMS_UTILITY.comma_to_table(:P4_STATES)))) loop INSERT INTO COVIDAPI(STATES_NO) value...

Test case is connect to sqlplus in silent mode in korn shell and run a bunch of sql queries in one single sqlplus session..and where the out would be seen..tried the following in test.ksh#!/usr/bin/kshCURR_DIR=`pwd`echo "current directory is:" $CURR_DIR. $CURR...

Has anyone setup CMU to work for all the PDBs within a CDB ?I have it working for a single PDB but when I created a new wallet based off the GUID it doesn't work I get an ora-1017 but it works for the 1st PDB I setup.I'm running Oracle 19.14 on Windows 2019. I...

Hi team,Is it possible to explain the purpose of '%CFG' broker services and who and how is using it ?Regards Dejan...

Hello, Ask Tom Team.I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and there is a high...

Hi,I tried to learn PIVOT concept to Pivot the Rows to Columns, but i could not understand how it works internally and how it identify which rows should get turn into Columns. So would you Explain How it Makes Rows as Columns....

Hi Tom !As there is put a restriction on GTTs:Distributed transactions are not supported for temporary tablesdoes that mean that inline views in a query, i.e. using WITH clause,but those with MATERIALIZED hint will not work properly?If there is neither MATERIA...

Hi,There is a SELECT statement which returns columns (around no of coloums are 25).I suppose to apply LISTAGG function on 16 columns (with delimiter as ',') whenever those columns has distinct values during group by one another column.For example, I have below...

hi tomi have a databse with 125 tables. i can find the total number of tables of the database by the sql select * from tab; now i would like to know the number of rows in each table of my database with out executing select count(*) from...; each time.please...

Hi TomCan you explain briefly the difference between soft and hard parse?cheers ...

Hi,I have to calculate accumulated value for a column, but I have to filter some rows (and it depends on that accumulated value).For example:<code>CREATE TABLE dummy_table ( var_id NUMBER(5) PRIMARY KEY, prc number(5), qty number(5), ...

My application has tables that are 30-40 GB (130M to 480M records) and indexes (indices) that are 10-25 GB.There is potential for significant growth.Oracle documentation recommends considering partitions when tables grow to 2GB, so I am trying to determine whe...

I tried to duplicate source database to create physical standby database using the below command duplicate target database for standby FROM ACTIVE DATABASE USING COMPRESSED BACKUPSETSPFILEnofilenamecheck;I have a very big database size with multiple big data ...

Good Morning Tom.I need your expertise in this regard. I got a table which contains millions or records. I want to update and commit every time for so many records ( say 10,000 records). I dont want to do in one stroke as I may end up in Rollback segment i...

1) Platform and Oracle version i tried. OS platform Windows 10 & windows 11 Oracle version : Oracle 18CXE , Oracle 21c XE, Oracle 12c (on Oracle Linux 7) ORDS 22.2 TOMCAT 9.2) I tried it on oracle 18cXE and ords 21.4 to upgrade ords 22.2 but after inst...

I am trying compare dates. Following SQL returns one row<code>SELECT COUNT(1) FROM ( SELECT 1 FROM DUAL WHERE TRUNC(CAST(CAST('13-JUN-22 05.47.49.000000000 PM' AS TIMESTAMP) AS DATE)) = TO_DATE('13-JUN-22','DD-MON-YY'));</code>where as the same query without...

Problem:We are working on a report, which displays tickets related metrics, based off of a custom Oracle view. TICKET's can be OPENED with any PRIORITY at any time of the day 24 x 7 x 365.We have imposed an SLA on this ticket, there are 5 levels the tickets ca...