Oracle

Oracle Listener disable connect logging…

Disable logging of every connect request:

listener.ora:
LOGGING_ = OFF

otherwise the listener will log every connect and fill up the listener.log

[oracle@Database trace]$ ls -la
total 30696
drwxr-xr-x 2 oracle oinstall 4096 Aug 24 20:19 .
drwxr-xr-x 14 oracle oinstall 4096 May 4 09:18 ..
-rw-r—– 1 oracle oinstall 31393482 Aug 25 17:28 listener.log

Oracle

Flashback Database ….

It is pretty cool to create a restore point for the database before doing some upgrades. If upgrade fails the database can be set back to the restore point by a single statement.

CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

FLASHBACK DATABASE TO RESTORE POINT before_upgrade;

DROP RESTORE POINT before_upgrade;

Oracle, WinCC OA

WinCC OA pivot query to combine history values with same timestamp in one row…

This example combines the WinCC OA audit trail datapoint elements to a single row for one audit trail entry.

select “TS”,”TIME”,”USERNAME”,”UINUM”,”BATCHID”,”ITEM”,”ITEMTYPE”,”ACTION”,”OLDVAL”,”NEWVAL”,”REASON”,”HOST” from (
select h.ts, e.ELEMENT_NAME, NVL(to_char(h.VALUE_NUMBER),h.VALUE_STRING) val
from eventhistory h
join elements e on e.ELEMENT_ID = h.ELEMENT_ID
where e.ELEMENT_NAME in (
‘System1:_AuditTrail.time’,
‘System1:_AuditTrail.username’,
‘System1:_AuditTrail.uinum’,
‘System1:_AuditTrail.batchid’,
‘System1:_AuditTrail.item’,
‘System1:_AuditTrail.itemtype’,
‘System1:_AuditTrail.action’,
‘System1:_AuditTrail.oldval’,
‘System1:_AuditTrail.newval’,
‘System1:_AuditTrail.reason’,
‘System1:_AuditTrail.host’
)
) a
pivot (listagg(val) within group (order by element_name)
for element_name in (
‘System1:_AuditTrail.time’ as time,
‘System1:_AuditTrail.username’ as username,
‘System1:_AuditTrail.uinum’ as uinum,
‘System1:_AuditTrail.batchid’ as batchid,
‘System1:_AuditTrail.item’ as item,
‘System1:_AuditTrail.itemtype’ as itemtype,
‘System1:_AuditTrail.action’ as action,
‘System1:_AuditTrail.oldval’ as oldval,
‘System1:_AuditTrail.newval’ as newval,
‘System1:_AuditTrail.reason’ as reason,
‘System1:_AuditTrail.host’ as host
));

WinCC OA

WinCC OA read csv file…

A simple code how to read/import a CSV file into an array (dyn_dyn_string).

main()
{
 string content;
 fileToString(DATA_PATH+"/csvtest.txt", content); // read file into a string variable

 dyn_dyn_string result;
 dyn_string lines = strsplit(content, "\n"); // split the string into lines
 for ( int i=1; i<=dynlen(lines); i++ ) {
  result[i]=strsplit(lines[i], ";"); // delimiter ";"
 }
 DebugTN(result);
}

Limitations:
* Don’t do it in that way with very big files because it is read from the file into memory
* The delimiter “;” cannot be inside of a cell value

With oracle you can use external tables to access CSV files. After creating an external table it is possible to access the file by SQL:

select * from ext_tab

CREATE DIRECTORY EXT_DIR AS '/home/oracle/data';

-- Create table
create table ext_tab
(
Col1 VARCHAR2(30),
Col2 VARCHAR2(30),
Col3 VARCHAR2(30)
)
organization external
(
type ORACLE_LOADER
default directory EXT_DIR
access parameters
(
RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ";"
)
location (EXT_DIR:csvtest.csv')
)
reject limit UNLIMITED;

Allgemein

Downtime Query WinCC OA with RDB-Manager…

Example how to query the downtime of a device which has an alert configuration on the state of the machine (state 0/1):

select element_name, sum(downtime) downtime from (
select e.element_name,
h.ts,
h.VALUE_NUMBER,
h.direction,
case
when h.DIRECTION = 0 then
CAST((h.ts + 0) - ((lag(h.ts, 1, h.ts) OVER(ORDER BY ts)) + 0) AS FLOAT)*24*60*60
else
null
end as downtime
from elements e
join alerthistory h
on e.element_id = h.ELEMENT_ID
where e.element_name like
'Device_%.Status'
and ts between to_date('01.07.2014','DD.MM.YYYY') and to_date('31.07.2014','DD.MM.YYYY')
) group by element_name

Oracle

Oracle sum of intervals …

When timestamps are used in your tables and you subtract two timestamps then you will get as result a value of type “interval”. And it is not possible to sum up intervals. But it can be done by casting to float. The only bad thing is that you will loose the milli seconds…

CAST((ts1 + 0) – (ts2 + 0) AS FLOAT)*24*60*60 /* seconds */