Tom Kyte

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

Query index information is very slow

Fri, 2016-08-19 02:06
Hello, I do a <code> SELECT AIC.INDEX_NAME AS IndexName, CASE ALC.CONSTRAINT_TYPE WHEN 'P' THEN 'T' ELSE 'F' END AS IsPrimary, CASE ALC.CONSTRAINT_TYPE W...
Categories: DBA Blogs

Partitioning

Fri, 2016-08-19 02:06
I have a table that is partitioned on a date column. When I insert rows into the table and commit all is well. When I select from the table => select * from tableA partition(xyz); I get the number of rows I expect. Also if i select from the table...
Categories: DBA Blogs

New Time Model Clarifications

Fri, 2016-08-19 02:06
Good Saturday Morning, Gentlemen. I am getting ready to give a talk to a local Oracle User's group. One item that I plan to discuss with the club is the Time Model. I have a few questions that will help me understand it better. Where will t...
Categories: DBA Blogs

expdp/impdp with Full Database but need only METADATA

Fri, 2016-08-19 02:06
i want to do expdp and impdp of full database but condition is METADATA_ONLY. SOURCE DATABASE IS 11.1.0 and TARGET DATABASE IS 11.2.0. Please tell a. while exporting and importing which user should be used to export. b. FULL=Y, CONTENT=MET...
Categories: DBA Blogs

IF THEN ENDIF in SQL

Fri, 2016-08-19 02:06
In SQL-Server I could do a IF <Condition> BEGIN <SQLStatement> END example: IF Exists (select 1 from document where id=5) begin select * from sometable END in an SQL-Statement - notice: SQL-Statement! In Oracle as far as I know I could...
Categories: DBA Blogs

explicit Foreign Key constraints in DW

Fri, 2016-08-19 02:06
Hi Tom, In all the 3 Data Warehouse projects that I had worked on we never created any Foreign key constraints in the fact tables explicitly. When I asked for the reason the standard answer that I get is "It will impact the performance and N...
Categories: DBA Blogs

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

Pages