hi, there are queries which are performing slow from past 1 day, earlier same queries were giving output in 22 mins and not it takes 55 mins, this is basically the EOD process.Query 1:<code>SELECT DISTINCT DECODE(:B1 ,'C',IR.REASON_CODE,'D',IR.DESCRIPTION) ... | 3 days ago | 6 weeks ago |
Hi,I am getting following error while migration.ORA-00054: RESOURCE BUSY AND ACQUIRE WITH NOWAIT SPECIFIEDMigration code is given below. I am not able to understand I have acquired exclusive lock on this table then why I am getting this error. <b>Please help A... | 3 days ago | 10.8 years ago |
Do you have a full example of writing a ddl_Even trigger. I want to audit on a schema level any creates, alters, or drops of any objects....and write the actual calling syntax to a table.Oracle8 has the new feature of DDL_EVENT, and their is an example in the... | 4 days ago | 23.5 years ago |
My question: Is it possible to truncate multiple tables in a single line statement?Firstly, thanks for giving this opportunity to ask a question.I have developed a code to truncate multiple tables in a single line statement. Please refer the below code.----cre... | 4 days ago | 3.8 years ago |
Tom, I was working with External tables and Oracle came up with an error when I tried to insert, update or delete from external tables, as shown below.SQL> delete from student_ext;delete from student_ext *ERROR at line 1:ORA-30657: operation not su... | 5 days ago | 21.4 years ago |
Hello Connor/Chris,I am in the middle of troubleshooting a slow-running application maintenance job for a database that has been migrated/upgraded from on-premise 12.1.0.2 on linux x64 to Exadata Cloud @ Customer setup. Many of the "Top SQLs" that have regress... | 5 days ago | 6 weeks ago |
Hi,Please refer to the linked LiveSQL for the table, index, and data definition.I execute a query that uses ROW_NUMBER() OVER (ORDER BY SOME_DATE DESC). When a partitioned index on SOME_DATE DESC is used, the result contains wrong row numbers. The order of row... | 5 days ago | 3 weeks ago |
Tom,I am trying to POST the webservice all using UTL_HTTP function with the file from pl/sql procedures. The same processed working from Curl scripts.PL/SQL :<code>DECLARE req utl_http.req; resp utl_http.resp; l_value VARCHAR2(1024); l_url_import VARCHAR... | 5 days ago | 6.6 years ago |
Hi,is it possible to audit (unified auditing) access to tables only once per session? I don't need to catch all selects from audited tables in a session, I just want to to know if a table was at querried at least once. Currently it generates huge amount of aud... | 5 days ago | 2.9 years ago |
1) Platform and Oracle version i tried. OS platform Windows 10 & windows 11 Oracle version : Oracle 18CXE , Oracle 21c XE, Oracle 12c (on Oracle Linux 7) ORDS 22.2 TOMCAT 9.2) I tried it on oracle 18cXE and ords 21.4 to upgrade ords 22.2 but after inst... | 5 days ago | 1.4 years ago |
Dear Tom,I am trying to tune a query (12.1.0.1 version, 4 node RAC).The database appears to use DBMS_RLS policies.I see one context defined as:<code>CREATE OR REPLACE CONTEXT CRAMERSESSION USING CRAMER.PKGSESSIONCONTEXT/</code>CRAMER.PKGSESSIONCONTEXT code is ... | 6 days ago | 3 weeks ago |
Hello T[o|ea]m,Once, a long time ago, you gave an example of queuing a message asynchronously using PLSQL.https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8760267539329I implemented this example myself and got it working.(I think) I understan... | 9 days ago | 3 weeks ago |
We have been trying to enable auditing on few DDL activities in autonomous database. As per the following code we created, enabled and verified the audit configs. Everything looks normal to us, however none of the table create/drop activities (listed at the en... | 10 days ago | 3 weeks ago |
I am trying to write an sql, that takes two inputs at run time, if I am passing null then it looks at a database table and gets values from it.To make the NULL checks and then assign values from database, I implemented the logic as its given here https://askto... | 10 days ago | 6.6 years ago |
I am trying to size my undo tablespace properly( requirement is 24 hours retention for flashback query), So I need historic undo usage per day for last 90 days. and I am using the query below but I suspect that it is giving me wrong answer. Autoextend is off f... | 11 days ago | 7.1 years ago |
How to Extract the Tag value from xml which as tag name with colon.<code> <link>https://www.rba.gov.au/statistics/frequency/exchange-rates.html#JPY</link> <description>AUD/JPY representative rate as at 4.00 pm Eastern Australian time on 14 Nov 2023</descrip... | 12 days ago | 2 weeks ago |
Hello,i am very new at Oracle licensing issues and i am facing a situation with an Oracle SE2 Licence NUP.i am very confused how to count the number of NUP's i will have to purchase.is a NUP an explicit Oracle user i have created? what about system Oracle use... | 12 days ago | 2 weeks ago |
hi sir i got this message when i try to run my app"503 Service Unavailable2023-11-15T09:24:51.336389300Z | 8-GbuZcF3_cnIHgb1ul5Kg | ORDS was unable to make a connection to the database. This can occur if the database is unavailable, the maximum number of sessi... | 12 days ago | 2 weeks ago |
Hi Tom,I have 5 tables in a query, Tab1, Tab2, Tab3, Tab4 and Tab5 and I have the following query below.Select * From Tab1, Tab2, Tab3, Tab4, Tab5 Where Tab1.x = Tab2.y(+) and Tab1.x = Tab3.y and Tab3.x = Tab4.y(+) and Tab3.x = Tab5.y;Question 1. Can a... | 12 days ago | 3 weeks ago |
Hi Tom!ROWID works in a subselect: <code>SELECT * FROM(SELECT rowid, d.* FROM DUAL d)</code>and it also works with connect by: <code>SELECT rowid, LEVEL FROM DUAL CONNECT BY LEVEL < 2</code>But when used with both: <code>SELECT * FROM(SELECT rowid, LEVEL FROM ... | 13 days ago | 2 weeks ago |