Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 hour 47 min ago

Confusion surrounding "buffer busy waits" and "read by other session" wait events

Fri, 2018-05-18 05:06
Tom - I'm investigating a relatively minor but attention-getting issue that occurs from time to time around my workplace. I observe a few dozen identical queries (select only - no DML) generated by an Oracle Financials concurrent program being ex...
Categories: DBA Blogs

Get the length of CLOB columns in bytes

Fri, 2018-05-18 05:06
Hello Tom, I have a table with a CLOB column: <code> create table plch_clob (i int primary key, x clob); begin for indx in 1 .. 1000 loop insert into plch_clob( i, x) values (indx, 'CLOB Row: ' || indx); end loop; ...
Categories: DBA Blogs

Data Masking

Fri, 2018-05-18 05:06
Does Oracle provide a package or function for data masking ? For example, in a development environment, for data protection purposes, the information of the table customer needs to be masked. create table customer (last_name varchar2(25), first_n...
Categories: DBA Blogs

Read only partitions in 12.2

Fri, 2018-05-18 05:06
Team, Started reading about Read Only partitions in 12.2 <u>http://docs.oracle.com/database/122/VLDBG/partition-create-tables-indexes.htm#VLDBG-GUID-9D7149B6-A2FF-47CA-8F00-47CBFD33F82B</u> <quote> A higher level setting of the read-only cl...
Categories: DBA Blogs

Generating Duplicate Rows

Tue, 2018-05-15 03:46
Hi I am new to oracle plsql and want advise on a Biz scenario: Biz want to run a shipping label report and each shipping has one record. They will choose particular shipping record and based on provided parameter they want to see number of labe...
Categories: DBA Blogs

DB Change from 12.1 to 12.2 RAC getting ORA-02297

Mon, 2018-05-14 09:26
We recently changed from a 12.1 Single Instance to a 12.2 Version running in a RAC environment with two nodes. Wa are now facing the problem that a PL/SQL procedure nearly takes factor 3 longer to run than on the single instance. Our Adminis...
Categories: DBA Blogs

Sorting and comparation alphanumeric

Mon, 2018-05-14 09:26
Hello Tom, We have configure the client this way: <code>SQL> select parameter, value from nls_session_parameters where parameter in ('NLS_COMP','NLS_SORT','NLS_LANGUAGE'); PARAMETER VALUE ------------------------------ -------------------...
Categories: DBA Blogs

SQL Query to remove duplicate values across columns

Mon, 2018-05-14 09:26
I have three field in the table say from_city,to_city and distance. <code> Table name: City_distance From_city to_city distance ---------------------------------------- A B 100 B A 100 C B 200 C A 300 E F 700 F E 700 </code> Here I ...
Categories: DBA Blogs

Logical Storage Structures\chained and migrated rows

Mon, 2018-05-14 09:26
if we have multiple datafiles of 32GB and there are fully utilized. Can chained and migrated rows occurs due to the maximum datafile utilization?
Categories: DBA Blogs

performance tunning for Sql query

Mon, 2018-05-14 09:26
hi Team, I am struggling in one of query tunning , below are details assoicated with , please analyse and recommend on it . <code> SELECT * FROM (SELECT /*+ INDEX(A INDX14_TABLE1) INDEX(B IDX51_TABLE2) */ * F...
Categories: DBA Blogs

Tables Access

Mon, 2018-05-14 09:26
In our Production database is has been decided to drop a tablespace because already a datafile was lost due to accidentally file was dropped at OS level with command rm -rf, somehow luckily there was not huge loss but some of index which were cre...
Categories: DBA Blogs

Connecting to Database.

Mon, 2018-05-14 09:26
Hi, I have two versions of Oracle Databses(12C,11G) installed on my personal computer running on Windows 8. When i execute below command its connecting to 12C instance. sqlplus system/welcome; i would like to know how can i connect to 11G instan...
Categories: DBA Blogs

XMLSERIALIZE dynamic order by statement

Sat, 2018-05-12 02:26
Hi, I've the following problem. I call the function with the order-by parameter, but it will not be used. When I use the order-by hardcoded, the values ??are sorted. Do you have any idee what is wrong in the function or the calling? Than...
Categories: DBA Blogs

All Parent - Child tables in the database

Fri, 2018-05-11 08:06
Hi Tom, Can you please explain the way to get a list of all parent child relation in the database. The list should have the Grand parent as the first item and the last item will be the grand child. For Example, Parent ...
Categories: DBA Blogs

performance tuning - sql slows down after gather stats

Fri, 2018-05-11 08:06
Hi , I have faced a situation where sql id plan hash value is changed due stats gather on one of table currently i dont understand why this stats gathering cause chnage in plan and due to which execution time is poor now can you guide...
Categories: DBA Blogs

insert into local table with select from multiple database links in a loop

Fri, 2018-05-11 08:06
Hi Tom, i would like to apply the Orignial SQL Statement from Oracle MOS DOC ID 1317265.1 and 1309070.1 for license and healthcheck for all of my database instances. My Goal is to create a centralized repository with informations of my databases. Un...
Categories: DBA Blogs

Impdp not failing even if target table have missing column

Fri, 2018-05-11 08:06
My question why import is not failing even the source and target have different table structure <b>Source DB</b> has below table (with additional column COL3 and populated SQL> desc tab1 Name Null? Type ---------------------------...
Categories: DBA Blogs

Unique key across tables

Thu, 2018-05-10 13:46
Dear tom, How can i enforce unique key across multiple tables. Table1 and Table2 both have ID primary key column. Is it possible to restrict, while inserting and updating into these tables, the union of ID values from two tables are unique. r...
Categories: DBA Blogs

SEQUENCE

Thu, 2018-05-10 13:46
hi tom, during one interview i got one question in sequence i.e if there is one sequnce whose max value is 40,but after got nextval 20.without execute the select query 20,000 times and without alter the sequence i want to get 20,000 in the nextva...
Categories: DBA Blogs

enq: TX - row lock contention wait event for an UPDATE statement

Fri, 2018-05-04 11:06
Hi, We are facing this wait event "enq: TX - row lock contention" when an update statement is being executed.THis update has filter columns which are part of unique index.What I observed during analysis is table has PCTFREE=10 and PCTUSED=NULL in ...
Categories: DBA Blogs

Pages