Tom Kyte

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

removing control characters from text

Sun, 2018-11-18 22:06
Is there a routine in Oracle that can test for and remove unwanted characters in a text string, ie control characters?
Categories: DBA Blogs

Migration from 11g to 12c change execution plan(Adaptative plan)

Fri, 2018-11-16 15:06
Hi, we are working on a PeopleSoft Migration and Database too. We're migrating Oracle 11.2.0.3 to 12.2.0.1, so we have an issiue with a PeopleSoft Query. The query on actual database enviroment(11.2.0.3), have a excecution plan with minimal cost ...
Categories: DBA Blogs

pushing predicate into union-all view

Fri, 2018-11-16 15:06
Hi, LiveSQL link: https://livesql.oracle.com/apex/livesql/s/hjml6z0yg45qznob5sebg53vk I have the big table with an index on ID: <code> create table tst1 as select level id, mod(level, 10) code from dual connect by level < 1000000; create...
Categories: DBA Blogs

Nvarchar to Varchar2 conversion (UTF8 to AL32UTF8)

Thu, 2018-11-15 20:46
We are planning to convert all NVarchar fields to Varchar2 fields as we're going to change our character set and since Oracle recommends AL32UTF8 character set encoding. My question is it 100% sure that all characters from Nvarchar (UTF8) can be conv...
Categories: DBA Blogs

sql plan management - difference in defining parameters at system and session level

Thu, 2018-11-15 20:46
Hi Tom, I am very new to performance tuning. there's something that I am unclear about sql plan management. which one is faster - 1. setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE at session level (inside function body) and OPTIMIZE...
Categories: DBA Blogs

Moving Oracle DB from one server to another

Thu, 2018-11-15 20:46
Hi, I am having an Oracle 11g database in an AIX linux server. I am planning to move this to a different server with same OS. I will be using same version of Oracle database in target DB as well. I have multiple schema in source database and in t...
Categories: DBA Blogs

Log DML, DDL and DCL user activity

Thu, 2018-11-15 02:26
Hello, Ask TOM Team. I want to know if there's a straightforward (not using triggers or things like that lol) way to log DML, DDL and DCL user activity on specific objects (12c). I do not know if <b>Database Vault</b> can help me with that. Any Do...
Categories: DBA Blogs

DB link is not working between 2 databases

Thu, 2018-11-15 02:26
Hi, We have 2 databases DB A and DB B. we have created db link between 2 dbs as a2b; in DB A, we have below table_A and data, <code>create table table_a (emp_id number, emp_name varchar2(30)) / insert into table_a values (1,'Test1') / ...
Categories: DBA Blogs

ORA-64610: bad depth indicator with Utl_Call_Stack

Thu, 2018-11-15 02:26
Hi, I have a database in Oracle 12.2.0 There I have deployed a PL/SQL logic which is called from an update trigger. From this logic, following code segment is called to get the format call stack. <code>FUNCTION Format_Stack___ ...
Categories: DBA Blogs

Impact of Altering an Oracle sequence from ORDER to NO ORDER

Tue, 2018-11-13 13:46
I have a table TAX_INFO where the primary key TAX_INFO_ID is generated using a sequence SEQ_TAX_INFO_ID. Below is the sequence definition. It is defined as ORDERED at the moment <code>CREATE SEQUENCE SEQ_TAX_INFO_ID MINVALUE 1 MAXVALUE 999999999...
Categories: DBA Blogs

Manually refresh materialized view in trigger

Tue, 2018-11-13 13:46
Hello, I have a set of MV that are dependant each other and from master tables. All are elligible to be fast-refreshed but for a reason I don't know, in some tables after a delete or update the fast refresh is longer than the complete one. So ...
Categories: DBA Blogs

pragma autonomous in exception

Tue, 2018-11-13 13:46
can you create pragma autonomous in exception handling
Categories: DBA Blogs

Database security

Tue, 2018-11-13 13:46
Hello, i'd like a suggestion about the use case below We have a database with 2 schemas, for the schemas 1 and 2 every objects are full granted <code>grant all on "object_name" to public</code> Each users have default role <code>"Resourc...
Categories: DBA Blogs

Strange behaviour with excecute immediate.

Mon, 2018-11-12 19:26
Hi, Had problems with SQLLive, (500 response), therefore the examples are her. I have a strange behaviour with execute immediate, where it behaves differently from within a PL/SQL procedure than it does when running it standalone. Here is th...
Categories: DBA Blogs

Difference between named sequence and system auto-generated

Mon, 2018-11-12 19:26
Hello, Guys. A new db (12c) will have lots of tables with sequence used as PK. What is the difference between named sequence and system auto-generated in Oracle 12c? What would be the best approach?
Categories: DBA Blogs

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

Pages