Tom Kyte

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

Data Guard

Sat, 2017-02-25 21:26
Hello, Tom Can you explain guaranteed protection mode of the Data Guard? What happens with primary database instance in this mode if standby database is unavailiable? Documentation says in this case all changes to primary is prohibited. If it i...
Categories: DBA Blogs

Load JSON data file into Oracle 12c

Sat, 2017-02-25 21:26
Hi Ask Tom Team, I have a requirement to load JSON file into a table in Oracle database. Have seen some examples using External Table and filling it from a JSON File & then extracting fields through JSON functions to load data into relational ...
Categories: DBA Blogs

Update query

Sat, 2017-02-25 21:26
CREATE TABLE DB_FV_WG ( STORE_ID NUMBER(3,0) Not Null, FV_02 NUMBER, Y_02 NUMBER, YEAR VARCHAR(6), Month_NO NUMBER(2,0), L_Code NUMBER(2,0) ) Insert into DB_FV_WG (STORE_ID,FV_02,Y_02,YEAR,Month_NO,L_Code) values ('111',null,null,2016,...
Categories: DBA Blogs

Logminer

Sat, 2017-02-25 21:26
The v$logmnr_contents view shows the transactions from some tables as "unsupported". It seems to not like some of the column data types... I need to know how is it that in normal RMAN recovery, these transactions are successfully applied to the datab...
Categories: DBA Blogs

Procedure help - privileges

Sat, 2017-02-25 03:06
I am creating a procedure within a schema that has dba access and delete any table privilege. I am deleting from other schemas using this procedure. It gives a compilation error saying the table doesn't exist. Do I need to have direct delete privile...
Categories: DBA Blogs

Best approach / best practices for application integration at the database level

Sat, 2017-02-25 03:06
Hi there, There are two applications, which I'll call Application A and Application P. Both applications are third party products with limited ability to modify the application. However, it's possible to make changes to the database, such as addin...
Categories: DBA Blogs

parameterized view revisited

Sat, 2017-02-25 03:06
Hi Connor and Chris (and welcome Maria), I've read a few entries regarding stored procedures and ref cursor to support parameterized views. However my situation is slightly different. In my case I'm given n tables (TBL_1, TBL_2, ...) each defin...
Categories: DBA Blogs

millions of dup rec on join multiple tables

Sat, 2017-02-25 03:06
Hi Tom, I have one SELECT query , which has join around 10 tables, out of 10 tables 2 of them are big tables and remaining are small tables.These 2 big tables together has 90 laks records. When I execute this query it is throwing me error saying t...
Categories: DBA Blogs

cost linked to DBLink

Sat, 2017-02-25 03:06
Hi Connor/Chris/Maria, My question is I have 2 schemas on 2 different servers Schema A1 and Schema B1. There is a DBlink on Schema B1 to access A1. Now I have a query where I need to access 2 huge tables (each contains 10 million records). My c...
Categories: DBA Blogs

How can I track the execution of PL/SQL and SQL?

Sat, 2017-02-25 03:06
Hi Tom, How can I know what a given user is executing if his status is ACTIVE ? How can I know which PL/SQL blocks or SQL statements are being run by him ? As to SQL statemets, I can join v$session.user# with v$sqlarea.parsing_user_id ( am I reall...
Categories: DBA Blogs

migrating data from non-partitioned to partitioned table with exchange then...?

Sat, 2017-02-25 03:06
Hello, I have to migrating data from one non-partitioned to table into a new range-list composite partitioned. I started with exchange subpartition into P_DEFAULT_MODULES (wich holds all the literal values in subpartition key) without validation...
Categories: DBA Blogs

IMPDP over network link ORA-3916 Schema XXXXX not found error

Fri, 2017-02-24 08:46
Hi there. I'm trying ot migrate database objects between 2 instances via network link. Source database is 11.2.0.3 version Target database is 12.1.0.2 version On both systems exists a schema named TAGDATI with DATAPUMP_EXP_FULL_DATABASE and DATAP...
Categories: DBA Blogs

Alternative to Oracle Wrap Utility

Fri, 2017-02-24 08:46
Hello, We have requirement to release our product at customer location(usually we just deliver processed data to the customer) and allow them to use it for sometime before they are satisfied, hence to secure code/algorithm we need to wrap the code...
Categories: DBA Blogs

Dynamic SQL to execute a function

Fri, 2017-02-24 08:46
Hi, I'm trying to write a function which will accept arithmetic operator(example : mod(100,10)) as input and the result will be from the input arithmetic operation ----- declare l_retval varchar2(200); ...
Categories: DBA Blogs

'Order by' within or outside View, gives same execution plan and results

Fri, 2017-02-24 08:46
==================================================== -- v_product view definition create or replace view v_product select <<column_names>> from t_product order by col_1, col_2; --Below is the exiting code running since many years in product...
Categories: DBA Blogs

Calling SQL file from batch file with return vairables

Fri, 2017-02-24 08:46
Hi.. I have to write a batch file which will call a sql file by passing one parameter. The sql file has to select two columns from a table and return them to the batch file and the batch file should display it on the screen. Can I get some inpu...
Categories: DBA Blogs

Clob and NClob

Fri, 2017-02-24 08:46
Sir, I want to know when I would you a NClob and can not use a Clob ? The character of the database is now US7ASCII. I test using some Chinese and Japanese characters. Looks like I can get back what I inserted on the Clob column, but for NClob col...
Categories: DBA Blogs

Access updated column names in DDL alter trigger

Fri, 2017-02-24 08:46
I have a trigger create or replace TRIGGER audit_trigger_update AFTER ALTER OR CREATE ON SCHEMA WHEN (ORA_DICT_OBJ_TYPE = 'TABLE') BEGIN create_audit_trigger(ORA_DICT_OBJ_NAME); END audit_trigger_update; with calling procedure to re...
Categories: DBA Blogs

INTERVAL PARTITIONING with custom TABLESPACE names

Fri, 2017-02-24 08:46
We have a range partitioned table on daily basis. Every year, we are splitting the MAX partition into 365* days partitions such as: ALTER TABLE txn SPLIT PARTITION p_txn_max AT (to_date('01/02/2017','MM/DD/YYYY')) INTO (PARTITION ...
Categories: DBA Blogs

Update query using case and joins

Fri, 2017-02-24 08:46
<code>Table 1: select * from FV Table 2: select * From DB_FV_W UPDATE DB_FV_W Set FV_02 = (CASE WHEN db.FV_02 IS NULL THEN '0' Else fv.MD END) FROM DB_FV_W d LEFT OUTER JOIN FV f On...
Categories: DBA Blogs

Pages