Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 month 1 day ago

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

Date constraint to validate all bookings are in the future

Thu, 2019-03-21 01:06
Hi, I need to create a constraint which will not allow appointments to be booked in the past. I wonder if someone could help me with this please. Thank you. Juliana
Categories: DBA Blogs

Monitoring parallel excution of FULL table scan

Thu, 2019-03-21 01:06
Hi I'm on 12.2 EE on Win 2016 I have the following SQL which selects from a 550 GB table (yes, it is GB due to massive GDPR logging) <code> create table GFAUDIT.fga_log$_kopi_201809 as select /*+ PARALLEL (8)*/ (select instance_name from v...
Categories: DBA Blogs

REGEXP_LIKE Statement

Thu, 2019-03-21 01:06
Why is this statement returning value - <code>select * from ( SELECT 'AAaaanders4n' name FROM dual ) WHERE REGEXP_LIKE (name, '^[A]{1}');</code> I have given {1} in regexp_like, still this statement returns 'AAaaanders4n'
Categories: DBA Blogs

Generate number based on start and end columns.

Wed, 2019-03-20 06:46
Generate value based on start and end columns without using procedure. How to modify the select query. <i>select key_column, start_point, end_point FROM tab1 WHERE key_column='10254';</i> key_column start_point end_point 10254 -2 ...
Categories: DBA Blogs

How to recover the whole database with RMAN Backup

Wed, 2019-03-20 06:46
Hi Team, First off all a big Thanks for your supports Now i wanna know the steps to recover a fully operational database with RMAN backup. I haven't done this scenario before,So i am going for a Test case here. My requirement is 1) I have dat...
Categories: DBA Blogs

cannot access objects in different schema

Wed, 2019-03-20 06:46
I am the admin user and can create tables and procedures in any schema. I have few tables in Schema B which I am referencing in a package i am creating in Schema A however upon compiling it does not see the tables in Schema B. Schema B does not ha...
Categories: DBA Blogs

Virtual columns in Oracle 11g

Wed, 2019-03-20 06:46
hi tom what is virtual column in 11g. Why oracle has introduce it. Can you give us its possible usages. regards Amir Riaz
Categories: DBA Blogs

Predict tablespace growth for next 30 days

Tue, 2019-03-19 12:26
How to Predict tablespace growth for next 30 days need to configure using oem any possible solutions
Categories: DBA Blogs

How to count pairs in a consecutive number of rows

Mon, 2019-03-18 18:06
I have the following example: COLUMN 19 20 26 28 29 32 33 34 I need to count the rows based on pairs, 19-20, 28-29, 32-33. I'm having difficulty to check if a pair is already counted or not, any sugestions ? The result should be s...
Categories: DBA Blogs

Error generating DUMP: ORA-39006: internal error

Mon, 2019-03-18 18:06
Hi, I have a problem creating a dump with SQL Developer, the PL/SQL generated is: <code> set scan off set serveroutput on set escape off whenever sqlerror exit DECLARE h1 number; s varchar2(1000):=NULL; errorvarchar varchar2(1...
Categories: DBA Blogs

ORA-06533: Subscript Beyond Count error

Mon, 2019-03-18 18:06
Hi I have the following PLSQL code - if run 1st time - it works fine - running 2nd or 3rd time it fails with "Subscript beyond count" error If I make the declaration of g_response private to the procedure (not globally in the package) - it works...
Categories: DBA Blogs

Explicitly providing values in a WHERE clause showing much better performance compared to using sub query

Fri, 2019-03-15 16:46
Hi I am new to oracle and not sure how to provide the liveSQL link. I have 2 tables to join huge_table contains about 1 billion rows big_table contains about 100 million rows and small tables contains 999 rows providing the condition to fil...
Categories: DBA Blogs

Compare columns in two tables and report which column is different

Fri, 2019-03-15 16:46
Compare columns in two tables and list out column names which are different. for ex:- create table t1(c1 number(2), c2 varchar2(10)); create table t2(c1 number(2), c2 varchar2(10)); insert into t1 values(1,'a'); insert into t2 values(1,'b'); result ...
Categories: DBA Blogs

Taking More Indexing Time on ORACLE database While Performing QUERYS

Thu, 2019-03-14 22:26
Hi Team, Very impressive for your way of explaining. So we have ORACLE db production server,from that we are trying to perform indexing with 40 Querys on every day.For Example i am showing few Querys:: <code>DROP INDEX GINQCON_ix1 FORCE; ...
Categories: DBA Blogs

Pages