Feed aggregator
SQL-profile
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?
Categories: DBA Blogs
ETL and replication
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.
Categories: DBA Blogs
Configuring TLS for connections from Oracle database to another server
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
Categories: DBA Blogs
Large table and small table behave differently when defining cursors
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>
Categories: DBA Blogs
Table TTL and foreign key relationships
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?
Categories: DBA Blogs
INITIALLY DEFFERED FOREIGN KEY constraint doesn't work
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>
Categories: DBA Blogs
Pooled timeout with 19c
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
Categories: DBA Blogs
MY_WALLET_DIRECTORY in TNSNAMES not working as expected
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...
Categories: DBA Blogs
Windows-Based SQL*Plus Scripting Issue
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.
Categories: DBA Blogs
REST HTTP
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
Categories: DBA Blogs
Creating a DIRECTORY - Forensics Example in 23c
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
Categories: Security Blogs
Data load wizard page support for Excel files?
Does oracle apex 23.1 data load wizard support excel files like the data loader in the SQL Workshop?
Categories: DBA Blogs
create an account equivalent to APPS but with read only access
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?
Categories: DBA Blogs
Calculate more than attendance period per day with some conditions
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.
Categories: DBA Blogs
Sending Email with Apex 4.2.6.00.03
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.
Categories: DBA Blogs
SQL help
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.
Categories: DBA Blogs
Instance Caging
What do we mean by instance caging in context of oracle database? How it works? Kindly elaborate with examples?
Categories: DBA Blogs
Pages
