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

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 ...

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...

Mimicking Sql Server OUTPUT Clause

Wed, 03/07/2018 - 00:00
I am trying converting SQL Server T-SQL queries to Oracle based queries. In SQL Server there is an ability to use the OUTPUT Clause within a query. See example below. BEGIN TRANSACTION DELETE TableA OUTPUT "DELETED".* INTO "TESTARCHIVE"....

Need some suggestion on Special characters.

Tue, 03/06/2018 - 00:00
Hi Tom, I'm using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production. Need some suggestion regarding how to handle special characters. <b>My doubt with converting any special characters to normal character except belo...

ORA-01652: Unable to extend temp segment by 2048 in table space TEMP

Tue, 03/06/2018 - 00:00
Hi Team, We have facing the issue with 1 query is consuming 331GB of TEMP tablespace. WE have configured 340GB of TEMP TS and have some limitation to add more temp files on DB server. due to it the other session get starved with TEMP tablespace an...

Pivoting via SQL

Sat, 03/03/2018 - 00:00
Hai, Tom sir...I have table with columns like this...I have 6 columns in my table with data in below.I need to write an SQL code to get the in below output.Out i mentioned at the ending.Please look at and give me the sol. Create table t3 ( ...

nulls with NOT and booleans

Fri, 03/02/2018 - 00:00
Hi Tom, I've always understood it to be the case that a=b and a!=b both evaluate to FALSE if one or both is NULL. However, I'm seeing some odd behavior which I'm trying to distill and understand. <code> DECLARE v_Var1 VARCHAR2(12) := 'A'; ...

Pages