Tom Kyte

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

non-cdb architecture on 12c

Sun, 2016-10-09 05:26
Tom. If we upgrade our 11g database to 12c 12.1.0.2, can we still use the non-cdb model? With 11g going into extended support very soon we would like to upgrade to 12c however, we are not familiar with PDBs yet so we would prefer to mo...
Categories: DBA Blogs

How to speed up an Insert with SELECT

Sat, 2016-10-08 11:06
Hi Tom, good morning Sir. I am trying to speed up an Insert statement where the SELECT is selecting all the source table according to this example: var_select_a_insert := 'insert into lwm_usrappods.ITEM_LOC_SOH select to_number...
Categories: DBA Blogs

High number of misses in library cache during execute - where to dig for explanations?

Sat, 2016-10-08 11:06
I'm relatively familiar with using SQL_TRACE and tkprof for tuning, but I recently spotted something in a tkprof report for one of our customers that raised my suspicions. There is a lot of SQL in the file where the number of library cache executi...
Categories: DBA Blogs

Session wait time of a transaction

Sat, 2016-10-08 11:06
Dear Tom, please help me with the below. Session 1: update table set field = 'A' where field2 = 'B'; it is not committed/rollbacked Session 2: update table set field = 'C' where field2 = 'B'; The session will be waiting for sessio...
Categories: DBA Blogs

How to observe current values of a running plsql code.

Sat, 2016-10-08 11:06
Hello, I can observe the current value of a bind variable, using v$sql_bind_capture. I don't know how to do it, when having no bind variable. Having a simple code like this below, I would like to see a VALUE of rec.column1 being passed to my_functi...
Categories: DBA Blogs

BULK Delete

Sat, 2016-10-08 11:06
Hi Tom, This is my first question to you and hope to receive a positive response :) Straight to the Question: I have a table named trail_log. This is a huge table and a daily growth of this table is 12GB Approx. Currently 76216160 rows. Import...
Categories: DBA Blogs

How to determine if db objects are still used?

Sat, 2016-10-08 11:06
I am working on a database that has nearly 3,900 tables, and 450 views, 90 packages, 740 procedures, and 325 functions. Some of those are called by apps and report servers that I do not have access to all the source code. I have a feeling that m...
Categories: DBA Blogs

UNDO tablespace usage

Sat, 2016-10-08 11:06
Hi Team, I want a customized query which should give me which SQL statement is taking more amount of undo tablespace size with all the details. Request you to please help me on this. Regards, Sridhar
Categories: DBA Blogs

Copy millions of blobs from one table to the other one

Sat, 2016-10-08 11:06
Hello colleagues, I've got a "small" problem, I have to copy millions of blobs from one table to another one. <b>Target</b> table: PAGE ---------------------- ID NOT NULL NUMBER(19) DOCUPLOADCODE VARCHAR2(255) MIMETYPE ...
Categories: DBA Blogs

PL/SQL Code Coverage.

Fri, 2016-10-07 16:46
Hi, I am implementing a system for automated unit testing of our plsql, and would like include statistics on code coverage, especially to highlight where code has not been tested, e.g. "your tests only cover 75% of the code". I have been lookin...
Categories: DBA Blogs

Split intervals

Fri, 2016-10-07 16:46
below is sample data: with sql as (select 3 as level_,1 as start_,4 as end_ from dual union all select 2,2,5 from dual union all select 1,1,7 from dual ) LEVEL_ START_ END_ 3 1 4 2 2 5 1 1 7 each ...
Categories: DBA Blogs

Read a file dynamic - oracle

Fri, 2016-10-07 16:46
create or replace procedure myfun(schemaname varchar2) as Filename VARCHAR2(200) :=&1; --32767 DirectoryName UTL_FILE.FILE_TYPE := &2; schemaname varcahr2(20) := &3; BEGIN DirectoryName := UTL_FILE.FOPEN(DirectoryN...
Categories: DBA Blogs

Optimizer Choosing Partition Pruning Path

Fri, 2016-10-07 16:46
I am attempting to push the Oracle optimizer toward a partition-pruning path unsuccessfully. Any thoughts/ideas appreciated CREATE TABLE small_table (p_key.....value1...value2....value120.) -- 40k Records indexes on each value, and one on (val...
Categories: DBA Blogs

tablespace point in time recovery for a DB including partitioned tables

Thu, 2016-10-06 22:26
Hi Tom, I would like to know is it possible to perform tablespace point in time recovery for a database having partitioned tables. i.e, tables were list partitioned based on one column in each table. Also the partition is moved to its own tablesp...
Categories: DBA Blogs

ModPlSql Authentication in Chrome and Firefox

Thu, 2016-10-06 22:26
Our application, using mod_plsql authentication, retains the password when accessed from Chrome of Firefox. This is when the user is asked to save the password and clicks "YES". The application is using DAD/logmeoff for de-authenticating the user. Th...
Categories: DBA Blogs

System tablespace migration from dictionary to locally managed in 12c.

Thu, 2016-10-06 22:26
Tom, I am an Oracle DBA who has inherited a large production landscape which has been in continuous operation since 1999 and Oracle 8. Back in the days of 10g, the previous DBA performed a migration of all the tablespaces from dictionary to loc...
Categories: DBA Blogs

Cumulative Sum

Thu, 2016-10-06 22:26
Hi, Need some expertise here. Im trying to fetch cumulative sum from a transaction table downwards starting from a outstanding balance that I have handy. Below is the output (trying to get running total based on the calcn formula) that Im trying t...
Categories: DBA Blogs

Oracle XMLUPDATE and XMLQUERY namespace usage difference

Thu, 2016-10-06 22:26
Hi, I am trying to change the updatexml with xquery way of update, and found the namespace what was used in updatexml cannot be used the same way in the xquery. Kindly let me know the usage mentioned below is valid or not. <u>Test code</u> ...
Categories: DBA Blogs

Remove Duplicate values from table

Thu, 2016-10-06 22:26
Hi Team, I have a table (user_details ) which has duplicate values, i need to remove the old values from the table. Please suggest Here is the sample of my user_details table- <code> leg_id update_time login_id 1-100CAFS 20140911153...
Categories: DBA Blogs

XMLIndex on In-Memory Variables

Thu, 2016-10-06 04:06
Hi, I went through the articles on XMLIndex, which will be created on the table - with XMLType columns. If i am processing PL/SQL package - procedure or functions - with XMLType variables passed through different procedures, can i make use of XM...
Categories: DBA Blogs

Pages