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: 44 min ago

SQL to identify duplicates of data groups.

Mon, 11/06/2017 - 00:00
Hi, I have a peculiar requirement to identify duplicates of data groups. So here is a sample data set. <code> PI BU PR AC ---------- ---------- -------------------- ---------- 1001 100 PR1 ...

ASSM and basicfile LOB

Sun, 10/29/2017 - 00:00
Hello Tom According to https://docs.oracle.com/database/121/ADLOB/adlob_smart.htm#ADLOB46175 basicfile lob must be stored in a tablespace with segment space manually managed. However, it seems oracle does not prevent user from creating it in ...

Make a simple audit of table connections

Fri, 10/27/2017 - 00:00
Dear Tom. I want to know which tables in my Oracle schema are impacted by any software, web application or direct queries. An provider said me : " Make an audit on all tables (and views) of your schemas is not a good idea because your DB will w...

Data Model (Design) Question

Fri, 10/27/2017 - 00:00
I have a around 2 million assets that send 15 min interval data for a given day (i.e) total of 96 intervals for a given day. What is best database modeling approach. My target RDBMS is Oracle. I'm partitioning by week and for each interval I need ...

Optimize Query

Tue, 10/17/2017 - 00:00
I want to optimize the following query SELECT t.merchant_id, t.brand_id, t.transaction_type, t.trns_currency_code, <b>COUNT (*) AS total_no_tx, SUM (t.mv_is_trnx_approved) AS approved_no_tx, ...

SQL * Loader connection errors

Wed, 10/11/2017 - 00:00
To previous answer to MY SQL*Loader question I had asked, you said: Examples of SQL*Loader String <code>sqlldr.exe userid=my_user/my_password@some_string control=my_control.ctl sqlldr.exe userid=scott/tiger@//my_host:my_port/my_service_name con...

Using Analytical Functions to get Group Total

Wed, 10/11/2017 - 00:00
I need a GROUP TOTAL for EACH MONTH that SUMS up all the values in APPL column even when the query is for a particular APPL value. <code>create table test_grp_total (dttm_month date, appl varchar2(5), mins number) insert into test_grp_total va...

Hierarchical query with count of leave attributes

Thu, 09/21/2017 - 00:00
Hello Experts. I want to calculate the sum of the count of the leaves' attributes in a hierarchical query <code> create table hq_test (parent_id NUMBER, child_id NUMBER); INSERT INTO hq_test (parent_id, child_id) VALUES (25,26); INSERT INTO hq...

TNS error about 'TNS-12535 and TNS-00505' in alert log file of my Oracle 11.2.0.4.0 ?

Thu, 09/21/2017 - 00:00
Hi,teams I found that there has a plenty of TNS error about 'TNS-12535 and TNS-00505' in alert log file of my Oracle 11.2.0.4.0. part content as follows, <code> ...... *********************************************************************** Fata...

extract numbers from varchar2 upto first occurrence of a non-numeric value

Thu, 09/21/2017 - 00:00
On Oracle12 database, we have this table tbl_house_number that has one column "house_number" which is a varchar2 and has data entries of all different combinations. And all I need is the numbers from left to the first occurrence of a non-numeric li...

How you manage you're work-spaces!!

Tue, 09/19/2017 - 00:00
Greetings Team, I am very curious to know how you actually manage you're work spaces.I mean you always face different kind of questions all day along.How many laptops you use(of course you are using VM). But my question is if you guide someone fo...

need to extract numbers from a varchar upto a non-numeric character

Tue, 09/19/2017 - 00:00
Hi, I have a column called house number which is a varchar2, and it has all kinds of combinations of data entered (incorrect format) as shown below: House_Number ------------- 416-A 416-A 1573A 131# A23 133 A-21 133 A22 13320A 133A-21 1...

Flashlogs not getting deleted

Sat, 09/16/2017 - 00:00
Hi Tom, I see FRA size is Filling up and when checked Flashback Logs are occupying most of it as seen below, SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------...

Need to generate the output in incremental loops

Fri, 09/08/2017 - 00:00
Hi Tom, I have a query which returns the below output. ONE TWO THREE I need to get the out as ONE ONE TWO ONE TWO THREE ONE TWO THREE FOUR i.e like incremental sets (by step 1). ONE ONE TWO ONE TWO THREE ONE ...

Standby Redo Logs Required For All Protection Modes ?

Fri, 09/01/2017 - 00:00
Hi TOM, Why do we need standby redo logs on standby database when the protection mode is MAX PERFORMANCE? Oracle documentation (http://docs.oracle.com/database/121/DGBKR/dbresource.htm#DGBKR270) says : "You must add standby redo log files on a...

Tuning Question

Wed, 08/30/2017 - 00:00
Hi I have the following query: <code> SELECT cust_id, cosmos_cust_id, prot_id, customer_name, protocol_name, dist_order_no, cust_ivr_ref_no, ship...

JSON object access using JSON_TABLE

Sun, 08/27/2017 - 00:00
Hi I have a JSON object containing complex MVA, and would like to have relational access to my JSON object using JSON_TABLE + View. The way I am doing it right now, gives me 4 rows when I query the View, and I would like the SQL to return two r...

Using Dynamic Table Name in Select Statement To Prepare Extract File

Fri, 08/25/2017 - 00:00
Hi. We use to prepare extract file from oracle using extract script. There are few tables whose name gets change every month. Need to handle these table names dynamically. <code>SELECT TICKET_ID ,SOURCE_ID ,SERV_ID ,...

Visualize Processes Vertical

Wed, 08/23/2017 - 00:00
Hello, I've got a tricky one and can't solve it. Hopefully you can point me in the right direction. we have a table in which processes with their start and end time are listed. Here is a simplified example:<code>PROCESS | START_DATE | END_DATE ...

dbms_metadata - getting inconsistent SXML (ordering)

Tue, 08/22/2017 - 00:00
Hi Tom I am trying to use dbms_metadata to retrieve a SXML document per object in my production database, and compare it to the same document from my test database, in order to compare the two environments. Initially, I calculate a HASH value f...

Pages