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

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

Non-Deterministic Functions and Scalar Subquery Caching

Tue, 2019-03-26 09:26
Hi, i got one question regarding scalar subquery: Why does Oracle not restrict scalar-subquery-caching to functions which are declared to be deterministic? With the deterministic keyword the developer states that a function is deterministic...
Categories: DBA Blogs

dbms_assert package

Tue, 2019-03-26 09:26
Hi, Oracle provides dbms_assert package with functions which can be used to safeguard SQL injections. Shall I use this package like below in my java application. select DBMS_ASSERT.sql_object_name ('test') from dual; I didn't see people usi...
Categories: DBA Blogs

How to move to a new tablespace a column LOB of the dataype ANYDATA ?

Tue, 2019-03-26 09:26
Hello Masters, I have a table with a PUBLIC.ANYDATA column. <code>SQL> desc XL_EURO_AXAIA.EXT_TCV_TCH_VALEUR Nom NULL ? Type ----------------------------------------------- TCH_ID NOT NULL NUMBER ID NO...
Categories: DBA Blogs

Migration of a very large warehouse database

Fri, 2019-03-22 13:46
This question is more of a solicitation for advice than an actual question. I've been tasked with migrating a large warehouse database from one set of hardware in one datacenter to a new set of hardware in a different datacenter. Excluding temp, ...
Categories: DBA Blogs

How to show special/unseen characters from a column in a plsql ?

Fri, 2019-03-22 13:46
Hi Tom, I have a table with special characters in a column. The column values are like this with the plsql below. <code>set serveroutput on; declare c varchar2 (100); a number; begin for i in ( select ekd0756_cur...
Categories: DBA Blogs

ORA-02396: exceeded maximum idle time, please connect again

Thu, 2019-03-21 19:26
Dear Mr. Tom, i have an issue according the IDLE_TIME parameter.. I used to open more than one session to the database (different scheam's on same Database)... and our dba set IDLE_TIME as 15 minutes, and even if the session is not idle (i'm ...
Categories: DBA Blogs

truncating empty table generate ORA-02266:

Thu, 2019-03-21 19:26
Tom , why truncating empty table generates the error ORA-02266 ? SQL> select count(*) from t_data; COUNT(*) ---------- 0 SQL> truncate table t_data * ERROR at line 1: ORA-02266: unique/primary keys in t...
Categories: DBA Blogs

SQL query to find FK IDs with a series of values

Thu, 2019-03-21 19:26
we have below tables <code>create table bca(id number(2)); insert into bca(10); insert into bca(11); insert into bca(7); create TABLE abc( di NUMBER(2), fk_id NUMBER(3), yek VARCHAR2(20), elv VARCHAR(15...
Categories: DBA Blogs

Pages