Tom Kyte

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

Oracle Tracing with Bind Variables

Mon, 2016-11-14 20:06
Hi , I enabled tracing on the particular session in oracle database by using "dbms_system.set_sql_trace_in_session" and i am not enabled to trace back the binding variables associated with insert statements . Below is the sample statement: i...
Categories: DBA Blogs

Validation procedure

Mon, 2016-11-14 01:46
How to write a validation procedure for three conditions which are dependent on each other . first condition will be for country zone in that there will be condition for branch for specific date range .
Categories: DBA Blogs

Chapter 12-user management

Mon, 2016-11-14 01:46
I am trying to create a role, but every time i type it in on sql plus, i get an error saying insufficient privileges. I was wondering what am i doing wrong. I have tried typing in CREATE ROLE PURCH_ROLE_1; and CREATE ROLE PURCH_ROLE_1 IDENTIFIED BY c...
Categories: DBA Blogs

Multi Table Insert with Differing Exists on Destination Tables

Mon, 2016-11-14 01:46
Is it possible to write a "Insert all into <table_1> ( col_1,col_2,col_3 ) values ( col_a,col_b,col_c) into <table_2> ( col_1,col_2,col_3 ) values ( col_a,col_b,col_c) into...
Categories: DBA Blogs

Data Loading with APEX - How to avoid upload of null values

Mon, 2016-11-14 01:46
Hi guys, I'm developing an application wich has a data loading wizard section. Users can upload several CSV files where some of the columns values are checked against a reference (I used Table Lookup for that). Problem is that I just realized tha...
Categories: DBA Blogs

Log File Sync Wait

Mon, 2016-11-14 01:46
Hi Tom, 1 ) As per my understanding, Log-File-Sync wait is the duration, after which I receive an acknowledgement that my commit is successful. In my database the only prominent issue I see is huge log-file-sync waits. 2 ) There are 32 cpu and ea...
Categories: DBA Blogs

Populate varchar2 collection from object type

Sat, 2016-11-12 13:06
I get the following error when I execute the code listed below - <b>ORA-21700: object does not exist or is marked for delete</b> The example is trivial the sake of illustration, but in real-life, I would be handing sets of several thousands...
Categories: DBA Blogs

Connection fail with differente permissions on a network folder.

Sat, 2016-11-12 13:06
Hi, Tom We have a .NET application in a network path and when the user only have read & execute permission on the folder the connection to the Data Base can't be made, but when the user has also the modify permission the connection can be done. ...
Categories: DBA Blogs

historic undo usage by day( last 90 days)

Fri, 2016-11-11 18:46
I am trying to size my undo tablespace properly( requirement is 24 hours retention for flashback query), So I need historic undo usage per day for last 90 days. and I am using the query below but I suspect that it is giving me wrong answer. Autoexten...
Categories: DBA Blogs

LAST and COUNT values after VARRAY trimming?

Fri, 2016-11-11 18:46
I would like to ask about the values of LAST and COUNT of the varray after operation TRIM. In the documentation is written, that " For varrays, COUNT always equals LAST." [https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collection_method.htm...
Categories: DBA Blogs

Data pump export to multiple mount points

Fri, 2016-11-11 18:46
How to export a schema with size 500GB to different mount points using datapump? Expamle Shema size 500gb Mount point size /disk1 200gb /disk2 200gb /disk3 200gb I need to do export using above mount points. Thanks & Regards Krish...
Categories: DBA Blogs

Modify Nested table without altering the type

Fri, 2016-11-11 18:46
I have nested table like below which is already loaded. <code> create or replace TYPE access_t AS OBJECT ( AccessID VARCHAR2(50), Eligibility char(1) ); / create or replace TYPE Access_tab IS TABLE OF access_t; / c...
Categories: DBA Blogs

Partition file size in Oracle 12c

Fri, 2016-11-11 18:46
Dear Tom, Greetings and thanks for helping community here. I am in process of partitioning and using below logic... 1 MB can store = No Of Rows / Current Table Size(MB) => will give me the number of rows in 1MB Then I can decide how many r...
Categories: DBA Blogs

Use of views to provide consistent extraction of data

Fri, 2016-11-11 00:26
Over the years, I have debated the proper use of views, and, almost without exception, developers are loathe to use them - especially Java developers. The number one reason for their argument is performance. Two part question (Please forget about...
Categories: DBA Blogs

Data base keys

Fri, 2016-11-11 00:26
Hi Tom, I am Oracle Beginner,I know integral constraints.So Please Explain me below concepts 1.What are candidate key,super key,Prime Attributes,non-prime attributes? 2.What is the difference between candidate and unique key? 3.how we can use ...
Categories: DBA Blogs

Auditing logons with V$SESSION.AUDSID in AWR

Fri, 2016-11-11 00:26
Hi Tom, I have got request from an audit company to monitor logon/off events. Of course in this case it would be really silly question and maybe it really is. The trick is that they want to also track program and module (v$session). This information...
Categories: DBA Blogs

Materialized view

Fri, 2016-11-11 00:26
Hi I am creating the Materialized view using COMPLETE REFRESH. how do I see the progress of the operation ? %age complete etc session longops is not showing as it depends on the operation type regards
Categories: DBA Blogs

Flashback Data Archive Limitation -- Please clarify

Fri, 2016-11-11 00:26
Hi Tom, I have observed a limitation with Flashback Data Archive. Once Flashback Data Archive is disabled for a table, all the information in History table until that point is lost. Once Flashback Data Archive is enabled again for the same table, ...
Categories: DBA Blogs

Trigger not Dropped from Recyclebin

Thu, 2016-11-10 06:06
Hi, On one of our 11g databases we are not able to purge an object from the recyclebin: sys@DEVDB11:SQL> select count(*) from dba_recyclebin; COUNT(*) -------------- 1320 sys@DEVDB11:SQL> purge dba_recyclebin; DBA Rec...
Categories: DBA Blogs

Using with clause and double function in where clause causes non joined rows to be processed

Thu, 2016-11-10 06:06
Hi, I seem to have stumbled upon Oracle behaviour I can not explain, so I've come to the magical place called Ask Tom for guidance. I have created the following example to make it as short as possible while still reproducing my problem: 2 tabl...
Categories: DBA Blogs

Pages