Feed aggregator

Performance issue with data densification process

AskTom - Hot Questions - Mon, 02/18/2019 - 00:00
Hi Tom, I am facing an issue while making sparse data dense. Here is the problem statement: We are getting price information for securities from upstream in a file and prices will come only when either there will be new security on boarded or t...

Efficient way to fetch data using rownumber from a billion record table??

AskTom - Hot Questions - Thu, 02/14/2019 - 00:00
I'm trying to fetch required data from a billion record table using the row number approch (i.e, select "ingest_ts_utc" from (SELECT to_char(sys_extract_utc(systimestamp), 'YYYY-MM-DD HH24:MI:SS.FF') as "ingest_ts_utc" ,ROWNUM as rno from XYZ.ABC ) ...

Access Table Without Synonym and Without Schema Name

AskTom - Hot Questions - Wed, 02/13/2019 - 00:00
I have a schema called HCR_SCHEMA, in which I have one table called TEST_EMP, and I have a user say USER_A and granted him select on the TEST_EMP table. I connected as USER_A as tried to execute SELECT * FROM TEST_EMP. Obviosuly it threw me error ...

Update production database from ETL Process

AskTom - Hot Questions - Wed, 02/13/2019 - 00:00
Hello, Ask Tom Team. I am using a Microsoft SSIS ETL Package to insert data to Oracle (source and destination). I want to do an incremental load every day but I have to flag the rows that are already loaded in the destination. It is not just s...

DB Link between Oracle 12c (Linux) and Oracle 11.2 R2 (Windows)

AskTom - Hot Questions - Tue, 02/12/2019 - 00:00
Hi everyone, I have a problem with DB Link. I'm trying to create a dblink from and Oracle 12c (installed on Linux Server) and Oracle 11.2 R2 (installed on Windows Server). I've correctly created the dblink, but when i try to query the result is: "<b...

Log Recovery in Dataguard alert log

AskTom - Hot Questions - Thu, 02/07/2019 - 00:00
Hi all, I am using Oracle 11g Active Dataguard in maximum performance mode. I notice 2 general pattern of log recovery in the database alert log. ===================== Pattern1 <code>RFS[7]: Selected log 11 for thread 2 sequence xxxxx Arc...

Parsing through a Long Character with 255 characters and Stripping out Words

AskTom - Hot Questions - Wed, 02/06/2019 - 00:00
Hi, I have a long character field for every one of my 10,000 rows and I need to write a PL/SQL that goes through every row a parses this long field for any WORD (Mix of Alpha and Numeric) up to 6 characters and spits it out and Save it to a differ...

Sortorder in Table Functions and Pipelined Table Function

AskTom - Hot Questions - Mon, 01/21/2019 - 00:00
Hey, suppose there is a function that return a numeric collection with 100.000 records. Will the rownum pseudocolumn always have the same value than the column_value pseudocolumn? Is there a difference between TF and PTF? What about Parallelism? ...

Please help, can't drop a corrupt table

AskTom - Hot Questions - Wed, 01/09/2019 - 00:00
Hi all, we have a corrupt object named "TO_BE_DROPPED" in our development database. Previously it had a different name, but someone renamed it, because it cannot be deleted, only renamed. It cannot be dropped by any means we could find. Apparently...

what are tables with MDXT in the name and can I delete them?

AskTom - Hot Questions - Mon, 01/07/2019 - 00:00
Hi, I have a datawarehouse with 5000 tables in it. <code> select count(*) from tab where TABTYPE = 'TABLE' </code> In this datawarehouse there are also tables with MDXT or MDRT or BIN in the tablename, more then 4600!! 400 actually use 4...

Replicating Data to another database in the same server

AskTom - Hot Questions - Mon, 01/07/2019 - 00:00
Hello, Ask Tom Team. Happy New Year for all of you. I have two single instance databases (A and B) in the same server. I need to replicate from A to B (some columns of some tables) in order to customers can view the data through a web applicat...

Alphanumeric Counter

AskTom - Hot Questions - Mon, 01/07/2019 - 00:00
Hi, i would like to know if it is possible to generate a alphanumeric counter like below. A001 A002 . . A999 AA01 AA02 . . AA99 AB01 AB02 . . AB99 AC01 . . . ZZZZ how can we implement this in a function? i have tried couple o...

ORA-01031 in view DML with dual reference

AskTom - Hot Questions - Mon, 01/07/2019 - 00:00
At a customer I work for there is a design standard that each DB-view should start with a so-called 'comments' block. This is to ensure that comments are stored in the data dictionary in the DB. create or replace view xxxx as with comments as ( ...

how to equate substring of one table column to a value in a list from another table column as part of an exists?

AskTom - Hot Questions - Mon, 12/17/2018 - 00:00
Hello Oracle Masters, I was looking at 'connect by level' (https://asktom.oracle.com/pls/apex/asktom.search?tag=clever-sql) hoping I could make use of it, but don't seem to be able to. Perhaps there is another way? We have a table of products ...

Database Design for Invoices Table with Many Null Columns

AskTom - Hot Questions - Mon, 12/17/2018 - 00:00
Hello, Ask Tom Team. I have a 70 columns table storing valid invoices. There are cases where the invoices are rejected because business rules. When the invoices are rejected just a few columns are filled (up to 5), then all 65 remaining columns ar...

Perf : Insert into Select Vs For All

AskTom - Hot Questions - Mon, 12/17/2018 - 00:00
I need to perform db operations where I need to inserts .5 million reocrds from another complex query involving joins,exists etc: As of now I am using INSERT INTO SELECT inside a pl/sq package. but some time I am observing for same data set t...

How to compare date duration of first row with second and so on.. and convert it to rows to column with indicators

AskTom - Hot Questions - Thu, 12/13/2018 - 00:00
Hi Tom, I have below table <code>CREATE TABLE PRDCT (ID NUMBER(10,0), PRDCT_CD VARCHAR2(1), START_DT DATE, END_DT DATE);</code> With data in this like - <code>Insert Into PRDCT Values (1,'A',to_date('01-May-2017'),to_date('31-Jul-2017')...

Packages are invalid -- ORA-04061

AskTom - Hot Questions - Wed, 12/12/2018 - 00:00
Hi, In our DB, few packages became invalid. And when we verified it we saw that there are no errors related to them. I was expecting sessions to run this package without any error, however when it was executed for first time, we got ORA-04061 erro...

SQL Window function to skip and add rows dynamically

AskTom - Hot Questions - Wed, 12/12/2018 - 00:00
<code>create table test1 (col1 number,col2 varchar2(5),col3 date,col4 number,col5 number,col6 number); --Positive scenario sample data insert into test1 values(1,'A',trunc(sysdate),0,5.5,2); insert into test1 values(1,'A',trunc(sysdate)+...

Pages