Tom Kyte

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

Index creation on empty column on Large Table

Tue, 2017-11-07 21:26
Quite a time we face a situation where we have large table with Hundreds of Millions of records(sometimes even Billions of records), and we might need to add column to that table and then add index on that new column. We have absolute control over...
Categories: DBA Blogs

ORA-29284: file read error for a few lines

Tue, 2017-11-07 21:26
Hi Experts, Thanks for taking the time out to ready my Question. I am receiving a file from a third party as a flat file, with different lines of different lengths. The first two characters of each line represents what data that line will hav...
Categories: DBA Blogs

using connect by without relationship using parent_id

Tue, 2017-11-07 21:26
Hi, I have information about father's , mother's and children but there is no relationship between the rows using Paernt_id as follows, <code>drop table tbl_family; create table tbl_family ( father nvarchar2(50) , mother nvarchar2(50) , ...
Categories: DBA Blogs

database migration from AIX to Linux

Tue, 2017-11-07 21:26
Hello Tom, We are planning to migrate database from AIX to Linux. Because of different endian we can't built the standby,here my request was Production databases have 30-40TB of data. Some tables have 1-5 TB of only data what was the best way...
Categories: DBA Blogs

Replication of multiple sourde database to a single read only database

Tue, 2017-11-07 21:26
Dears Hope you are fine,,, I have a distributed database about 20 branch,,, each database have the same schema structure we need a centralized report that reads from only four tables. currently we take a dump file from each branch and impo...
Categories: DBA Blogs

Performance comparison with delete and insert into backup table or using Oracle 12c archival feature

Tue, 2017-11-07 03:06
<code>Hi, Which of the below mentioned approaches should be better performance wise? The Update vs Insert/Delete both take almost equal time ... Is this correct or am I doing something wrong? I know about the limitation of Ora archive as well ...
Categories: DBA Blogs

cost of the query is too much. it is taking around 50 hours to run the query.

Tue, 2017-11-07 03:06
<code>when i am running below query, it is taking around 50 hours in dev environment. same query is completed in other environment with in a minute. data is same in both the environments. Any thoughts? delete FROM uet_extract_clinical uec W...
Categories: DBA Blogs

SQL to identify duplicates of data groups.

Tue, 2017-11-07 03:06
Hi, I have a peculiar requirement to identify duplicates of data groups. So here is a sample data set. <code> PI BU PR AC ---------- ---------- -------------------- ---------- 1001 100 PR1 ...
Categories: DBA Blogs

Need to convert Rows to Columns

Mon, 2017-11-06 08:46
How to convert the Rows to Columns I have Audit data as below <code>create table c_t (t_id varchar2(50), Change_Field varchar2(50),new_value varchar2(50), old_value varchar2(50), a_dt date); insert into c_t values('T1','Status','WIP','Pendin...
Categories: DBA Blogs

ORA-12899: value too large when inserting over DB link with different character sets

Mon, 2017-11-06 08:46
Hi Tom, We are using two databases with two different character sets those are 11.2.0.1.0 - 64bit = AR8MSWIN1256 12.1.0.2.0 - 64bit = AL32UTF8 Trying to insert a data from the 11.2 version to the 12.1 through a DBLINK but it gives 'ORA-12899: ...
Categories: DBA Blogs

ALTER SESSION ENABLE PARALLEL DML

Mon, 2017-11-06 08:46
Hi, I came across below code spinet: DECLARE .. .. BEGIN .. .. EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML' ; DELETE /*+ PARALLEL(TABLE_TEST, 4) */ FROM TABLE_TEST WHERE ID IN (SELECT ID FROM TABLE_2); END; Since EXECUTE ...
Categories: DBA Blogs

How to Create Datewise Monthly Sheet

Mon, 2017-11-06 08:46
Our environment is Database 10g 10.2.0.1.0, Forms 6i Operating System: Windows Server 2008R2 We have tables to record schedule for our distributors like following <code>create table distributor ( dcode int, distributor_name varchar2 (200)...
Categories: DBA Blogs

Is RAC one node option available in Standard Edition 2?

Mon, 2017-11-06 08:46
Hi Tom, According to Oracle Database Licensing Information User Manual, https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC116, RAC one node option is not available in Standard Edition 2. However you can choose this option in the install...
Categories: DBA Blogs

How to Deserialize a HTTP POST RESPONSE

Mon, 2017-11-06 08:46
I am getting a response for a request(l_value) as below: <?xml version="1.0" encoding="utf-8"?> <boolean xmlns="http://tempuri.org/">false</boolean> How do I get the boolean value false and assign it to a variable lv_return <code> httpres...
Categories: DBA Blogs

Warning Event: <hostname.domain name>: <db name> DB - Failed logon attempts

Mon, 2017-11-06 08:46
OS_USER HOST_NAME TERMINAL USERID Login Date CLIENT_PROGRAM_NAME Error massage oracle hostname.domain name SYSTEM 11-02-2017 14:54:05 sqlplus@hostname.domain name (TNS V1-V3) ORA-1017:invalid user...
Categories: DBA Blogs

Oracle memory and processor requirement

Mon, 2017-11-06 08:46
Hello Tom, I'm new in database and just want to understand the memory and proc requirement for a database server. Here if I say that approx. 500-600 concurrent user are going to connect 12C database through application and size of database is ap...
Categories: DBA Blogs

Synchronize data from Oracle Se to Oracle EE

Mon, 2017-11-06 08:46
Dear All, Could anyone share with me either data from Oracle SE can be synchronize to Oracle EE? Regards
Categories: DBA Blogs

Determine whether the given is numeric , alphanumeric and hexadecimal.

Sat, 2017-11-04 21:06
Dear Team, May I know how do we determine the below for a string. 1. If its numeric. 2 . Alphanumeric. 3. Hexadecimal (E.g Mac address). Regards Kalyana Chakravarthy
Categories: DBA Blogs

REG_EXP is a problem

Sat, 2017-11-04 02:46
WHERE email IN ( select regexp_substr('one@gmail.com,two@gamil.com,three@gmail.com,four@gmail.com','[^,]+', 1, level) from dual connect by regexp_substr('one@gmail.com,two@gamil.com,three@gmail.com,four@gmail.com', '[^,]+', 1, level) is not nul...
Categories: DBA Blogs

I need to replace this query with substr and instr

Sat, 2017-11-04 02:46
SELECT REGEXP_SUBSTR(val_PC, '[^, ]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(val_PC, '[^, ]+', 1, LEVEL) IS NOT NULL
Categories: DBA Blogs

Pages