Questions

Dear Tom,I am trying to create a SQL_MACRO, which will accept the subpartition name and return the SELECT statement with that particular subpartition. Idea is to use this SQL_MACRO despite the view, where we cannot dynamically build the subpartition clause on ...

Hi,I am trying to update a view, actually just one really big table(fact) that is part of the view.The view consist of a one really big table (fact) and another inline view joined together. The inline view does full outer join of two small tables. Because of f...

Dear Tom,I just simply can't drop one user in sqlplus.SQL> drop user a;drop user a*ERROR at line 1:ORA-00604: error occurred at recursive SQL ldwvel 1ORA-00942: table or view does not existORA-06512: at line 7I also checked there is no table or other objects c...

Hi Team,First of all, Thanks a lot for all the great work you are doing! It would be great if you can help me with a query.While reviewing AWRSQL report of a SQL I noticed that session specific stats were used for that execution. This SQL is an update statemen...

Hi Tom,I want to rename a constraint without recreating it because the table contains too many records and time to recreate constraint is unacceptable for my task.But ALL_OBJECTS and USER_OBJECTS views still display the old constraint name.I boiled down my pro...

Hi Team,Need your help understanding Oracle DB's philosophy in the following code.What purpose does the "ON DELETE SET NULL" serve here?<b>Code:</b><code>CREATE TABLE IF NOT EXISTS t1( c1 NUMBER, c2 VARCHAR2(255), CONSTRAINT pkey_t1 PRIMARY KEY(c1...

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

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

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