Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 hour 19 min ago

Leaving fake hints in queries

Wed, 2018-06-13 22:46
Hello, I'm creating a script to automatically generate plan reports usings DBMS_XPLAN.DISPLAY_CURSOR, and to do so I want to put in a standard comment in the table and query it via dba_source and v$sql. e.g. <code>select /* xplan_my_test_pkg01 ...
Categories: DBA Blogs

Active clone recover needed arc files

Wed, 2018-06-13 04:30
Oracle 12.2 I am running the following command: *.db_file_name_convert='/db1/ifddb1/dbf/','/db1/ifdtest1/dbf/' *.log_file_name_convert='/redo/ifddb1/redologs/','/redo/ifdtest1/redologs/' sqlplus ' / as sysdba ' <<EOT shutdown abort startu...
Categories: DBA Blogs

Use DBMS_OUTPUT or HTP depending call origin

Wed, 2018-06-13 04:30
Hey, I have this procedure: <code>procedure output(i_msg in varchar2) is begin dbms_output.put_line(i_msg); -- if call is from apex i want to use htp.p end;</code> Is it possible to switch the "output chanel" to htp when the caller is...
Categories: DBA Blogs

Creating dll for executing external procedure('c' language)

Wed, 2018-06-13 04:30
Hi Tom, I am using Wint NT,Oracle 8i(server) and C language. My goal is calling 'c' routine thru stored procedure. For that I had made neccesary steps.I had modified Tnsnames and listener entry as follows. Tnsnames entry...
Categories: DBA Blogs

Avoid duplicates

Wed, 2018-06-13 04:30
Hi Tom, Thanks for your time. We have the scenario like this... DML on the table t should not populate the table with duplicate entries. So if we have table with data in it as : create table t ( x number); insert into t values (1);...
Categories: DBA Blogs

PDB AWR Host CPU

Tue, 2018-06-12 10:06
Does the "Host CPU" in the PDB AWR mean the CPU usage for the PDB, CDB or the host?
Categories: DBA Blogs

Insert multiple csv in a zip

Tue, 2018-06-12 10:06
Hello, My requirement is to 1. Read two query result and write into two different CSV files 2. Zip these two CSV files in a single zip file. There is a similar qn posted that tells who to create csv file and store the result (clob and blob) i...
Categories: DBA Blogs

Sql Execution Time v/s Elapsed time v/s CPU Time

Tue, 2018-06-12 10:06
Hello , I have been working on Database monitoring stuff where we are looking for long running queries in DB . From the inbuilt setup which i have received from DBA , its showing CPU_TIME and ELAPSED_TIME but none of them is matching with Oracle E...
Categories: DBA Blogs

what is read consistency

Mon, 2018-06-11 15:46
<i></i>Could you explain in your words what is Read consistence in Oracle 4.0 <i></i>
Categories: DBA Blogs

SESSION parameter shows different value

Mon, 2018-06-11 15:46
hi there As you guys suggested, last day i was trying to change process and session parameter values as follows, Everything gone perfectly. But after starting up the database, for the SESSION PARAMETER it shows me a value that i wasn't ex...
Categories: DBA Blogs

Listagg returning multiple values

Mon, 2018-06-11 15:46
hello, I am new to writing this kind of SQL and I am almost there with this statement but not quite. I'm trying to write a query using listagg and I am getting repeating values in the requirement column when I have 2 passengers. This is because...
Categories: DBA Blogs

Difference between Procedure and function(at least 5, if there are)

Mon, 2018-06-11 15:46
Difference between Procedure and function(at least 5, if there are) Seems like a basic question but its a very tricky question.. Some of the differences which I encountered on the internet seems incorrect later, I will list some of them below...
Categories: DBA Blogs

How to use conditional case with aggregate function

Mon, 2018-06-11 15:46
I have a query like the one below: SELECT CH.BLNG_NATIONAL_PRVDR_IDNTFR CASE WHEN CH.TCN_DATE BETWEEN TO_DATE('01-JAN-2016','DD/MM/YYYY') AND TO_DATE('30-JUN-2016','DD/MM/YYYY') THEN ROUND(SUM(CH.PAID_AMOUNT)/COUNT(DISTINCT CH.MBR_IDENT...
Categories: DBA Blogs

Conversion of Date format

Mon, 2018-06-11 15:46
I am having Column value as 'YYYYDDMM' and need to convert into 'DD/MM/YYYY' and store the data.
Categories: DBA Blogs

extract only two digit after fraction

Mon, 2018-06-11 15:46
For example, My query is following input data is in number format :- 123.456 and I want to see 123.45. Not need round off.
Categories: DBA Blogs

alter database noarchivelog versus flashback

Mon, 2018-06-11 15:46
SQL> alter database noarchivelog; alter database noarchivelog * ERROR at line 1: ORA-38774: cannot disable media recovery - flashback database is enabled
Categories: DBA Blogs

Can you Override USER built-in function ?

Mon, 2018-06-11 15:46
Hi Tom, Good Morning. My question may be naive, I'm not an Oracle expert. Can the USER built-in function be overridden ? Context: My current team has used Oracle for decades ( along with power-builder). We are now transitioning to a n...
Categories: DBA Blogs

SYSDATE and the At sign

Thu, 2018-06-07 01:46
Hello. I've seen this code <code>"sysdate@!"</code> used in a program, and i became curios, as I couldn't find any documentation of it. From what I saw, both give the same result: <code>select sysdate@!, sysdate from dual;</code> So my qu...
Categories: DBA Blogs

Is there a way to bulk collect into associate array in 10G?

Thu, 2018-06-07 01:46
Hi Tom. First of all, I just want to say thank you for creating such a forum. I have just started using Oracle and have learnt a lot from you here. Now, my question is as follows: Let's say I have a table with 3 columns: create table T (col1 n...
Categories: DBA Blogs

Declare a variable of type DATE using var

Thu, 2018-06-07 01:46
Tom, How do I declare a variable of type DATE in SQL*Plus? All I see is CHAR/NCHAR, VARCHAR2/NVARCHAR2, CLOB/NCLOB, REFCURSOR, NUMBER, BINARY_FLOAT and BINARY_DOUBLE. Thanks...
Categories: DBA Blogs

Pages