Tom Kyte

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

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

XMLROOT

Thu, 2016-10-06 04:06
Hello Oracle Guruji Thanks for your support in Advance, I have one doubt on XMLROOT, this is my query ops$tkyte@ORA9IR2>select xmlroot(XMLELEMENT("DriverDescription",'$'||'$'||'s partner payout by PL '||' ='||'>' ||' L2 BU'),VERSION '1.0')as emp ...
Categories: DBA Blogs

Oracle bulk collect/ forall for dynamic usage

Thu, 2016-10-06 04:06
Hi Tom, Thanks to all the great comments provided in the below link : https://asktom.oracle.com/pls/apex/f?p=100:11:114010771330237::::P11_QUESTION_ID:3675533064673 I am currently looking for a solution to a problem in similar lines. Inste...
Categories: DBA Blogs

How to get Unlocked records from a table

Thu, 2016-10-06 04:06
Hi Tom, i am a beginner in Oracle. I want to lock the records while we update in a stored procedure. Meanwhile if any other user trying to fetch the same record which is locked, i should not allow him to fetch that record. As per my knowledge i ca...
Categories: DBA Blogs

datafile block fractured

Thu, 2016-10-06 04:06
Hi, Can you pls let me know what is the difference between - select * from v$database_block_corruption; corruption column showing - corruption or fractured What is corruption and what is fractured How to resolve corruption or fra...
Categories: DBA Blogs

View Syntax isn't working

Thu, 2016-10-06 04:06
Tom, I have an existing view that works fine. It is used to compare rows in two tables and to display which table has records that the other doesn't. I've been asked to add one more column to this view from a new table. For one reason or ano...
Categories: DBA Blogs

Temporary tables (comparision with SQLServer)

Thu, 2016-10-06 04:06
We are migrating some Stored Procedures from Sybase 11 to Oracle 8i. Most of them use temporal tables like: SELECT x, y, z INTO #Temp FROM some_table ... -OR- CREATE TABLE #Temp ( .... ) Where #Temp is a Temporal Table in a temporal area wh...
Categories: DBA Blogs

Pages