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: 2 hours 5 min ago

md5 vs natural key

Thu, 08/15/2019 - 00:00
Hello, I start a new data warehouse project. In the ETLs for the ins/upd steps, I suggest to use MD5 and not the natural key (my sources are txt files from a mainframe). I need to explain to my project manager why I want to use md5 and not the na...

Select Performance Too Bad on Oracle

Wed, 08/14/2019 - 00:00
Dear Tom, I just faced an issue that I could not solve it. I have two virtual machines: Client: 32Core, 32G Ram, Virtual Machine on HPE Server Server: 96Core, 256G Ram, Virtual Machine on HPE Server I did write a simple Java application usi...

Script to create dynamic partitions for multiple backup tables

Mon, 08/12/2019 - 00:00
Hi Team, We have a requirement wherein there are 30 backup tables which are currently non partitioned and each table have data around 15-30TB's. Size of the table is very huge. We have to write a script which will create partitioning of a backu...

SQL query using Rollback segment heavily

Thu, 08/08/2019 - 00:00
Dear Team, I am querying from a table where dml operations are continuously taking place in current financial year(19-20). But I am retrieving data of 17-18 financial year's and it was pretty slow. On investigating, I found that it was using rollbac...

Insert performance in .NET Application

Thu, 07/11/2019 - 00:00
Hello, Ask Tom Team. We have a .Net Application and we are using Oracle Database 18c. We have many tables working with relational model. An example of what we have in our code (for every table): #region insert const str...

Find all the Fully Connected Subgraphs

Mon, 07/08/2019 - 00:00
Hi Team , <code> with data ( p1 , p2 ) as ( select 'a' ,'b' from dual union all select 'a' ,'c' from dual union all select 'a' ,'d' from dual union all select 'b' ,'c' from dual union all select 'b' ,'d' from dual union all ...

Plan Change

Fri, 07/05/2019 - 00:00
Hi Team, We have upgraded Peoplesoft Oracle database from 11.2.0.3 to 11.2.0.4. After this upgrade, the queries using the same table which is running simultaneously is having performance issues. Please let us know if we need to set any databa...

Best debuging steps for PLSQL procedure AND SQL Querys

Fri, 07/05/2019 - 00:00
Hi Team, In my project, we have a 100 of the procedure and those procedures we have 1000 to 5000 lines of codes. we need to debugging those procedures. Is there any best methods, techniques for debugging plsql procedure and SQL queries. this...

Query is taking much time (not using index)

Thu, 07/04/2019 - 00:00
Hello, Ask Tom. I have a delete statement that involves three tables. All id are pk (indexes are present on each pk by default). There is also an index on t2.columnA but in the plan it says that there is a table access full on table2. The query i...

Taking nearly four days to delete the records

Wed, 07/03/2019 - 00:00
Following Query is taking nearly four days to delete the records from C_HDR_PARENT_TB table, we need to tune this query. Can you please suggest and modify this <code>declare rowcount number := 0; type rowid_typ is table of ...

What is efficient way to paginate on the large table?

Sun, 06/30/2019 - 00:00
Hi Tom, We are using the following query in java application, for getting the query results in the pagination. To speed up the task we are executing 15 queries at the same time. The queries are being executed on different machines but targeting th...

How to analyse or dig log.xml of Oracle Listener Log with XMLTABLE?

Fri, 06/28/2019 - 00:00
Hey Connor and Chris, I want to analyse or dig <b>log.xml</b> of Oracle Listener Log with <b>XMLTABLE</b>. Afterwards I found Thomas Kyte's this thread - https://asktom.oracle.com/pls/apex/asktom.search?tag=xmldb. What he replies to somebody attra...

Commit / rollback in nested procedures calls

Fri, 06/28/2019 - 00:00
Hi, The situation: Procedure Master and procedure Slave, both include transactions. Procedure Master calls procedure Slave in its body. External code could calls both procedures. The target: When external code call procedure Slave the transac...

WITH Function Returns Results that Do Not Meet Constraint

Sun, 06/23/2019 - 00:00
Hi Tom et al, Code below uses a function declared inside of a WITH clause. Function calls dbms_random and returns the result to my WITH table. Final SELECT queries WITH table using a constraint, but I get results that do not meet the constraint....

Is it possible to get duplicates using ORA_ROWSCN in the filter?

Thu, 06/20/2019 - 00:00
Hello Tom, I just wanted to ask you if the problem that we are facing could even be related to Oracle, as we can't find a reason and we don't have access to the sources to check how the source objects are created. Is it even possible that a sel...

Usage analysis of Stored Procs

Thu, 06/13/2019 - 00:00
Hi, Please can you help with tracking of execution of stored procedures in DB. One legacy DB is running, application owner wants to know how many of the SP's are actually used. He is looking for usage report on SP's We have tried to get data...

Substr, to_char gives a difference of 1 hour from actual time

Wed, 06/12/2019 - 00:00
Hello, 1) When we execute below query : <code>with rws as ( (SELECT (FROM_TZ(CAST(sysdate AS TIMESTAMP), 'Europe/Paris') AT TIME ZONE (select nvl(timezone,'Europe/Paris') date1 from dim.nloqt_top_sit_d where sit = 'KE' )) sysd FROM DUAL)...

Purging a huge table

Wed, 06/12/2019 - 00:00
I have a huge table with CLOB and BLOB two columns.The size of the table is 300 GB ( 80 GB Table Size + 220 GB LOB Columns). We have requirement to keep only 3 years of latest data.What would be the best way to achieve this ? My solution : 1. Di...

Value gets overwritten when using application context to be accessed by multiple sessions

Tue, 06/11/2019 - 00:00
Hi Tom! Running on Oracle 11g (11.2.0.4.0) I try to use a global context to share parameters between user sessions. Like the docs says: "username set to a value - client_id set to NULL" means: This combination enables an application context t...

Update primary key column or update other columns in a hierarchy

Sat, 06/08/2019 - 00:00
I hava a table like the following structure: <code>create table table_a ( "ID_TABLE_A" NUMBER, --primary key column "PARENT_ID" NUMBER, "CONTENT_1" VARCHAR2(32), "CONTENT_2" NUMBER, "CONTENT_3" NUMBER, "CONTENT_4" VARCHAR2(256), "CONTENT_5...

Pages