Tom Kyte

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

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

I want to move from DBA to Developer, how to develop my skills

Thu, 2019-03-14 22:26
I am working in my DBA role for the past 5 years,vexed with on call support,shift 24*7. I want to switch to developer role and much more I am interested in it. I also want to know the roles and responsibilities what do developer do, which tool...
Categories: DBA Blogs

Number of Chunks in DBMS_PARALLEL_EXECUTE

Thu, 2019-03-14 22:26
Hi TOM, I am having a data movement activity from one table to another table. Due to Huge data size in both tables, i have planned to use DBMS_PARALLEL_EXECUTE functionality, so that i can finish it quickly. I am creating chunks based on ROWID. ...
Categories: DBA Blogs

Is there a nowait clause for an insert statement

Thu, 2019-03-14 22:26
I have inserted the same primary key data but the Second Session is locking forever! Is there an option to alter user session to raise timeout after 60seconds or NOWAIT for the second sessions INSERT command. In our environment session 1 is a lo...
Categories: DBA Blogs

A table has a owner but also a creator?

Thu, 2019-03-14 22:26
Hello Masters, I saw something weird last time and I need your expertise to understand. I create two users. <code>[ora1210[XXX] /home/ora1210]$ sqlplus / as sysdba SQL> create user ZZ01 identified by xxxxxxxxxxxxxxxxxxxx; SQL> create...
Categories: DBA Blogs

Weighted Average Inventory calculation.

Tue, 2019-03-12 15:26
Hello, Hope you are at the best of you health. I am working in Microsoft Windows environment and installed database is Oracle 11gR2. I have following two table with sample data. <code>CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype...
Categories: DBA Blogs

How to decide to create index on column

Tue, 2019-03-12 15:26
Dear, I talk about single table contain approx 20 to 25 columns. And in production database this table used with different column combination or single column also used in different where clause at different query. When some time query run slow s...
Categories: DBA Blogs

Pages