Tom Kyte

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

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

Lot of time to add partitions to a Big Table

Sat, 2016-11-26 07:06
Hi Tom, I have a big table with 6 partitions(hash), the table have <b>270 Million records</b>, i try to add 2 partitions(i want to have 8 partitions for use "power of 2"), after prepare temp space and undo space, then i run process to add to partiti...
Categories: DBA Blogs

Synonyms

Sat, 2016-11-26 07:06
Hi, Please answer the below questions. 1. What Is the Difference between PUBLIC and PRIVATE synonyms? 2.Do we need GRANT privilages to acces PUBLIC SYNONYM? 3.Is PUBLIC Synonym a NON-Schema Object Or Schema Object?
Categories: DBA Blogs

INSTEAD OF TRIGGERS

Sat, 2016-11-26 07:06
INSTEAD OF triggers are executed instead of DML statements that fired it. Is it correct??
Categories: DBA Blogs

Drop MetaData

Sat, 2016-11-26 07:06
Hi, I installed 11g(enterprise) on my personal laptop.I Droped the Metadata DBA_OBJECTS,DBA_USERS(my intention is to know DB fuctioning),my Data base working properly.But When I execute Select query(SELECT * FROM dba_objects) getting *ora-10775 ...
Categories: DBA Blogs

Moving table partitions

Sat, 2016-11-26 07:06
Tom, Is it possible to move a table partition from one tablespace to another in the same way it is now possible to move a table from one tablespace to another ?
Categories: DBA Blogs

Oracle database In Memory

Sat, 2016-11-26 07:06
Hello Tom, I seen a new feature for Oracle 12c : In-Memory database cache. I seen it's a new memory area to organize data in column format for BI/DWH queries. I would like to know how Oracle decides when use the data from In-memory area or from...
Categories: DBA Blogs

Reading xml data from nested xml nodes using PL/SQL

Fri, 2016-11-25 12:46
Hello Tom, I am new to xml and don't know Java and, unfortunately, do not have time to learn it right now as I need to have a little prototype done of this in a couple of days. I did a lot of reading and reviewed many of the OTN references but I...
Categories: DBA Blogs

Oracle 12.2 new features: long names

Fri, 2016-11-25 12:46
I was just going through https://blogs.oracle.com/sql/entry/12_things_developers_will_love and checking the new Oracle 12.2 features, and noticed something about the "Loooooooooooooooong Names". So say if I define a variable as v_table_name use...
Categories: DBA Blogs

Synonyms

Fri, 2016-11-25 12:46
Hi, Please Go through the below code SQL> select * from san; --querying tbale NUM ---------- 1 2 SQL> select * from sam; --querying table VAR ---------- santhosh reddy SQL> SE...
Categories: DBA Blogs

Relation

Fri, 2016-11-25 12:46
Hi, Is it possible to create a relationship between tables without using foreign key?
Categories: DBA Blogs

Pages