Tom Kyte

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

SQLPLUS query output to *.csv or *.txt format

Wed, 2017-10-11 10:26
Hello Tom is there anyway to do a query in sql*plus, then have the result output to a file in *.csv or .*.txt format without invoking UTL_FILE, using only sql*plus command. I'm not allowed to creat any procedure. PS: this link "http://asktom.ora...
Categories: DBA Blogs

Second Highest Sal

Mon, 2017-10-09 21:46
Hello Tom, How are you, After long time i visited the site and able to find the button. Ok Here is my question how can i get from sql second highest salary record from the table but with deparment wise Ram 10 1000 Jai 10 2000 San 20 3000...
Categories: DBA Blogs

Long to Varchar2 conversion....

Fri, 2017-10-06 02:06
Hi, Thanks for your earlier responses.... See, i have one more problem, like i want to retrive the first 4000 characters of the long datatype, with out using the pl sql code. i just wrote a function like // create or replace function g...
Categories: DBA Blogs

date function in oracle: find the date of a day

Tue, 2017-10-03 00:46
can i find the date of a day that is on which dates saturdays of a specific month fall in oracle sql ??
Categories: DBA Blogs

conversion of MSACCESS query to Oracle SQL

Tue, 2017-10-03 00:46
The Goal is to convert a successful MS_ACCESS Query to Oracle SQL. Access Query ------------------ UPDATE target_table T INNER JOIN source_table S ON T.linkcolumn = S.linkColumn SET T.field1 = S.field1, T.field2 = S.field2, T.field...
Categories: DBA Blogs

Oracle Express Commercial Use

Tue, 2017-10-03 00:46
Hi - Can you please confirm that the Oracle Express Database group is free for commercial use ? The licensing implies it is but it would be good to get confirmation from Oracle (Masters). Regards Erick
Categories: DBA Blogs

remove compress basic

Sat, 2017-09-30 17:46
What is the best way to remove compress basic from tables and partitions in production environment.
Categories: DBA Blogs

FILE_ID vs RELATIVE_FNO

Sat, 2017-09-30 17:46
Hi TOM, Trying to understand the difference between FILE_ID & RELATIVE_FNO in dba_data_files and dba_extents.
Categories: DBA Blogs

Is UTL_MAIL supported in 11g EE

Sat, 2017-09-30 17:46
Hi Team, Wanted to know wherher utl_mail is supported in 11g EE.I installed the version in my local windows machine.I am able to connect to it via SQL Developer. I can see utl_smtp and utl_tcp packages are installed. I tried to install the utlmail...
Categories: DBA Blogs

What performs better NVL or DECODE for evaluating NULL values

Fri, 2017-09-29 23:26
Afternoon, Could anyone tell me which of the following statements would perform better? <code> SELECT 1 FROM DUAL WHERE NVL (NULL, '-1') = NVL (NULL, '-1') </code> OR <code> SELECT 1 FROM DUAL WHERE DECODE(NULL, NULL, '1', '0') = '...
Categories: DBA Blogs

Which Index is Better Global Or Local in Partitioned Table?

Fri, 2017-09-29 23:26
We have partitioned table based on date say startdate (Interval partition , For each day) We will use query that will generate report based on days (like report for previous 5 days) Also we use queries that will generate report based on hours (li...
Categories: DBA Blogs

SQL to find the ip address

Fri, 2017-09-29 23:26
Hi Tom, I want to capture the IP address of any client who has shutdown the db. Support currently in my db 5 clients are connected, one client shutdown the db, then I want to capture the IP address of client who has been shutdown the db. How to solv...
Categories: DBA Blogs

AWR

Fri, 2017-09-29 23:26
Hi Tom/Team, I am aware of the definition of terms used in AWR Report. but i want to know that - how to calculate and on what basis we need to calculate values listed for points in below 2 section of AWR report 1. Top 5 timed foreground event ...
Categories: DBA Blogs

BInary operator like AND, XOR

Fri, 2017-09-29 23:26
Hi i have a simple question can we use the binary operator like AND or XOR in a SQL statement. For example "select 1 AND 1 from dual;" result = 1 or true or "select 1 XOR 1 from dual;" if not, please can you tell me how can i do to have th...
Categories: DBA Blogs

Lots of archivelog generation when shrinking and compacting segments

Fri, 2017-09-29 23:26
Hi Tom, 1. what is the reason of huge redo and archivelog generation when compacting and shriking huge segments in 10g? 2. How it can be avoided or minimized? Thanks JP
Categories: DBA Blogs

Calling Procedure Parallel

Fri, 2017-09-29 05:06
I have below procedure which in turn calls two other Procedures. It calls and works fine but the two procs runs serial. I want to run them parallel and get the results on the main procs cursor. How do I do that? I tried with dbms_job.submit but could...
Categories: DBA Blogs

Getting sub-string from two Clobs object and compare those substrings

Fri, 2017-09-29 05:06
Hi, I am new to CLOB objects but seems like I need to get my hands dirty on this. I have a CLOB column in my table and I need to get item SKU values from this column separated by commas. This is hoe my CLOB Column value looks like. ------- <...
Categories: DBA Blogs

External table concepts

Fri, 2017-09-29 05:06
Hi All, I am new to oracle external table concepts. Have a very basic query - if i have a csv with the below columns Col1, Col2, Col3 Col4 .... Coln and i want to insert only Col3 & Col4 into an oracle external table , what would be my ...
Categories: DBA Blogs

sql query to update a table based on data from other table

Fri, 2017-09-29 05:06
Hi, Looks like my other similar questions got closed, so asking a new question. I have a cust_bug_data table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows: <code>create table cust_bug_data(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER N...
Categories: DBA Blogs

Updating records with many-to-1 linked table relationship

Thu, 2017-09-28 10:46
I have an MS_ACCESS Query to convert to Oracle SQL. Access Query <code>UPDATE target_table T INNER JOIN source_table S ON T.linkcolumn = S.linkColumn SET T.field1 = S.field1, T.field2 = S.field2, T.field3 = S.field3;</code> Note: T...
Categories: DBA Blogs

Pages