Tom Kyte

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

Data loading in DWH tables

Sat, 2016-10-01 14:06
I am inserting data into non partitioned table contained data 250M records. But it is taking too long time to insert data from source system table having same number of data both the tables in different databases. I would like to know different load...
Categories: DBA Blogs

Oracle Substr for Description field

Fri, 2016-09-30 19:46
Hello I need help with the following Scenario. I have the table Create table Descr_temp(Company_cd Varchar2(10), Company_Name Varchar2(254)); with its data <b></b> Insert into descr_temp values ('123', 'ABC SOLUTIONS INC'); Insert in...
Categories: DBA Blogs

The same SQL, but different result: dbms_xslprocessor.clob2file

Fri, 2016-09-30 19:46
Actually this has been resolved. It is OS directory permission issue. Directly login used group permission while "oracle" is the part of the group. The sqlnet login might need the owner permission I think? After asking the system change the file...
Categories: DBA Blogs

Backup and Recovery

Fri, 2016-09-30 19:46
Tom, In responding to a question you mentioned that when you take a cold backup in NoArchiveLog Mode, it is a bad idea to include redo log files, in case you later decided to put the database in archivelog mode. - I understand that if we have to ...
Categories: DBA Blogs

Query to find the Index which is not created by primary key

Fri, 2016-09-30 19:46
Hi, how to find the index list which is not created by primary key for a particular schema? <b>select * from all_indexes where owner='sys'</b> currently i am running this query but this is giving the index which is created by primary key also. ...
Categories: DBA Blogs

Subqueries reduction in Merge

Fri, 2016-09-30 19:46
I'm having a <code>MERGE </code>with a <code>subquery </code>to get and ID, and I'm wondering if the subquery in the <code>NOT MATCHED statement</code> is always executed or not. <code> MERGE INTO CAR_STOCK st USING CAR_PRODUCTO pr...
Categories: DBA Blogs

CALCULATION OF AGE

Fri, 2016-09-30 01:26
I HAVE A DOUBT REGARDING CALCULATION OF AGE. WHY DO WE DIVIDE BY 365 WHILE FINDING AGE.IN MY OPINION IT HAS TO BE ONLY SYSDATE -DATEOFBIRTH
Categories: DBA Blogs

extended statistics and dynamic sampling

Fri, 2016-09-30 01:26
Hello AskTom team, Please correct me if I am wrong, as I understand, "extended statistics" is useful for multi-predicate/non indexed columns for achieving realistic cardinality estimates. Is there any DB level setting where Oracle generates the...
Categories: DBA Blogs

Theory behind adding a 'Select' before a 'Delete' in a program

Fri, 2016-09-30 01:26
We are upgrading software that executes against an Oracle database. The vendor modified code to add a Select statement before issuing an existing Delete statement. The criteria of both commands were the same except the Select included a table that o...
Categories: DBA Blogs

How to add two numbers without + or SUM

Fri, 2016-09-30 01:26
Sir...i was laughed when i was seen one answer from you...That is..). How to add two numbers without using + and sum functions... Actually that is an interview question.When i was attended an interview at TCS then were asked me these type of quest...
Categories: DBA Blogs

Parsing JSON and storing In Different table.

Fri, 2016-09-30 01:26
Hi AskTom, First of all thanks for your contribution to oracle Community! :-) I have this problem where i want to parse a json file stored in clob column and after parsing i have to store it in separate table , i am able to parse this throu...
Categories: DBA Blogs

How to convert only HH24:MI:SS from SYSDATE?

Fri, 2016-09-30 01:26
Hello. I have a quick question about Oracle 11g SQL command. I need to get only HH24:MI:SS data from SYSDATE; for example, select TO_DATE(SYSDATE, 'HH24:MI:SS') from dual; I tried it then error is like below. ORA-01861: literal does no...
Categories: DBA Blogs

insert statement returns error very late

Fri, 2016-09-30 01:26
hi tom, i have an insert statement which inserts select in specific table, the ids in this table are inserted by trigger and sequence and it works fine. it inserts 8600 row in about second which is fine. the question is, if something is viola...
Categories: DBA Blogs

package specification and body

Fri, 2016-09-30 01:26
my question is...if name of procedure along with procedure name and parameters are given in package specification but body of "procedure" is not given in package body will the procedure is going to work ..? if not ...why..? if yes then how..?
Categories: DBA Blogs

SIze of data

Fri, 2016-09-30 01:26
1)? How do you find the size of the data for an application? 2). How to add two numbers without using + and sum functions.
Categories: DBA Blogs

Exp. Consistent=y

Thu, 2016-09-29 07:06
hello Sir, Could you pls. explain about this parameter consistent=y in exp. What is cross table consistency. Is it parent child relationship. Thanks, Js
Categories: DBA Blogs

SQL SELECT query slowness after database upgrade from 11.2.0.4 to 12c

Thu, 2016-09-29 07:06
Hi Tom, What would be the cause sudden SELECT query slowness in a Production Environment after database gets upgraded from Oracle 11.2.0.4 to Oracle 12c? Earlier these 2 SQL queries were ran less than 1 sec. Not its taking 30 to 40 sec's to exec...
Categories: DBA Blogs

Displaying week days for current week

Thu, 2016-09-29 07:06
Hey guys Wondering if you could please help with an issue I am having. I would like to output all weekdays (monday-friday) of the current week. For example if I run it anytime between September 26 and September 30 then it would output the we...
Categories: DBA Blogs

Hash Partition

Thu, 2016-09-29 07:06
Hi Tom, I have a table in PCA database(11g R2) with 30 columns.It has POS data.Data volume is very high in first year,near about 5 TB.Next 3 yrs It will be 6TB approx.This table will be use for huge insert(Informatica will be used for load) like 2...
Categories: DBA Blogs

Buffer busy waits

Thu, 2016-09-29 07:06
Hi tom, AS i have seen we are getting buffer busy waits and insertion is very slow then i increase the table ini_trans from 1 to 50 and continuous showing index lock for that i have increase index ini trans from 2 to 10 Now i have seen the inser...
Categories: DBA Blogs

Pages