Tom Kyte

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

Oracle memory and processor requirement

Mon, 2017-11-06 08:46
Hello Tom, I'm new in database and just want to understand the memory and proc requirement for a database server. Here if I say that approx. 500-600 concurrent user are going to connect 12C database through application and size of database is ap...
Categories: DBA Blogs

Synchronize data from Oracle Se to Oracle EE

Mon, 2017-11-06 08:46
Dear All, Could anyone share with me either data from Oracle SE can be synchronize to Oracle EE? Regards
Categories: DBA Blogs

Determine whether the given is numeric , alphanumeric and hexadecimal.

Sat, 2017-11-04 21:06
Dear Team, May I know how do we determine the below for a string. 1. If its numeric. 2 . Alphanumeric. 3. Hexadecimal (E.g Mac address). Regards Kalyana Chakravarthy
Categories: DBA Blogs

REG_EXP is a problem

Sat, 2017-11-04 02:46
WHERE email IN ( select regexp_substr('one@gmail.com,two@gamil.com,three@gmail.com,four@gmail.com','[^,]+', 1, level) from dual connect by regexp_substr('one@gmail.com,two@gamil.com,three@gmail.com,four@gmail.com', '[^,]+', 1, level) is not nul...
Categories: DBA Blogs

I need to replace this query with substr and instr

Sat, 2017-11-04 02:46
SELECT REGEXP_SUBSTR(val_PC, '[^, ]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(val_PC, '[^, ]+', 1, LEVEL) IS NOT NULL
Categories: DBA Blogs

Multiple query question

Sat, 2017-11-04 02:46
I have an Argos report that takes a query(s) and output a report based on those entries. My problem is there are 5 possible querys. I trying to use and/or ( I already tried the CASE function) to pulls data base on their entry. The queries are; Recei...
Categories: DBA Blogs

Generating XML files from clob field

Sat, 2017-11-04 02:46
Dear I'd like to know if it's possible to generate several XML files from a clob filed, for instance: A) The clob field is in a XML format. I've just need to run a loop to export every single row in a new XML file. B) These files need to be expo...
Categories: DBA Blogs

samplescheam.xml and samplescheam.dfb are not found in this folder......./assistants/dbca/templates

Sat, 2017-11-04 02:46
Hello Tom, I have installed Oracle DB 12.2 on my Laptop and I am trying to create PDB with SampleScheam. I am following the steps in the following link. http://holowczak.com/installing-sample-schemas-for-oracle-12c-using-the-database-configuration-...
Categories: DBA Blogs

Locking on Hash table partitions

Sat, 2017-11-04 02:46
Hi, I have a data warehouse application running to "Enqueue TX row lock contention waits". Here is the situation: Table has 50 hash partitions on point_id column. the data load process is trying to delete rows using ROWID values, it is locki...
Categories: DBA Blogs

Invalid datetime format after migrated to Oracle 12.2 client

Sat, 2017-11-04 02:46
Hi We recently upgraded to Oracle 12.2. We are not able to use existing scripts to enter data which includes time in format YYYY/MM/DD HH:MM:SS ex: 2017/11/03 17:16:31 using oracle 12.2 client to insert in to Oracle 12.2 server. We are getting error...
Categories: DBA Blogs

Exception in declration section

Sat, 2017-11-04 02:46
create or replace procedure proc_delme is n number:=1/0; begin dbms_output.put_line(n); exception when others then dbms_output.put_line(sqlerrm); end proc_delme; / If I do following then raised error is not ...
Categories: DBA Blogs

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

Pages