Tom Kyte

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

Oralce Open v$open_cursor counts simple "updates" as open with the use of a cursor (open, execute, fetch, close, commit)

Thu, 2018-04-19 23:06
I am checking for open cursors while running our client server application (application info below) with the query below and noticed that a simple ?update? without the use of any cursors shows as open cursor. When another ?update? is issued its repla...
Categories: DBA Blogs

Performance issue/session getting hang

Thu, 2018-04-19 23:06
Hi Tom, I have a table having around 5 million records. Table Structure : DESC RPT_MSG_CHANGE <code>Name Null Type ---------- -------- -------------- OID NOT NULL NUMBER PRODUCT NOT NULL VARCHAR2(20)...
Categories: DBA Blogs

Long Raw to BLOB

Thu, 2018-04-19 04:46
Hi Tom, We are using an Oracle 8.1.7 database. Is there a way in PL/SQL or Java Stored Procedure to convert a Long Raw into a BLOB? Thanks, Firas Khasawneh
Categories: DBA Blogs

Bug in Export Wizard?

Wed, 2018-04-18 10:26
When using the export wizard and browsing to an existing file that I want to overwrite with my new data results, I find when I select that specific file from the Export File Chooser window and click SAVE, the file that is actually selected is some ot...
Categories: DBA Blogs

dbms_stats and optimize techniques

Wed, 2018-04-18 10:26
I am setting the degree parameter of dbms_stats.gather_table_stat. could any one tell me that how we can calculate the value of this parameter and how this is link with the hint optimize techniques.
Categories: DBA Blogs

Best way to index uuid

Tue, 2018-04-17 16:06
Hello, What is the best way to index uud if I only do equal comparaison on it ? I gess that Hash index is better but I'm not sure. Regards Stephane GINER
Categories: DBA Blogs

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

Pages