Tom Kyte

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

Order by at runtime

Tue, 2018-04-17 16:06
Hello, we have some huge tables to query, and with order by clause (must be used) it takes a very long time for a query to be done. as I know that we can do the order by at run time using dynamic SQL, but my questions are: 1. do we have any o...
Categories: DBA Blogs

Automatic list partitioning

Tue, 2018-04-17 16:06
Hi Tom! I use Oracle 12c version. I have partitioned by list table. How can I change non automatic partitioning to automatic? Thank you!
Categories: DBA Blogs

how to generate .dsv files using SQL script?

Tue, 2018-04-17 16:06
we have around 100 table out of 200, in which there is a column date. what we want is, first we want to chagen the <b>NLS_date_format to DD-MON-YYYY HH12:MI:SS AM</b>(using script) then save the tables with date in a .DSV files. also n...
Categories: DBA Blogs

Julian Date Full Explanation

Mon, 2018-04-16 21:46
Hello, I'm fairly new, but I have been finding bits and pieces on Julian date conversion, but not a full explanation of the Julian date conversion? <b>I.E TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYDDD'))-1900000</b> Firstly, the SYSDATE is using the T...
Categories: DBA Blogs

Help needed with match_recognize

Mon, 2018-04-16 21:46
Dear Mr. Tom, Thank you for all your help and time in supporting our requests. I have some issues with MATCH_RECOGNIZE Oracle Version - 12.1.0.2.0 OS - REDHAT Linux <code>CREATE TABLE test_match_recognize(employment_id NUMBER (10, 0) NOT N...
Categories: DBA Blogs

How to remove multiple word occurance from an input string using oracle PL/SQL

Mon, 2018-04-16 21:46
Remove duplicate words from a address using oracle pl/sql: There are two types of addresses will be there, below is the example 1. '3 Mayers Court 3 Mayers Court' : where total no of words in address is even and either all words/combination of ...
Categories: DBA Blogs

merge and dbms_errlog behaviour with ORA-30926

Mon, 2018-04-16 21:46
Hi all, I have a merge statement that sometimes fails when the source table has duplicated merge keys. To save time I tried to use dbms_errlog package and let it save the coulript rows, without failing the statement itself. The error I get befor...
Categories: DBA Blogs

How to trap DDL Activities and get the Sql text of such statements

Mon, 2018-04-16 03:26
Hello Sir, I am quite surprised to see that Today urs ite is not blocked for me. Sir ,I've an immediate requirement to trap all the activities fired in the Databse(DML as well As DDls). As Auditing is not supportive for this purpose. I need the...
Categories: DBA Blogs

Oracle text index super slow

Sun, 2018-04-15 09:06
Hi Tom I am not an expert in oracle so thought I will use your help here. I have an application which does a full text search but it is very very slow. Not sure if I build the index correctly. Below are the details: BELOW IS USED TO CREATE INDE...
Categories: DBA Blogs

schedule the PL SQL query and save in .csv file

Sun, 2018-04-15 09:06
>Hi Tom, I need to schedule a sql script and run everyday on 10 am. But I don't know how. I have search some same topic but I still don't understand. Here's the sql that I have been use to queries my data and I export it manually in <b>.csv</b...
Categories: DBA Blogs

Missing values using pipelined functions and refcursor

Sun, 2018-04-15 09:06
Hi, We are using a several pipelined functions to return values to an AIP based on plan number paramerer (table type). The result of these functions are combined (using table(function)) and returned in an open refcursor to the webservices (API). ...
Categories: DBA Blogs

Getting IP Address of DB Server

Sun, 2018-04-15 09:06
Hi, I would like to know IP Address of DB Server. So i executed below Query found on Google. SELECT A.HOST_NAME,A.INSTANCE_NAME,UTL_INADDR.GET_HOST_ADDRESS FROM V$INSTANCE A; I got one IP Address but its not matching with the IP address in...
Categories: DBA Blogs

ACL created but unable to fetch the result

Fri, 2018-04-13 02:06
Hi TEam, I have created function to return the different language by refrencing http://www.orafaq.com/forum/t/170320 also i have created ACL and after running the function we have below error. select eospb_translation ('cats', 'fr','en') fr...
Categories: DBA Blogs

How to improve performance of select query

Fri, 2018-04-13 02:06
Hi Tom, I have table(coupon_c ) where millions of records in which there is one column coupon which have hardly 100 distinct values but total count are in millions so data distribution is like for one coupon there is around 1 millions records. <...
Categories: DBA Blogs

Move subpartition to another partition

Fri, 2018-04-13 02:06
Hi. Today I've discovered another issue on the DB I've inherited. I've found this range subpartitions in wrong partitions. Let me try to explain. The scenario is: Partitions with month values subpartitioned by weeks, so I expect to have a ...
Categories: DBA Blogs

PL/SQL Performance Tuning

Fri, 2018-04-13 02:06
My stored PROCEDURE searches for potential duplicate PeopleSoft Vouchers (PS_VOUCHER, PS_VOUCHER_LINE, PS_DISTRIB_LINE, PS_VENDOR, PS_PYMNT_VCHR_XREF). The first step is to get the set of unique concatenated/delimited values for records that are cur...
Categories: DBA Blogs

Avoid full-table scan when filtered by ora_rowscn

Fri, 2018-04-13 02:06
Hello, We have a need to extract data using "sqoop" on a bunch of tables. However, there is no column defined in those tables that store information like "update date or last modified date". We are thinking of using ora_rowscn as a filter. Howe...
Categories: DBA Blogs

Column deleted by mistake

Fri, 2018-04-13 02:06
Hi Tom, Thanks for wonderful support always. I want to know is there any way to know who has deletes the entire column from the table. There is no manually created trigger in my database for auditing on column delete. One entire column ha...
Categories: DBA Blogs

Conditional execution one of two parts of union all query

Fri, 2018-04-13 02:06
I have have two complex, expensive queries that I have performed a union all to bring together for use in a report tool I have a bind variable that, if set to one value, the union query should execute the first part of the union. If the bind ...
Categories: DBA Blogs

In order to connect to 12C DB, do we have to change our client version to 11.2.0.3(or above)?

Fri, 2018-04-13 02:06
Hi Team, We've installed a latest Oracle Database 12C Release 2 on OEL6.8. And found that we could not connect to our DB unless using client which version is above 11.2.0.3(included)! Here's our senario: First, we tried to connect to DB using 11...
Categories: DBA Blogs

Pages