Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 45 min 56 sec ago

Database migration from physical server to VM

Thu, 2016-08-18 07:46
Hi Top, We have a new project proposal to migrate the databases from physical servers to VM. When compared to physical servers, VM servers will have some performance impact. I need some information on finding the pre-requisites: 1. What are pe...
Categories: DBA Blogs

Conditional Where clause with decode

Wed, 2016-08-17 13:26
Hello there, Good Day! I have a query at hand: <code> SELECT mf_trn_id FROM mf_transactions WHERE MF_TRN_AGENT_CD = :b1 AND MF_TRN_PAN_NO = :b2 AND MF_TRN_SCH_CD = :b3 AND MF_TRN_COMP_CD = :b4 AND MF_TRN_CD = :b5 AND M...
Categories: DBA Blogs

Join a large table ( 70 Million Records) with two other tables

Wed, 2016-08-17 13:26
Hi, I have a requirement where I have to join three tables, say Employee (~70 Million records) with department (~2000 records) and Cities (~2000 records). The query looks something like <b>select /*20 fields*/ from employee e,department d,ci...
Categories: DBA Blogs

Transformation between ROWs and COLs, then GROUP

Tue, 2016-08-16 19:26
Hi Team, Suppose that we have a table named TEST like this: SQL> SELECT * FROM TEST; WHO L W H -------- ---------- ---------- ---------- TOM <b>10 20 30</b> TOM <b>11 ...
Categories: DBA Blogs

Questions about the upper limit of records in one database block

Tue, 2016-08-16 19:26
Hi, team I was told that the last 3 characteristics of ROWID stands for the row number of database block. Theoretically, every database block could store as many as 65536 rows. I was wondering how many records could stores in one block (with the s...
Categories: DBA Blogs

Performance tuning for report query

Tue, 2016-08-16 19:26
Dear Tom, Thanks for your valuable info which is being provided by you for various questions on oracle to all the users which is also helping us in lot of ways... Now we have a problem with a report query which is almost taking 2 minutes 40 sec...
Categories: DBA Blogs

dbms_parallel_execute.run_task error when parallel_level > 1

Tue, 2016-08-16 01:06
Hi, I am trying to use dbms_parallel_execute to execute a procedure in multiple threads. when the value of parallel_level = 1, the below code just works fine, but when the value of parallel_level > 1 then this below code fails with the error shown b...
Categories: DBA Blogs

Eliminating rows on condition

Tue, 2016-08-16 01:06
I have a table for Vehicle owners as follows. The table has 3 columns, Customer id, Vehicle Identification number, and whether the customer is a Primary or Secondary driver on the vehicle. Customer_ID, Vehicle_VIN, Relationship 0001, 12345678, P...
Categories: DBA Blogs

Function rendom_id

Tue, 2016-08-16 01:06
HI We do have store procedure and CURSOR is looking for random id per month.We can see result like one visit from 08/may/2016 2 - 09/may/2016 5 - 11/may/2016 20 - 12/may/2016 2 - 18/may/2016 Can you tell me why 20 visits get selected from...
Categories: DBA Blogs

Can I send messages on mobile phone from Oracle database?

Tue, 2016-08-16 01:06
Hi TOM, We are running an application on Oracle 8.1.5 on solaris and oas 4.081. We are using basic oracle users for authentication. We want to develop a new functionality in our system by which we will be able to send messages on the user's mobile...
Categories: DBA Blogs

Lost all Redo log file

Tue, 2016-08-16 01:06
Hi Tom, I am practising Recovery . CASE 1)I have Development TEST DB in NON-ARCHIVE Mode. NEVER backed up. All Redo log files including the active one ,are dropped from the OS. How to recover such a DB. I dont mind loosing Transacti...
Categories: DBA Blogs

Find duration of client times SQL Server

Mon, 2016-08-15 06:46
i have data with 3 columns: (column1,col2,col3) as (start date, incident status (Assigned/Inprogress/Resolved), incident_status_reason as (Client Action/ Client Follow) as below values: Start Date incident_status Incident_Status_Reason...
Categories: DBA Blogs

Select query in-consistent results

Mon, 2016-08-15 06:46
Hi Tom, We have an issue with one of our select queries. The query returns in-consistent results (at times) for the same input. The query is fired from a Web Application (named PIPES) using JDBC templates (Spring). A Transaction is created for ...
Categories: DBA Blogs

shrink space behavior of lob tablespace

Mon, 2016-08-15 06:46
Hi I'm trying to understand the shrink space behaviors when dealing with multiple tables with multiple lob columns sharing a single tablespace. We've 3 tables (e.g. T1, T2, T3) with each table having a lob column. And the lob columns are stored in...
Categories: DBA Blogs

multiple layers of aggregation

Mon, 2016-08-15 06:46
greetings experts, I haven't seen this matter among other questions, certainly won't mind having it pointed out if I've missed it. I have an awkward requirement from management that requires summing a set of values, and each value is often going t...
Categories: DBA Blogs

regarding subquery terminology

Mon, 2016-08-15 06:46
When we can use any query with ''From '' clause, can we call it subquery??? eg. select * from (select * from emp order by sal desc);
Categories: DBA Blogs

Hide sensitive Data

Mon, 2016-08-15 06:46
Hi ask tom team, I want to hide sensitive data when querying from SQL Plus or any other SQL tool (Toad, SQL Developer), but show data when accessing from application server. How can i do this ? preferably without physical access to database se...
Categories: DBA Blogs

oracle locks,blocks,deadlocks

Mon, 2016-08-15 06:46
what is mean by locks,blocks,deadlocks in oracle? Please explain with one good example because I am learner,use e.g(emp,dept ) tables
Categories: DBA Blogs

Oracle

Sun, 2016-08-14 12:26
There is one table.How sir. table1(input)...........target(output). 1 0000000001 12 0000000012 123 0000000123 1234 0000001234 12345 123456
Categories: DBA Blogs

ORA-02292: integrity constraint <constraint name> violated

Sun, 2016-08-14 12:26
Hello, I am attempting to delete a record / object (that may use several tables at the DB level) from an application. The delete statement is raising a ORA-02292: integrity constraint <constraint name> violated How can I find the Delete state...
Categories: DBA Blogs

Pages