Tom Kyte

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

Oracle View object - performance Issue with Outer Join including a WITH clause

Fri, 2018-09-14 07:46
Hi Tom ; Thank you , I've been using your site for 2 years now, resolved many issues based on your answers. Case Scenario : Customer having multiple addresses , only one is active ; some cases ALL the addresses of a customer could be inactive....
Categories: DBA Blogs

MATERIALISED VIEW ISSUE

Fri, 2018-09-14 07:46
Error starting at line : 12 in command - CREATE MATERIALIZED VIEW EMP_MV BUILD IMMEDIATE REFRESH FORCE ON COMMIT AS SELECT EMPID,EMPNAME FROM EMP Error report - ORA-12054: cannot set the ON COMMIT refresh attribute for the materializ...
Categories: DBA Blogs

Recommended partition size

Fri, 2018-09-14 07:46
We want to partition some tables using interval partitioning on the creation date. Partitioning is for manageability - we want to drop older partitions eventually - and partition pruning for improving performance. What is the Recommended partition si...
Categories: DBA Blogs

Identify the missing object in an ora-08103 error

Thu, 2018-09-13 13:26
We have a large cursor that runs nightly and on a random night we get the following error from the job that calls the cursor: 180911 180019 (APPS.GAINS_COMMON,1194) Begin export_data 180911 194348 (APPS.GAINS_COMMON,4735) BEGIN export_sla 1809...
Categories: DBA Blogs

Oracle locking a table while deleting set of rows

Thu, 2018-09-13 13:26
I have 2 scripts running at the same time deleting different set of rows from same table. Would this cause any locking issues or contention? Please advise. Thank you very much.
Categories: DBA Blogs

What SQL is currently running in the database

Thu, 2018-09-13 13:26
I am using an application that submits SQL queries to the Oracle database. These queries can have hundreds of bind variables in them. I want to be able to see the SQL that is running with the bind variables substituted. I have used this query to g...
Categories: DBA Blogs

Exchange Partition - Error with VIRTUAL & CLOB columns

Thu, 2018-09-13 13:26
Hi Folks, Need your expert advice on this. Could you please guide me through the below issue - Issue : Exchange partition doesn't seem to work when both Virtual column and CLOB column exists together in a table :( . However it works fine on it ...
Categories: DBA Blogs

Giving grant role to invoker from stored procedure

Wed, 2018-09-12 19:06
Hello there, As schema owner, I give grant execute on package to another user, who is considered as executor of the package. The package contains security part (procedure with invoker rights (authid current_user)), which checks whether the caller ...
Categories: DBA Blogs

How to strip off characters upto a specific character in a BLOB type column

Wed, 2018-09-12 19:06
I have a column of type BLOB which is being used to store images. A typical value that is currently getting stored in this table is: data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAYEBQYFBAYGBQYHBwYIChAKCgkJChQODwwQFxQYGBcUFhYaHSUfGhsjHBY...
Categories: DBA Blogs

Parsing the CLOB data seperated by delimiters

Wed, 2018-09-12 19:06
<code> CREATE TABLE T ( DT_FIELD DATE, SKEY NUMBER, FIELD_VALUES CLOB ) INSERT INTO T VALUES('06-SEP-18',10,'68|} 88026 |}ABC|}101010|}ADSD|}'); INSERT INTO T VALUES('07-SEP-18',11,'70|} 88027 |}DEF|}'); INSERT INTO T VALUES('07-SEP-18',12,'...
Categories: DBA Blogs

As per Oracle recommendation we are using Automatic Segment Space Management . We see contentions when we route traffic for DML on multiple RAC nodes

Wed, 2018-09-12 19:06
As per Oracle recommendation we are using Automatic Segment Space Management . We see contentions when we route traffic for DML on multiple RAC nodes. The only way I could think we can solve this Manual segment space management for LARGE table wit...
Categories: DBA Blogs

Remap_Data Mutiple Tables- Cannot be applied in expdp?

Wed, 2018-09-12 00:46
Hi Tom, Trying to apply remap_data to multiple tables it always fails. The column name is same across both tables where i am applying the function, it exports and imports without any errors from the source schema to the target schema, but it...
Categories: DBA Blogs

give range of ISO weeks allowed for a year.

Wed, 2018-09-12 00:46
Hi, I have to include startweek and endweek validation in an oracle form. Here, startweek and endweek includes year and a week number like '201801'. Whenever user enters a startweek or endweek value beyond the allowed range then I have to throw ...
Categories: DBA Blogs

Split multiple strings into rows.

Wed, 2018-09-12 00:46
Some records in First Name and Last Name are delimited by "/" and would like to split them into rows. CONNECT BY Clause works perfectly for one column. How do split the strings in multiple columns into rows as given below? Sample Table : ...
Categories: DBA Blogs

Convert comma separated values in a column into rows and Join the result set with another table

Wed, 2018-09-12 00:46
I have a table as below R_ID R_Site R_Name R_Role ----- ------------- ------ ------ 1 123,-456,-789 qwer Owner 2 56,-741-852 qaz Manager 3 369,-741,-987 wsx Employee 4 All eddc Employee 5 All ...
Categories: DBA Blogs

Problem reading data from a flexible attribute based XML

Wed, 2018-09-12 00:46
Hi Tom, I am not an XML expert. I have a flexible attribute based XML stored into a table in XMLTYPE column TBTest.C1(XMLTYPE) which is a message from upstream queue. The XPATHs of message are like - <code>/*[name()='Message'] /*[name()='Message']...
Categories: DBA Blogs

Database link queries

Tue, 2018-09-11 06:26
create or replace procedure P_POP_ILC (P_POL_NO VARCHAR2 DEFAULT NULL) is cursor c1 is select * from rsds_locn_exposure@dmn_rsk_150 where RLE_ULM_NO = NVL(P_POL_NO , RLE_ULM_NO); begin DELETE FROM IDS_LOCN_CLM@dmn_rsk_150; comm...
Categories: DBA Blogs

oracle 12g directory object feature. Is there an equivalent to utl_file_dir='*'?

Tue, 2018-09-11 06:26
Ok so I am trying to get some things with oracle to work. I am TSQL guy, and new to oracle. The guy who used to manage the Oracle side of things retired. One of our products is pretty old, but still has a large customer base. Some of those customers ...
Categories: DBA Blogs

generate test data automagically

Tue, 2018-09-11 06:26
To me is often send only description of tables, I need to create and some business logic to put in stored packages. But no flat files to load test data. That is the reason for my thoughts of dynamic procedure, which should (depended of column typ...
Categories: DBA Blogs

No matching authentication protocol

Tue, 2018-09-11 06:26
Hello All, I am trying to connect to Database . I am getting error as below while trying connection to oracle DB: Can't get the Connection for specified properties; java.sql.SQLException: ORA-28040: No matching authentication protocol...
Categories: DBA Blogs

Pages