Tom Kyte

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

DB Time

Tue, 2016-09-20 21:46
Hi Tom, I have been reading your questions/answers thread and like it. Want to ask you one simple question. Database Time= CPU Time +Non-Idle Wait; I see in my database, CPU Time is more all the time and Non-Idle Wait is less. say for exampl...
Categories: DBA Blogs

Materialized View

Tue, 2016-09-20 21:46
Hello Tom, What are the best practices with MV - <1> a) generate MV on the DB that owns base tables - and refresh MV and populate over the DB links to other DB's or b) Pull data from the source DB over the DB link and populate the MV from...
Categories: DBA Blogs

dynamic pl/sql - ora-00922

Tue, 2016-09-20 21:46
Hi Tom, I need to use dynammic sql in a stored procedure. I created an anonymous block to test it. The code fails at execute immediate with ORA-00922. But, when I run the code generated by the block, it works. Why doesn't it work in an anonymous bl...
Categories: DBA Blogs

Importing data from file to oracle databse via command prompt

Tue, 2016-09-20 21:46
Hi Experts, I am trying to load data from file into oracle database via command prompt throwing error like IMP-00038: Could not convert to environment character set's handle IMP-00000: Import terminated unsuccessfully I installed oracle 11g...
Categories: DBA Blogs

Listener Error Related on Web Server

Tue, 2016-09-20 21:46
Hello Chris, Conner and Tom, I am building two Apache/jBoss servers. These servers are connected to an Oracle database server version 12.1.0.1.0 on Windows 2012. 1. Web server one: The website is up and connected to the database. However, the ...
Categories: DBA Blogs

Is ROWNUM=1 on queries makes them faster all the time ?

Tue, 2016-09-20 21:46
Hi Chris/Connar, I have been checking on lot of contents in the Internet to find a "Simple Answer" for this and my final resort is AskTom. For tuning of the our PLSQL programs for our various application we have been using "ROWNUM=1" co...
Categories: DBA Blogs

Oracle EXISTS clause

Tue, 2016-09-20 21:46
Hi Tom, I have recently joined a new organization. Here I am facing performance issue for one of my stored procedure. The problematic sql in my procedure is, <i>SELECT iCustomerGroupSeqNo FROM Col_Cur_AgreementAlloc CCAA,V_Tmp_Allocationsumm...
Categories: DBA Blogs

how to install SQLCL on windows machine 86bit

Tue, 2016-09-20 21:46
hello Connor, I wanted to know if can guide me in right direction where by i may be able to download and install(Step By Step) Sqlcl. i heard about it thats its awesome tool and i really wanted to dig into it. I am using windows machine 86bit ...
Categories: DBA Blogs

Direct path reads of a FTS on a table slow - what could be the reason

Tue, 2016-09-20 21:46
We have two Oracle database instances X & Y with the same parameter / configuration. There is a table "bigtable" which has around 2 Million rows in both the databases. In a query, if the "bigtable" table is full scanned, we observe the below: 1) ...
Categories: DBA Blogs

Sorting By Nearest Future date

Tue, 2016-09-20 21:46
Hi Tom, I need to get the record of nearest future date as first record from the data set and rest by Ascending order For example: <b>Before Sorting : </b> Order Quantity Schedule Date 1 0 Jan-15-2016 2 0 NOV-20-2016 3 1 ...
Categories: DBA Blogs

Comparison of schema in controlled environment

Tue, 2016-09-20 03:26
I learnt that some of the production objects were missing. At that point I decided to change the password of the schema in production so that the comparison can be done with the Stage environment and the extent of damage to the production system can ...
Categories: DBA Blogs

Returning matching or highest row in a SELECT statement

Tue, 2016-09-20 03:26
I'm trying to retrieve only two rows when joining these two table. In table A: I have an ID and Date In table B: I have an ID and Timestamp. My select is retrieving 4 rows, however, I want to pick the timestamp from table B that matches the ...
Categories: DBA Blogs

What does "//" in jdbc connection string mean?

Tue, 2016-09-20 03:26
Hi AskTom team. I'm testing a jdbc connection to database in my java application. When I use the following form: <code>jdbc:oracle:thin:@myhost:1521/myservicename</code> everything's fine, my java application connects to the db. When I ...
Categories: DBA Blogs

sets of ORA-603/604/1000/6512/4088 errors

Tue, 2016-09-20 03:26
Hello All, The question here is how does Oracle handle cursoring with a trigger? Th emax cursor error is happening during execution of the following simple code: CREATE OR REPLACE TRIGGER TNT_OWNER.TRG_USC_ABI_MESSAGE_EMAIL AFTER INSERT OR U...
Categories: DBA Blogs

Move data from one table to other in PLSQL procedure

Tue, 2016-09-20 03:26
HI tom, i have one master table which has more record . i need to move that record into temp table while moving data if any error appear i need to save that row in error table. how to do this ?
Categories: DBA Blogs

Slow Full Table Scan on an IO sufficient machine

Tue, 2016-09-20 03:26
Hi Tom, I am spotting a performance bottleneck when performing Full Table Scan on all the tables. Take a small table example, SQL> SELECT COUNT(*) FROM schema.frozen_woappoint; COUNT(*) ---------- 1767373 SQL> select segment_name,...
Categories: DBA Blogs

DeadLock

Tue, 2016-09-20 03:26
hi , below insert query is causing deadlock.. insert into T_POLICY_GEN_CUST (UPDATE_TIME, INSURED_FLAG, PAYER_FLAG, INSERT_TIME, POLICY_ID, PARTY_ID) values (:1, :2, :3, :4, :5, :6) can you please give me some detailed information about why this...
Categories: DBA Blogs

SQL Plan changes

Sun, 2016-09-18 14:46
I'm in a large organization, and we have many environments (Dev, Sys Int, User Acc and Prod). Many times, our code is moved from one environment to the next, and somehow the execution times skyrocket. (Example, 21 seconds to over an hour). We some...
Categories: DBA Blogs

Could you let us now whether there are possibiities to optimize this query?. Due to space issue we have taken (*) in select statements instead of column names. we wil give a query plan in the next post..

Sun, 2016-09-18 14:46
WITH tgt AS ( SELECT TRIM ( REGEXP_SUBSTR ( REPLACE (MS.tgt_column_name, CHR (10), ','), '[^,]+', 1, LEVEL)) ...
Categories: DBA Blogs

Which algorithm uses dbms_random.random

Sun, 2016-09-18 14:46
Hi, I could not find in documentation. Do you know which algorithm uses dbms_random.random? Thanks
Categories: DBA Blogs

Pages