Feed aggregator

Update current row witrh values from previous row

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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.

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - Mon, 01/29/2018 - 00:00

conceptual question regarding ODBC driver

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

AskTom - Hot Questions - 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

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