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 32 sec ago

Why we should segment advisor to get fragmented space for tablespaces where segment space management is AUTO

Wed, 01/10/2018 - 00:00
Hi AskTom Team, Normally, to get fragmentation we do (num_rows*avg_row_length) from dba_tables and compare that with bytes in dba_segments to see if there is any fragmentation. But for tablespaces with segment space management "AUTO" oracle re...

oracle commit writes to redo, but not to datafiles

Wed, 01/10/2018 - 00:00
Hi Tom, I want to verify once the commit the transaction, only writes to redo log, but does not write to data files. How the verify the situation??

Very slow network interaction between Oracle DB and java application

Wed, 01/10/2018 - 00:00
I have a problem with my Oracle DB network speed. First of all, what's the essence of the problem. There are java application on my computer and Oracle DB on a remote server. Connection speed between them is about 2,5MB/s. I execute in my java app...

Get rows for first where condition

Wed, 01/10/2018 - 00:00
we face some issue as <code>select * from t where a is null or b is null or c is null</code> the question is if the query find the first where (a is null) then retrieve it only and not retrieve other where statment(b is null or c is null) can...

SPM accepted plan with different bind variable

Mon, 01/08/2018 - 00:00
<code>Hi Tom, I have POC on my lab about SQL Plan Management and found some thing that not make sense. My Oracle Database Version is "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production" with a single node, parameter ...

get file names in a directory not working anymore due to SQJ is no longer supported by 12.2 or later version

Mon, 01/08/2018 - 00:00
Hi Tom, I have been using your script as listed below to get the list of the file names in a directory and insert them into a global temporary table. Everything was working fine until last week I have upgraded my database to 12.2 and the script i...

UTL_FILE or EXTERNAL Table operation on files in Application Server

Mon, 11/27/2017 - 00:00
Hi Tom, We are running Oracle EBS R12.2.6. We have application server and database server hosted on two separate physical servers. They are not having any shared file locations. We are receiving the inbound files from a third party system in...

Validate constraint recursively run a SELECT with an "ordered" hint

Mon, 11/27/2017 - 00:00
I have been looking into minimizing the time it takes to validate a referential constraint. We want it to be validated for the optimizer to utilize join elimination during query rewrite. However, for a big table it takes a lot of time to validate ? a...

Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ?

Wed, 11/22/2017 - 00:00
Hello, teams:-) Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ? There has an example that I have given in Oracle 11.2.0.4.0. <code> SYS@orcl28> select round(sum(user_bytes)/(1024*1024*1024),2) fro...

INSTR Function to find exact match of a value

Wed, 11/15/2017 - 00:00
Hi, Struggling for sometime now. I have a list read from a table column - (1,2,3,10,11) I need to check if my number is present in the above list So for 1 INSTR(1, List) return 2 which is fine. Now the issue is if the list contain (2,3,10,11)...

Exporting multiple rows from a table to single row text using utl

Wed, 11/15/2017 - 00:00
In my project, I need export multiple rows from a single column to single row text using utl file Example below My table : Employees Employee_name ----------------------------- Smith John Tom Adam And my output text file should export like...

Is Using ROWTYPE is better then fetching 75% columns values to different variables

Wed, 11/15/2017 - 00:00
Hi Tom, I am using multiple variables to get columns values from a table(obviously I am using INTO clause and getting one row with filter criteria). I can do this by using %ROWTYPE as well. This way it will fetch all of the columns. Now, pro...

Disabling DBMS_SCHEDULER jobs on import

Tue, 11/14/2017 - 00:00
We refresh our test and development environments from production weekly. Is there a way to automatically disable DBMS_SCHEDULER jobs on import? I have a "reset" script that I run after the refresh, but "overdue" jobs seem to run immediately after imp...

Object info w.r.t redo

Mon, 11/13/2017 - 00:00
Dear Sir, How can I found which object generate more redo in database. Thanks Pradeep

Table with date column datatype (Storing datetime) causing issue in fetching result and when use with to_date and to_char causing performance issue.

Mon, 11/13/2017 - 00:00
Hello Tom, Good Morning. This is my first time posting question, I always got helped for my most of problems from your post. Need your help in understanding what will be good way with respect of performance. I have one table with date dat...

INACTIVE session is blocking active session

Sun, 11/12/2017 - 00:00
DBA is throwing information as follows 06112017:11:00:09 WELOPP@n1pv97/46581 (Session=('300,19867')Status=INACTIVE sqlid=>) blocking WELOPP@n1pv97/45876 (Session=('1803,10683') Status=ACTIVE sqlid=fp5x2quh0zpqk) f...

export issue

Fri, 11/10/2017 - 00:00
Hi team, We are taking daily export of schema with expdp But for a few days we are continuously getting error saying - snapshot too Old. Table is a partitioned table weekly base. And the script which we are using for expdp is - expdp us...

Identify patterns and create groups

Thu, 11/09/2017 - 00:00
I have data that looks like this: <code>create table t (a varchar2(30), b date); insert into t values (NULL,TO_DATE('2003/05/03 16:02:44', 'yyyy/mm/dd hh24:mi:ss')); insert into t values (NULL,TO_DATE('2003/05/03 17:02:44', 'yyyy/mm/dd hh24:mi...

how SPM works with cursor_sharing=force?

Tue, 11/07/2017 - 00:00
<code>Hello Tom The post https://blogs.oracle.com/optimizer/how-do-adaptive-cursor-sharing-and-sql-plan-management-interact explains the interaction between cursor sharing and SPM quite clear. But I met some unexpected results if I set the cursor_...

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

Pages