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: 12 min 54 sec ago

DENSE_RANK - AGGREGATE function

Tue, 06/20/2017 - 00:00
Below is the the employee table, EID NAME SALARY COMM_PCT 100 Steven 24000 101 Neena 17000 0.6 102 Lex 17000 0.8 145 John 14000 0.4 If I do the below select to employee table I get the output as 3. SELECT DENSE_RANK(17000,0.6) W...

Updating Table Using with Clause or Analytical Function

Fri, 06/16/2017 - 00:00
Hi, Good Day ! From last few days I came across many situations where I need to update a table based on some analytical function output. Since, my table don't have any primary key I am having limitation on update using SQL and hence I left with...

How to hidden password of connect to oracle database someone user is needed in shell script file?

Tue, 06/13/2017 - 00:00
<code> ;;; Export: Release 11.2.0.4.0 - Production on Tue Jun 13 04:00:02 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Pr...

ROWNUM Bugs

Fri, 05/19/2017 - 00:00
Hi, i read this below question https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:948366252775 in latest follow up a reader posted like this <code>( 5 years later... select * from X where rownum in (select 1 from dual) sti...

How to create tables automatically from multiple csv files in oracle database?

Mon, 05/08/2017 - 00:00
How to create tables automatically from multiple csv files in oracle database? For Example: I have a client requirement that load data from several csv files into an oracle database.I have 50 files with different structures.so ,i want create table ...

Show rows where the running total is zero and all preceding rows

Fri, 05/05/2017 - 00:00
Hi Team, Data Setup: <code>create table test_item as with t1 as ( select 1 category, 'abc' Item_name,100 item_volume,12.3 item_price,to_date('04-may-2017 12:22:33 AM','dd-mon-yyyy hh:mi:ss AM') time from dual union select 1,'abc' ,-100 ,1...

how the data stored in partition table

Thu, 05/04/2017 - 00:00
Hi Tom, I have a partition table, Range partition by date, list sub-partition by region. my question is: Is it possible that same day but different regions' data can be stored at same data block. when running complicated query (the query join a...

Find agents with no transactions for 5 or more continuous days

Wed, 05/03/2017 - 00:00
Table1: TRANSACTION FIELDS : AGENT_ID, TRNX_AMT, INSERTED_ON <code>create table TRANSACTION (AGENT_ID varchar2(10), TRN_AMT number(4,2),INSERTED_ON date);</code> Table2: AGENT_DETAILS FIELDS: AGNET_ID,NAME,STATUS <code>create table AGENT_DE...

Combining multiple rows with a "priority"

Wed, 05/03/2017 - 00:00
I have a table: <code>create table mytab (ROLE_NAME VARCHAR2(30), MENU_TAB VARCHAR2(20), colA VARCHAR2(1), colB VARCHAR2(1), colC VARCHAR2(1));</code> I populate: <code>INSERT INTO MYTAB VALUES('TECH','MAIN','A','B', NULL); INSERT INTO MYTA...

Unexpected results with NUMBER(32,16) datatype

Tue, 05/02/2017 - 00:00
Hi Number(32,16) datatype columns are behaving differently, When we update those columns, oracle automatically rounding the values to something else. For eg. when we try to update the existing value from 1.0690000000000000 to 1.0690000000001234...

how to count active login hours with minutes of a employee

Mon, 05/01/2017 - 00:00
how to count login hours with minutes of a employee my scenario is if a employee login at time 'x' and he continue his work up to a certain time period 'y'. my question is how to calculate the active login hours of a employee from time 'x' to ...

inactive session kill

Mon, 04/24/2017 - 00:00
Hi team, As i have seen i my db where active users are 15 and inactive session are 1500 so load become high then i kill the inactive session like alter system kill session 'SID,SERIAL#' immediate; it works fine and load become normal. So ho...

Real-time materialized view not working as expected

Fri, 04/21/2017 - 00:00
Hello, I have a problem with <b>Real-time Materialized View `ON QUERY COMPUTATION?</b> functionality. My Real-time MV is enabled for both <b>QUERY REWRITE & ON QUERY COMPUTATION</b>. As I understand, when the MV is fresh, we get a MAT_VIEW R...

ROWID not incrementing. Need an alternative for deduping

Thu, 04/20/2017 - 00:00
Hi Tom, I have a SQL script which (runs daily) inserts data into a table and then de-duping is done based on the rowid. But recently I have observed that rowid is not incrementing with inserts and instead Oracle is re-using space to generate rowid...

Query Writing Differently (Relational Division)

Thu, 04/20/2017 - 00:00
<code>create table developers (name varchar2(30), skill varchar2(30)); create table projects (name varchar2(30), skill varchar2(30)); insert into developers values ('SMITH','ORACLE'); insert into developers values ('SMITH','JAVA'); insert i...

Correctly identifying Dynamic Sampling queries run by Optimizer

Wed, 04/19/2017 - 00:00
It is clear that dynamic sampling queries run by the optimizer contains <b>/* DS_SVC */ </b>clause in them (when traced). e.g. SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel...

Changing Hostname, IP address, DNA Name for the database

Mon, 04/10/2017 - 00:00
We have an Oracle 11.2.0.4 database with ASM storage. The linux version is OEL 7.0. Recently, the clients are having changes to Host Name, IP Address, DNS Name. We have to update the same for the database. Below is the list we have made, where w...

latch: cache buffers chains

Tue, 04/04/2017 - 00:00
Dear Experts, I work in a telecom domain, We have AIA and SOA application which we are tunning for increasing the tps. We have observed below even when same session are hitting the database. I would request you to please provide me suggestion or s...

TNS Issues when connecting to database with Forms Builder 12.2.1.2.0

Sun, 03/19/2017 - 00:00
I am getting the following issue when trying to connect to the database with Oracle Forms Builder (12.2.1.2.0): ORA-12154: TNS: Could not resolve the connect identifier specified. I am using: username: hr password: hrpassword database: pdb...

how to reclaim space from table fnd_lobs?

Fri, 03/17/2017 - 00:00
I want to reclaim the space from the table "fnd_lobs", which measures 100 gb, but it actually measures 50 gb, I have not found any method to recover the other 50 gb. they are looking for in google, and in a few forums, there was a user who said that...

Pages