Tom Kyte

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

Row Chaining

Mon, 2018-11-12 19:26
Hi Tom What is row chaining/migration ? what are the consequences of Row Chaining/Migration ? How I can find whether it is there in my database or not ? and if it is there what is the solution to get rid from it? Thanks in advance Howie
Categories: DBA Blogs

Run procedures or functions in parallel

Mon, 2018-11-12 19:26
hello, I've web app which is using procedures and functions inside packages most of the time, in some cases procedures and functions execution taking long time to return data (as SYS_REFCURSOR). the problem is that when other users execute other p...
Categories: DBA Blogs

Export Tables from Oracle-12c to Oracle-10g

Mon, 2018-11-12 19:26
Why the following table is not being Exported from Oracle-12c to Oracle-10g Table : <code>create table stock(ModID varchar(20) primary key, Name varchar(30), Type varchar(15) ,mQty number, cmpID number, price number, Warranty number);</code> ...
Categories: DBA Blogs

Counting specific days between to two dates

Mon, 2018-11-12 19:26
Hi Tom, ? have a case that i need to count specific days between two dates. For example i have a table that contains contract startdate, enddate and specific date like 15. 15 means every 15th day of months. i need to count specific dates. for exa...
Categories: DBA Blogs

User_dump_dest is inconsistent with the actual trace path

Mon, 2018-11-12 19:26
If my question is too simple or meaningless, you can ignore it. Why does my user_dump_dest parameter get a different path than the actual path? I run this example: <code>EODA@muphy>select c.value || '/' || d.instance_name || '_ora_' || a.spi...
Categories: DBA Blogs

copy partition table stats

Mon, 2018-11-12 19:26
Hi Team , as per the requirement from application team , we need to copy table stats from one table to other table . Both source and destination table are partition tables . here we tested out in local system below steps : 1. created dum...
Categories: DBA Blogs

Join like (1=1)

Mon, 2018-11-12 19:26
Hi All, I am sorry if this is pretty basic,but it is intriguing me a bit. I saw a join written like Inner Join table B on (1=1) Why join like this should be written and under what scenario.Thanks in advance.
Categories: DBA Blogs

Moving data across DB link when one database uses pass-through configuration

Sat, 2018-11-10 12:26
There is a source with the settings below: NLS_LANGUAGE AMERICAN NLS_TERRITORY CIS NLS_CURRENCY ?. NLS_ISO_CURRENCY CIS NLS_NUMERIC_CHARACTERS , NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD.MM.RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET W...
Categories: DBA Blogs

Oracle Sequence and EXPDP/Historical Data

Fri, 2018-11-09 18:06
Hello, Ask TOM Team. We are designing a new database and there are lots of tables with identity column. The sequence used by each table is a named sequence (we are not using the system-generated sequence). The default values of these tables is lik...
Categories: DBA Blogs

Function to gather the stats on the latest partition of a table

Fri, 2018-11-09 18:06
I have a function - ANALYZE_TABLE as below <code>create or replace FUNCTION ANALYZE_TABLE (P_TAB_NAME IN VARCHAR2) RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,P_TAB_NAME); RETURN 0; END;</cod...
Categories: DBA Blogs

Not exist without subquery

Fri, 2018-11-09 18:06
I have 4 tables <code>table item_values (item_key, item_value) table filter_model (filter_key) table item_includes (include_key, filter_key_fk, item_key, include_value) table item_excludes (exclude_key, filter_key_fk, item_k...
Categories: DBA Blogs

sum() at each level in hierarchial query

Fri, 2018-11-09 18:06
consider the following query select level,sys_connect_by_path(last_name,'\') name,salary from employees start with last_name='King' connect by prior employee_id=manager_id is it possible to get the result like name sum_of_all_l...
Categories: DBA Blogs

Date in comparison with string

Fri, 2018-11-09 18:06
Hi Tom, I have a table named RTBS_SUBS_EVENT and there is a date column named expiry_date. expiry_date is a partitioned column too. a column named ID and it's indexed. NLS_DATE_FORMAT of the database is 'DD-MON-RR'. I run this query and th...
Categories: DBA Blogs

Join elimination

Fri, 2018-11-09 18:06
Hello All, While making some tests using LiveSQL, which is Oracle 18c, I found a strange behavior regarding join elimination. That is, join elimination DOES happen even if the constraints are DEFERRED ! My LiveSQL test script can be found he...
Categories: DBA Blogs

Is there any point backing up flashback 'flb' files?

Fri, 2018-11-09 18:06
Hi, Is there any point/benefit in backing up flb files to tape using an OS utility? Or, to put it another way: would backing up flb files just be a waste of tape? Thanks
Categories: DBA Blogs

Sorting -- sort a string like a number.

Thu, 2018-11-08 23:46
I have a alphanumeric column in the database. It contains only numeric values.Is it possible to do a numeric sort directly by a sql command like "order by <column>"
Categories: DBA Blogs

Blocks read using index vs. full table scan

Thu, 2018-11-08 23:46
Hi Tom, I have a 3 part question about performance... 1) The first question is sort of a introductory summary question. 2) Then I have an example ending with a question. 3) Finaly I have a very specific 'important' question. Why is it some...
Categories: DBA Blogs

Do we know for sure from the trace file, that the application uses transactions on the database?

Thu, 2018-11-08 05:26
We got deadlock. In the trace file there is the deadlock graph: <code> Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds wai...
Categories: DBA Blogs

Using REGEXP to find ten consecutive digits with possible separators

Thu, 2018-11-08 05:26
I HAVE THIS FREE FLOWING DATA FIELD IN A VARCHAR2 COLUMN THAT I AM CLEANING UP. I WOULD LIKE TO GET THOSE VALUES THAT HAVE A TOTAL OF 10 DIGITS IN ALL AND MIGHT CONTAIN THESE CHARACTERS : . OR - OR / OR ( ) CREATE TABLE E ( PHONE VARCHAR2(5...
Categories: DBA Blogs

Oracle Sequence vs GUID

Thu, 2018-11-08 05:26
We're developing a new system in for the company with .NET and Oracle Database 12cR2 using RAC. It basically does the following: receives invoices in XML format, then validates it according to business rules. A requirement is to give to the clien...
Categories: DBA Blogs

Pages