Impressum

Responsible for content:

Peter Ramm 
Hauptstraße 18 
D-01723 Oberhermsdorf 
Germany 

Email: Peter@ramm-oberhermsdorf.de

Comments

Post a Comment

Popular posts from this blog

Oracle-DB: New SQL Diagnostic Report in rel. 19.28

Image
One of the new features backported from 23ai into release 19.28 is the SQL Diagnostic Report. This shows a comprehensive summary of diagnostic information for a certain SQL statement. Included are execution plan info, optimizer statistics, object info, ASH info, SQL Monitor reports and lots more. The documentation for 19c already includes the new method DBMS_SQLDIAG.Report_SQL . Which edition or option pack license is needed Including ASH, SQL Monitor etc. raises the question: What about licensing limitations for use of this function. As of current documentation there is no Enterprise Addition or additional management pack license needed for execution of DBMS_SQLDIAG.Report_SQL. The package DBMS_SQLDIAG and it's subroutines are often named as "SQL Repair Advisor". Looking at Features and Licensing there is no limitation for usage of SQL Repair Advisor and there's also a section for SQL Diagnostic Report confirming no limitation: The note "How To U...
Read more

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet...
Read more

Oracle-DB: Cleanup Unified Audit Trail with dynamic number of rows and oldest timestamp, but limited storage size

For housekeeping of Unified Audit Trail in a recent project there were some objectives: A hard rule that audit records must be kept in all cases for at least x days A hard rule that the stored audit records should not exceed a limited storage size, except the the above age limit requires this A soft objective thet audit records should be stored as long as possible if the storage size limit is not reached The package DBMS_AUDIT_MGMT only provides the method CLEAN_AUDIT_TRAIL with the ability to cut the records to purge at a timestamp limit. This would require to estimate a maximum age of held audit records that ensures also in worst cases that the storage size limit will not be exceeded. In result, outside the worst case of audit volume you will purge the audit records far too early. You could have stored them much longer because there's enough space below the storage limit. The following SQL script accepts all named objectives by ensuring: Audit records younger ...
Read more