Questions

<code>Hi,Tom I have a question and want to learn from you.the following is an execution plan from OTN,but I don't understand thedifference between "filter" and "access" listed in "Predicate Information"and I did not find any OTN documents which explain the d...

I have an old table that was created once with an initial extent. E.g. <code>create table a (id number(3), col_date timestamp(6)) storage (initial 128M) </code>Now data growed and a partitioning was applied: <code>alter table a modify partition by range (col_d...

Hi Tom,We are considering to create a partition table in Oracle 19c DBour partition key column is INSERT_DATE, INTERVAL DAY Partitionwe have message_id column which is unique key, using this we will have update on row. it is a sequence.Table def:msg_id number,...

I am trying to understand how oracle calculates months_between.Here is the query:<code>SELECT MONTHS_BETWEEN(to_date('07/17/2027','mm/dd/yyyy'),to_date('06/22/2027','mm/dd/yyyy')) "Months" FROM dual;</code>This returns: 0.83870967741935483870967741935483870967...

How to tune procedure, function, package while running slow?...

Hi Tom,Can provide a detail guide of how to release unused space in tables /tablespaces to OS Disk, Is there any slowness or lagging if shrink tables / tablespaces online? How to know what percentage has it been shrinking? Shall i start with shrink tables then...

Hello, Guys.A new db (12c) will have lots of tables with sequence used as PK.What is the difference between named sequence and system auto-generated in Oracle 12c?What would be the best approach?...

Hi all,When trying to improve the performance of my query i have created an index on a table for the column employee_number with datatype NUMBER(9).The query that i'm using is using Bind variables, these are necessary because this query will be deployed as a r...

Can I, within a package/procedure/function/..., test a query, stored as text in a variable, for syntactic and semantic soundness? Without executing the query, that is.And when the answer is 'yes': how?In what way does the test result present itself to me? Is t...

Hi Tom, I am a long time reader of asktom.oracle.com and delighted to see 'submit a new question' enabled after a very long time. Which of the following would be a better choice from scalability, performance, ease of development, ease of maintenance. First opt...

Hi Tom,I was at your conference in MTL in february. It was a pleasure to finally meet you. My question is regarding the refresh of MVs. We are using Discoverer with a 9i Database in Archivelog mode. The problem is when we need to refresh our Materialized V...

Dear Colleagues,I have come across a problem when ranking regression slopes over a database link.I have a table containing dates and file volumes for downloads from several sources. I want to make a Top N for the sources most growing in their volumes. For this...

Greetings,I have a table that stores multiple locations (lat and lon) for a project. I want to convert this list of lat and lons into a sdo_geometry so that I can plot a polygon on the map. I created this procedure to insert the sdo_geomety column in a tempora...

Bank of Ireland has requested that you detect invalid transactions in December 2022.An invalid transaction is one that occurs outside of the bank's normal business hours.The following are the hours of operation for all branches:Monday - Friday 09:00 - 16:00Sat...

I try to sign simple text using dbms_crypto.sign package but when I do it with a nodejs script then I get different results.Main purpose of this is to sign and get DKIM signature, when I do it with a nodejs then its ok and will pass example Gmail DKIM check, b...

I have done some searching on the web with documentation saying when running incremental level 1 for recover of copy backup may need to turn block change tracking on to avoid performance issue. Currently we are having the block change tracking as disabled. How...

Hi Tom,Need your help. I searched about the error but couldn't find anything about it.Below is the snippet from Oracle's Database PL/SQL Language Reference:"LIMIT is a function that returns the maximum number of elements that the collection can have. If the co...

Hello, Tom!My colleague and I were discussing a scenario in Oracle 19c, and we had differing opinions, so we'd appreciate your insight.In an Oracle 19c database, consider the following scenario:User A performs data modifications and commits the changes.User B ...

The database is being migrated from Oracle 12 to Oracle 19cThe client applications are developed in MS .net framework 3.5 and use ODBC. They are currently running on Windows 2008 R2Instant Client version 19c needs minimum Windows 2012Is it possible to user In...

Good afternoon.I have an APEX application that receives a token.This token has been created in JAVA with the nimbus JOSE library.It is first signed with the following method:<code> public static String signer(final String signerKey, final Payload payload) thro...