Tom Kyte

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

usage of CLOB

Thu, 2017-09-07 19:46
Hi Team, We are doing a migration from DB2 (Mainframe-z/OS) to Oracle 12c. There are some columns in DB2 with CLOB DATATYPE. When is migrated(Only DDL ) by Siebel application those were created as LONG. We are suggesting the team to go with CLOB a...
Categories: DBA Blogs

ORA-14763 when referencing a partition with INSERT

Thu, 2017-09-07 19:46
I have 2 tables. One of those is interval partitioned. <code>CREATE TABLE testtable ("MYVALUE" VARCHAR2(200) NOT NULL ENABLE, "COLLECTION_TIME" DATE NOT NULL ENABLE ) PARTITION BY RANGE (COLLECTION_TIME) INTERVAL(NUMTODSINTERVAL(1, '...
Categories: DBA Blogs

Quoted csv file into a table using SQL Loader.

Thu, 2017-09-07 19:46
Hi, I am trying to use SQL*Loader to put data into my table. The data is comma separated and each field has double quotes around it to handle the embedded commas in the data. But my problem is that the data fields can also have double quotes embed...
Categories: DBA Blogs

Selected VARCHAR2 values are altered after comparison with CHAR values?

Thu, 2017-09-07 19:46
Hiya, TOMs I stumbled on a thing I think is a bit weird. I made this test script over at LiveSQL: <code> create table ch ( ch char(10) ); create table vc ( vc varchar2(10) ); insert into ch values ('MICKEY'); insert into ch values ('M...
Categories: DBA Blogs

catalog database gets shutdown frequently

Thu, 2017-09-07 19:46
Greetings, A catalog database is present. It gets automatically shutdown freqently and we need to start it up manually. How should i know the reason behind the shutdown. Are there any logfiles or trace files i should refer to? Your's Sincerely, S...
Categories: DBA Blogs

Generating Date range depend on Week Days

Thu, 2017-09-07 19:46
Daer Tom I would like generate date range and insert into a table depend upon week days. Example: I have date range 01-09-2017 to 30-09-2017 and the week days I want choose is Mon,Wed Following thing I want to generate: 04-09-2017 06-09-...
Categories: DBA Blogs

Stored procedure management

Thu, 2017-09-07 19:46
Hi Tom, I have created 3 stored procedures, I need to run the stored procedure in such a way when the activities of 1st stored procedure completed then run 2nd, only when the activities of 2nd stored procedure completed then only run the 3rd. Please...
Categories: DBA Blogs

Concatenate the rows WITH NO column to group

Thu, 2017-09-07 19:46
Hello Experts, I am preparing some stored procedure and there is a need to group the result into a string with any delimiter. The challenge here is I dont have any column ( with same data ) to group. Below is my scenario. <code>CONTACT_NO ...
Categories: DBA Blogs

ORACLE 11 - 1-way hashing

Thu, 2017-09-07 19:46
I am using Oracle 11 and I need to use a Hashing-Function to store passwords. Oracle 11(DBMS_CRYPTO) supports only SHA-1. Since SHA-1 is pretty broken, I want to user other, more secure hashing functions. Unfortunately, I cannot use Oracle 12 in ...
Categories: DBA Blogs

Restricting Production access while granting Read-only Access to Replica DB (Data guard)

Thu, 2017-09-07 01:26
Hello Experts, We replicate our critical Production Databases using Data guard, which implies that all the security user roles are as well replicated in both the environments. As such what techniques one can follow to GRANT READ ONLY Access to Rep...
Categories: DBA Blogs

What overhead (if any) do pipelined functions have?

Thu, 2017-09-07 01:26
I have some situations where I have a report built in Apex with a huge query in the application. Ideally we like to keep as much code as possible on the database (for dependency checking purposes etc) however the reason these have been left in Apex i...
Categories: DBA Blogs

How to retrigger an external remote Job

Thu, 2017-09-07 01:26
TITLE: How to retrigger an external remote Job Oracle external Job fires only once (an External, Remote, and Recurrent job): INTRODUCTION: My External, Remote, and Recurrent Oracle Job fires only once, and then it remains RUNNING. Then, ?I...
Categories: DBA Blogs

Checkpoint processing

Thu, 2017-09-07 01:26
My Question: 1. When there is check point, data is written on Disk even if the transaction is not committed yet, when roll back issued, how does it happen..? I Mean end up with physical delete insert and update or logical operation like if ? Ins...
Categories: DBA Blogs

Error while sending the attachment using UTL_SMTP mail

Thu, 2017-09-07 01:26
Dear Tom, We are getting error Sqlcode : -29279 - Sqlerrm :ORA-29279: SMTP permanent error: 554 Transaction failed: Missing start boundary while sending the attachment using UTL_SMTP. please help us on this issue. Thanks in advance. ...
Categories: DBA Blogs

Exploring connect by

Wed, 2017-09-06 07:06
Hi Tom, I am new here, so if i ask wrong ques or not following the guidelines, apologizes. Suppose i have a table like this: <code>COL1 ---- A B C D E F G</code> I have to transform this columns in a single cell like this A,B,C,D,E,...
Categories: DBA Blogs

Performance issue.

Wed, 2017-09-06 07:06
we are in the process of performance tuning , the below program is taking time, Please guide us to tune further or can we write any other way to improve the performance, <code>CREATE OR REPLACE TYPE "RECO_FE" FORCE AS OBJECT ( o_o_id ...
Categories: DBA Blogs

Upper bound length constraint for list partitioned tables (ORA-14116)

Wed, 2017-09-06 07:06
Dear Tom, We do have some table where partitions will be created frequently: <code>CREATE TABLE partitioned_table ( someUsefulPartitionIndicator NUMBER (*,0), someData varchar2(4000) ) partition by list (someUsefulPartitionIndicato...
Categories: DBA Blogs

How to optimize query for multiple joined tables

Wed, 2017-09-06 07:06
Hi TOM, I have this simplified version of a query. I would just like to check if there are other ways to further optimize the query especially on the subqueries. I'm limited to SQL only. Reason for me thinking that this can be further optimized...
Categories: DBA Blogs

mutating table

Wed, 2017-09-06 07:06
hello, i've got a table MRC and a trigger on it (AFTER INSERT) thus, after an insert in the table MRC, this trigger has to determine if a new line must be inserted into an other table PLAN : for that, it does compare the :new values with the MO...
Categories: DBA Blogs

Tuning Question

Wed, 2017-09-06 07:06
Hi I have the following query: <code> SELECT cust_id, cosmos_cust_id, prot_id, customer_name, protocol_name, dist_order_no, cust_ivr_ref_no, ship...
Categories: DBA Blogs

Pages