Tom Kyte

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

XML Aggregation

Wed, 2019-10-23 15:46
Consider: <code>with data as (select 'MH' initials, to_date('01092019','ddmmyyyy') cal_date, 23 quantity from dual union all select 'MH' initials, to_date('02092019','ddmmyyyy') cal_date, 18 quantity fro...
Categories: DBA Blogs

Remove a hint with DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR failed

Wed, 2019-10-23 15:46
Hello Masters, I am testing the two packages DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR and they work fine : for exemple I can remove an ORDER BY from a SELECT. But, there is always an exception, I have problems with Hints : I want to remove...
Categories: DBA Blogs

Constraining JSON Data

Wed, 2019-10-23 15:46
Hello AskTom-Team, is there a possibility to contrain and check JSON data? For example: <code>insert into test(questionnaire_id, var_val, year) '{ "f1": "2571", "f11": "38124", "f31": "332.64", "f4...
Categories: DBA Blogs

FAR SYNC FAILOVER

Tue, 2019-10-22 00:46
Dear Sir, In case of an outage on the primary database, the standard failover procedure applies, and after some time primary server available then how it going to sync all 3 server. please help me to understand this. Thanks Pradeep
Categories: DBA Blogs

Oracle query running slow

Tue, 2019-10-22 00:46
Hi Team, we have a SQL query which is a source query for the ETL load job, this take around 3 hours to run, could you please help us how we can make it run faster. The row count of the tables involved are as follows. D_PERSON 4618595 ...
Categories: DBA Blogs

Keep pooI

Tue, 2019-10-22 00:46
when I assign any of the segment to keep pool , It is nessesary to set appropriate DB_KEEP_CACHE_SIZE as per the sizes of the segment. or it will dynamically set DB_KEEP_CACHE_SIZE?
Categories: DBA Blogs

Limit parallelism

Tue, 2019-10-22 00:46
Hi ASKTOM team, I am not very good with parallelism, so have a question about DW database. These are my current settings: <code> parallel_adaptive_multi_user boolean TRUE parallel_automatic_tuning boolean FALSE parallel_degree_li...
Categories: DBA Blogs

Log Files in Oracle External Tables

Tue, 2019-10-22 00:46
My External Tables were working fine before i accidentally deleted all the .log and .bad files from the default location. Now I am getting below error ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-...
Categories: DBA Blogs

(Theoretical) Confusion with roles and public synonyms

Tue, 2019-10-22 00:46
Hi Tom, <b>Confusion1:- </b> Suppose I have three user accounts in my database: A, B and C. 'A' user has the privilege to create a role. Suppose there is a table named 'employee' in schema 'A' and 'A' issues:- 1. create role GiveAccess; 2...
Categories: DBA Blogs

Stat gather impact on production environment

Mon, 2019-10-21 12:45
On OLTP production environment, during huge transaction period, what is an impact if we run the stat gather of used schema for transaction???, It will missed any indexes, and other operation issues???
Categories: DBA Blogs

Best practice for "archiving" legacy tables and their data

Mon, 2019-10-21 12:45
Hi, I recently removed the last piece of front-end functionality that relied on a table, and am certain that that table and its data is no longer needed for the application to function. We'll have more similar tables in this situation in the near ...
Categories: DBA Blogs

Fetch across commit

Mon, 2019-10-21 12:45
what do you mean by 'Fetch across commit'
Categories: DBA Blogs

Is there a maximum number of schemas that can be included in a datapump par file?

Mon, 2019-10-21 12:45
I've been tasked with migrating a very large warehouse database (9TB) from hardware in one data center to new hardware in a different data center. For various reasons, the method I've selected for the migration is datapump. I'm breaking up the data...
Categories: DBA Blogs

Index Rebuild and analyze

Mon, 2019-10-21 03:45
Hello Tom , I have a query regarding Index rebuild . what according to you should be time lag between index rebuilds. We are rebuilding indexes every week .but we found it is causing lot of fragmentation. is there any way we could find out whet...
Categories: DBA Blogs

Views of Views

Mon, 2019-10-21 03:45
I remember hearing some time ago that creating views based upon other existing views should be avoided as it can often confuse the optimiser and result in full table scans. I expect that this is just another urban myth however I would be intereste...
Categories: DBA Blogs

Inserts with APPEND Hint.

Mon, 2019-10-21 03:45
<code>insert /*+ append */ into t select rownum,mod(rownum,5) from all_objects where rownum <=1000 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- -----...
Categories: DBA Blogs

Kill a session from database procedure

Mon, 2019-10-21 03:45
How i can kill a session from a stored database procedure. There is some way to do this?
Categories: DBA Blogs

Why cost for TABLE ACCESS BY INDEX ROWID to high for only one row

Sat, 2019-10-19 15:45
Dear Tom, I have problem with query on table have function base index. create index : <code> create index customer_idx_idno on Customer (lower(id_no)) ; --- id_no varchar2(40) </code> <b>Query 1:</b> execute time 0.031s but cost 5,149, 1 row ...
Categories: DBA Blogs

How to get unique values/blanks across all columns

Sat, 2019-10-19 15:45
Hi, I have a wide table with 200 odd columns. Requirement is to pivot the columns and display the unique values and count of blanks within each column <code>CREATE TABLE example( c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10) ); / INSERT ...
Categories: DBA Blogs

Elastic search using Oracle 19c

Sat, 2019-10-19 15:45
Team, Very recently we got a question from our customer that "can I replace Elastic search using Oracle 19c or any version of Oracle database prior to that"? Any inputs/directions to that please - kindly advice.
Categories: DBA Blogs

Pages