Questions

Tom:I have a question regarding index. can you explain me in detailwhat the following means.If the index is a concatenation of multiple columns and oneof the columns contains a NULL value, the row will be in the index column containing the NULL value and will ...

Hi Ask Tom Team,I have a requirement to load JSON file into a table in Oracle database. Have seen some examples using External Table and filling it from a JSON File & then extracting fields through JSON functions to load data into relational table. Need your s...

Hello,I have written a stored procedure to truncate the multiple tables. When running the procedure no error but when checking by select statement still I can see that in the table. This means the procedure is not doing anything. Any help much appreciated. Wha...

Hi Tom,I am not able to fully understand below recursive query. Especially, the condition "WHERE POS > 0" and " WHERE POS = 0". my understanding is pos is result of REGEXP_INSTR (STR, '[A-Z][0-9]{5}',1, RN). 1. if the first POS > 0, how could the final conditi...

Hi,i want to export only the objects owned by given schema using EXPDP, but unfortunately it is exporting all the objects which schema has access to.i am using below syntax.<code>expdp school/school@orcl schemas=(demo) directory=DATA_PUMP_DIR dumpfile=demo.dmp...

HiWe have an application written with PL/SQL and thus with commit point optimization. However AWR reports show a lot of log file sync waits. How do autonomous transactions, which we are using for logging purposes operate with pl/sql code. Is the commit point o...

Please can I get the zoom link for today's conference?...

Hello Tom, I would like to be able to roll up a word count (for a WordCloud control in a UI) based on a query ( a spatial query in this case).I have a table as such<code>create table items (id raw(16), text clob, geom sdo_geometry )</code>This table has a full...

As you know, the association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited time period and an error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.Example:<code>select scn...

Dear Ask TOM Team,Thank you for your appreciated efforts and providing the guidance to all concerned clients.My question:I have running and in production oracle DB, 11g ... and would like to import and update the data from other DB with the same structure and ...

Oracle Database, SQL Language Reference 19c E96310-11 August 2021CREATE TABLESPACE documentation (15 SQL Statements, CREATE TABLESPACE) says that:'Use the storage_clause to specify storage parameters for all objects created in the tablespace. This clause is no...

<code>create user testuser1 identified by "DummyPass1";create user testuser2 identified by "DummyPass1";select name, spare4 from sys.user$ where name in ('TESTUSER1','TESTUSER2');</code>For the above test case, the password hashes are different between testuse...

Hi Tom,We are getting Intermittent Ora-06502: PL/SQL: numeric or value error - For Update Skip locked. This error occurs only when application (.Net) calls this SP and it occurs at last pending row from table. But we are able to execute same SP from TOAD succe...

Hello,I was curious about something that I am running in to. The following happens:select dbms_stats.get_param('ESTIMATE_PERCENT') from dual;AUTO_SAMPLE_SIZEselect * from dba_tab_statistics where sample_size != num_rows;no rows selectedWe have over 200 tables ...

Hi Tom,I have a need to export the data as csv from a table based on a query and store it in database column as blob.I need a plsql procedure for this and the same will be called inside oracle scheduler.Below are my challenges. 1. The table has over 35...

Hello!I have a particular ETL job that fires below 4 select queries on a view definition to our Oracle database - <code>select * from view where mod(id,4) = 1;select * from view where mod(id,4) = 2;select * from view where mod(id,4) = 3;select * from view wher...

Hi Tom,I have an AQ and the Queue table [QT_WRITE_OFF] has 40,196, 299 records. The task is to do dequeue about 28 million items[which meet a certain criterion] from this queue. Unfortunately, the queue was left to grow too big and I have to find a way of de-q...

hi tomcan u explain me in detail about "start with connect by" sql statement (tree structure).i know there is documentatin but it is very confusing. ...

Hi,There is no test case needed, it's more of a question.We have a stored proc which on success of the execution of the stored proc there is either a 1 or 0 returned. Can the 1 or 0 be captured by the oracle scheduler? ThanksVic...

Let's say we have a table of documents with key of doc_id. This table contains millions of documents(rows). There is also a table that assigns one or more industry codes to each doc:create table doc_ind( doc_id integer, ind_code varchar2(10))This ta...