Tom Kyte

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

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

Changing dbms_job to dbms_scheduler

Tue, 2018-01-09 03:06
Hi Tom, I have a requirement where I need to change dbms_job functionality to dbms_scheduler.Regarding that I have few query : 1.The existing dbms_job.submit_job is called within loop which calls certain procedure ex: for I in 1..10 dbms_job.su...
Categories: DBA Blogs

Export application through command prompt

Tue, 2018-01-09 03:06
I have moved some pages from 1 application to another but when exporting application through the application itself. Then some component is not working of the application. So can you provide an alternative way to export application through command p...
Categories: DBA Blogs

ORA-01722: invalid number ORA-06512: in "SYS.DBMS_SQL" - Fetching cursor with bind variables

Tue, 2018-01-09 03:06
Hi, I'm trying do run the following PL/SQL block: <code>DECLARE v_trad_cur CLOB; v_trad_par VARCHAR2 (1000); cur PLS_INTEGER := DBMS_SQL.open_cursor; fdbk NUMBER; retorn VARCHAR2 (1000); vd...
Categories: DBA Blogs

DISTINCT clause and nulls

Mon, 2018-01-08 08:46
I have a table Test_Null with columns A, B and Data Present Inside Table is: SQL>select * from test_null; A B ---------- ------ 1 NULL 2 NULL 3 NULL 4 NULL <u>Query</u> SELECT DI...
Categories: DBA Blogs

ORA-01417 when outer joining many tables in 11g but not 12c

Mon, 2018-01-08 08:46
Hi, I ran this below statement in 12C and 11g Data bases. <code>select * from temp0101 a ,temp0101 b ,temp0101 c where 1 = 1 and a.sno = b.sno(+) and c.sno = b.sno(+);</code> in 12 C it executed successfully but in 11i it throwed below error...
Categories: DBA Blogs

SQLNET.COMPRESSION

Wed, 2017-12-27 04:06
Hi Tom, I would like to get your opinion this new parameter SQLNET.COMPRESSION for network performance and the additional ones (compression level) in 12c, and any recommendations to use it under some circumstances. I know this is a very vague q...
Categories: DBA Blogs

Pages