BLOG

listagg()

Today I needed to create an audit statement with a lot of users who are excepted from auditing. It looks somewhat like : audit policy <policy_name> except <comma separated list of users>   For my requirement all Oracle Maintained account should be excepted from auditing. Following statement made it easy: spool policy_on.sql select ‘audit policy […]

Continue Reading

Distribution of values in a column

Distribution of data For calculating the right execution-plan Oracle often needs the distribution of values for a column. For an outlier value a index can easily be used. For a value that is present in half the rows a index might be less effective. For a similar exercise I needed the distribution of values myself. […]

Continue Reading

Creating an ACL

A very simple example of creating an ACL for a principal and a host: Creating the ACLS (qith the associated principal) itself. exec dbms_network_acl_admin.create_acl( acl=>’print_permissions.xml’, description=>’Gebruikt voor Print functionaliteit’, principal=>’APEX_230100′, is_grant=>TRUE, privilege=>’connect’); Associating a host to the ACL: exec dbms_network_acl_admin.assign_acl( acl=>’print_permissions.xml’, host=>’machine-name.domain.domain’, lower_port=>8010, upper_port=>8010); This is a starting point. You can go from here … […]

Continue Reading

Assembly and opcodes

Intro When a program is compiled (and linked) it will no longer contains readable lines of code. Instead it will consist of opcodes (operation codes). I would like to see whether I can find out what dome opcodes mean. You might say “just look at the intel or AMD manual and there you go”…but where […]

Continue Reading

OEM : No Data

This is reaaly memory for myself 🙂 We often encounter sql performance screens where OEM says “Unable to Retrieve Data” What we do : create a job (in OEM) that deploys Database Management PL/SQL packages In OEM main Menu Click Enterprise –> Job –> Library –> Select Job Type “Deploy Database Management PL/SQL Packages” –> […]

Continue Reading

Oracle system statistics

Oracle System Statistics When I look at the system statistics I’m not really getting any wiser. There a re really a lot of statistics: SQL> select count(*) from v$sysstat; COUNT(*) ———- 2252 SQL> Above from a Oracle 21c instance. I do not want to see them all. I would like to see the change in […]

Continue Reading

sar usage

I like to use the linux utility sar (system activity reporter (?)) I gives a fast and quite complete overview over the performance of a linux system. SAR has a lot of possibilitys. I just highlight a few of them to remember how the syntax is 🙂 CPU information of today oracle@lqas4342:~$sar Linux 4.18.0-425.13.1.el8_7.x86_64 (lqas4342.mod.nl) […]

Continue Reading

ORAENV_ASK

Just a reminder I keep forgetting what the environment variable is called: ORA_ASKENV ORA_ENVASK ORAASK_ENV ORAENV_ASK This is _the_ variable: ORAENV_ASK=NO ORAENV_ASK=YES Happy scripting

Continue Reading

tcpdump

Just a write up of the filters I sometimes use/need. (work in progress…) Filtering _out_ ssh: tcpdump “port ! ssh” Just seeing ICMP packets: tcpdump “icmp” Filtering on port _and_ host: tcpdump “port ssh && host host-name”

Continue Reading