Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 3 hours 14 min ago

I/O of Database

Wed, 2016-11-30 02:46
Hi, I am reading a lot on AWR reports, and also posts about this subject. But everyone say something different. My client asked me for: "I want to know the I/O of the database" Is there a metric, so I can execute a script on a time interva...
Categories: DBA Blogs

Imcomplete CSV File

Wed, 2016-11-30 02:46
Hi, Iam exporting a simple select command into a csv file. but the resulting csv file is incomplete ie. amon 44 columns it is displaying only 3 columns. please find my code below, set headsep on set term off set newpage 0 set space 0...
Categories: DBA Blogs

How to check in apex, if there is new data

Wed, 2016-11-30 02:46
Hello Tom, I'm a bit new to apex and I wonder if there is a way to check in apex, if new data was inserted in my DATA table. I want to fire a dynamic action that refreshes the region only when new data has arrived. For now, this works for me: ...
Categories: DBA Blogs

Unable to connect to database using sql developer

Wed, 2016-11-30 02:46
Hi , I am unable to connect to oracle database using SQL DEVELOPER but I am able to connect through windows cmd command. It gives me error i/o error network adapter could not establish connection. vendor code 17002. Please let me know what are the...
Categories: DBA Blogs

LOB data type comparisons

Wed, 2016-11-30 02:46
Hi Team We have migrated data from SQL server to Oracle,As part of data validation we want to compare the data from both databases. We have some tools which compare data but that tool will not compare lob columns. Please note we had migrated dat...
Categories: DBA Blogs

Insert All with sequence - feature or featurette?

Tue, 2016-11-29 08:26
I posted this problem elsewhere and we had a bit of a discussion. However I would like to know whether what we see is a feature (i.e. it can be relied upon to be somewhat consistent in future releases) or whether it is a "featurette" - i.e. unintende...
Categories: DBA Blogs

Generating excel report with multiple tabs from Oracle APEX

Tue, 2016-11-29 08:26
Hi, I'm new to Oracle Application Express and currently working on an APEX application. We have several reports which can be downloaded as csv files in various modules of the application. What I need is to have a master report(excel file with ...
Categories: DBA Blogs

Decoding from base64

Tue, 2016-11-29 08:26
Hi, I'm in need to encode and decode big xml, however when i'm decoding from base64 after some iterations it fails to decode and i get symbols like this, "S??FS???S??FS???S?". Here's the script to reproduce the problem i'm facing: <code>declare...
Categories: DBA Blogs

Drop a partition of reference partitioned tables with the UPDATE INDEXES clause

Tue, 2016-11-29 08:26
I have a set of 3 tables that are reference partitioned (a parent and two child tables). The parent table is range partitioned by date (TST_ADM_DTE) and contains 1,152 partitions, ranging in value from 3/1/14 to 12/31/16. The first seven (7) partitio...
Categories: DBA Blogs

Complete MV refresh not using Smart Scans in 12c

Tue, 2016-11-29 08:26
Hi, I have a query which i am trying to load the data using 2 options: 1. Materialized View Refresh with Non-Atomic Refresh 2. Insert into Select Materialized View Refresh: When i try to Complete refresh a Materialized View in 12c Database...
Categories: DBA Blogs

Deinstallation of Grid infrastructure

Tue, 2016-11-29 08:26
Hi Tom, I have done installation of GI for standalone and oracle database, with different OS user. Now, I want to remove GI software only as I am not using ASM for datafiles. Can I able remove only GI with deinstall utility. I donot want to re...
Categories: DBA Blogs

how can i use dbms_session.unique_session_id

Mon, 2016-11-28 14:06
dbms_session.unique_session_id returns unique value for my session. understood. but the question is - what information can i gain from it? how or where i use the returned value? are there any dba or system tables where i can: select * from tabl...
Categories: DBA Blogs

Count elements in JSON Clob

Mon, 2016-11-28 14:06
Hi, I have been working on JSON features in Oracle to extract field values from the Clob & came across one question - Is it possible to extract the number of elements in the JSON data ? Ex: If i have below the JSON , is it possible to get the...
Categories: DBA Blogs

Search for string containing letters

Mon, 2016-11-28 14:06
Hi, I'm trying to create a SQL statement with a condition in the WHERE clause that will deliver all results that start with 3 letters. The column type is varchar, and all entries either look like 'ABC123' or 'A12345'. I'm not looking to retu...
Categories: DBA Blogs

Query runs really fast on 1st attempt, but then slows down considerably in subsequent runs

Mon, 2016-11-28 14:06
Hi Tom and Team, I have a SELECT query that runs fine if submitted the 1st time (takes about 3 seconds), but if I submit it right after again, it could take anywhere from 22 to 37 seconds (on a pretty consistent basis). If I wait a few hours (or ...
Categories: DBA Blogs

FULL TABLE SCAN

Sun, 2016-11-27 19:46
What are the things that result in FULL TABLE SCAN?? Thanks in advance...
Categories: DBA Blogs

About archiving status of redo logs

Sun, 2016-11-27 19:46
Does archive of redo logs get completed when STATUS of redo log is active ? and what happens when STATUS of redo log is inactive in background ? Scenario that i tried Three groups of Redo log size 50mb each and Archiving is Enabled and insert...
Categories: DBA Blogs

need to access hana columnar table data in Oracle 11g through remote source

Sun, 2016-11-27 19:46
what will be the performance impact, if I remote source hana tables to Oracle 11g for real-time data read and update some oracle master table. as Oracle is OLTP and hana is used as data warehouse. Can you pls suggest, the performance impact due to ...
Categories: DBA Blogs

The real cost of an exception

Sun, 2016-11-27 01:26
Hello, Tom. I my project we have a package with a pretty standard structure, I think. It has public procedures calling in turn private procedures, calling innermost procedures eventually. Simplifying, something like this: -------------------------...
Categories: DBA Blogs

Bad cardinality in join with column with skewed data

Sun, 2016-11-27 01:26
Hi guys. I have a problem with the estimation of the cardinality of a skewed column The distribution of the data is as follows: <code>select m.m_pricelist_id, count(*) from m_pricelist_version m group by m.m_pricelist_id 2 3 ;</code>...
Categories: DBA Blogs

Pages