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 ... | 11 hours ago | 5 days ago |
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... | 30 hours ago | 21.6 years ago |
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... | 30 hours ago | 21 years ago |
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... | 34 hours ago | 6.7 years ago |
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... | 35 hours ago | 2 days ago |
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... | 35 hours ago | 3 days ago |
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... | 2 days ago | 8 days ago |
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... | 7 days ago | 8 days ago |
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. ... | 7 days ago | 5.3 years ago |
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... | 7 days ago | 10 days ago |
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... | 7 days ago | 2 weeks ago |
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... | 7 days ago | 2 weeks ago |
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... | 7 days ago | 22.1 years ago |
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 ... | 7 days ago | 2 weeks ago |
<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... | 9 days ago | 10.8 years ago |
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... | 9 days ago | 10 days ago |
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,... | 9 days ago | 2 months ago |
How to tune procedure, function, package while running slow?... | 9 days ago | 10 days ago |
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... | 10 days ago | 2 weeks ago |
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?... | 10 days ago | 5.9 years ago |