Tom Kyte

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

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

Identify overlapped intervals

Thu, 2016-08-04 14:06
Hi team, I have a table with intervals of numbers. However, my table have intervals that overlap and a number can be in more than one interval. For example, start_interval | end_interval 1 | 2 2 | 4 3 ...
Categories: DBA Blogs

Nullable Property When Copying a Table Using CTAS

Thu, 2016-08-04 14:06
Hello, In the example below, I'm using a CTAS statement with where 1=2 to copy the structure of a table. Four of the columns that are defined as NOT NULL on the source table retain that property in the new table. However, one column (patient_id) b...
Categories: DBA Blogs

Change the creator of a dbms_scheduler job

Thu, 2016-08-04 14:06
Hello, is it possible to change the creator of a dbms_scheduler job, so it is executed with the rights of another user? Regards Wolfgang
Categories: DBA Blogs

Find rows inserted yesterday

Thu, 2016-08-04 14:06
Hi, I have a table with 3 columns emp_id,last_name and date_of_birth. And each day data loading starts after 12 am . I have to generate the report on data loaded before today's load (exclude loaded today after 12 am). I was asked this questio...
Categories: DBA Blogs

PLSQL INTERVIEW QUESTIONS

Thu, 2016-08-04 14:06
HI TOM, PLEASE EXPLAIN WITH AN EXAMPLE ...BREIFLY I KNOW ONLY LITTLE BIT 1)How to debugg your code? 2)How to trace error handling? 3)Can you alter procedure with in package? 4)Is it possible to open cursor which is in package in another procr...
Categories: DBA Blogs

ORA-01000 - How to close open CURSORs?

Thu, 2016-08-04 14:06
Hi, I have a cursor leak at the application level (Java), how can I close open cursors? If I Kill the session, does this close the related cursors? Also, if I use DBMS_SHARED_POOL.PURGE to flush the query behind the issue, does this close the ...
Categories: DBA Blogs

Sessions disconnected automatically.

Thu, 2016-08-04 14:06
Hi Tom. I can't find how my database is disconnecting the inactive sessions because when I ran the following statements: select * from user_resource_limits a where a.resource_name in ('IDLE_TIME','CONNECT_TIME'); IDLE_TIME UNLIMITED CONNE...
Categories: DBA Blogs

related to stored procedure

Wed, 2016-08-03 19:46
why we can't call stored procedure from sql select? and why stored function called from sql select statement has only in variables? and can sys_refcursor exhibits dynamic sql?
Categories: DBA Blogs

ORA-06548: no more rows needed

Wed, 2016-08-03 19:46
Hi Team, Below is the sql used SELECT * FROM TABLE(pkg.fn_api('PR_LOAD_PL_PROT_TO_SITE','ROLE_ABBR') Below is the code snippet of pkg TYPE t_parm_val_txt_rec IS RECORD ( o_parm_value_txt VARCHAR2 (4000 BYTE) ); TYPE t_...
Categories: DBA Blogs

how to find bottleneck sql's in a package in oracle without using dbms_profiler or dbms_hprofiler? is there any alternatives?

Wed, 2016-08-03 19:46
how to find bottleneck sql's in a package in oracle without using dbms_profiler or dbms_hprofiler? is there any alternatives?
Categories: DBA Blogs

Waiting for parallel query to finish

Wed, 2016-08-03 19:46
Is there any way I could know the status of a parallel query i.e. the query has finished the job. I am trying to use few parallel updates and inserts in a VB6 application and finally a truncate. The truncate query causes "ORA-00054: resource busy a...
Categories: DBA Blogs

Pages