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

Table Statistics Get Null or Empty

Thu, 01/18/2018 - 00:00
What could be the possible reason(s) why the table statistics (DBA_TAB_STATISTICS) got null or empty i.e. num_rows, last_analyzed columns? At certain date <b>DBA_TAB_STATISTICS</b> num_rows, last_analyzed columns have values (not empty/null), and...

Analytical Function to compute running daily overtime by week

Thu, 01/18/2018 - 00:00
Hi there, I may be overthinking this query and would really appreciate some input/kick in the pants. How in the heck do I get daily overtime based on a 40 hour work week? If the week isn't complete but an employee has racked up more than 40 hours...

Finding records certain time apart

Tue, 01/16/2018 - 00:00
Hi, I have a following table: <code>create table t ( user_id varchar2(10), clinic varchar2(50), visit_dt date ); INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '08-APR-2008') ); INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '21-APR-2008'...

DBMS_AQ.LISTEN to listen to a Single/Multi-Consumer Queue

Sat, 01/13/2018 - 00:00
Dear Experts, Need your guidance/suggestions to resolve this issue: Part of oracle advance queueing implementation, we've to dequeue the message as soon as it has been enqueued into the queue. This should happen immediately without any manual inter...

Possibility of functional index/key in foreign key

Fri, 01/12/2018 - 00:00
Hi, I want to ask if there is possibility to use functional index/key in foreign key. As an example : In T1 there is opera_user_id column, with index upper(opera_user_id), in T2 a column ID, indexed as PK. From T1 there is FK defined by the column ...

Is there a way to search .fmb or .rdf for a string

Thu, 01/11/2018 - 00:00
Is there a way to search .fmb or .rdf for a string outside of the development tools?

Create table using existing table and copy column defaults

Thu, 01/11/2018 - 00:00
Hi Tom, I am creating table "tmp_pks1" with SID as DEFAULT 1. <code>SQL> CREATE TABLE tmp_pks1(SID NUMBER DEFAULT 1, NAME VARCHAR2(50) NOT NULL); Table created. SQL> CREATE TABLE tmp_pks2 AS SELECT * FROM tmp_pks1; Table created. SQ...

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