Skip to Main Content

Questions

QuestionUpdatedAsked

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 ago6 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 ago10.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 ago23.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 ago3.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 ago21.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 ago6 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 ago3 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 ago6.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 ago2.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 ago1.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 ago3 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 ago3 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 ago3 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 ago6.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 ago7.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 ago2 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 ago2 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 ago2 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 ago3 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 ago2 weeks ago