AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 1 hour 7 min ago

Inserting N rows from a cartesian

Wed, 06/24/2020 - 00:00
I have the following setup (test case below). How can the query be modified to INSERT N number of rows per employee_id. I want to add something like this but can't figure how to implement this for my test case ( select level dbms_random.value(5...

Oracle doesn't release undo even if time is way past the undo_retention value

Wed, 06/24/2020 - 00:00
Good Evening, I'm hoping that I can finally find out how Oracle decides when it is time to release the UNDO. In this database, the UNDO_RETENTION is set to 600 seconds. However, it has been over two hours since the huge purge job that used u...

How to find why the same SQL is slower with a different user

Fri, 06/19/2020 - 00:00
I have run the same SQL on production environment with System and other normal user. while checking the explain plan, showing same using both users. But while executing the SQL, system user return the result 50 times faster than normal user. I have s...

Using 1 CTE for 2 different DML statements

Thu, 06/18/2020 - 00:00
I have a query that reads through access_history data, which looks for record_type='T' as I only want Time & attendance records, which haven't been paired previously (processed=0). I am also keeping the Systimestamp for each record so I can get the ...

Timestamp with local time zone

Sun, 06/14/2020 - 00:00
Trying to deal with timezone + DST aware datetime handling, but I couldn't get it safe and convenient at the same time. The almost perfect solution would be timestamp with local time zone , except the fact that it uses nls_timestamp_format (opposed...

Ignore statistics while executing a SQL

Sun, 06/14/2020 - 00:00
Hi, Is there a way I can disable statistics while running a SQL. Unfortunately our SQL runs v fast when all the statistics have been deleted from the tables involved. But we need the statistics to be gathered for other queries to work effici...

how to fix getting unreadble characters in utl_http response using oracle database?

Sat, 06/13/2020 - 00:00
I'm using oracle 19c database character set <b>AR8MSWIN1256</b> ON WINDOWS I have used UTL_HTTP to get a soap response but I am getting invalid characters also i have switched to <b>AMERICAN_AMERICA.AL32UTF8</b> on another database but with no luck i...

Error with Read-Only partitions when inserting via SQL View

Wed, 06/10/2020 - 00:00
Hello, We had the following issue and we are seeking for your advise what can be done. 1) We have a very large tables (Finance applications, i.e. millions of lines) so we decided to use Partitions to improve performance and also to archive old data...

Automating External Table load

Fri, 05/29/2020 - 00:00
Hi There, Will like some thoughts on the following (working on 12c). We get flat files from various sources and each of these might come in with different delimiter ('|', ',','tab'). Is there a way to create an automated process to do the f...

Inactive Session is blocking

Wed, 05/27/2020 - 00:00
I found some inactive session block the other session so what is the issue? I also check sql_id of blocking session is null. and Wait session is doing insert operation.

Adding an optional json array to a json object

Wed, 05/27/2020 - 00:00
I have a parent table and child table with a 1 to 0-many relationship. I need to create a json object that includes parent data and a list of child table data. The goal is an object that includes: <b>"child":[]</b> when there is no matching data in ...

Create MVW takes 15 mins. Refresh of same MVW takes 12 hours (or mostly failes)

Mon, 05/25/2020 - 00:00
We have a materialized view, which has undergone some tuning. When creating the new MVW, it took 15 mins. The MVW is then refreshed using a shell in which many MVWs are refreshed in order. As part of the refresh, to make it faster, the indexes on ...

Scheduler Jobs not starting at the requested time

Wed, 05/20/2020 - 00:00
Solaris 11.4 Oracle 12.2 Hi, we have about 70 Schedueler jobs defined, which are starting in intervalls from 1 minute to about 1 month. Sometimes (about 2 to 3 times a weeks) there are time-windows of about 1 to 60 minutes , in which none of th...

Partial indexing using multiple function indexes or precomputed json object

Wed, 05/20/2020 - 00:00
I have an orders table with a status flag showing whether that order has been completed. Most orders in the tables are completed: probably only 5%ish of the table are incomplete. I know I can build a partial index by using a function based index whi...

Copy 50 million records

Mon, 05/11/2020 - 00:00
Hi bro, Please you should help on this. My question was :- I want copy a table from other table with 50 million records, now what is the best method and fastest way to re-create it. 1)PLSQL-BULK COLLECTION WITH LIMIT(It is also taking 2 t...

Generating JSON data for data in given table dynamically

Wed, 05/06/2020 - 00:00
My goal is to generate the data in JSON format from one table. The table might have 10 millions records. Another challenge is I am not aware of the number of columns and column names of the table for which I need to generate the JSON. My DB serve...

Multi Tenancy - Application SYNC to PDB

Thu, 04/23/2020 - 00:00
Hi Friends, I am trying to get the idea clear regarding Application Sync in Multi-Tenancy architecture. I have an Application Container, and I started installing application - created a user (application schema) - created objects in it ...

Wildcards in Oracle Text query relaxation and escaping

Thu, 04/23/2020 - 00:00
Hi Tom, my question is probably very basic but I found some trouble while using the query relaxation feature along with the '%' wildcard. So I would like to escape all the chars in the query with {} and then as a suffix add the % to make a prefix...

Use of TIMESTAMP datatype

Thu, 04/23/2020 - 00:00
Hello, Ask TOM Team. I have three DATE columns in some tables. I have been asked to do some math and show it in milliseconds. I need to change the DATE datatype to TIMESTAMP, maybe TIMESTAMP(3). This is my query: SELECT COUNT(*) QTY_DOCS, R...

Best approach to handle customer-specific data attributes in a data model for packaged software

Sun, 04/19/2020 - 00:00
I need to create a module to allow for entering one or more quality attributes of delivered products. This is for packaged software and so I need to be able to configure this module to work for a variety of customers. Here are the general requireme...