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: 2 hours 47 min ago

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

Datapump SQL minitoring with SQL (not PL/SQL)

Thu, 03/01/2018 - 00:00
I'm on 11.2 and 12.1 and would like to monitor datapump sessions with pure SQL (I am not allowed to create objects on the databases). I can monitor using: <code> select ss.sid , s.job_name , j.state --, j.job_mode --, , dbms_rcvm...

Foreign key constraint DDL & locks

Wed, 02/28/2018 - 00:00
For the period when DDL runs to add FK constraint (on un-indexedcolumn), both child and parent tables are unavailable to be read by 2nd session. Is this behaviour expected / documented? Is there anyway to avoid issue (which manifests in applicati...

database performance select count

Fri, 02/23/2018 - 00:00
Hi Team , i have query mentioned below : T1(id number , name varchar2 (20)); Column ID is full of null in table T1. select count(*) from table t1 where id is not null and name like :b1; This query is going on FTS , so we rewritten i...

Using SELECT * combined with WITH-CLAUSE - Bad Practice? View gets compiled with static columns list

Tue, 02/20/2018 - 00:00
Hey guys, I have a question regarding clean SQL Code / Bad Practice around the use of wildcards in SELECT-Statements. In the provided example I have a base-query with a huge list of columns selected and two (or more) sources I need to have combin...

Update current row witrh values from previous row

Thu, 02/15/2018 - 00:00
Hi, I'm searching for a solution to solve update with a single SQL statement instead of a PL/SQL procedure: <code>create table test (stock_date DATE, stock NUMBER(5), stock_in NUMBER(5), stock_out NUMBER(5), stock_val NUMBER(5)); INSERT INTO tes...

Select data without duplicate

Wed, 02/14/2018 - 00:00
Right now data like this, Case# Unit# ------------------------ X0002270301 null X0002460601 T0707802116 X0002460601 null X0002683101 null x0002683101 T0721128616 x0002922501 null x0005125201 null x0005125201 T0813774816 ...

Using defined variable (ampersand) for a part of column name

Sun, 02/11/2018 - 00:00
Good day. I am having problem to define <b><u>only part</u></b> of a column name as a variable (using ampersand). Script example: <b><i>Select &Product._ID, &Product._NAME, &Product._SALES, &Product._DEBT From TABL...

Using SYS_GUID() has primary key in large table

Thu, 02/08/2018 - 00:00
Hey, I have a large table 600 million rows, approximately 70gb, not partitioned (yet), a few indices, etc. I have reports that query this table often but they're very slow (20-60 minutes at times). I want to create this materialized view but ...

Materialized views and Synonyms

Mon, 02/05/2018 - 00:00
Good day. I need help with refresh materialized view. I created synonym MySynonym: <code>CREATE OR REPLACE SYNONYM "MyScheme"."MySynonym" FOR "MyScheme2"."SomeTable";</code> I created materialized view: <code>CREATE MATERIALIZED VIEW MyMView T...

SQL request with analytics to fill previous values

Thu, 02/01/2018 - 00:00
Hello, I have a table with this structure table values: <code>(code varchar2(10), date_value date, value number);</code> Example data : <code>'Code1','15/03/2017',25000 'Code1','06/06/2017',26000 'Code1','18/07/2017',29000 'Code1...

No estimate time remainings drop column unused, with checkpoint.

Wed, 01/31/2018 - 00:00
Hi Tom! I had a problem removing a column set unused(version RDBMS Because he was using CHECKPOINT, could not keep track of a running development, as well as estimate in v$SESSION_LONGOPS. For time remaings estimate, I was using an ave...

TDE Column Enablement

Wed, 01/31/2018 - 00:00
Hi Oracle Masters, Two questions: 1) I read the below line in "Advanced Security Guide" for TDE: "If you enable TDE column encryption on a very large table, then you may need to increase the redo log size to accommodate the operation". Ho...

SQL Query Optimization

Mon, 01/29/2018 - 00:00

conceptual question regarding ODBC driver

Mon, 01/29/2018 - 00:00
Hi Tom: I used to work as a analyst who developed SQL queries mainly using SQL developer and SSIS, and recently I have moved to a new team which I have slightly different role , leading me to some questions I never consider before. I am not so sur...

Security model for SQL Tuning sets

Thu, 01/25/2018 - 00:00
Hello Tom, Is it possible for a user without DBA priv and Select privs on base tables to create SQL Tuning Tasks? what if we create a procedure under another user with DBA priv and grant the user execute on the procedure? Thx for the Supp...

Default date part of TO_DATE function

Thu, 01/25/2018 - 00:00
Hi AT-Team, LiveSQL seems to be broken, so sorry no test case there. We are building a system in which developers are converting strings holding time-only data to a DATE in Oracle SQL in order to build queries involving the time of day later on...

clob data conversion

Tue, 01/23/2018 - 00:00
I Have table, which has clob data type, value stored is in hexadecimal data type. Please let me know how can i convert clob data to varchar

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