Tom Kyte

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

Updating / Resetting columns

Sun, 2016-08-07 15:26
Hey Guys, I have a process where I need to update one column with the value of another if the 1st column is null, when I transfer the value I need to set the value of the second column to null, I am doing this in a single update statement, see exa...
Categories: DBA Blogs

MVIEW Dependencies

Sun, 2016-08-07 15:26
Where are the MVIEW dependencies stored? SQL> create view y1 as select 1 as y1 from dual; View created. <code>SQL> create materialized view y2 as select y1 as y2 from y1; Materialized view created. SQL> create view y3 as select y2 as y...
Categories: DBA Blogs

Selecting rank wise records

Sun, 2016-08-07 15:26
Hi Tom, Its been such a long time to visit here and resolve the doubts but this is the first time I am asking. There is an Employee table with Name, Department, Salary and Date of Joining in it. I need to write a query which will give the outpu...
Categories: DBA Blogs

Oracle Text CONTAINS with NULL input string

Sun, 2016-08-07 15:26
Hi Tom, I am on Oracle 11gR2. I have a table where I have created a text index. I have written a query as below: SELECT * FROM my_transactions t WHERE 1=1 AND CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0; It prompt...
Categories: DBA Blogs

out-of-place refresh of a materialized view

Sun, 2016-08-07 15:26
I am researching how to improve the availability of data in an MV. The doc says that while doing a complete refresh of MV using the out-of-place option that the data in the mv is still available, which I find is true. But it also says the mv is av...
Categories: DBA Blogs

Oracle

Sun, 2016-08-07 15:26
TABLEA ****** C1 C2 C3 (COLS) * - - - * - * - * - * * I have 6(*) in the above table? But when i select my table i get the count 6 result how? sir? From my side i have done like select count(c1) from tb...
Categories: DBA Blogs

plsql trigger

Sun, 2016-08-07 15:26
create or replace trigger foremp before insert on emp declare day char(10); begin if (to_char(sysdate,day) in ('sunday')) then raise_application_error(-20300,'not allowed on sunday'); end if; end; / I created this trigger t...
Categories: DBA Blogs

Are we really create table aumatically as per our requirement

Sun, 2016-08-07 15:26
Hi, I want to create a table automatically as per based of various column data types and various number of column. Please ! dont cconsider the constraint. for example: table name :table1 required output is : DDl structure of crea...
Categories: DBA Blogs

PLSQL syntax

Sun, 2016-08-07 15:26
VARIABLE NAME CANNOT START WITH A) NUMBER B) SPECIAL CHARACTER C) CHARACTER D) ALL OF THE ABOVE I KNOW THAT WE CANNOT START A VARIABLE USING NUMBER ALSO SPECIAL CHARACTERS EXCLUDING(&,#,_). THIS IS A QUESTION I GOT FOR AN EXAM AND I SELECTED...
Categories: DBA Blogs

Is there a way to report on PROCESSES, SESSIONS AND CONNECTIONS that were killed using the kill command?

Sat, 2016-08-06 21:06
Is there a way to report on PROCESSES/SESSIONS/CONNECTIONS that were killed using the kill command?
Categories: DBA Blogs

Timestamp comparison in Oracle

Sat, 2016-08-06 21:06
Hi Guys, I need to fetch almost 2 years old records for 1 day i.e if today is 5 Aug I want to fetch records of 25 July. For this I wrote below query select * from Employee where employee_start_ts between add_months(sysdate-12, -24) and ad...
Categories: DBA Blogs

IOPS in oracle database

Sat, 2016-08-06 21:06
Hi Team, We need to check the read write iops in oracle database how can we check. Thanks
Categories: DBA Blogs

Do tons of sequences create any kind of problems?

Sat, 2016-08-06 21:06
Do sequences take up much memory or cause any other trouble if you end up with 10s of thousands of them? ========================= Unnecessary details: We have to design a service to generate unique IDs. We can do to a couple ways - One i...
Categories: DBA Blogs

Deleting large number of records

Sat, 2016-08-06 21:06
I have a table with 1 Million records. Because of undo log size, i can not delete this 1M records in one shot. So, need to create a PLSQL block which will delete the record in batches (let's say undo log can sustain .1 M records at one shot). Could y...
Categories: DBA Blogs

Sql trace and identify problem

Sat, 2016-08-06 21:06
My main question is how to identify cause of sql? alter session set timed_statistics=true alter session set max_dump_file_size=unlimited alter session set tracefile_identifier='MYSESSION_TODAY' alter session set events '10046 trace ...
Categories: DBA Blogs

SQL IN trimmed trailing blanks!

Sat, 2016-08-06 21:06
<Fred Feuerstein writes> I recently received an email from a puzzled developer. So...what's wrong with the following scenario? SQL> SELECT 'SQL IN trimmed trailing blanks!' 2 FROM DUAL 3 WHERE 'CA ' IN ( 'CA', 'US' ) 4 / 'TRIMMEDTRAILI...
Categories: DBA Blogs

The new Identity clause doesn't increment the id

Fri, 2016-08-05 08:26
To reproduce the problem : <code>CREATE TABLE MY_TABLE ( ID NUMBER(19, 0) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name NVARCHAR2(255) NOT NULL ); INSERT INTO MY_TABLE (ID, name) VALUES (1, 'test1'); INSERT INTO MY_TABLE (name) VAL...
Categories: DBA Blogs

Use select query to concatinate, and remove spaces from two columns

Fri, 2016-08-05 08:26
Compare table with spaces in between data, with table without spaces Example:- Table A has three fields: ID, Cd_1, and Cd_2 Fields: Cd_1 or Cd_2 can have spaces between the codes. Below example has space between X and Y in Cd_1 ID Cd...
Categories: DBA Blogs

I've done the test and got the wrong answer but I don't know why

Fri, 2016-08-05 08:26
It's about the code, I don't know why it displays "before 5 after 5". CREATE OR REPLACE PROCEDURE plch_test AS BEGIN NULL; -- 5 lines of code END; / CREATE OR REPLACE TRIGGER plch_bca BEFORE CREATE OR ALTER ON SCHEMA DECLARE l_li...
Categories: DBA Blogs

SELECT Query

Thu, 2016-08-04 14:06
How to select a last value of a column which is not in group by For eg Emp id salary j_dt 1. 100. 1-Jan 1. 200. 1-Feb 1. 130. 1-Mar select last value of amount, sum of amount, avg of amount ...
Categories: DBA Blogs

Pages