DBA Blogs

REG_EXP is a problem

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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?

Tom Kyte - 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?

Tom Kyte - 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

Tom Kyte - 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)

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Two Talks Accepted for RMOUG Training Days

Bobby Durrett's DBA Blog - Thu, 2017-11-02 14:01

I got two talks accepted for RMOUG Training Days in February. I mentioned these two titles in a earlier post:

  • Python for the Oracle DBA
  • Toastmasters for the Oracle DBA

These two talks are about topics that interest me so I am glad that RMOUG thinks that they are valuable to the conference attendees.

I plan to do the two talks for my DBA coworkers and shorter versions at Toastmasters so I should get some constructive feedback and practice before the conference.

Should be fun. Hope to see you in Denver next February.

My Python posts: url

My Toastmasters posts: url

Bobby

Categories: DBA Blogs

Limitations of REGEXP functions

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs