Tom Kyte

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

Get the most recent 3 transaction records for a customer

Tue, 2019-04-30 16:06
I have a customer session table that has session id and session date along with other columns. I want to see the 3 most recent sessions of each customer. Provided the sample SQL for creating data below: <code>CREATE TABLE Customer ( Custo...
Categories: DBA Blogs

Generate Random number and compare it for uniqueness in database

Tue, 2019-04-30 16:06
Hi Tom, I have a situation where I have to create a function which will return a unique number (Suffix R and 7 numbers) which will than be compared with data present in database with a particular column which is primary key. if the number generate...
Categories: DBA Blogs

Issue Global temporary table

Tue, 2019-04-30 16:06
Hi Tom I have Stored procedure return some data, <code>create PROCEDURE "SP1" (CV_1 IN OUT SYS_REFCURSOR) /*CREATE GLOBAL TEMPORARY table TT_TABL2 ( ORDER_ID NUMBER, REQ_ID NUMBER, TXN_ID NUMBER, ...
Categories: DBA Blogs

Oracle Insert

Tue, 2019-04-30 16:06
Hi Tom, How exactly big insert or update getting processed in oracle? Suppose I have to insert >=10GB more records in this table will this much of insert floods the db buffer cache or how it affect the SGA. Is it the same case with update...
Categories: DBA Blogs

problem to drop tables

Tue, 2019-04-30 16:06
hi i have a problem when i am trying to drop tables i get this error Error dropping CITIZEN: ORA-04098: trigger 'SYS.DELETE_ENTRIES' is invalid and failed re-validation thanks israel
Categories: DBA Blogs

.NET Core Connection String to RAC database

Mon, 2019-04-29 02:46
Hello, Ask Tom Team. We have a .Net Core app connecting to a Oracle 18c RAC database. We are not using tnsnames.ora file (there is no oracle client installed on the client-server side). We are using managed .Net Core oracle client and the followin...
Categories: DBA Blogs

Stored Procedure behaves randomly - sometimes takes 15 minutes where it should take 15 milli-seconds.

Mon, 2019-04-29 02:46
We developed a procedure to be used in database-A and in database-A. In this procedure we send an input parameter on the basis of which a select statement searches the data from a table located in remote database-B. The selected columns are set in Ou...
Categories: DBA Blogs

Cluster Waits Rac database

Mon, 2019-04-29 02:46
Hello, Ask Tom Team. I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and t...
Categories: DBA Blogs

High commit wait on RAC database

Mon, 2019-04-29 02:46
Hello, Ask Tom Team. I have a two-nodes RAC database running 18.4.0 and I'm running a load test (lots of inserts). Basically, two tables are being hit. These tables are using identity column and have a relationship (fk). I'm checking OEM 13c and t...
Categories: DBA Blogs

Redo log file size and Database Performance

Mon, 2019-04-29 02:46
Hi there, We have a database setup as follows 1) SQL server -- Where transactions are happening here 2) Oracle(DSS)-- we are transferring the incremental data from SQL server(NOT ALL THE TABLES ONLY 22 TABLES) with the help of a 24/7 running ...
Categories: DBA Blogs

Redo logs Sizing

Mon, 2019-04-29 02:46
Hello, Ask Tom Team. What is the best practice: small redo log groups or have a few but with a bigger size? Thanks in advanced. Regards,
Categories: DBA Blogs

RMAN - Full vs. Incremental - performance problem

Thu, 2019-04-25 22:46
Hi. I am testing RMAN. I have run an incremental 0 and an incremental 1 cumulative test without having any database activity in-between these test. I am using OmniBack media manager with RMAN. I have three databases. Here are the timing results ...
Categories: DBA Blogs

Subtract time from a constant time

Wed, 2019-04-24 10:06
Hello there, I want to create a trigger that will insert a Time difference value into a table Example: I have attendance table Sign_in date; Sign_out date; Late_in number; Early_out number; Now I want to create a trigger that will insert la...
Categories: DBA Blogs

Clob column in RDBMS(oracle) table with key value pairs

Wed, 2019-04-24 10:06
In our product in recent changes, oracle tables are added with clob column having key value pairs in xml/json format with new columns. <b>example of employee:(Please ignore usage of parenthesis) </b> 100,Adam,{{"key": "dept", "value": "Marketi...
Categories: DBA Blogs

current value of sequence

Wed, 2019-04-24 10:06
Hi. Simple question :-) Is it possible to check current value of sequence? I though it is stored in SEQ$ but that is not true (at least in 11g). So is it now possible at all? Regards
Categories: DBA Blogs

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6843, maximum: 2000)

Wed, 2019-04-17 13:26
HI iam using below query to read xml from blob and find the string but facing error buffer to small ora-22835 blob to raw conversion (actual 15569,mximum 2000) please help me out with below example <code> SELECT XMLTYPE (UTL_RAW.cast_to_varchar...
Categories: DBA Blogs

Merge two rows into one row

Wed, 2019-04-17 13:26
Hi Tom, I seek your help on how to compare two rows in the table and if they are same merge the rows. <code>create table test(id number, start_date date, end_date date, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10)); insert into t...
Categories: DBA Blogs

Help with v$statname and v$sysstat

Tue, 2019-04-16 19:06
Tom, Can you please provide info on how can I find the full table scan and index table scan activities in the database using v$statname and v$sysstat? Do I need to set TIMED_STATISTICS=TRUE before running queries against v$sysstat?...
Categories: DBA Blogs

Create Object with Column type attributes

Mon, 2019-04-15 06:26
Hi Tom, Please help me on one of our prod issue. We have an object and all the attributes in the object(EMP_OBJ) are columns of a table(Lets say EMP) in diff schema. So when we defined the object we gave the datatype and size of the object attr...
Categories: DBA Blogs

STATS_BINOMIAL_TEST does not work

Mon, 2019-04-15 06:26
Dear Tom, I tried to run this query, following example here https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions150.htm: <code></code> SELECT AVG(DECODE(cust_gender, 'M', 1, 0)) real_proportion, STATS_BINOMIAL_TEST ...
Categories: DBA Blogs

Pages