Tom Kyte

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

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

Can we have multiple versions of Oracle database instance in 11gR2 RAC

Thu, 2019-04-04 13:46
We would like to install 2 11.2 database and one 10.2 database in 11GR2 RAC. Is this possible? ? Can we run all 3 instances on a Single RAC 11.2 Cluster? Will this complicate backup/recovery? Thanks
Categories: DBA Blogs

interval-list partitions

Tue, 2019-04-02 06:26
Hi Tom, I would like to ask you about interval-list partitions. On oracle-base.com is mentioned that one of the new features of 11g is interval partitioning (available over number and date columns) and it should be possible to create interval-lis...
Categories: DBA Blogs

Oracle Trace

Tue, 2019-04-02 06:26
Hello, I need to analyze my database. I used trace + tkprof and I see this result : SQL ID: 4yvsj6a508pgf Plan Hash: 2709293936 SELECT NAME FROM SYS.USER$ WHERE USER# = :B1 call count cpu elapsed disk query ...
Categories: DBA Blogs

When it's time to close cursors

Tue, 2019-04-02 06:26
Hello, Tom. Hope you're doing well :) My question is - is it bad practice to let the 'main' procedure of the package to close all open cursors? Here is the code I want to use: create or replace PACKAGE body pck_test AS -- Global package curso...
Categories: DBA Blogs

Oracle Analytics Cloud - Current Week SQL Filter

Mon, 2019-04-01 12:06
I'm trying to automate some reports in the Analytics Cloud by using SQL syntax within SQL filter. I was able to make a report refresh on a daily basis; but I'm stuck on getting it to work for the week. Is there a syntax that will recognize Monday to ...
Categories: DBA Blogs

Subtotal, Grand Total, ordering and breaking on different fields

Mon, 2019-04-01 12:06
Hi Tom I was looking through the already asked questions about Subtotals and Grand totals , but couldn't really find anything that suits my problem. I have a table with the following fields <code>DATE REGION REG_NUM AMOUNT 0...
Categories: DBA Blogs

Why oracle stored procedure execution time is greatly increased depending on how it is executed?

Mon, 2019-04-01 12:06
I have three different procedures written in PL/SQL (Oracle database). They do various calculation and analysis, end product of all three being ?INSERT INTO TABLE_A? (so all functions are inserting into same table). It?s a pretty straightforward PL/...
Categories: DBA Blogs

DBMS_SCHEDULER JOB (Need to make repeat_interval parameterized)

Mon, 2019-04-01 12:06
How can i dynamically retrieve the value for repeat_interval in the following job:- BEGIN DBMS_SCHEDULER.create_job ( job_name => 'create_Subpartition_Ongoing_LOCDB_M', job_type => 'PLSQL_BLOCK', job_action => ...
Categories: DBA Blogs

Granting select on all tables and views of one user to other

Sat, 2019-03-30 05:06
Tom, I have a user with x tables any y views. I wish to grant select on all the tables and views owned by this user to another user. Is there any thing like "select any table" for this purpose. I am thinking to implement by granting the select...
Categories: DBA Blogs

best way to get high value of partitions from data dictionary

Sat, 2019-03-30 05:06
Hi, Chris and Connor, I'm trying to find the best way to get "high value" of partitions from data dictionary, in order to write a customized statistics gathering program. As you know, the data type of "high_value" column of DBA_TAB_PARITTIONS i...
Categories: DBA Blogs

MAX aggregation function shows different results when partitioned

Thu, 2019-03-28 16:26
Hi everyone, I'm getting strange results from a query. I have a table list partitioned by id, when I execute this : <code>select max(col1) from table where id=100;</code> it returns the value 'A1'. Then I execute the following (keep in mind ...
Categories: DBA Blogs

Infidelity when storing XMLType data elements (spaces)

Thu, 2019-03-28 16:26
When saving XMLType data into table. The data fidelity is is not maintained for fields containing only spaces (see LiveSQL). Before save, spaces are present in <COL> </COL> After reading the saved data, spaces are gone and we see empty tag <COL...
Categories: DBA Blogs

Pages