Tom Kyte

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

Formatting negative values to sort correctly but keep the formatting

Fri, 2017-11-10 07:35
I have an old and a new query. I need help with the new one. The old query works fine. For the new one, I can't seem to find a way to format two columns (latitude and longitude, I need 6 digits after the decimal) in such a way as they sort correctly....
Categories: DBA Blogs

Oracle Block Size

Thu, 2017-11-09 10:06
Hi Tom, I would be very grateful if you could share your thoughts on Oracle block size. "rule of thumb" is Oracle Database block sizes (2 KB or 4 KB) for online transaction processing (OLTP) or mixed workload environments and larger block size...
Categories: DBA Blogs

Where clause mix of AND and OR with()

Thu, 2017-11-09 10:06
Hello, I need to mix and or in where cluse: like: and con1 and (con2 or con3 or con4)... t_where := t_where || ' and a.field1 = ''' || l_1 || '''' || ' ( ' || 'a.field2 = ''' || l_2 || '''' || ' or ' || ......
Categories: DBA Blogs

sql loader and date

Thu, 2017-11-09 10:06
hi!!! i am using sqlloader, i have a table T in my database T (empno, start_date date, resign_date date) my data file has data like this (date format IN THE DATAFILE is 'YYYYMMDD') 1, 19990101,20001101 2, 19981215,20010315 3, 19950520...
Categories: DBA Blogs

Tabuler row no

Thu, 2017-11-09 10:06
In oracle forms 12c, I created data block with tabula, I changed the otem row number displayed to 10 row, I need to get current selected row no in this tabuler on form at run time not current_record, O mean this number between 1 to 10, I tried get ...
Categories: DBA Blogs

SGA_target is greater than the total physical memory on the server (Windows)

Thu, 2017-11-09 10:06
HI Tom, We have a databases running on 11.2.0.3 with memory parameters set as below: This is a windows server 2008 R2. SQL> show parameter sga NAME TYPE VALUE ------------------------------------ --...
Categories: DBA Blogs

What´s your oppinion about the DBA job in the future

Thu, 2017-11-09 10:06
Hi Tom, First, i would like to thank you for your site in web. I learn a lot of things with the doubts of my coleages registered in this site. I learn too how to explain to the developers using examples (like you). Well, i would like to know wh...
Categories: DBA Blogs

IS JSON is not working for Nested Jsons

Wed, 2017-11-08 15:46
Hi Team, In one of our table we have column which holds JSON format text. data will be inserted to this column from a file we receive from Vendor. While Inserting the data to this column we dont't validate whether its in JSON format or not but bef...
Categories: DBA Blogs

Error handling behavior change according to PLSQL_OPTIMIZE_LEVEL

Tue, 2017-11-07 21:26
We had faced a case in our application where error message disappear according to PLSQL_OPTIMIZE_LEVEL. I had isolated the problem in a simple script. Run this script, you will see that at first execution of the procedure "test_error_proc#" error i...
Categories: DBA Blogs

SQL Query based on performance

Tue, 2017-11-07 21:26
Hi Tom, There is a table say t_tab with columns a,b and c. Data in the table is huge(more than million). You run the following three statements: 1. select * from t_tab 2. select a,b,c from t_tab 3. select b,c,a from t_tab Will there be a diffe...
Categories: DBA Blogs

Fuzzy Matching in SQL

Tue, 2017-11-07 21:26
Is there any SQL construct that does fuzzy matching ? As an example , if I have the values as Monroe , Monroe Twp , Monroe Township , "Monroe Twp,NJ" , I would like to consider them as one value .
Categories: DBA Blogs

Subquery with Select statement works in 12C but not on 11g.

Tue, 2017-11-07 21:26
Hi I am trying to run a select query which has the sub queries it is running well and good in 12C environment but its throughout error in 11g. Could you please help me on this. Thanks, Kumar
Categories: DBA Blogs

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

Pages