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