Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 2 months 2 weeks ago

How lob columns are transferred by Oracle Net Services.

Thu, 2019-04-11 10:46
Hi Oracle manual states: 'Starting with Oracle Database 11g, Oracle Net Services optimized bulk data transfer for components, such as Oracle SecureFiles LOBs and Oracle Data Guard redo transport services. The SDU size limit, as specified in the n...
Categories: DBA Blogs

How to check the Table structure of both the data bases & merge into single database.

Thu, 2019-04-11 10:46
Two Banks are running on Oracle database for the same application software. Now those 2 Banks merged and need to merge the Oracle Databases also.. How to check the Table structure of both the data bases & merge into single database.
Categories: DBA Blogs

Order of Update statements

Wed, 2019-04-10 16:26
Hello, Thanks for taking up this question. I have noticed a strange behavior in Oracle database (11g R2). The database I am working on has 2 instances (RAC implementation). I am executing a script containing multiple update statements. They ar...
Categories: DBA Blogs

Execute procedure in anonymous block returns ORA-06550 & PLS-00222

Wed, 2019-04-10 16:26
Hi, I created these objects: ------------------------------- <code> create table mwallet.tb_test (test_id number GENERATED ALWAYS AS IDENTITY, test_name varchar2(50) ); </code> ------------------------------- <code> create or replace pr...
Categories: DBA Blogs

How would you implement this? Returning name format based on country code

Wed, 2019-04-10 16:26
Here's the situation. System is used in several countries. User logs in and can only be at one country at a time. There's id in the system, which belongs to one country. There are functions/procedure in the system, which are country specific. F...
Categories: DBA Blogs

How to avoid functions being called twice in a single sql-statement?

Wed, 2019-04-10 16:26
Hi We have merge statements which get their values from non deterministic functions. Some of the updates merge does are not 'real' in a sence that actual values do not change. We would prefer so that these updates are not made at all but do not...
Categories: DBA Blogs

Mismatch between XML IsNumber and APEX JSON IsNumber

Wed, 2019-04-10 16:26
Hello, <code> DECLARE x xmltype; y CLOB; b varchar2(6); BEGIN WITH a AS (SELECT '<adr><str>bachstr.</str><tel>- 087</tel><tel1>-87</tel1><tel2>-087</tel2></adr>' t FROM dual ) SELECT xmltype(t) INTO x FRO...
Categories: DBA Blogs

Creating dummy data for chart

Tue, 2019-04-09 22:06
Hi, I am generating a bar chart in Apex and my data has few gaps as a result there are gaps between bars.If I fill data in query with zero value then my bar chart will be correctly displayed. Below is data and required output <code>create table...
Categories: DBA Blogs

Like Operator with IN clause

Tue, 2019-04-09 22:06
I have to compare more than one Patter using LIKE operator Say I want to prepare query SELECT name FROM employee WHERE name <b>LIKE IN</b> ('sasho','shashi%','rags')
Categories: DBA Blogs

JSON path expression syntax error

Tue, 2019-04-09 03:46
Hi TOM, I am trying to use json_value function infor loop as below. then getting error as 'PL/SQL: ORA-40442: JSON path expression syntax error'. Could you please help me with solution. Thanks set serveroutput on; DECLARE p_dn_list CLO...
Categories: DBA Blogs

Parallel recursive WITH Enhancements

Tue, 2019-04-09 03:46
Team, was reading about this feature Parallel recursive WITH clause enhancements in 12.2 database from the below link: <u>https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C...
Categories: DBA Blogs

Attempting primary / standby's using same scan listener name and configuration on both clusters.

Tue, 2019-04-09 03:46
Hi Tom, I've never gotten through to you in 20 years, so hopefully you'll be available as I could use some sound advice (you're so in demand, I gave up about 10 years ago :-) Here are the particulars: * We're planning a migration from data cen...
Categories: DBA Blogs

Check db_link from several databases, from one server via dv_links to these servers

Tue, 2019-04-09 03:46
Hello, I need to check db_links from all of my servers and I want to check it from procedure or view on statistic db server. Statistic db server have db_links to all of my db's. It is possible? If it is possible how I can do this?
Categories: DBA Blogs

Why the same query performs much slower in PL/SQL procedure/package than directly querying

Tue, 2019-04-09 03:46
Dear Tom, I have a complex query joining multiple tables which takes about 10 to 15 seconds to return results. There are 3 layers (2 sub queries) of the "FROM" to get the final results. However, if I create a procedure to run the same codes, whic...
Categories: DBA Blogs

To create multiple instances and databases on same unix machine

Tue, 2019-04-09 03:46
I have a requirement to create 2 instances and 2 databases on same unix machine. Do I need multiple homes, I think not because it is same version (11g). I already created one instance/database using OUI. Are these steps right for creating second one?...
Categories: DBA Blogs

Format columns correct datatype for CSV output

Thu, 2019-04-04 13:46
Hello, I generate in SQL Developer version Version 17.4.0.355 files in CSV format (execute as SQL script). The table has column values like <b>2-4 or 9/11</b>, that is in CSV file displayed as 02. Apr. I tried with <code>col testcol format...
Categories: DBA Blogs

APEX 4.x.x. Support For 18c Database

Thu, 2019-04-04 13:46
Hi , I would like to know does Oracle 18c database support Apex Version 4.x.x. our application is build on APEX 4.x.x right now we do not want to upgrade APEX version to 5.x.x. but want to upgrade only Database version from 12c to 18c
Categories: DBA Blogs

Display all combination of given number

Thu, 2019-04-04 13:46
I want to print those combination which have exact 110 sum. Here I give some data for an example. 10 20 30 60 40 70 99 90 80 And output is 80,20,10 70,30,10
Categories: DBA Blogs

Query regarding BULK COLLECT CLAUSE

Thu, 2019-04-04 13:46
Good Day Folks, I have one query regarding the BULK COLLECT clause. Could you please let me know how we can fetch/handle multiple column's data using Bulk Collect clause. Please feel free to alter below code. <Code> -- BULK COLLECT CLAUSE WIT...
Categories: DBA Blogs

Buffer Limit

Thu, 2019-04-04 13:46
Hi, The Buffer size we give for dbms_out.put_line is different from Data base buffer cache in SGA or same? and can you show me am example on how to use dbms_out.getline function. Thanks in Advance.
Categories: DBA Blogs

Pages