Tom Kyte

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

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

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

Pages