Tom Kyte

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

extracting particular pattern from data using sql

Mon, 2016-10-24 11:46
Dear Tom, Need to extract particular pattern as example. select ('|100|BXX656|:20:200100O0012|:32A:010607USD6025,10|:50:XYZ LABORATORIES PVT LTD|B/2 TESTCHAMBERS 22 B DESAI RD|CBD-26| -||||||||||||||:59:W.S.A. TEXT SZCZESIUL|USA|||:71A...
Categories: DBA Blogs

How to Maximize Performance for Date Logic Queries when Base Tables Only Contain Year and Month Columns

Mon, 2016-10-24 11:46
Hi Tom, <b>It's my first question!</b> Also, I love this forum--as a newbie to Oracle, Ask Tom is invaluable :) In our organization, we have many base tables that are built with YEAR and MONTH number type columns that serve as part of a composi...
Categories: DBA Blogs

Oracle Job and Email notification

Mon, 2016-10-24 11:46
Hi, I have a Oracle Chain, which calls multiple Jobs and internally Job calls SP to update a table Flag. Oracle Chain ------------- BEGIN DBMS_SCHEDULER.CREATE_CHAIN ( CHAIN_NAME => 'CCR_CHAIN', RULE_SET_NAME => ...
Categories: DBA Blogs

Archive log list Generating high in my Production server

Mon, 2016-10-24 11:46
Hi Tom, In my Production server generating archivelog files more than 200GB per day. Usually we taking manual backup (OS level move files) of this archive log to another drive for emergency purpose. after that will delete next day. and monthly onc...
Categories: DBA Blogs

Catch ORA-28002 in Oracle forms

Sat, 2016-10-22 04:46
Hello, How can I catch warning ora-28002 (password will expire within days) in Oracle forms 6i? I tried in several triggers but with no success. Is there a way to catch such warnings in Forms? Also for example the ora-28001 (expired) code. ...
Categories: DBA Blogs

commands execution sequence of sqlplus

Sat, 2016-10-22 04:46
dear Tom, I have command echo "exit" | sqlplus $CONDB @LONGSQL in AIX. Questions: 1. will sqlplus always execute exit after executing LONGSQL. 2. If there is no exit in LONGSQL?what is the better way to let sqlplus exit after executin...
Categories: DBA Blogs

Result of view, if base table is modified

Sat, 2016-10-22 04:46
Hi Tom, During a recent interview, I was asked that what happens to a view, if the base table is modified. For eg. I have a table emp with 3 columns viz eid, did, sal. I have created a view vw_emp as CREATE VIEW vw_emp AS SELECT * FROM emp; Thing...
Categories: DBA Blogs

ORAPWD file usage and its not using properly

Sat, 2016-10-22 04:46
Hi Tom, I dont know below is fault or am i missing something in the architecture. Iam using orapwd file , -- When connecting from OS level password will be taken from this file -- once i change / recreate the ORAPWD file its not working as ...
Categories: DBA Blogs

Extract domain names from a column having multiple email addresses

Sat, 2016-10-22 04:46
Hi Tom, i am trying to extract the domain names from a comma delimited email address and show them as comma delimited. i was successful to some extent where i am able to grab the domain name using REGEXP_SUBSTR and REGEXP_REPLACE and show them in...
Categories: DBA Blogs

Deadlock on two delete statements

Fri, 2016-10-21 10:26
Hi Tom, I'm not sure I understand the root cause of the following deadlock trace. Assuming I'm reading it correctly the trace is showing two different sql sessions attempting to delete the same row in the AAA_WF_OPERAND table. However, I do not se...
Categories: DBA Blogs

REGEXP_REPLACE help

Fri, 2016-10-21 10:26
Hi! This is (should be...) a trivial question for those who are familiar with Regular Expressions, I guess (and hope). I used them almost 25 years ago, and I remember I was comfortable with them at the time. Weird enough, no matter how hard I am stru...
Categories: DBA Blogs

DDL of composite partition table without subpartition in each partition

Fri, 2016-10-21 10:26
Hi Tom, I have one composite partitioned table. When we generate ddl of the table, subpartition details are present in each partition. I know that we can have different high values for subpartition in different partition so subpartition details in...
Categories: DBA Blogs

alert logfile

Fri, 2016-10-21 10:26
Hi , I need your help to find alert log text data between a range of dates eg: 10 june 2016 to 11 june 2016 in linux RHEL 6 ,as my alert log file is very big i can't do that manually. Your help will be appreciated ,thanks in advance. regards.
Categories: DBA Blogs

before insert of update on a column ROW tigger

Fri, 2016-10-21 10:26
currently i am using REGEXP_SUBSTR function to encrypt cc number in a text column of varchar2. is there a function for oracle 9i that i can use to encrypt the number in any combination to XXX. thank you
Categories: DBA Blogs

Defference

Fri, 2016-10-21 10:26
1) What is difference between conventional path load and direct path load? 2) when to use conventional path load? 3) when to use direct path load?
Categories: DBA Blogs

dbms_parallel_execute and 2 packages of mine !

Fri, 2016-10-21 10:26
Hi I've 2 PL/SQL packages. - First is dedicated to : * create a parallel task, * then to create chunks (by rowid), * and finally to execute (previous) created task by executing a second PL/SQL Package....
Categories: DBA Blogs

How to check if oracle directory points to the right location?

Fri, 2016-10-21 10:26
Hello! Is there a way to check if my directories look at the right folders on my network? ALL_DIRECTORIES view says the directory path is something like /d01/data/xfer/BLA/BLABLA/IN. The actual network location is something like \\xyz14311.bla.c...
Categories: DBA Blogs

Update rows using MERGE on rows that do not have a unique identifier

Thu, 2016-10-20 16:06
I have an external table that reads from a csv file. I then need to merge any updates or new rows to a table. The problem is the table does not have a unique identifier. I have account numbers and dates, but the date may get updated on an account ...
Categories: DBA Blogs

Handle individual UKs on bulk inserts

Thu, 2016-10-20 16:06
Hello, I need to execute bulk insert into a table where two columns have unique constraints. One column has native values (cannot be changed) and another one contains abstract pseudo-random value (I generate it myself but I cannot change the algorit...
Categories: DBA Blogs

Convert my rows in columns

Thu, 2016-10-20 16:06
Hi friends! I tried to use pivot, unpivot and other ways to return the expected, but i'm not successfully. <code>create table t1 (ID NUMBER(5), tp char(1), nm varchar2(5), st number(2), en number(2)); insert into t1 values (1,'A', 'a', 0, ...
Categories: DBA Blogs

Pages