Tom Kyte

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

how to send email where email body more than 32 K

Mon, 2017-08-28 03:06
Hi, am using utl_smtp.data package and i have some emails where body size more than 32 K, is there any way to send these email in one email? Best Regards
Categories: DBA Blogs

SQL Loader and NLS_LANG

Mon, 2017-08-28 03:06
Hi, I need to load into oracle a text file with some decimal number in it. My database recognize the decimal separator as "." and in the flat file numbers are in the format 19.89 The problem is that the pc that I use for the import uses the "," as...
Categories: DBA Blogs

Whether or not to partition tables

Sun, 2017-08-27 08:46
My application has tables that are 30-40 GB (130M to 480M records) and indexes (indices) that are 10-25 GB. There is potential for significant growth. Oracle documentation recommends considering partitions when tables grow to 2GB, so I am trying ...
Categories: DBA Blogs

function data type

Sat, 2017-08-26 14:26
Hi Tom, I need to create such a function just like, create or replace function (p_name in varchar2(10), p_col1 in varchar2(10), p_col2 in varchar2(10)) is begin select p_col1, p_col2 from p_name; end; / now i need to modify the functio...
Categories: DBA Blogs

Bulk collect into an existing collection that already has data

Sat, 2017-08-26 14:26
Can I bulk collect into an existing collection that already has data and preserve the original data? In other words append the new data to the existing data? The output I'm looking for would be (order not important): QWERTY ASDF Haikus are e...
Categories: DBA Blogs

Top N salaries but department with hightest overall salary should be top/first

Fri, 2017-08-25 20:06
Top N salaries but department with hightest overall salary should be top , then dept with 2nd highest in the dept (with rank 1) should come next . How do I achieve this SQL query ? Here is sample data : <code>create table emp_sal_test ( dept# nu...
Categories: DBA Blogs

partition_extension_clause in where clause

Fri, 2017-08-25 20:06
In the Oracle 12c documentation SQL Language Reference, for the SQL SELECT statement syntax, under the partition_extension_clause section, there is this statement: <i>For PARTITION or SUBPARTITION, specify the name or key value of the partition or...
Categories: DBA Blogs

Using Dynamic Table Name in Select Statement To Prepare Extract File

Fri, 2017-08-25 20:06
Hi. We use to prepare extract file from oracle using extract script. There are few tables whose name gets change every month. Need to handle these table names dynamically. <code>SELECT TICKET_ID ,SOURCE_ID ,SERV_ID ,...
Categories: DBA Blogs

DBMS_FILE_TRANSFER unix permissions settings

Fri, 2017-08-25 01:46
Hi Tom, We are having different unix development environment .when I'm using DBMS_FILETRANSFER.Fcopy in one environment it is creating the file with Rw permission to oracle used only. In another environment it is creating the file rw to user orac...
Categories: DBA Blogs

Passing encoded url string containing %3A (colon) truncates everting after the last %3A

Fri, 2017-08-25 01:46
Hi I am a relative newbie to APEX from Sweden (not so god at english) I am passing an url string to an apex application where i have encode all special caracters, if there i a %3A (colon) in the url APEX seemes to truncate the value i am passin...
Categories: DBA Blogs

RAC Vs Master/Slave configuration

Fri, 2017-08-25 01:46
Hi Tom, I am lil bit confused about Master slave..... could you please explain what are the different between Oracle RAC and Master slave.... why we use master slave instead of RAC or vice-versa.
Categories: DBA Blogs

Oracle 12c DB creation

Fri, 2017-08-25 01:46
Hi Tom, ?I have 2 source systems ( A & B ) each running with 10 numbers of Oracle11gR1 databases. Now I need to migrate this 20 databases from 2 sources into 1 target system(C) which is running in 3 Node Oracle 12c RAC cluster. I proposed 1 conta...
Categories: DBA Blogs

Exporting a table that contains a CLOB column

Fri, 2017-08-25 01:46
Hi Ask-Tom-team, What is the recommended way for exporting a table that contains a CLOB column to a SQL script? The standard feature of the SQL Developer only exports all columns that are not CLOBs. I want to export the table to a SQL script for i...
Categories: DBA Blogs

Assinging Batch Numbers to Query Rows

Fri, 2017-08-25 01:46
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production create table orders ( order_number number, order_line number) order_number line_number 50000 1 50000 2 50000 3 50010 1 50010 2 50300 3 50301 1 5030...
Categories: DBA Blogs

dbms_metadata - getting inconsistent SXML (ordering)

Thu, 2017-08-24 07:26
Hi Tom I am trying to use dbms_metadata to retrieve a SXML document per object in my production database, and compare it to the same document from my test database, in order to compare the two environments. Initially, I calculate a HASH value f...
Categories: DBA Blogs

Cloning the ORACLE_HOME

Thu, 2017-08-24 07:26
Please advise if the below steps are correct while copying the ORACLE_HOME from one location to another. Also advise if anything needs to be added to the below before we can start using the ORACLE_HOME. Copy ORACLE_HOME from old location to new...
Categories: DBA Blogs

Unable to use DOP ( Degree of parallelism) in simple insert statement

Thu, 2017-08-24 07:26
I am totally new to oracle and stuck in one application specific issue which was using oracle db.So below is the query regarding the issue which i faced : I was trying a very simple query ( insert into DIM select * from FLAGGING ) with 18 mill...
Categories: DBA Blogs

function based indexes

Thu, 2017-08-24 07:26
I'd like to use function based indexes on index organized tables. Can you explain why a function based index isn't allowed on a primary key? Is there a workaround? Thanks
Categories: DBA Blogs

Fastest Delete in 2TB table

Wed, 2017-08-23 13:06
Hi, I have a 2TB table. It has also large index. I want to delete records from this large table with date key. Date key is a column in side table. Below is my query. But it takes huge time. delete /*+ PARALLEL(6)*/ FROM F_CUST_TRANSACTION_...
Categories: DBA Blogs

Get the details of *historical* queries/sessions/sql_id generating huge redo for last one week.

Wed, 2017-08-23 13:06
Dear Experts, To my recent past query from the below link was very helpful. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536412000346656749 Can you please help me on how to get the details of *historical* queries/sessi...
Categories: DBA Blogs

Pages