Feed aggregator

SQL-Profile fundamentally are additional optimizer statistics at the SQL statement level generated by SQL-Tuning-Advisor to fill the gaps of Oracle Optimizer. My question is, can these additional finer optimizer-statistics within profiles, be shared/used by other similar SQL statements. Or is it that only that specific SQL for which the sql-profile was generated benefits?

We have a system in which reference data (about 100 tables) is created and maintained by a set SMEs in a specific database for this purpose. On frequent (by not regular) occasions, the schema is cloned to another schema which acts as a source for distribution to other databases. The reason for the second schema is so those SMEs are not interrupted for the duration of the distribution to 20ish other database/schemas. We have significant problems with the hand-coded and maintained ETL process. For instance, when a new column is added to a reference table, someone must remember to add this to the ETL, and it is not infrequently forgotten. Dependency order of foreign keys can also bollix the process. This distribution is on demand and not continuous, so it doesn't appear that regular database replication would work. Do you have any suggestions? Second question, is it possible for delayed foreign keys and unique constraints to result in a non-acid compliant data condition. Thanx in advance. In response to your request for clarification, yes, the "ETL" is cloning of the base schema to the secondary schema. In this case, there is not transformation; it is a literal clone. There was a time when there was massive transformations, and the term just stuck.

Hello, How to configure TLS for connections from Oracle database to another server (e.g mailing server)? We have an on premises SMTP email sever to which the database pushes the emails to be sent out. All internal emails are working perfectly but external emails (e.g to Gmail) are failing with ORA-29024: Certificate validation failure. The TLS wallet has been created on the database server and the SMTP email sever certificate (certificate are still valid) have been added to this wallet. The code has also been updated with wallet location and wallet password. The mailing team confirmed they did not need our server certificate. At this point we are not sure what we are missing on our setting. Please, we'll appreciate if you can point us to a documentation/video or provide us with advise on how to figure it out. Thank you

Hi, Tom, I do two tests about cursor in ORACLE 19.3C. In test1 why print "x" is normal but print "y" is abnormal ? In test2 why print "y" run half of the way ? I get information from: https://asktom.oracle.com/pls/apex/asktom.search?tag=ora-08103object-no-longer-exists <b>[Test1]</b> <code>SQL> select count(*) from dba_objects; COUNT(*) ---------- 13770 create table tx as select * from dba_objects where rownum < 11; create table ty as select * from dba_objects; variable x refcursor variable y refcursor declare begin open :x for select * from tx; open :y for select * from ty; end; / drop table tx; drop table ty; SQL> print x OWNER OBJECT_NAME .... --------- ---------------- SYS ICOL$ SYS I_USER1 SYS CON$ SYS UNDO$ SYS C_COBJ# SYS I_OBJ# SYS PROXY_ROLE_DATA$ SYS I_IND1 SYS I_CDEF2 SYS I_OBJ5 10 rows selected. SQL> print y ERROR: ORA-08103: object no longer exists no rows selected</code> <b>[Test2]</b> <code>create table tx as select * from dba_objects where rownum < 11; create table ty as select * from dba_objects; variable x refcursor variable y refcursor declare nomeaning tx%rowtype; begin open :x for select * from tx; open :y for select * from ty; fetch :y into nomeaning; end; / drop table tx; drop table ty; SQL> print x OWNER OBJECT_NAME .... --------- ---------------- SYS ICOL$ SYS I_USER1 SYS CON$ SYS UNDO$ SYS C_COBJ# SYS I_OBJ# SYS PROXY_ROLE_DATA$ SYS I_IND1 SYS I_CDEF2 SYS I_OBJ5 10 rows selected. SQL> print y OWNER OBJECT_NAME .... --------- ---------------- ... ... SYS SYS_LOB0000006212C00003$$ ERROR: ORA-08103: object no longer exists 6210 rows selected.</code>

 This video introduces db developer role in Oracle database 23c.

I would like my schema to delete rows automatically after a certain number of days. Therefore, I was planning to use this statement for example: <code> ALTER TABLE MY_TABLE USING TTL 5 days </code> However, I'm unable to find specifics in the TTL documentation about how FK relationships are handled in this scenario. Will TTL automatically ignore these FK relationships when it reaches that date and expire these rows anyway? (OR) Will TTL fail, as a delete would fail, because these FK relationships exist and need to be removed first?

Hi, I would like to create initally deferred constraint for FK as below. The problem is, that the constraint cars_service_car_id_fk is validated after DELETE statement, not at the end of transaction(COMMIT). I don't understand this behavior... <code> DROP TABLE cars; CREATE TABLE cars( car_id INTEGER , name VARCHAR2(20) , CONSTRAINT cars_pk PRIMARY KEY(car_id) ); DROP TABLE cars_service; CREATE TABLE cars_service( service_id INTEGER , car_id INTEGER , CONSTRAINT cars_service_pk PRIMARY KEY(service_id) , CONSTRAINT cars_service_car_id_fk FOREIGN KEY (car_id) REFERENCES cars(car_id) <b>DEFERRABLE INITIALLY DEFERRED</b> ); INSERT INTO cars(car_id, name) VALUES(1, 'Volvo'); INSERT INTO cars_service(service_id, car_id) VALUES(1, 1); COMMIT; DELETE FROM cars WHERE car_id = 1; -- And now Iim getting error to early.. <b> DELETE FROM cars WHERE car_id = 1 Error report - ORA-02292:</b> </code>

I am facing issue of "Pooled connection request timed out" when I upgraded database to 19c and moved the application to cloud(TAS) from VM. There is no fixed scenario when this happens. No code changes has been done except replacing DataAccess.dll to ManagedDataAccess.dll. SGA is set to 2GB. TAS memory is 2GB. Could you suggest me what configurations has to be setup or checked to support pooling, as I am developer and can delegate the same to DBA to have such configurations on database

Hello Chris/Connor, I am trying to use the option MY_WALLET_DIRECTORY in TNSNAMES file in order to avoid having to specify WALLET_LOCATION in SQLNET.ORA file. However, it fails with ORA-01017: invalid username/password; logon denied error. Tracing the SQLNET (from client side) reveals that it never attempts to access wallet when specified as MY_WALLET_DIRECTORY but works fine when used conventionally (as WALLET_LOCATION in SQLNET.ORA file). Can you please help me figure out how to get this working? In MOS note <b>The Impact of the Sqlnet Settings on Database Security (sqlnet.ora Security Parameters and Wallet Location) (Doc ID 1240824.1)</b>, it appears to mention that using MY_WALLET_DIRECTORY is supported (excerpt below) <code>#8. Client settings. OCI Clients are using the same sqlnet.ora and wallet settings as the database server. However, a very good hint to use, when multiple wallets should be used by the same client and using multiple TNS_ADMIN environment variables can become a real pain (one example would be when using ODP.NET applications requiring multiple SSL certificates to logon to multiple services, especially on application servers), then this could be easily solved using a tnsnames.ora syntax similar to the one below: <tns_alias> = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = <machine_name>)(PORT = <port_number>)) (CONNECT_DATA = (SERVICE_NAME = <service_name>) ) (SECURITY = (MY_WALLET_DIRECTORY = <some_wallet_directory>)))" </code> Below is my attempt First, to prove that the wallet is set up correctly and WALLET_LOCATION mentioned in SQLNET.ORA. <code>C:\Users\JoeBlog\TNSHome\ScottWallet>copy ..\sqlnet.ora . 1 file(s) copied. C:\Users\JoeBlog\TNSHome\ScottWallet>set TNS_ADMIN=C:\Users\JoeBlog\TNSHome\ScottWallet C:\Users\JoeBlog\TNSHome\ScottWallet>tnsping DB_SCOTT TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 11-JUN-2021 15:02:41 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: C:\Users\JoeBlog\TNSHome\ScottWallet\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbscan.oracle.world)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbserv.oracle.world.com)) (SECURITY = (MY_WALLET_DIRECTORY = C:\Users\JoeBlog\TNSHome\ScottWallet))) OK (70 msec) C:\Users\JoeBlog\TNSHome\ScottWallet>sqlplus /@DB_SCOTT SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 11 15:02:51 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Fri Jun 11 2021 10:03:06 +01:00 Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.10.0.0.0 SQL> show user USER is "SCOTT" SQL> exit Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.10.0.0.0 </code> Now, when I try to use only MY_WALLET_DI...

I will preface this with not having had tested SQL*Plus scripts on Windows utilizing script arguments in a number of years so I can't categorically state when this stopped working on said platform. That being said, the issue described does not occur on any *nix system. Additionally, this is running the SQL*Plus from the Instant Client not the Full Client. For Windows-based SQL*Plus scripting if you plan to use script arguments such as (being executed as "sqlplus <username>/<password>@<tns_alias> @test.sql 1"): <code> select to_number( '&1' ) from dual; </code> You get the following: <code> SQL> select to_number( '&1' ) 2 from dual; select to_number( '&1' ) * ERROR at line 1: ORA-01722: invalid number </code> as it is not translating the '&1' to mean the first script argument. If you add "set define '&'" to the options setting up the SQL*Plus environment then it runs as expected: <code> old 1: select to_number( '&1' ) new 1: select to_number( '1' ) TO_NUMBER('1') -------------- 1 1 row selected. </code> My question is why is it only mandatory on Windows to purposefully set DEFINE to its default value ('&')? I struggled with this for many weeks as there wasn't even a thought in my head I needed to set DEFINE to its default value as it makes no sense to do so. Only after re-reading the SQL*Plus User's Guide for the thousandth time did I say what the heck and as soon as I did it worked. Either this needs to be fixed or the documentation needs to address this because in theory SQL*Plus scripts, short of shelling out with HOST, should be portable.

Hola! en Apex 23.1 Estoy fallando al intentar ejecutar un metodo GET luego de crear desde Componentes Compartidos >> Origenes de Datos una conexion a un servicio web http (no seguro) publico. El metodo GET lo pruebo desde la opcion de edicion. Y lo he probado tambien desde un boton en mi pagina. Aclaro que los servicios https que he creado me funcionan correctamente. Necesito saber si hay que realizar alguna configuracion para estos casos.. si me pueden aportar alguna documentacion.. se los agradezco!! Estoy trabajando en https://apex.oracle.com/pls/apex/r/apex/workspace Dejo el error: is_internal_error: false ora_sqlcode: -403 ora_sqlerrm: Forbidden component.type: APEX_APPLICATION_PAGE_PROCESS component.id: 37623776800238671091 component.name: Nuevo error_backtrace: ----- PL/SQL Call Stack ----- object line object handle number name 0x3709919170 590 package body APEX_230100.WWV_FLOW_INVOKE_API_PROCESS.INVOKE_API_REST 0x3709919170 639 package body APEX_230100.WWV_FLOW_INVOKE_API_PROCESS.INVOKE_API_PROCESS 0x3256fedff0 1295 package body APEX_230100.WWV_FLOW_PROCESS_NATIVE.EXECUTE_PROCESS 0x35e89acdf8 3443 package body APEX_230100.WWV_FLOW_PLUGIN.EXECUTE_PROCESS 0x34524835b0 189 package body APEX_230100.WWV_FLOW_PROCESS.PERFORM_PROCESS 0x34524835b0 479 package body APEX_230100.WWV_FLOW_PROCESS.PERFORM 0x38de05fa70 4233 package body APEX_230100.WWV_FLOW.ACCEPT 0x38bdd2eaf0 2 anonymous block

I want to talk about the foibles of DIRECTORY creation in the Oracle database. This is not a 23c specific issue but one that goes back years. I want to understand what rights and objects are created when we make....[Read More]

Posted by Pete On 26/05/23 At 01:29 PM

Does oracle apex 23.1 data load wizard support excel files like the data loader in the SQL Workshop?

How to create a user for eBusiness DB account equivalent to APPS user but only with select and execute on FND, INV and APPLSYS objects?

I have the following query: select OPERATION_CODE, to_char(OPERATION_DATE, 'dd/mm/yyyy hh24:mi:ss') as OPERATION_DATE, EMP_CODE from HR_ORIGINAL_ATTENDANCE where EMP_CODE = 4415 and to_char(OPERATION_DATE, 'yyyymmdd') = 20230517 order by OPERATION_DATE; And I have the following output: OPERATION_CODE OPERATION_DATE EMP_CODE 1 17/05/2023 07:08:03 4415 1 17/05/2023 07:55:15 4415 2 17/05/2023 08:00:00 4415 1 17/05/2023 15:07:01 4415 2 17/05/2023 16:00:00 4415 2 17/05/2023 16:58:27 4415 2 17/05/2023 17:26:05 4415 1 17/05/2023 20:00:00 4415 The report query I want is as follow: Entrance Leave 17/05/2023 07:08:03 17/05/2023 08:00:00 17/05/2023 15:07:01 17/05/2023 17:26:05 17/05/2023 20:00:00 Witch meaning that, the first attendance period row is the min entrance and the max leave before the next entrance period start. Where the leave can be overrided by the user, but if the user entrance more than one time without leave, I want to select the min entrance. I hope it is clear. Thank you. The above was my question in an old thread, and Chris answer me with the following: select * from hr_original_attendance match_recognize ( order by operation_date measures first ( op1.operation_date ) st_dt, last ( op2.operation_date ) en_dt pattern ( op1+ op2+ ) define op1 as operation_code = first ( operation_code ), op2 as operation_code <> first ( operation_code ) ); The result was: ST_DT EN_DT -------- -------- 17/05/23 17/05/23 17/05/23 17/05/23 And Connor follow up, and answer me the following: with prep (operation_code, operation_date, emp_code) as ( select operation_code, operation_date, emp_code from hr_original_attendance where emp_code = 4415 and operation_date >= date '2023-05-17' and operation_date < date '2023-05-18' ) select emp_code, entrance, leave from prep match_recognize( order by operation_date measures first(emp_code) as emp_code, first(one.operation_date) as entrance, last (two.operation_date) as leave pattern ( one* two* ) define one as operation_code = 1, two as operation_code = 2 ); The result was: EMP_CODE ENTRANCE LEAVE ---------- ------------------- ------------------- 4415 17/05/2023 07:08:03 17/05/2023 08:00:00 4415 17/05/2023 15:07:01 17/05/2023 17:26:05 4415 17/05/2023 20:00:00 the exactly was I need. Thank you very much Chris and Connor. The new question is that: When I have oracle 11gr2 version, how to rewrite the query? Thank you.

We have been using an older version of Apex for about 10 years now and now want to set up email capability for sending reports as attachments. I have scoured the internet for the requirements to use but am getting confused with all the different answers. We need to connect to an external smtp server. What exactly do I need to get in place to get this to work on this older version of Apex? Please let me know if you need more info to better answer this question. Thank you.

Hi, I have column having data like below. 101010 10203040 2030405060 etc From each row above I have to break as below to check whether that number exist in a table or not, if yes skip and if no then insert. E.g. for first row 101010 First I have extract two digit ie 10 and check if exist in a table or not and if not then insert if yes then skip. Second time i have to take first 4 digital eg 1010 and verify whether exist in a table or not. Similarly I have to look 101010 last time for first row to verify if it exist or not. Can you please help how to achieve this. Thanks.

What do we mean by instance caging in context of oracle database? How it works? Kindly elaborate with examples?