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 59 min ago

Display master child data as a set - from 2 different tables

Fri, 06/01/2018 - 00:00
Hi , I will be glad if you could help me in this. I have a Parent Table ( ORDER_HEADER ) and a Child table ( ORDER_LINE ). They are linked by order_id. ORDER_HEADER holds order details for customers and ORDER_LINE holds the child lines for ea...

Parsing a CLOB field with CSV data and put the contents into it proper fields

Fri, 06/01/2018 - 00:00
My Question is a variation of the one originally posted on 11/9/2015. parsing a CLOB field which contains CSV data. My delimited data that is loaded into a clob field contains output in the form attribute=data~ The clob field can contain up to 6...

How to generate some big test tables and rapidly export their SQL data

Tue, 05/29/2018 - 00:00
Hello teams, Our Developer manager asks me to export Oracle production database's three big tables with <i><b>CSV</b></i> or this format <i><b>"insert ... into ..."</b></i> for giving another App teams (their database is MySQL 5.7) once again, I r...

Regular Expression is not working if the search criteria with LIKE and NOT LIKE in single input field

Fri, 05/25/2018 - 00:00
Hi Tom, Need your help ! Please find the LiveSQL link Thanks in Advance ! I have a table st_exp with s_desc column only, user has option to search by s_desc criteria. Scenario :The user may enter text critiria LIKE and NOT LIKE in the ...

Oracle Database - Grant/Revoke High Concurrency

Tue, 05/22/2018 - 00:00
We have an Oracle 10g release 2 database running on a production environment. It's experiencing a lot of concurrency, as Sql Developer 17.4 "Waits for past 1 hour" graph shows. When the database is behaving slow, we take a look at that graph, and ...

Update all database tables with current timestamp

Mon, 05/21/2018 - 00:00
Hi, I have a Production database ( version shown below) with about 2000 tables, having over 10 million rows each. <code>BANNER ----------------------------------------...

performance tunning for Sql query

Sat, 05/12/2018 - 00:00
hi Team, I am struggling in one of query tunning , below are details assoicated with , please analyse and recommend on it . <code> SELECT * FROM (SELECT /*+ INDEX(A INDX14_TABLE1) INDEX(B IDX51_TABLE2) */ * F...

insert into local table with select from multiple database links in a loop

Mon, 05/07/2018 - 00:00
Hi Tom, i would like to apply the Orignial SQL Statement from Oracle MOS DOC ID 1317265.1 and 1309070.1 for license and healthcheck for all of my database instances. My Goal is to create a centralized repository with informations of my databases. Un...

User Password Masking

Wed, 05/02/2018 - 00:00
Hello, Application user dont want to put the clear text password in the script which in turn connecting to database using this authentication. Is there any way to avoid clear text password and to use encrypted password for login . Regards,...

Why are all table subpartitions going in the same tablespace?

Wed, 05/02/2018 - 00:00
Dears, I have a problem regarding automatic list partitioning with hash sub-partitioning. the problem is the automatic created hash sub-partitions are not well distributed on the correct table spaces although the first hash-partitions are well dis...

How to remove multiple word occurance from an input string using oracle PL/SQL

Mon, 04/16/2018 - 00:00
Remove duplicate words from a address using oracle pl/sql: There are two types of addresses will be there, below is the example 1. '3 Mayers Court 3 Mayers Court' : where total no of words in address is even and either all words/combination of ...

Order by at runtime

Mon, 04/16/2018 - 00:00
Hello, we have some huge tables to query, and with order by clause (must be used) it takes a very long time for a query to be done. as I know that we can do the order by at run time using dynamic SQL, but my questions are: 1. do we have any o...

PL/SQL Performance Tuning

Thu, 04/12/2018 - 00:00
My stored PROCEDURE searches for potential duplicate PeopleSoft Vouchers (PS_VOUCHER, PS_VOUCHER_LINE, PS_DISTRIB_LINE, PS_VENDOR, PS_PYMNT_VCHR_XREF). The first step is to get the set of unique concatenated/delimited values for records that are cur...

Move subpartition to another partition

Thu, 04/12/2018 - 00:00
Hi. Today I've discovered another issue on the DB I've inherited. I've found this range subpartitions in wrong partitions. Let me try to explain. The scenario is: Partitions with month values subpartitioned by weeks, so I expect to have a ...

Custom pivot with count and sum summaries and horizontal sorting

Fri, 04/06/2018 - 00:00
Hello Team, Good Day! I have linked livesql script for data creation. Data basically looks like this. <code> 1 symnum NUMBER 22 2 symname VARCHAR2 100 3 remnum NUMBER 22 4 remname VARCHAR2 32 5 grade NUMBER 22 </code> ...

Split 1 row into 2 rows based on column values without UNION

Fri, 04/06/2018 - 00:00
Hi, I will be glad if you could help me to know if the below can be achieved without using UNION I want to split a row into 2 based on a column value create table xx_test_split ( id number, amount number, discount_amount number, currency ...

How to identify database export files

Mon, 03/26/2018 - 00:00
Any tools/mechanisms to identify database export files taken using exp and expdp, so that i can do a scan on the desktops in my organisation to find who all have kept the export files in their desktops.

Is safe to use row_number() over (order by null) as a rownum alternative?

Fri, 03/23/2018 - 00:00
Hi, I did some testing and it seems that row_number() over (order by null) could be used as an alternative to oracle's rownum pseudocolumn. Is this behavior by design or is just a coincidence? I'm trying this because some queries are very slow...

How to Optimize design a 1000+ columns in a Oracle DB

Tue, 03/20/2018 - 00:00
Hi, We need to store 5 minute interval data, with additional attributes, which will be around 1000+ columns. How to design this optimally for a Fact Table? Doe we have any columnar design in Oracle, such as a vertical split? Please provide your ex...

using max function

Sun, 03/18/2018 - 00:00
Hi, Look at here..... Find highest salary in each department without using MAX function. Note:- Use a single SELECT statement only. For an added complexity (optional): try not using ANY functions a...