Tom Kyte

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

Sql Loader (NULLIF and NVL(TO_NUMBER) - NULLIF not yielding the correct result. The columns is not being set as NULL but loaded with 0.0.

Wed, 2016-11-09 11:46
I have the following Sql Loader Control file : POS_COST_PRC_FUND POSITION(173:202) DECIMAL EXTERNAL NULLIF V4_NULL='?' "NVL(TO_NUMBER(LTRIM(:POS_COST_PRC_FUND,'0')),0)", V4_NULL FILLER POSITION(203:203) CHAR, The column in Oracle is defined a...
Categories: DBA Blogs

APEX 5.0 ORDS 2- Tomcat 7 - Errors on tomcat log and performance issues

Wed, 2016-11-09 11:46
Hi Tom, We have implemented Apex 5.0 as our new Developing tool which is a real great tool. The configuration is on HP/UX, tomcat, ORDS and APEX on Oracle 11g r2. We have 4 environnements (dev, qualification, preprod, prod). On the 3 first ...
Categories: DBA Blogs

Table dependencies

Tue, 2016-11-08 17:26
To find databse table dependencies I ran below sql, select rpad( '*', (level-1)*2, '*' ) || table_name table_name from ( select p.table_name , p.constraint_name primary_cons , f.constraint_name foreign_cons , f.r_constr...
Categories: DBA Blogs

finding regular expression

Tue, 2016-11-08 17:26
Hi i am trying to match a pattern in a string. It is web log data so basically i need to look as follows find a comma followed by two spaces followed by anynumber of characters other than a quote follwed by = Eg: is string is abc=1, name='1...
Categories: DBA Blogs

Casting complex object IN parameter of a procedure multiple time for inserting into multiple tables VS Looping through the object and inserting into all the tables

Tue, 2016-11-08 17:26
Hello Tom First, thanks for all the help and advice you provide to the Oracle world. I have a procedure which takes IN parameter as a complex object (Ex. Array Starts with Product Events which contains Engines(object) which in turn contains par...
Categories: DBA Blogs

Receiving the error: "Couldn't switch user" while executing a job

Tue, 2016-11-08 17:26
Hi Experts, I have a job under oracle scheduler that executes a chain. This chain has 3 steps: Step 1: executes a stored procedure Step 2: executes a unix shell and Step 3 executes another stored procedure. The JOB used to work fine but suddenl...
Categories: DBA Blogs

Data Archival

Tue, 2016-11-08 17:26
Hi Tom, Couple of questions here There is requirement of data archival, one option i can find is create separate database and load data using dblink. Is there any efficient way to do archival? Post archival there is a requirement where c...
Categories: DBA Blogs

Regarding high CPU usage for index query

Tue, 2016-11-08 17:26
Hi As part of a query execution we observed high CPU usage. The query is using the indexed column in where condition. Table : Select * from mytable where mycol1 <= 200000 and xxxx This table is having index on column mycol1. This tab...
Categories: DBA Blogs

Loading The XML like data into columns

Tue, 2016-11-08 17:26
Hi Tom, I have a data in a csv file like this: something_here, "{ "entityValue" : { "vlanId" : { "type" : "string" , "value" : "121a" }, "vlanDescription" : { "type" : "string" , "value" : "asdf" ...
Categories: DBA Blogs

I am trying to access a function stored in a package and it gives me invalid sql statement error

Tue, 2016-11-08 17:26
HERE IS MY <b>PACKAGE</b> AND ITS <b>BODY</b>: CREATE OR REPLACE PACKAGE TBOOK AS FUNCTION TBOOK1(JID VARCHAR2) RETURN VARCHAR2; END; CREATE OR REPLACE PACKAGE BODY TBOOK AS JID2 VARCHAR2(6); JID1 VARCHAR2(6); FUNCTION TBOOK1(JID VARCHAR2)...
Categories: DBA Blogs

Data retrieval from two databases

Tue, 2016-11-08 17:26
Hi Team, I have requirement - Consider : 1. Table1 is holding data current 5 years of data in primary database 2. Table1 is holding data older 5 years of data in other database(archived) is there any way we can manage records retr...
Categories: DBA Blogs

Copy table data From One DB to Another DB

Mon, 2016-11-07 23:06
Hi Team, I need to Copy table data From One DB to Another DB. One approach I can recollect from one of the conversation asked in asktom.oracle.com is that create database link and simply execute - insert into local_table select * from table@...
Categories: DBA Blogs

export import

Mon, 2016-11-07 23:06
Hi team, I wanted to know that when i export entire schema from one database and import into anothere database then- Objects like - tables,functions,triggers,procedures,dblinks,synonyms,public synonyms and many more. Which objects export dur...
Categories: DBA Blogs

SQL not using index

Mon, 2016-11-07 23:06
Tom, There is an index on a table, but that index is not being used by SQL(even with hint). Can you please tell if something is wrong with the syntax? Below is the definition of the index on the table <b>XLA.XLA_TRANSACTION_ENTITIES</b> (PS:...
Categories: DBA Blogs

How to find the tables of a particular string

Mon, 2016-11-07 23:06
Hello there, I'm trying to find the name of the table and column in which it has the particular string. The below code searches for the string in the whole database and prints it out. However I wanted to use wild card because there are instance...
Categories: DBA Blogs

TEMPORARY TABLESPACE

Mon, 2016-11-07 23:06
HOW CAN I DETERMINE THAT WHAT SHOULD BE THE SIZE OF TEMPORARY TABLESPACE FOR ORACLE DATABASE? WHAT ARE THE MEASURES TO BE CONSIDERED FOR SIZING TEMPORARY TABLESPACE? HOW SHOULD I RESIZE THE TEMPORARY TABLESPACE SO THAT WE DO NOT ENCOUNTER ER...
Categories: DBA Blogs

Could not able to drop an empty tablespace. ORA-23515: materialized views and/or their indices exist in the tablespace

Mon, 2016-11-07 23:06
Hi Tom, I have been trying to drop a tablespace, but it is showing the below error. I've checked under dba_segments from any Mviews but I couldn't find anything. 1) SQL> drop tablespace GOLFX including contents and datafiles; drop tablespace ...
Categories: DBA Blogs

Drop Schema

Mon, 2016-11-07 23:06
Hi, If i Drop a shema with DROP SCHEMA <name> RESTRICT,Will oracle also drop tha USER associated with that Schema?
Categories: DBA Blogs

should i use users tablespace?

Mon, 2016-11-07 23:06
hi tom. i am junior dba. my senior dba said to me today that i should not create users in USERS tablespace, if this users create objects, cause this objects will be created in USERS tablespace and that is somehow bad. so my question to him was why...
Categories: DBA Blogs

trigger body

Sun, 2016-11-06 10:26
Hi Tom, I want to search a trigger body for some particular text and would like to know the names of all such triggers... select * from user_triggers where trigger_body like '%CONSULTATION%' My Oracle Version is 10G Rel 1. Now my problem i...
Categories: DBA Blogs

Pages