Tom Kyte

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

Hardware resource planning

Mon, 2018-09-17 08:46
Hello, Thanks for taking up this question. I am interested in understanding how to optimize the hardware resources (cores, memory, disk space) required for Oracle without impacting performance. There are multiple virtual machines in a VMwa...
Categories: DBA Blogs

system user could login without password or incorrect password

Mon, 2018-09-17 08:46
hi all, recently i had an incident.. i just logged into the database as system using sqlplus when sqlplus prompted for username i put 'SYS AS SYSDBA' and when prompted for password,instead of entering my password i just hit the ENTER key and s...
Categories: DBA Blogs

Returning count of rows deleted using execute immediate

Sun, 2018-09-16 14:46
How to I get the number of rows deleted within PL/SQL using the EXECUTE IMMEDIATE command?
Categories: DBA Blogs

Parse string then flatten into columns

Fri, 2018-09-14 07:46
Hi, LiveSQL link not accepted by the form: https://livesql.oracle.com/apex/livesql/s/g88hb5van1r4ctc65yp4lq9gb I have this situation (see link): <b>ID String</b> Id1 Thing1: Sub1, <br>Thing2: Sub7 ,Sub8 , Sub9 <br>Thing3: Sub12 Id1 Thing...
Categories: DBA Blogs

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

Pages