AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 8 min 59 sec ago

Issue with shared pool and query performance

Wed, 04/07/2021 - 00:00
Hello, Ask TOM Team. I have an application (is like a report tool) that reads data from a 18c Oracle Database. One day the results return quick but a couple days after it gets slow. When I execute "ALTER SYSTEM FLUSH SHARED_POOL" command the the results return quick again. What could be the problem? Thanks in advanced. Regards,

Need help with regular expression for address replacements

Wed, 03/31/2021 - 00:00
I have a recursive query as below: <code>with addresses as ( select cust_id,address addr from ( select 10 cust_id,'9 Help Street, Level 4' address from dual union all select 11 cust_id,'22 Victoria Street' address from dual union all select 12 cust_id,'1495 Franklin Str.' address from dual union all select 13 cust_id,'30 Hasivim St.,Petah-Tikva' address from dual union all select 14 cust_id,'2 Jakaranda St' address from dual union all select 15 cust_id,'61, Science Park Rd' address from dual union all select 16 cust_id,'61, Social park road.' address from dual union all select 17 cust_id,'Av. Hermanos Escobar 5756' address from dual union all select 18 cust_id,'Ave. Hermanos Escobar 5756' address from dual union all select 19 cust_id,'8000 W FLORISSANT Ave.' address from dual union all select 20 cust_id,'8600 MEMORIAL PKWY SW' address from dual union all select 21 cust_id,'8200 FLORISSANTMEMORIALWAYABOVE SW' address from dual union all select 22 cust_id,'8600 MEMORIALFLORISSANT PKWY SW.' address from dual ) t1 ), replacements as ( select id,to_str,from_string from_str from ( select 1 id,'St' to_str,'Street' from_string from dual union all select 2 id,'St' to_str,'St' from_string from dual union all select 3 id,'St' to_str,'Strit' from_string from dual union all select 4 id,'St' to_str,'Str' from_string from dual union all select 5 id,'Rd' to_str,'Rd.' from_string from dual union all select 6 id,'Rd' to_str,'road' from_string from dual union all select 7 id,'Av' to_str,'Av.' from_string from dual union all select 8 id,'Av' to_str,'Ave' from_string from dual union all select 9 id,'Av' to_str,'Avenue' from_string from dual union all select 10 id,'Av' to_str,'Aven.' from_string from dual union all select 11 id,'West' to_str,'W' from_string from dual union all select 12 id,'South West' to_str,'SW' from_string from dual ) t2 ), r(cust_id,addr,test_addr,l) as ( select cust_id,addr,regexp_replace(addr,'(^|\W)' || from_str || '(\W|$)','\1' || to_str || '\2') test_addr, id - 1 from addresses, replacements where id = (select count(*) from replacements) union all select cust_id,addr,regexp_replace(test_addr,'(^|\W)' || from_str || '(\W|$)','\1' || to_str || '\2') test_addr, l - 1 from r, replacements where id = l ) select cust_id,addr,test_addr,l from r where l=0 ;</code> PRESENT OUTPUT: <code>cust_id addr test_addr 10 9 Help Street, Level 4 9 Help St, Level 4 11 22 Victoria Street 22 Victoria St 12 1495 Franklin Str. 1495 Franklin St. 13 30 Hasivim St.,Petah-Tikva 30 Hasivim St.,Petah-Tikva 14 2 Jakaranda St 2 Jakaranda St 15 61, Science Park Rd 61, Science Park Rd 16 61, Social park road. 61, Social park Rd 17 Av. Hermanos Escobar 5756 Av Hermanos Escobar 5756 18 Ave. Hermanos Escobar 5756 Av Hermanos Escobar 5756 19 8000 W FLORISSANT Ave. 8000 West FLORISSANT Av 20 8600 MEMORIAL PKWY SW 8600 MEMORIAL PKWY South West 21 8200 FLORISSANTMEMORIALWAYABOVE SW 8200 FLORISSANTMEMORIALWAYABOVE South West 22 8600 MEMORIALFLORISSANT PKWY SW. 8600 MEMORIALFLORISSANT PKWY South West.</code> Query not working as expected for cust_id like 16,18,22. in cust_id dot is there after road but still it changes to Rd. I need 2 queries...one with exact match including dot and the other sql having match with dot or without dot. The expect out put for first sql with exact match: <code>cust_id addr test_addr 10 9 Help Street, Level 4 9 Help St, Level 4 11 22 Victoria Street 22 Victoria St 12 1495 Franklin Str. 1495 Franklin St. 13 30 Hasivim St.,Petah-Tikva 30 Hasivim St.,Petah-Tikva 14 2 Jakaranda St 2 Jakaranda St 15 61, Science Park Rd 61, Science Park Rd 16 61, Social park road. 61,...

Automatically update the previous end_date column when new record inserted

Thu, 03/18/2021 - 00:00
Hi, I am new to Apex and I would like to add a dynamic action to automatically update the old end_date column when new record inserted. The idea is, for the new record, whatever we put in start_date, same date will automatically assign to previous record end_date. For Example Business_Name | Business_id | City_name | City_id | Start_date | End_Date ABC 1000 NY 20 03-10-2021 12/31/9999 When user create another record for same business name but with different city, the end_date from previous record set to the start_date for new record. Business_Name | Business_id | City_name | City_id | Start_date | End_Date ABC 1000 NY 20 03-10-2021 03-15-2021 --Old Record ABC 1000 Dalas 30 03-15-2021 12/31/9999 --New Record IS there any way that i can achieve this without triggers?

Setting bind variables in SQL*Plus

Thu, 03/18/2021 - 00:00
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/VARIABLE.html#GUID-B4A0DAA3-B6E0-42F7-89B0-EF9C41F02FA3 I read the followings at the above link The following example illustrates assigning a value to a variable for input binding: <code>VARIABLE tmp_var VARCHAR2(10)=Smith</code> The following example illustrates an alternate method to achieve the same result as the previous example: <code>VARIABLE tmp_var VARCHAR2(10) VARIABLE tmp_var=Smith EXECUTE DBMS_OUTPUT.PUT_LINE(:tmp_var)</code> My problem: When I tried to run above commands to create these variables in sqlplus.exe, Oracle did not create them, I am a newbie I even tried to run these command in Oracle live too but couldn't succeed, I was just executing them as Oracle instructed me to do. could you please guide me what wrong I am committing. Regards, Anna

impdp and remap_data doubts

Wed, 03/17/2021 - 00:00
Dear AskTom followers, Let's consider following DWH scenario. I want to use expdp/impdp as methods for populating stage area in DWH. Tables in stage area have the same structure as business tables, except column int_date date, where we catch the date when the data is valid. So, simple example, let's use schemas hr and hr_stage. <code>conn hr_stage hr_stage@PDB1> create table int_employees 2 as select * from hr.employees 3 where 1=0; Table created. hr_stage@PDB1> hr_stage@PDB1> alter table int_employees add (int_date date ); Table altered. hr_stage@PDB1> hr_stage@PDB1> create or replace 2 package pkg_date 3 as 4 function f_int_date (p_int_date in date) return date; 5 end; 6 / Package created. hr_stage@PDB1> hr_stage@PDB1> hr_stage@PDB1> create or replace 2 package body pkg_date as 3 function f_int_date (p_int_date in date) return date as 4 begin 5 return p_int_date; 6 end; 7 end; 8 / Package body created. -- now, conn as hr hr@PDB1> host expdp hr/hr@pdb1 directory=DIR_HOME dumpfile=imp_cli.dmp TABLES=employees CONTENT=DATA_ONLY Export: Release 19.0.0.0.0 - Production on Wed Mar 17 15:58:53 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@pdb1 directory=DIR_HOME dumpfile=imp_cli.dmp TABLES=employees CONTENT= DATA_ONLY Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . exported "HR"."EMPLOYEES" 17.08 KB 107 rows Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HR.SYS_EXPORT_TABLE_01 is: /home/oracle/imp_cli.dmp Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Wed Mar 17 15:59:02 2021 elapsed 0 00:00:08 -- So table hr.empoyees is exported. Now, let's connect as hr_stage and try to import table employees into the table hr_stage.int_employees -- We want to populate as sysdate-1, using hr.pkg_date.f_int_date(sysdate-1) -- As the documentation says, it is possible using REMAP_DATA parameter using pkg.function conn hr_stage hr_stage@PDB1> host impdp hr/hr@pdb1 directory=DIR_HOME dumpfile=imp_cli.dmp remap_schema=hr:hr_stage REMAP_TABLE=hr.employees:hr_stage.int_employees CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND remap_data=hr_stage.int_employees.int_date:hr_stage.pkg_date.f_int_date(sysdate-1) Import: Release 19.0.0.0.0 - Production on Wed Mar 17 16:01:53 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39001: invalid argument value ORA-39232: invalid remap function: HR.PKG_DATE.F_INT_DATE(SYSDATE-1)</code> Not clear what is wrong. Thanks, Dusan

PivotImpl and FLOAT data type

Wed, 03/10/2021 - 00:00
When playing with the well known PivotImpl and my own derivate of it, I discover a strange behavior with <b>OCDITableDescribe</b> and <b>OCDITableStart</b> when the query contains a FLOAT column: while the <b>FLOAT</b> is (correctly) detected as <b>NUMBER</b> in <b>ODCITabeDecribe</b> implementation of PivotImpl: the <b>CASE desc_tab(i).col_type</b> returns 2... the <b>CASE type_code</b> in <b>ODCITableStart</b> returns 4 ! (and 4 is not documented anywhere as far as I was able to search in the doc...). And of course the outside call fails with a ORA-06595: CASE not found while executing CASE statement. When looking a the generated TYPE "ST000..." for the OBJECT, it contains a <b>FLOAT(126)</b>: how possible if the <b>CASE</b> in <b>ODCITableDescribe</b> defines it as <b>DBMS_TYPE.TYPECODE_NUMBER</b>... ???? One could think just add a "<b>WHEN 4</b>" in the various <b>CASE</b> in Describe, Prepare, Fetch... Well first in Describe it never returns 4 but 2 for NUMBER of which FLOAT is a subtype, then in Prepare if you add a <b>WHEN 4</b> and call <b>dbms_sql.define_column</b> with a "<b>CAST(NULL as NUMBER)</b>" it pass this step but you still get a problem in Fetch: in the "<b>WHEN 4</b>" you may add try to do a <b>outset.setnumber</b>, another ERR is raised: ORA-22626, ORA-06512 "Type Mismatch while constructing or accessing OCIAnyData Cause: Type supplied is not matching the type of the AnyData". So except rewriting the queries (views, ...) to CAST AS NUMBER all the met FLOAT... (I don't control the data model generated by a 4G...) I don't found any solution yet. Any idea?

Delete trigger for record that does not exist

Wed, 03/10/2021 - 00:00
Hello, I want to write a delete trigger. The requirement is:- 1) There is a delete statement for a single record that does not exist. <code>Delete from t1 where pk = 'myuser'</code> 2) convert the delete to the uppercase value of the field old.pk <code>Delete from t1 where pk = 'MYUSER'</code> The problem is a row level delete will not fire as there is no record of myuser.

cursor

Thu, 03/04/2021 - 00:00
I have a database and I want to print the following, The last 10 rows The first 10 rows The largest zipcodes number The smallest zipcodes number This is the code I used. import pymysql db = pymysql.connect(host="192.168.0.180",port=4000,user="maxuser",passwd="maxpwd") cursor=db.cursor() cursor.execute("SELECT * FROM zipcodes_one.zipcodes_one where <= MAX_ROWS") results=cursor.fetchall() for result in results: print (result) Please help Thank you

Regexp_replace or replace function to replace every occurance of matching pattern

Thu, 02/25/2021 - 00:00
Hi Tom, Thanks in advance. I am trying to replace naked decimal with '0.' Here is the example. String: '.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or .45' Every occurrence of naked decimal point should be replaced with '0.' resulting in '0.45ML-0.91ML-25MCG/0.5ML-0.9ML(0.3ML)-25.5ML or 0.45'. Please note 25.5 in the string is not naked decimal and remains as is. I tried to achieve using replace function but am not totally confident of the solution. <code>with str_rec as ( SELECT '.45ML-.91ML-25MCG/.5ML-.9ML(.3ML)-25.5ML or .45' str from dual ) select case when substr(str, 1, 1) = '.' then regexp_replace(replace( replace( replace( replace( replace( str, ' .', ' 0.'), '/.', '/0.'), '\.', '\0.'), '(.', '(0.'), '-.', '-0.'), '[.]', '0.', 1, 1) else replace( replace( replace( replace( replace( str, ' .', ' 0.'), '/.', '/0.'), '\.', '\0.'), '(.', '(0.'), '-.', '-0.') end, regexp_count(str, '[.]', 1, 'i') from str_rec;</code> Can we achieve this using regexp_replace or is there a better way to do this. Thanks

uninitialized collections

Tue, 02/16/2021 - 00:00
Hi, i have de following error on this code: <code>--Exercici 1 Create type Adreca_T As Object( carrer varchar2(80), numero number(3), poblacio varchar2(80), codiPostal number(5) ); Create type E_Empleat_T As Object( Nif varchar2(9), Nom varchar2(50), adreca Adreca_T, dataNaixement date, sou number(5), cap REF E_Empleat_T ); create table TaulaEmpleats of E_Empleat_T; insert into TaulaEmpleats values ('38234567A','Josep Verdu Bargallo', Adreca_T('Estanislau Figueres',25,'Tarragona',43002), to_date('12/12/1973','dd/mm/yyyy'),4500,null); insert into TaulaEmpleats values ('39311345X','Pere Garcia Llorens', Adreca_T('Blanca dAnjou',45,'Tarragona',43002), to_date('05/07/1980','dd/mm/yyyy'),1000,(select REF(E) from TaulaEmpleats E where nif = '38234567A')); --Exercici 2 CREATE TYPE telefons_T is VARRAY(5) of varchar2(10); Create or replace type E_Empleat2_T As Object( Nif varchar2(9), Nom varchar2(50), adreca Adreca_T, dataNaixement date, sou number(5), cap REF E_Empleat2_T, Telefon Telefons_t, member procedure afegirTelefon(Telefon in varchar2), member procedure modificarTelefon (telefonold in varchar2, telefonnew in varchar2) ); drop table TaulaEmpleats; create table TaulaEmpleats of E_Empleat2_T; insert into TaulaEmpleats values ('38234567A','Josep Verdu Bargallo', Adreca_T('Estanislau Figueres',25,'Tarragona',43002), to_date('12/12/1973','dd/mm/yyyy'),4500,null,null); insert into TaulaEmpleats values ('39311345X','Pere Garcia Llorens', Adreca_T('Blanca dAnjou',45,'Tarragona',43002), to_date('05/07/1980','dd/mm/yyyy'),1000, (select REF(E) from TaulaEmpleats E where nif = '38234567A'),null); --Exercici 3 Create or Replace Type body E_Empleat2_T as member PROCEDURE afegirTelefon (telefon VARCHAR2) as taula_af telefons_T; numero number(5); begin select telefon into taula_af from TaulaEmpleats where nif =SELF.Nif; numero:=taula_af.Count; taula_af.EXTEND(); taula_af(numero+1):=Telefon; update TaulaEmpleats set telefon=taula_af where nif =SELF.Nif; end afegirTelefon; member PROCEDURE modificarTelefon (telefonold VARCHAR2, telefonnew VARCHAR2) as taula_af telefons_T; i number(5); fi boolean; begin select telefon into taula_af from TaulaEmpleats where nif =SELF.Nif; i:=1; fi:=false; while (i<=taula_af.COUNT and not fi) loop if (taula_af(i) = telefonold) then taula_af(i):=telefonnew; fi:=true; end if; i:=i+1; end loop; Update TaulaEmpleats set telefon=taula_af where nif=SELF.Nif; end modificarTelefon; end; declare x E_Empleat2_T; begin select value(E) into x from TaulaEmpleats E where E.nif = '39311345X'; x.afegirTelefon('659312111'); update TaulaEmpleats E set E= x where E.nif='39311345X'; End;</code> The problem, was in "Exercici 3" The banner Error que empieza en la linea: 1 del comando : declare x E_Empleat2_T; begin select value(E) into x from TaulaEmpleats E where E.nif = '39311345X'; x.afegirTelefon('659312111'); update TaulaEmpleats E set E= x where E.nif='39311345X'; End; Ther error: Informe de error - ORA-06531: Reference to uninitialized collection ORA-06512: at "SYSTEM.E_EMPLEAT2_T", line 8 ORA-06512: at line 5 06531. 00000 - "Reference to uninitialized collection" *Cause: An element or member function of a nested table or varray was referenced (where an initialized collection is needed) without the collection having been initialized. *Action: Initialize the collection with an appropriate constructor or whole-object assignment. Any help will be appreciated.

UTL_HTTP.begin_request

Tue, 02/16/2021 - 00:00
Hi, Could you help-me please. I am tryed to execute a simple command and does not work. SELECT utl_http.request('http://www.oracle.com/') FROM dual * ERROR at line 1: ORA-29273: HTTP request failed ORA-12535: TNS:operation timed out ORA-06512: at "SYS.UTL_HTTP", line 1491 ORA-06512: at line 1 I tryed in cloud anvironment and dos not work too. I am conected as Admin; ORA-01031: insufficient privileges ORA-06512: at "SYS.UTL_HTTP", line 1810 ORA-06512: at "SYS.UTL_HTTP", line 136 ORA-06512: at "SYS.UTL_HTTP", line 1745 ORA-06512: at line 1 I want to do a interface using HTTP to send data to a IoT plataform. when I comment the line where is the comand UTL_HTTP.begin_request don't occur error and show I have access to the package UTL_HTTP... DECLARE l_url VARCHAR2(50) := 'http://api.losant.com'; l_http_request UTL_HTTP.req; l_http_response UTL_HTTP.resp; BEGIN -- Make a HTTP request and get the response. --l_http_request := UTL_HTTP.begin_request(l_url, 'GET', 'HTTP/1.1'); <<----------------- Why don't I have access? --l_http_response := UTL_HTTP.get_response(l_http_request); UTL_HTTP.end_response(l_http_response); END;

Databases producing excessive loads of archive logs

Wed, 02/10/2021 - 00:00
Hi all, Our company is running on oracle database 19c version 19.9 on windows 2012r2 servers. Recently, I am facing an issue with two databases producing excessive loads of archive logs something around 100GB per day. I tried to resize the redo logs though what I achieved was less number of archives but total log size remained the same. Moreover, trying to find out what causes such high loads of logs, it usually appears to be the DBWx, M00x oracle processes producing such huge redo logging even at night. But how could I figure out what's is causing those processes to produce such loads all day, e.g. is it application, a materialised view refreshing frequently etc. As I am newbie, could please someone give me any suggestion how/where/what to look further on that issue, even some documentation to go through? Thanks in advance. Best regards, VangelP

Direct path insert via dblink

Fri, 02/05/2021 - 00:00
Hello Chris, Hello Connor. Is there any way to do a direct path insert when pushing data via dblink? Our DWH currently truncates and loads data into our tables and it takes way too long. I'm not sure what exactly takes it so long, whether it's the load on their servers or network or anything else, but I am certain that generating undo on our side is unnecessary because the tables can be either empty or in a complete state. DWH won't allow us to create a dblink to their base, so we cannot make an insert /*+ append*/ on our side. In other responses I've stumbled into mentions that dblinks are not meant to be a good way to transfer large amounts of data between databases. What is a good way to do that aside from golden gate?

RLS Causing Performance issue

Wed, 02/03/2021 - 00:00
Hi Tom, we have implemented RLS based on client identifier of a session when RLS is applied for a particular client identifier the query is keep on running if i apply same where condition with admin user the query is running fast is this due to RLS or any other reason? is where condition is applied for each row of the table or entire table at once?

Internal function in plan

Wed, 02/03/2021 - 00:00
Hi Team , <code>----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | | | | 1 | SORT AGGREGATE | | 1 | 54 | | | | | | 2 | NESTED LOOPS | | 3 | 162 | 4 (0)| 00:00:01 | | | | 3 | NESTED LOOPS | | 3 | 162 | 4 (0)| 00:00:01 | | | | 4 | PARTITION HASH SINGLE | | 3 | 108 | 3 (0)| 00:00:01 | KEY | KEY | |* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE1 | 3 | 108 | 3 (0)| 00:00:01 | ROWID | ROWID | |* 6 | INDEX RANGE SCAN | IDX_TABLE1 | 27 | | 1 (0)| 00:00:01 | KEY | KEY | | 7 | PARTITION HASH ALL | | 1 | | 1 (0)| 00:00:01 | 1 | 64 | |* 8 | INDEX RANGE SCAN | IDX_TABLE2 | 1 | | 1 (0)| 00:00:01 | 1 | 64 | |* 9 | TABLE ACCESS BY LOCAL INDEX ROWID | TABLE2 | 1 | 18 | 1 (0)| 00:00:01 | 1 | 1 | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(("TABLE1"."PART_T_TYPE"=:SYS_B_3 AND "TABLE1"."DFLG"=:SYS_B_4 AND "TABLE1"."PFLG"=:SYS_B_5)) 6 - access("TABLE1"."AIC"=:1) 8 - access("TABLE1"."TRIAN_ID"="TABLE2"."TRIAN_ID") 9 - filter((INTERNAL_FUNCTION("DEL_ID") AND "TABLE2"."COL_ID"=:2))</code> Need to know cause of internal function for DEL_ID column <code>select count(*) from TABLE1 a ,TABLE2 b where a.train_id= b.train_id ===> datatype is same for both joining columns and a.aic = 'AaaaaaQWESQ' AND (DEL_ID) in ('OOO','QQQ')====> causing internal function , datatype is varhcar2(10) and a.part_t_type ='L' and a.dflg='N' and a.pflg='Y' and a.col_ID=b.col_ID and a.col_ID= 'QWE'</code> manual execution : select count(*) from TABLE2 b where (DEL_ID) in ('OOO','QQQ') actual plan shows not internal function TABLE2 is hash partitioned table b.train_id is partitioning key

Hierarchical CONNECT BY NOCYCLE Issue

Wed, 02/03/2021 - 00:00
Hi In the shared livesql link contains re-producible test case. I'm interested to list all the parent entities for a given child entity. Below is the output I get when there is no cyclical loop condition: <code> SELECT child_entity AS child, parent_entity AS Parent, level, CONNECT_BY_ISCYCLE AS IsLoop, SYS_CONNECT_BY_PATH(child_entity,'\') AS Path FROM entities START WITH child_entity = 'abet' CONNECT BY NOCYCLE child_entity = PRIOR parent_entity ORDER SIBLINGS BY child_entity; CHILD PARENT LEVEL ISLOOP PATH ------ ------ ----- ------ ---------------- abet abe 1 0 \abet abe ab 2 0 \abet\abe ab a 3 0 \abet\abe\ab a null 4 0 \abet\abe\ab\a ab c 3 0 \abet\abe\ab abe d 2 0 \abet\abe abet b 1 0 \abet b null 2 0 \abet\b 8 rows selected. </code> Now when I introduce cyclical loop condition, the output is like below. Duplicate last 2 rows can been seen in output. <code> insert into entities values('abet','a',900); commit; SELECT child_entity AS child, parent_entity AS Parent, level, CONNECT_BY_ISCYCLE AS IsLoop, SYS_CONNECT_BY_PATH(child_entity,'\') AS Path FROM entities START WITH child_entity = 'abet' CONNECT BY NOCYCLE child_entity = PRIOR parent_entity ORDER SIBLINGS BY child_entity; CHILD PARENT LEVEL ISLOOP PATH ------ ------ ----- ------ ---------------- abet abe 1 0 \abet abe ab 2 0 \abet\abe ab a 3 0 \abet\abe\ab a abet 4 1 \abet\abe\ab\a abet b 5 0 \abet\abe\ab\a\abet b null 6 0 \abet\abe\ab\a\abet\b a null 4 0 \abet\abe\ab\a ab c 3 0 \abet\abe\ab abe d 2 0 \abet\abe abet b 1 0 \abet b null 2 0 \abet\b 11 rows selected. </code> I'm expecting to produce an output like below. How to remove the duplicate extra two rows coming in above SQL result. Please share inputs on how I can achieve the same. <code> CHILD PARENT LEVEL ISLOOP PATH ------ ------ ----- ------ ---------------- abet abe 1 0 \abet abe ab 2 0 \abet\abe ab a 3 0 \abet\abe\ab a abet 4 1 \abet\abe\ab\a abet b 5 0 \abet\abe\ab\a\abet b null 6 0 \abet\abe\ab\a\abet\b a null 4 0 \abet\abe\ab\a ab c 3 0 \abet\abe\ab abe d 2 0 \abet\abe </code> Thanks..

SQL For Elimination of Overlaps + MIN and MAX Project Wise

Tue, 02/02/2021 - 00:00
The requirement here is To Eliminate Overlaps but also Take MIN and MAX + Distinct Combinations per CUST_EQP_CONTRACT_NUM(Project) Accordingly. <code>--create table1(EXPORT_TABLE) this is my actual data create table EXPORT_TABLE(CUST_EQP_CONTRACT_NUM varchar2(150),EQP_ANT_BASE_HEIGHT_CALC number,EQP_ANT_TIP_HEIGHT_CALC number); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',166,174); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',167.52,172.48); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168903',168.53,171.48); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',99.69,108.31); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',102.69,111.31); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',231,239); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',231.5,238.5); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',232.5,240.5); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('283874',233.58,239.82); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',98.98,101.02); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',118.98,121.02); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',138.5,161.5); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('507286',148.5,171.5); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('666905',255,263); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('666905',256.64,261.36); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',4.2,5.2); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',3.9,4.9); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',2.1,4.8); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',6.2,6.8); Insert into EXPORT_TABLE (CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC) values ('168999',5.9,6.5);</code> Not I will create another Table just to eliminate source rows and see how many distinct combinations are available for me in One PROJECT(CUST_EQP_CONTRACT_NUM) <code>create table export_table2 as select distinct CUST_EQP_CONTRACT_NUM,EQP_ANT_BASE_HEIGHT_CALC,EQP_ANT_TIP_HEIGHT_CALC from EXPORT_TABLE; ---DESIRED OUTPUT CUST_EQP_CONTRACT_NUM EQP_ANT_BASE_HEIGHT_CALC EQP_ANT_TIP_HEIGHT_CALC 168903 166 174 283874 99.69 111.31 283874 231 240.5 507286 98.98 101.02 507286 118.98 121.02 507286 138.5 171.5 666905 255 263 168999 2.1 5.2 168999 5.9 6.8 --DB VERSION select* from v$version; Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 "CORE 12.1.0.2.0 Production" 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0</code> I Took...

What does it take to support Exadata competently?

Tue, 02/02/2021 - 00:00
GM, We are considering moving to Exadata. I am both excited and nervous about having to support what seems like it would require a skillset beyond what most DBAs have. How much more knowledge beyond being knowledgeable and experienced in RAC and having a good understanding of the Oracle engine is recommended when supporting Exadata? If it is significant, is it recommended that the company should bring in a consultant say to set it up and then support it in production for a 3-6 months for fear that someone without experience will set it up incorrectly or cause outages while managing it? Thanks, John

When Dba_Scheduler_Jobs.Start_Date is not specified, where does Oracle store Job Enabling time?

Thu, 01/28/2021 - 00:00
Hello, I've a question on DBMS_Scheduler functionality: When I create a scheduler job via DBMS_SCHEDULER.CREATE_JOB proc and I don't specify Start_Date value, then job is scheduled for execution as soon as it is enabled which is correct. I want to know where does Oracle store the Job enabling time because same enabling time can be seen in dba_scheduler_job_run_details.Req_Start_Date when job finishes. Basically I want to enabling time for a job which is in SCHEDULED state and Start_date value for that job is NULL. Thanks for sharing knowledge!

A replacement technology in Oracle database for ETL process

Thu, 01/28/2021 - 00:00
Hi, We currently use a third-party tool for the ingestion of files into Oracle database tables. This piece of software is quite expensive and I am investigating the possibility of replicating a small part of its functionality directly within Oracle technologies instead. I appreciate that this question is vague, so I will keep to the high-level features that need to be replicated. If you need more information, I am more than happy to discuss this further. * The ETL tool has its own scripting language that is dynamically generated for each invocation of the ETL process. Most of the dynamic parts of the script are driven from data values within the Oracle database. Once generated, the script is then executed to perform the work. * The ability to read text and binary files including multiple file formats within a single input file. Dynamic file formats are an example of the previous requirement. * To be able to perform optional enrichments by lookup up a key value. * Bulk loading of the transformed data directly into an Oracle table partition. * Multiple executions of the same ETL process may occur simultaneously working on a different set of files. I have experimented with the obvious such as external tables, global temporary tables, SQL, in-memory and PL/SQL pipelined functions. Whilst I can get the correct output for a simple use-case, I cannot compete with the speed of the ETL tool using Oracle technologies. I discounted the use of SQL joins because some of the enrichment lookups are conditional, sometimes based upon another value in the data row being processed. There could be multiple lookups per row and tens of millions of rows in a single file. I discounted the use of SQL in PL/SQL because of the number of context switches this will incur. I am stuck for ideas right now and would really appreciate your input as to whether there is another technique for implementing this type of ETL process directly in SQL or PL/SQL. Many thanks in advance for any help and advice you can give. Mark.

Pages