Tom Kyte

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

TRIGGER not dropping user in Oracle 11g?

Thu, 2016-08-18 07:46
Hi , i have database 11g , and i have user X and want to drop this user just once database startup . so i used this trigger : ---------------------------------------------------------------------------------------------------------- CREATE OR ...
Categories: DBA Blogs

diff between AS and IS in Subprogram syntax

Thu, 2016-08-18 07:46
IN The Syntax of Create procedure and function we have two keywords IS,AS what is the difference betwwen them how to use them.Belo I given A link https://livesql.oracle.com/apex/livesql/s/dqk6ejx3lpdaslymswdqfk347 By using is and as i can creat...
Categories: DBA Blogs

Copy huge table without the data on it.

Thu, 2016-08-18 07:46
Hi There, I have 2.7 TB table that contains lots of old data to be deleted. Due to the table usage and functionality I am planning to copy this table to a new one and rename after. My table is IOT partitioned and I want to keep the entire s...
Categories: DBA Blogs

Oracle row to column coversion

Thu, 2016-08-18 07:46
I was asked this question in an Interview and couldn't crack it. I have a Employee table with following values Id Name Sal 1 Sid 1000 2 Jon 800 3 Ram 600 I want the output to be displayed as follows: 1 2 3 Sid Jon Ram 10...
Categories: DBA Blogs

Convert EE to SE TABLE_EXISTS_ACTION=TRUNCATE

Thu, 2016-08-18 07:46
Hi Tom, I was asked to convert EE DB to SE DB with full expdp/impdp. My plan was to do it in 2 steps: 1. impdp to SE just to test if conversion will be ok. 2. impdp to SE with TABLE_EXISTS_ACTION=TRUNCATE with new dump Is this plan safe?...
Categories: DBA Blogs

Convert EE to SE remap_datafile

Thu, 2016-08-18 07:46
Hi Tom, I was asked to convert EE DB to SE DB on same machine. I have following doubts regarding creation of tablespaces and datafiles. 1. If I precreate TABLESPACES on SE database before full impdp then import logs error because it tries to...
Categories: DBA Blogs

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

Pages