Tom Kyte

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

How to insert the data using sql*loader by CSV file which contain comma as separator and comma present at column value

Mon, 2016-11-14 20:06
Hi Connor, I have an issue with sql*loader during loading below CSV file I have a csv file with below data:- Column names:- empid,empname,address,salary,deptn0 CSV file data:- 1123,Swarup,PO Box 42,1407 Graymalkin Lane,Salem Center, N...
Categories: DBA Blogs

Data Guard Log Apply method

Mon, 2016-11-14 20:06
I have a primary and a standby database which is running in maximum performance mode and LGWR ASYNC has been set for the same in Primary. Platform - Linux and Version - 12c This is regarding the apply process in Standby Database 1. I do not ...
Categories: DBA Blogs

Sql statistics per execution

Mon, 2016-11-14 20:06
Hi Tom, Is there a way to find cpu_time, db_time, physical_read_requests, physical_write_requests...etc per execution basis ? Say I run a particular SQL multiple times with different bind values. I'm interested in seeing sql with bind variables ...
Categories: DBA Blogs

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

Pages