Tom Kyte

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

delayed block cleanout

Tue, 2017-01-10 08:06
Hi Tom, Thanks for providing answers in detail for us Though you explained in so many times still I have confusion about this topic,can you clear my question. "A query that reads datablocks that have been updated by a large DML (INSERT/U...
Categories: DBA Blogs

SQLLDR & lob file path

Tue, 2017-01-10 08:06
Is there a way to specify the lob file path? I can not change my .dat files to include the path so I am looking for a way when I load to specify the lob file direcotry. Lob file name was dynamically generated so I can't use CONSTANT either. ...
Categories: DBA Blogs

Bitmap Indexes

Tue, 2017-01-10 08:06
Hi Tom, Good Day I was reading a book written by a popular author (I would prefer not to mention the name of the book and its author). He says: In discussion on bitmap indexes, the following was written: "Concatenated B*tree indexes were ...
Categories: DBA Blogs

unexpected query plan change from index range scan to full table scan

Tue, 2017-01-10 08:06
Our oracle server vision is 11.2.0.1, we found one issue related to query plan change when running stress test, which is unexpected. I have one table with following schema CREATE TABLE JOURNAL ( REVISION_ID NUMBER(20) NOT...
Categories: DBA Blogs

Table Lock During partition import.

Tue, 2017-01-10 08:06
Hi Guys, DB Info :needs to be available 24x7x365. Table Info : 300 GB , Partitioned by range and has Global Indexes. needs to be available 24x7x365. I want to do a character set conversion to UTF-8 in the oldest partitions while the table...
Categories: DBA Blogs

SQL Loader

Tue, 2017-01-10 08:06
Hi, i loading some into table by using sql loader. to uderstand my issue clearly See the Below Example INSERT INTO t1 VALUES (2,'san'thu'); above statement throws error ORA-00917 missing comma. so to insert san'thu below is the corr...
Categories: DBA Blogs

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

Pages