Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 3 hours 2 min ago

Inserting with WITH FUNCTION Select is giving error

Sat, 2018-10-06 10:46
Hi Ask Tom Team, Below select query is working fine for me, but am not able to insert the result using insert statement., Please provide me some suggestions <code>WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER IS BEGIN IF P_A1 = ...
Categories: DBA Blogs

Is the Oracle regular expression supporting this character extraction?

Sat, 2018-10-06 10:46
Hi Oracle SQL experts, I am using Oracle regular expression to deal with some characters. My db is 12c. I have a string every line like this input like this 1234adhefd#123 345bheufs15# ... output will be from the first alpha lette...
Categories: DBA Blogs

SGA Management

Sat, 2018-10-06 10:46
Hi Tom, I would like to know that, how Oracle internally manages when end user try to extract the data which is more than SGA, for Example if our SGA is 7 GB and user query is about 20 GB data, how it will internally manages, as far as I know, ser...
Categories: DBA Blogs

Moving datafile

Sat, 2018-10-06 10:46
Hi there in our production database,we have a tablespace called TESTDB and this tablespace has 2 DATAFILEs. location for these 2 datafiles are D:\ORADATA\TESTDB\TESTDB01.DBF D:\ORADATA\TESTDB\TESTDB02.DBF Recently i have added a new datafi...
Categories: DBA Blogs

How to move contents data from one datafile to another?

Sat, 2018-10-06 10:46
Hi Tom, Thanks for your asktom website. A tablespace consists of several data files. My purpose is to cleanly move contents in one data file to another on and then I can drop that empty file from the tablespace. So I can reduce the data file nu...
Categories: DBA Blogs

Lock wait timeout

Fri, 2018-10-05 16:26
How to set lock wait timeout in Oracle. We are executing insert/update/delete from java applications. Sometimes due to long running transactions or slowness lock acquired by one transaction on particular row gets hit by another transaction and blocki...
Categories: DBA Blogs

More archives during impdp, what is the exact reason and what will happen internally

Fri, 2018-10-05 16:26
Why more archives will be generated during import (impdp) activity, i would like to know what will happen internally due to which more archives being generated. Is it only for import activity or it will generate more redo for export as well ? Many...
Categories: DBA Blogs

Query on dba_hist_active_sess_history query taking too long

Fri, 2018-10-05 16:26
Hi, I'm using the below query to fetch details from dba_hist_active_sess_history which matches a specific wait event occurring at a specific hour of the day within the last 90 days: select USER_ID, PROGRAM, MACHINE from dba_hist_active_sess_his...
Categories: DBA Blogs

Excessive archive log generation during data load

Fri, 2018-10-05 16:26
Hi Tom, I am encountering a situation related to data loading and excessive archive log generation. I am using Oracle 8.1.6 under Solaris 7. I insert twice a week about 1 million rows in to a table whose columns are of number datatype. The loa...
Categories: DBA Blogs

The identity column jumps its value if using merge into statement

Thu, 2018-10-04 22:06
Hi, I have one table defined as below, one of the column is defined as identity type <code> create table TEST ( col1 VARCHAR2(10), col2 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 999999999999999...
Categories: DBA Blogs

dbms_lob.compare and length

Thu, 2018-10-04 22:06
Hello, I'm trying within a trigger to compare two clobs to see if there is any change. I am trying to prevent any unnecessary writes. Prior to writing to audit trail I compare two values. <code> v_clob_compare := dbms_lob.compare( :old.clob_tex...
Categories: DBA Blogs

Using function in conjunction with WITH query clause

Thu, 2018-10-04 22:06
Bit of a newbie, and hoping I can get pointed in the right direction. I've simplified things to demonstrate the issue I'm experiencing (and I'm really struggling to get a clear answer on other posts). When running the following: <code>with f...
Categories: DBA Blogs

SQL Query to Convert Ten Rows with One Column to Five Rows With One Column

Thu, 2018-10-04 22:06
i have a table with column name as value. There are 10 rows in the table. The desired output of this to be displayed as two columns first 5 rows as one column A and rows 6 to 10 as column B , next to each other as 5 rows of data like this <code>A B...
Categories: DBA Blogs

Calculate a variable date value and use it in a where clause to return all rows after that date

Thu, 2018-10-04 22:06
Long time SQL user of many flavors but brand new to PL/SQL and struggling to learn the "Oracle way". I've seen MANY examples of using variables in queries online and in documentation, but I've been unsuccessful finding a sample of what I want to do ...
Categories: DBA Blogs

SP execution plan should depend on input parameter

Thu, 2018-10-04 22:06
Hi guys, I have a SP having input parameters and the execution plan should depend on the parameters provided to the procedure. Ex : PROCEDURE GetData( DataType int, DataValue int ) I want this procedure to search DataValue in column1 if DataType =...
Categories: DBA Blogs

Understanding SQL Profiles

Thu, 2018-10-04 22:06
Hi Tom, My understanding of using SQL Profiles has always been that they would prevent (frequent) changes in access paths of SQL statement. This morning I noticed that, despite the fact that an SQL profile was connected to a statement and statias...
Categories: DBA Blogs

taking many days to merge clob column

Wed, 2018-10-03 09:46
Hi Tom, I have two tables which have clob data, Trying to merge the clob data from work table to Live table. its taking more and more days.. total data size 72GB. What is the best way to run the merge in this case to complete the task quickly. ...
Categories: DBA Blogs

What is the Oracle recommendation for Oracle RAC database and use of Sophos anti-virus software on LINUX?

Wed, 2018-10-03 09:46
What is the Oracle recommendation for Oracle RAC database and use of Sophos anti-virus software on LINUX? I have read nearly every Oracle document but can find nothing but Windows recommendation. Should i use that as a base for exclusions (? Oracle...
Categories: DBA Blogs

Calling a stored procedure to send a mail from after insert trigger

Wed, 2018-10-03 09:46
I have a requirement to send mail from an after insert trigger and I am passing the :NEW.MYID to the procedure. The procedure contains a query with aggregate functions and storing to an 'INTO mynvarchar2var' as a generated string to be send as email...
Categories: DBA Blogs

How to retrieve data from a quarter?

Wed, 2018-10-03 09:46
Right now am using the below query to extract the date. FROM c WHERE date <=TO_DATE ('30-SEP-18', 'DD-MON-YY'). Can you suggest me a way where I dont need to hardcode the date like 30-sep-18. Note: the example date is 30 sep 2018 because I'm ...
Categories: DBA Blogs

Pages