Tom Kyte

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

Alter table add column on a FDA enabled table - how to avoid the row chain effect without using the 'move' option?

Fri, 2017-11-03 08:26
Hi, We'd like to use FDA on our Oracle db for its bi-temporality feature. So far when we add a column to a table, we also perform the 'alter table T move;' + rebuild indexes, to avoid performance issues and to re-organize the row IDS. But the ...
Categories: DBA Blogs

Why differ inmemory_size in v$im_segments from used_bytes in v$inmemory_area?

Fri, 2017-11-03 08:26
Hi I'm testing In-Memory and my question is why the figures in v$im_segments differ from the used_bytes in v$inmemory_area? I have read a lot of great posts (for example https://blogs.oracle.com/in-memory/what-is-an-in-memory-compression-unit-i...
Categories: DBA Blogs

Advise for Analytics-related Workflow Automation

Fri, 2017-11-03 08:26
Hello, I work in the Analytics department where I support a team of many Data Scientists. We use Oracle Database Enterprise v11.2.0.4 as our back-end database and I have developed several automation using PL/SQL procedures, functions, etc. I...
Categories: DBA Blogs

How Result cache is managed in 12c Pluggable Database (PDB)

Fri, 2017-11-03 08:26
Hi Team, I ma having one scenario, where I am setting up my application in 3 pluggable db instances under single CDB. As per my app requirement, I have to create synonym for dbms_result_cache in all 3 PDBs. As the public synonym for dbms_result_ca...
Categories: DBA Blogs

fetch output (success/failure) status from web service

Fri, 2017-11-03 08:26
Hi, Could you please share any example to fetch web service output (i.e. success/failure) status into oracle PL/SQL procedure? The scenario is as below, we have created a stored procedure which will pass 2 input parameters from those input p...
Categories: DBA Blogs

Merge - unfold records based on conditional join

Thu, 2017-11-02 14:06
Hi Team, Need your help or suggestion on altering a merge statement. I have a below staging table A_TRANSACTION_STAGING which gets merged to main table A_TRANSACTION : A_TRANSACTION_STAGING : <code> TRANSACTION_ID NUMBER REGION_CD ...
Categories: DBA Blogs

SQL Query related to String

Thu, 2017-11-02 14:06
Hi Tom, There is a string 'ascjhsdndfdaja' i want to print only 'a' alphabet from this string there are 3-occurrence of 'a' so i want to print 'aaa' can you please help me this. Your help will be much appriciated. Thanks
Categories: DBA Blogs

trim in sql*plus

Thu, 2017-11-02 14:06
Hi Tom, I have a varchar2(30) field which when displayed on sqlplus, doesn't seem to be trimming the trailing spaces when I use rtim or trim in select stmt: set head off set colsep "," set trim on set wrap off set linesize 800 select part_id...
Categories: DBA Blogs

Limitations of REGEXP functions

Wed, 2017-11-01 19:46
Tom: I am trying to use this function to validate email addresses. I am reading that the regexp functions have a limitation of 512 bytes. Is there a way around this or a better way? Thanks! Mitchell <code>CREATE OR REPLACE FUNCTION is_valid_e...
Categories: DBA Blogs

Index organized table, secondary indexes, and stale guesses

Wed, 2017-11-01 19:46
Hi, Tom Secondary indexes of IOT have physical guesses. However guesses can become stale after many inserts. It is two ways to obtain fresh guesses: 1. Alter index ... rebiuld and 2. ALTER INDEX ... UPDATE BLOCK REFERENCES. 3. What is be...
Categories: DBA Blogs

How to calculate free space in the Database

Wed, 2017-11-01 19:46
How to calculate the actual database size and the free space in the database. I tried below queries <code>select round(sum(bytes)/1024/1024/1024) size_in_gb from dba_data_files; = 1073 GB select round(sum(bytes)/1024/1024/1024) size_in_gb from dba...
Categories: DBA Blogs

Retrieve fields from clob field with envelope and HTML chacaters

Wed, 2017-11-01 01:26
Dear I have tried to parse a clob field located in a Oracle table that contains a envelope and also some HTML characters (&quot, &lt, &gt), as shown below: <?xml version="1.0" encoding="utf-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xm...
Categories: DBA Blogs

Bulk Inserts with CLOB column Slower in Oracle 12c than Oracle 11g

Wed, 2017-11-01 01:26
We are noticing that large table inserts with CLOB is taking more time on 12c than 11g. The inserts are during an online transaction processing where we copy data from one business unit to another. There is one table in particular that is taking cons...
Categories: DBA Blogs

Get "LPX-00242: invalid use of ampersand" from xmltype() even though ampersand is already escaped

Wed, 2017-11-01 01:26
Any idea why I get the error message "LPX-00242: invalid use of ampersand ('&') character (use &amp;)" even though the ampersand in the below test case is already escaped? Tested with same result both in 11g XE, 11.2.0.4 SE, and on apex.oracle.com (w...
Categories: DBA Blogs

Implementing Old style SQL outer join along with condition query to the new style

Tue, 2017-10-31 07:06
Hi, I have SQL outer join query which is written in old format with (+) notation and I am working on its migration to the new presentation and without the (+) notation. I am unable to convert the last line of the query with the outer join condition....
Categories: DBA Blogs

Need to print latin ä charater through Dbms out into apex sql developer ?

Tue, 2017-10-31 07:06
Hi gurus, I am trying to print this character a through apex sql developer but it return me ? instead of this a , any one know how can I achieve this. begin dbms_output.put_line ('http://fam.co/') ; end; if my question is not clear please...
Categories: DBA Blogs

two stage RMAN backups

Tue, 2017-10-31 07:06
Hi team. We have an Oracle instance, which I think is using ASM on SAN LUNs. The SAN is multi-path, multi-fabric with the LUNs are provisioned from a high quality H/A storage array. At the moment the DBAs use RMAN to push backups via MML directly ...
Categories: DBA Blogs

Make a simple audit of table connections

Mon, 2017-10-30 12:46
Dear Tom. I want to know which tables in my Oracle schema are impacted by any software, web application or direct queries. An provider said me : " Make an audit on all tables (and views) of your schemas is not a good idea because your DB will w...
Categories: DBA Blogs

Data Model (Design) Question

Mon, 2017-10-30 12:46
I have a around 2 million assets that send 15 min interval data for a given day (i.e) total of 96 intervals for a given day. What is best database modeling approach. My target RDBMS is Oracle. I'm partitioning by week and for each interval I need ...
Categories: DBA Blogs

ASSM and basicfile LOB

Mon, 2017-10-30 12:46
Hello Tom According to https://docs.oracle.com/database/121/ADLOB/adlob_smart.htm#ADLOB46175 basicfile lob must be stored in a tablespace with segment space manually managed. However, it seems oracle does not prevent user from creating it in ...
Categories: DBA Blogs

Pages