Questions

I have a misunderstanding related to invalidating a spec package. I create 2 package spec.<code>create or replace package pkg_1as function f_1 return number;end;/create or replace package pkg_2as v number := pkg_1.f_1;end;/</code>When checking, both of them...

I'm running this query <code>select * from DOC_MANAGEMENT.project AS OF timestamp to_timestamp('20241002','YYYYMMDD') where project_id = 179121; </code>And I get this error<code>ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11_28086...

Hi,How can i connect to SQL developer using oracle wallet based security.from sql plus we can connect using the command like /@sid, but how can i connect to SQL developer (or) toad using the wallet based security.Thanks in advance. ...

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

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

Hi Tom, We have 8i db one on the production server and another on the webserver (public access for online service). I have the same table created in both the database. I have a procedure to load the all the images form the file system about (1400) into t...

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

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

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