Best Of
Re: About output of 'host' command in sqlplus
Can be hacked as follows.
The relevant host command to capture:
SQL> host ping -c3 127.0.0.1PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.048 ms64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.047 ms64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.049 ms--- 127.0.0.1 ping statistics ---3 packets transmitted, 3 received, 0% packet loss, time 2035msrtt min/avg/max/mdev = 0.047/0.048/0.049/0.000 msSQL>
Create a dynamic SQL script where the command's output is assigned to a bind variable:
SQL> var stdout varchar2(32767)SQL> host echo -en "begin :stdout := '">/tmp/x.sql; ping -c3 127.0.0.1 >>/tmp/x.sql;echo -en "'; end;\n/\n">>/tmp/x.sql
The dynamic script, /tmp/x.sql, contains:
begin :stdout := 'PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.048 ms64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.038 ms64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.044 ms--- 127.0.0.1 ping statistics ---3 packets transmitted, 3 received, 0% packet loss, time 2052msrtt min/avg/max/mdev = 0.038/0.043/0.048/0.006 ms'; end;/
Now run this script to get the relevant command's output assigned to the bind variable:
SQL> @/tmp/x.sqlPL/SQL procedure successfully completed.SQL> print stdoutSTDOUT------------------------------------------------------------------------PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.048 ms64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.038 ms64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.044 ms--- 127.0.0.1 ping statistics ---3 packets transmitted, 3 received, 0% packet loss, time 2052msrtt min/avg/max/mdev = 0.038/0.043/0.048/0.006 ms
Announcing Oracle Customer Support Webinar: SR Severity Project Overview
Description:
This webinar will review planned changes for how severity is set during the SR creation process, along with the how and why Oracle is adopting these changes. Included will be an important review of the differences you will see in the support portals that you utilize.
Join us to learn and experience:
- What communication and training materials are available to support you in understanding these changes
- A live demo of the screen changes and how this new process looks in the support portals
- Question and Answer time for you to ask questions and receive information that will increase clarity for your organization to successfully move forward regarding these updated SR Severity changes
Sessions will run on the following dates/times (Each requires separate registration):
03/16/23, 8a PT
Click this Link to view Event for 16 March at 8am PT (Please open the event to get to the registration/LOCATION link for Zoom - Do NOT just click the RSVP)
03/16/23, 8p PT
Click this link to view Event for 16 March at 8pm PT (Please open the event to get to the registration/LOCATION link for Zoom- Do NOT just click the RSVP)
03/21/23, 8a PT
Click this link to view Event at 21 March at 8am PT (Please open the event to get to the registration/LOCATION link for Zoom- Do NOT just click the RSVP)
Presented by:
- Hari Katla, Senior Director, Support Customer Experience Product Management, Oracle
Webcast:
Registration is required. This is a live webinar with an opportunity to ask your questions and have them answered.
You MUST REGISTER through the link provided in each EVENT separately.
Each registration link is unique to the email ID approved for the event and is valid on a single device only - DO NOT share the unique Zoom link received after registration is complete.
The replay and slide deck will be available from this event posting within 24 hours following the live session.
DCD (SQLNET.EXPIRE_TIME) - what it is and what it is not
In response to postings in some discussions as well as multiple service requests with Oracle Software Support heading off the wrong direction, I believe it is vitally important to emphasize WHAT DCD is used for and WHAT it is not (as well as mention a potential and useful "side effect" of DCD
What it is not
Firstly, many Oracle users assume that the DCD setting of "SQLNET.EXPIRE_TIME" in the sqlnet.ora file is used when you want to specifically TERMINATE* a client session on the Database side after a period of time. The assumption is that if DCD is set to, say, 10 (in minutes) then a client connection remaining IDLE for a period exceeding this setting will be DISCONNECTED.
This is incorrect ...
What it is
DCD is exactly what it states: DEAD connection detection.
In other words, when a client session is DEAD (which is, it has been abnormally terminated somewhere PRIOR to the DCD mechanism activating and by abnormal we mean no error, message, or reason is noted at the Oracle level FOR the disconnection), then the DCD packet will be sent out and never echoed back (the echo action is done when the client is still around - regardless as to whether the client session is idle or busy). Any dead sessions will be "flagged" in the Database and PMon will clean those up during housekeeping.
For details on DCD and it's functionality, please see the following reference notes:
Note:151972.1 "Dead Connection Detection (DCD) Explained"
Note:206007.1 "How To Automate Cleanup Of Dead Connections And INACTIVE Sessions"
* Note that there is no setting (or even code functionality) within SQL*Net / Oracle*Net that will terminate Client connections.
A Database USER profile setting (IDLE_TIME) can be set, but this defaults to UNLIMITED so is not normally the disconnection culprit.
Side effect
Secondly, DCD can SOMETIMES be used as a "virtual traffic generator" because it automatically sends out 10 byte packets every n minutes (whatever the setting is set to).
This can be useful in situations where you have a Network device (usually a Firewall) that will be monitoring traffic conditions for any security breaches. One common setting in all firewalls is an IDLE TIMEOUT setting (wording will vary).
Should a remote Client session remain idle for any period of time determined by the Firewall setting (which is determined by the System or Network Admin) then that session is CLOSED leaving the client and server disconnected.
The client will normally receive an error on the next attempt to use the session, and the Server maintains the client session OPEN (and with all the resources allocated to that client) until it is closed (marked by DCD).
The useful "side effect" of DCD, however, comes in where the packets being sent by DCD every n minutes actually resets the firewall's IDLE timer and the session remains open. However, with the newer firmware of many firewalls, "network intelligence" is ramped up and the Firewall sees these 10 byte DCD packets for what they really are: empty packets and not traffic at all and thus disconnecting the clients regardless of the DCD setting.
For details on this useful side-effect, please see:
Note:257650.1 "Resolving Problems with Connection Idle Timeout With Firewall"
Hope this all helps someone somewhere!
Kindest regards,
Roy
p.s. Comments on my postings are always welcomed
