Tom Kyte

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

oracle commit writes to redo, but not to datafiles

Thu, 2018-01-11 09:46
Hi Tom, I want to verify once the commit the transaction, only writes to redo log, but does not write to data files. How the verify the situation??
Categories: DBA Blogs

Drop an Oracle user (Schema) using JDBC java

Thu, 2018-01-11 09:46
Hi Tom; I am developing a java application using oracle as database. I connect to the database once per program execution using : try { pds = PoolDataSourceFactory.getPoolDataSource(); pds.setConnectionFactoryClassName("oracle.jdbc.pool.O...
Categories: DBA Blogs

SPM accepted plan with different bind variable

Thu, 2018-01-11 09:46
<code>Hi Tom, I have POC on my lab about SQL Plan Management and found some thing that not make sense. My Oracle Database Version is "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production" with a single node, parameter ...
Categories: DBA Blogs

Get rows for first where condition

Wed, 2018-01-10 15:46
we face some issue as <code>select * from t where a is null or b is null or c is null</code> the question is if the query find the first where (a is null) then retrieve it only and not retrieve other where statment(b is null or c is null) can...
Categories: DBA Blogs

Very slow network interaction between Oracle DB and java application

Wed, 2018-01-10 15:46
I have a problem with my Oracle DB network speed. First of all, what's the essence of the problem. There are java application on my computer and Oracle DB on a remote server. Connection speed between them is about 2,5MB/s. I execute in my java app...
Categories: DBA Blogs

Update Statement with Duplicate Rows and Without

Wed, 2018-01-10 15:46
Hi, i created two table emp, emp2 from hr.employees. after that i executed below insert statements now in both tables employee_id is unique. <code>insert into emp select * from emp; --4 times insert into emp2 select * from emp2; --4 time...
Categories: DBA Blogs

Why we should segment advisor to get fragmented space for tablespaces where segment space management is AUTO

Wed, 2018-01-10 15:46
Hi AskTom Team, Normally, to get fragmentation we do (num_rows*avg_row_length) from dba_tables and compare that with bytes in dba_segments to see if there is any fragmentation. But for tablespaces with segment space management "AUTO" oracle re...
Categories: DBA Blogs

Split one string into multiple rows

Wed, 2018-01-10 15:46
Hi , I have a row that may contains upto 6000 char length string in a column. I want to check the length of string and then split the string into smaller string each of length 2000 and insert that into a second table. I want to do that in sql only. ...
Categories: DBA Blogs

Questions on DBMS_REDACT

Wed, 2018-01-10 15:46
Our applications works with ADF as UI and Oracle Database 12c in the Backend. As a regulation requirement,we have to mask few columns of the applications.The Entity Objects in the ADF are based on the Views. We have tried addressing these Col...
Categories: DBA Blogs

To Merge or Not to Merge (Instances) ??

Wed, 2018-01-10 15:46
Should we follow our Oracle DBA directive and merge our 5 instances into 1? That's the question!!?? A little background: Our Oracle EPM consultant installed our Oracle OBIEE and EPM apps (e.g. Hyperion Planning, HFM, DRM, FDMEE etc) using 5 ...
Categories: DBA Blogs

Identify free space gaps in a datafile

Wed, 2018-01-10 15:46
Oracle 11.2.0.4 12.1 and 12.2. You recently responded to a thread about reclaiming space.and suggested moving an object so it will not be at the end of a data file. https://asktom.oracle.com/pls/apex/f?p=100:11:33323285896587::NO::: Is the...
Categories: DBA Blogs

Using escape character --> ORA-01425

Wed, 2018-01-10 15:46
If I use the tilde to escape an underscore in SQL it works fine: select x from ( select 'A_Babc' x from dual union all select 'A$Bxyz' x from dual ) where x like 'A~_B%' escape '~' <b>X ------ A_Babc</b> However, when u...
Categories: DBA Blogs

"BEFORE EACH ROW" Trigger causes table locks without updating primary key column

Wed, 2018-01-10 15:46
Hi Tom, I've found a strange behaviour with our "BEFORE EACH ROW" triggers. In our database we have some tables that are refenrenced very often (50-80 references). All our tables have five common columns PK, CREATIONTIME, CREATIONUSER, UPDAT...
Categories: DBA Blogs

Transfer table from sql server to oracle db

Tue, 2018-01-09 21:26
Hi Team, Thanks for your continues help. I want transfer one table(iccpayment) from sql server to oracle db. And also i want create schedule to this table because everyday user load data to sql server table same also happen in oracle. Pleas...
Categories: DBA Blogs

querying a clob

Tue, 2018-01-09 21:26
Hi Tom I have a table t_request (request_number number, request_detail clob) For example the clob could have a value of <REQUEST_DETAIL><GROUP_TYPE>PR</GROUP_TYPE><GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME><BUS_UNIT_ACRN>S...
Categories: DBA Blogs

Single Json object is splitting into multiple lines when the length is more than 32767.

Tue, 2018-01-09 21:26
Hi Team, Happy New Year. I am connecting to an External database from unix server with that external database details and executing a select statement to fetch data from one of the table in the external database and then the output will be wri...
Categories: DBA Blogs

Dynamically Discover Ref Cursor Attributes Like Column Name, Column Value

Tue, 2018-01-09 21:26
Hi, Is it possible to dynamically discover Ref Cursor attributes like, for example, Column Name? The attached LiveSQL Link contains many details about my question. It is my first time to use LiveSQL Link. If it does not work, please let me kno...
Categories: DBA Blogs

get file names in a directory not working anymore due to SQJ is no longer supported by 12.2 or later version

Tue, 2018-01-09 03:06
Hi Tom, I have been using your script as listed below to get the list of the file names in a directory and insert them into a global temporary table. Everything was working fine until last week I have upgraded my database to 12.2 and the script i...
Categories: DBA Blogs

Will DataPump STOP_JOB or KILL_JOB do data rollback upon table-space issues?

Tue, 2018-01-09 03:06
Half way through data load using impdp (DataPump) got index partition space error. So I did the following: 1. Attached to job. 2. Issued KILL_JOB 3. Restarted DataPump load. After this got duplicates. How do I rollback partial DataPump ...
Categories: DBA Blogs

alter table xxx shrink not reclaiming space

Tue, 2018-01-09 03:06
Dear Ask Tom Team, I hope you had wonderful vacations and happy new year!!! I have question related to Table's Space Reclaiming using "shrink" command. There are some fragmented tables in our DB. I found their current size and wasted space u...
Categories: DBA Blogs

Pages