Tom Kyte

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

Set up email notification for DML query for a table

Tue, 2017-01-10 08:06
Hi Team, My question is : I need to configure email notification for any insert, update, delete query in database table. I have a trigger in place for this . Trigger name is TTT_WNIU_USER_WORKGROUP Target table name is TTT_om_user_workgro...
Categories: DBA Blogs

Difference between Rownum and level in connect by (it is giving different result)

Mon, 2017-01-09 13:46
Hi, Hope your doing great! Can you kindly guide me with the difference between the rownum and level in connect by, I though it will give same result but it is little confusing. Can you please teach me in steps how it works. Thanks! drop tabl...
Categories: DBA Blogs

Check constraint violated while loading JSON doc into DB

Mon, 2017-01-09 13:46
<code>Team, Could you please help me to understand why this JSON document load got failed with check constraint enabled? but this is a Valid JSON document, validated it through http://jsonlint.com/ portal. for time being, we are able to just...
Categories: DBA Blogs

Executing .sql file in Pl/sql procedure

Mon, 2017-01-09 13:46
Hi , I am trying to execute a set of insert statements in a pl/sql block. However these insert scripts vary time to time. Hence i would like to save them in some location as .sql file and execute in pl/sql So i would like to know if it is poss...
Categories: DBA Blogs

issue with the usage of the oracle external table

Mon, 2017-01-09 13:46
Hi tom, I have an oracle external table in my database and i am running my database in linux operating system.I have a directory where i will be placing the (.csv) file and loading into the external table.But there is an issue with the externa...
Categories: DBA Blogs

error in packages

Sun, 2017-01-08 19:26
<code>i have table and packages like this,but iam getting error while executing it.so please help me: CREATE TABLE LOGGING_DATA_HDR ( LOG_ID NUMBER, TABLE_NAME VARCHAR2 (30 CHAR) NOT NULL, PK_DATA VARCHAR2 (500 BYTE...
Categories: DBA Blogs

Need to Know the process & result of my question

Sun, 2017-01-08 19:26
Emp, dept table is present for reference SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO ...
Categories: DBA Blogs

A select query uses Full table Scan. How to force the query using Index Scan option.

Sun, 2017-01-08 19:26
A Select query is using Full table Scan. The execution time takes much long. How to force the query to use index scan option.
Categories: DBA Blogs

How do I call MySQL Stored Procedure from oracle using HS link ?

Sun, 2017-01-08 19:26
How do I call MySQL Stored Procedure from oracle using HS link ? HS link is correct and I can get the tables data just by adding '@' at the end of the table name ,but I can't call the SP same way ! Help please.
Categories: DBA Blogs

External File with CHARACTERSET WE8MSWIN1252 does not create a .bad file (added additional information at the end)

Thu, 2017-01-05 00:06
So, I queried the following to (hopefully) answer what you've asked for - SELECT PRODUCT, VERSION FROM SYS.PRODUCT_COMPONENT_VERSION; NLSRTL 11.2.0.3.0 Oracle Database 11g Enterprise Edition 11.2.0.3.0 PL/...
Categories: DBA Blogs

Deadlock - Missing FK index, but no update to parent PK value

Thu, 2017-01-05 00:06
Hi Tom, Thank you for the wealth of knowledge on this site. I have read many, many threads on this site describing situations similar to what I am about to describe. It's possible as well I am completely off here and this isn't related to FK at all....
Categories: DBA Blogs

Problem with multiconsumer AQ.

Tue, 2017-01-03 11:26
I'm trying to broadcast a message using dbms_aq.enqueue with a queue set to multiple consumers. The consumers on the queue comes and goes, so building up a recipient_list through various SQL on the raw queue information is not an option. I'm stuc...
Categories: DBA Blogs

Update Parent account(s) balance using Child Account(s) Balance

Wed, 2016-12-21 11:46
I would like update the balance of the Parent account(s) using the sum of child account(s). Can you please guide ?
Categories: DBA Blogs

datapump export using DBMS_DATAPUMP package

Mon, 2016-12-19 23:06
I have to export many tables on different schemas in a single dump file, using DBMS_DATAPUMP. If I run this command the export goes fine: expdp fr/fr dumpfile=prova.dmp logfile=prova.log directory=dfr tables=MOD_BASE.PROBE_PROFILE,MOD_DNS.SCENA...
Categories: DBA Blogs

How to connect to the to database using unix termianl

Mon, 2016-12-19 04:46
Hi , I wanted to connect to oracle database using unix terminal. The Idea is that I have installed ubuntu as a virtual machine and I wanted to run procedures using ubuntu and the result of which should be seen in the terminal. Is is possible to...
Categories: DBA Blogs

Problem in EXPDP AND IMPDP with virtual column

Mon, 2016-12-19 04:46
Hey all I have create table with virtual columns like the following CREATE TABLE employees ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), salary NUMBER(9,2), comm1 NUMBER(3), comm2 NUMB...
Categories: DBA Blogs

PLSQL: Best/Alternate way to implement FAST Refresh for better performance

Mon, 2016-12-19 04:46
In my present db implementation, my db does not has any data/table. All the data it gets is from other sources using dblinks and then populate Materialized Views. These MVs in actual being used by my db to serve customer requests. To implement these ...
Categories: DBA Blogs

format disrupted upon using it

Mon, 2016-12-19 04:46
Hi Tom (or Chris or Connor), While i was trying something out i came upon a strange feature when using to_char. I have 2 columns in a with clause, a value and a format. Then I use these 2 in a to_char function. The strange thing is that the for...
Categories: DBA Blogs

Query to format the output

Fri, 2016-12-16 03:26
Hi Team, Hope you are doing well.. I have table like below ACC_NO DATE_OPENED PRODUCT_TYPE =================================== UN1 02-SEP-16 A UN2 02-OCT-16 B UN3 12-DEC-16 C UN4 22...
Categories: DBA Blogs

Inline views

Fri, 2016-12-16 03:26
Hi Chris/Connor, I have written below SQL to get Difference of Debit & Credit sum (DB version we are using is 11g). Problem here is I have used two inline views (with similar joins only difference is Bill_Amt_Sign = 1 & Bill_Amt_Sign = 2). Is ...
Categories: DBA Blogs

Pages