Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 2 months 1 week ago

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

issue with exponent value with number column

Mon, 2019-04-15 06:26
Hi , i am summing up the number column based other columns. But while doing sum small value converted into exponent. for exp. to_comm Number(10); 0.0000474 converted into 4.74E-5. I can change the setting of client to see small value ...
Categories: DBA Blogs

Insert trigger that do an update if record exists

Mon, 2019-04-15 06:26
I have a table: <code>create table test_tbl (id number, text varchar2(50));</code> with this data in it: <code>insert into test_tbl values (1,'Text 1'); insert into test_tbl values (2,'Text 2');</code> Now I want to insert a record, but ...
Categories: DBA Blogs

ORA-14692: STORE AS clause is not allowed for extended character type column

Mon, 2019-04-15 06:26
I just want to exp and imp,but when I imp the dmp file to another database,the failer message occured below: ORA-14692: STORE AS clause is not allowed for extended character type column. what should I do?
Categories: DBA Blogs

setting isolation level after gather stats worked; but did not work before it. Why ?

Mon, 2019-04-15 06:26
Hi, Please see below :- <code> SQL> create table t (x int); Table created. SQL> insert into t values (1); 1 row created. SQL> alter session set isolation_level=serializable; ERROR: ORA-01453: SET TRANSACTION must be first stateme...
Categories: DBA Blogs

global index in partitioned table

Fri, 2019-04-12 05:06
I have a question about a global index on a history table that has been partitioned into weeks from 1 to 53 with subpartitions from 1 to 4 all this into a list type partitioning. The question is that local and global indexes have been created. What w...
Categories: DBA Blogs

Update an ordered list value with consecutive numbers

Fri, 2019-04-12 05:06
I need to reset an ordered list to be consecutive numbers (ints) while maintaining the original ordering. 2,4,6 needs to become 1,2,3, as does -6, 53, 5498. I tried using rownum: <code>update T1 set SIBLING_ORDER = rownum where PARENT_ID...
Categories: DBA Blogs

Configuration of redo log and standby redo log in single instance standby database for RAC primary database

Fri, 2019-04-12 05:06
Hello, I would like your support to configure correctly at the level of redo log and standby redo log in a single instance standby database, whose primary base is RAC, so that there is no inconvenience when performing the change of roles. At pres...
Categories: DBA Blogs

Getting lowest record from duplicates

Fri, 2019-04-12 05:06
Hi Tom, Following is the View definition. <code>SELECT C.VERSION, C.DOW, C.DELV_TYPE_CODE, C.CURR_DELVPT_SYS_ID, C.EMP_ID, C.ZIP5, C.ZIP4, C.ZIP2, ...
Categories: DBA Blogs

Regular expression to find rows with characters that are not letters, numbers or keyboard symbols

Thu, 2019-04-11 10:46
Hi, This is the table t2 I have, Sl.No. Junk 1. Cigarette use ? last used 4/2017 ? NS at best; 2. test]]]]]]] 3. [[[[test 4. [CDATA[]] Now I want to write query to get only the 1st row which have junk chara...
Categories: DBA Blogs

Pages