Tom Kyte

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

Log of switchover/failover/open

Wed, 2018-06-20 01:26
What data dictionary view can be used to determine the number of times that a switchover/failover/open has occurred for a standby database?
Categories: DBA Blogs

connection pooling

Wed, 2018-06-20 01:26
Tom, What is connection pooling ? Please, can you give an example(s) that show thorough understanding of the subject matter as related to either ODBC OR JDBC application connections to the oracle database. Your site is more important and most val...
Categories: DBA Blogs

How to detect if insert transactions in oracle db are really slow?

Tue, 2018-06-19 07:06
At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data. Here's the situation: Some production devices send data results from tests to Server A, this server is a important server and it replica...
Categories: DBA Blogs

How i can optimize this operation DELETE if the values ares setted in codehard.

Tue, 2018-06-19 07:06
Hi, I'm a bit new to the development of plsql. I would like to know how I could optimize the delete operation with a forall if my query is the following: DELETE FROM SCH.TA_DELETE WHERE FIACUM < 1 AND FIPAIS = 1 AND F...
Categories: DBA Blogs

problem of inserting a long string of characters

Tue, 2018-06-19 07:06
Hello Team , I'm trying to insert into a table " TEST COM " the result of selecting rows of another table. I used the wm_concat function . /**********/ insert into COMMENTAIRE_TEST (SELECT wm_concat((DBMS_LOB.SUBSTR(COM_TEXTE,400...
Categories: DBA Blogs

Use RESULT_CACHE in subqueries

Tue, 2018-06-19 07:06
Dear Tom, I am thinking to use the new feature "RESULT_CACHE" to optimize some search queries for my paginated pages. So far, for a search page I have : 1.) a count query and 2.) the query that returns a page from the result set Both 1 an...
Categories: DBA Blogs

database links

Tue, 2018-06-19 07:06
how can i create database links to access remote databases. please tell me the procedure of creating database links.
Categories: DBA Blogs

How to avoid repeated function call for multiple columns' values.

Tue, 2018-06-19 07:06
Hi I'm refactoring an old procedure that calls a function for determining whether passed in values consist of only characters allowed in the front end app on top of the database. The procedure has a cursor that gathers all records it needs to ...
Categories: DBA Blogs

PL/SQL Procedure - Catching "ORA - 01013 - User Requested Cancel of Current Operation"

Mon, 2018-06-18 12:46
It may be a silly question but I am wondering if there is any way to catch this error "ORA-01013 - User requested cancel of current operation" in a PL/SQL procedure. The requirement that I have is to update a database record before exiting when t...
Categories: DBA Blogs

Ambiguous overloading when parameter is null

Mon, 2018-06-18 12:46
<code></code>I have a package with an overloaded function, wich accepts one parameter (INTEGER / VARCHAR2) and returns a booblean indicating if exists a register with the correspondient column having the same value as the parameter. In tests, w...
Categories: DBA Blogs

JDBC thin dirver vs OCI driver

Mon, 2018-06-18 12:46
Hi Tom, i had some discussions with other colleagues about which driver should / could be used for connecting to an oracle database. I searched the web but found no sufficient answer for me (only a pretty old question from this site https://askto...
Categories: DBA Blogs

Replace characters in string

Mon, 2018-06-18 12:46
What's the best way to replace every character in string on random character and every number on other random number. I think about best performance. Input: MatijZ34 Output: sWirpt77
Categories: DBA Blogs

SQL functions in control file when direct load

Mon, 2018-06-18 12:46
Hello Tom, I have 100 million records to load to a table. I am sqlldr to do so. I have been using the conventional load but is taking long. In the control file I have SQL functions to substring and other data manipulations. My question is, can I use...
Categories: DBA Blogs

How to find time taken by query at each stage while being processed by db

Mon, 2018-06-18 12:46
Hey, We have some queries that perform too badly during load test. As per DBA the explain looks good. I want to know if there is a way DBA can monitor the time taken by query at each stage like parsing, executing, returning rows, etc? Thanks!
Categories: DBA Blogs

After logon on database

Mon, 2018-06-18 12:46
<code>CREATE OR REPLACE TRIGGER LOG_T_LOGON AFTER LOGON ON DATABASE DECLARE osUser VARCHAR2(30); machine VARCHAR2(100); prog VARCHAR2(100); ip_user VARCHAR2(15); BEGIN SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_addres...
Categories: DBA Blogs

Generate a date range

Mon, 2018-06-18 12:46
Hi tom, I have one question in which suppose i take two date range '10-jun-2014' and '10-jun-2018' then i want the output like 10-jun-2014 to 10-jun-2015 10-jun-2015 to 10-jun-2016 10-jun-2016 to 10-jun-2017 10-jun-2017 to 10-jun-2018 Can...
Categories: DBA Blogs

SQLERRM:ORA-06531: Reference to uninitialized collection

Mon, 2018-06-18 12:46
<code>Hi I am facing this error. my script is like below. please suggest: / create table address_test( Addr_id number, addr_cus_id number, street_name varchar2(100), town varchar2(100), county varchar2(100), sub_county_state_province varchar2...
Categories: DBA Blogs

Sqlldr is throwing OCI.dll exception with Oracle 12.2 Instant Client

Fri, 2018-06-15 11:06
Hi, I have downloaded Oracle 12.2 Instant Client (both SQLPlus and Tools) and on my Window 7 64 Bit system from http://www.oracle.com/technetwork/topics/winx64soft-089540.html. I have unzipped the all files to C:\oracle122, along with by tnsname...
Categories: DBA Blogs

Converting rows to columns

Fri, 2018-06-15 11:06
Hi Tom, Hope you are good. I have a requirement where I need to display rows as columns. Suppose there are 2 rows with 4 columns each then the result should display 2*4 i.e, 8 columns. Is it possible just using SQL? Thanks
Categories: DBA Blogs

Oracle Goldengate

Fri, 2018-06-15 11:06
What is the advantage of Goldengate over Stream? Oracle Goldengate has high license cost compared to Streams. So, why an organization should use Goldengate for their data replication need and not Streams? Does Goldengate has advantage, which is wo...
Categories: DBA Blogs

Pages