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: 54 min 10 sec ago

Number of Chunks in DBMS_PARALLEL_EXECUTE

Wed, 03/13/2019 - 00:00
Hi TOM, I am having a data movement activity from one table to another table. Due to Huge data size in both tables, i have planned to use DBMS_PARALLEL_EXECUTE functionality, so that i can finish it quickly. I am creating chunks based on ROWID. ...

Need rank based on person_no column

Tue, 03/12/2019 - 00:00
Hi All, I have column ord which is common for all the records , then person_no. This can be repeated. I have a column called flag which is a sequence. I was trying populate a new column(NEW_FLAG) which will basically rank the person_no according...

Using connect by level to generate dates and times

Mon, 03/11/2019 - 00:00
I have two SQL Queries: SQL Query 1: <code>select to_date(:sDate,'dd-mm-rrrr')+(level-1)DateRange from dual connect by level <= ((to_date(:endDate,'dd-mm-rrrr')-to_date(:sDate,'dd-mm-rrrr')) + 1);</code> SQL Query 2: <code>select level,to...

How to decide to create index on column

Mon, 03/11/2019 - 00:00
Dear, I talk about single table contain approx 20 to 25 columns. And in production database this table used with different column combination or single column also used in different where clause at different query. When some time query run slow s...

Index skip scan with high NDV leading column

Wed, 03/06/2019 - 00:00
Hello Tom, I have the situation: A table tbl (220 Mio recs) the following query <code>select col_a, col_b, col_c from tbl t where Mod(Dbms_Rowid.Rowid_Row_Number(t.rowid, 2) = 0 --pred 1 and col_d =<lit>; --pred 2</code> col_d is in...

Caching for PLSQL packages over ORDS

Tue, 03/05/2019 - 00:00
I need to cache few values when the plsql procedure is called through a rest service multiple times i.e. when it is executed from a same user multiple times for optimization. I am calling below package procedure through ORDS rest service. Belo...

How to group by for a data set

Wed, 02/27/2019 - 00:00
Hi Tom, I have a table with many records for example as below. <code>create table test (a varcahr2(20)); insert into test values ('1'); insert into test values ('2'); insert into test values ('3'); insert into test values ('abc-oo cde')...

Oracle 12.2: Delete Privilege needs an additional Select Privilege

Tue, 02/26/2019 - 00:00
Hi, Why do we need an extra select privilege additional to the delete privilege? From my point of view, this does'nt make sense: <code>--execute as user a: create table b.t (col varchar2 (10)); insert into b.t values ('a'); commit; gr...

getting error ( ORA-06553: PLS-306: wrong number or types of arguments in call to) after migrating code from oracle 11.2.0.3.0 to 12 c

Fri, 02/22/2019 - 00:00
we are in the process of upgrading our oracle database from 11.2 to 12.2 ran into an error: ORA-06553: PLS-306: wrong number or types of arguments in call to I have created a simplified script that recreates the issue. the error occurs at com...

Performance issue with data densification process

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

With clause in distributed transactions

Thu, 02/14/2019 - 00:00
Hi Tom ! As there is put a restriction on GTTs: Distributed transactions are not supported for temporary tables does that mean that inline views in a query, i.e. using WITH clause, but those with MATERIALIZED hint will not work properly...

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

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

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

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)

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

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

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

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

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?

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

Pages