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 */

Oracle

CRS-2566 User ‘oracle’ does not have sufficient permissions to operate on resource ‘ora.reco.acfsvol.acfs’…

While installing an Oracle Standard Edition on an Oracle Database Appliance (ODA) on the cluster filesystem (cloudfs) i got the following error:

CRS-2566 User ‘oracle’ does not have sufficient permissions to operate on resource ‘ora.reco.acfsvol.acfs’

I installed the Oracle Standard Edition with the user “oracle” and this user didn’ have the permission to add dependencies to the cluster file system resource.

As root-user add permission for the oracle user:

crsctl getperm resource ora.reco.acfsvol.acfs
crsctl setperm resource ora.reco.acfsvol.acfs -u user:oracle:r-x

Oracle, WinCC OA

Oracle TCP/IP Connection Timeout…

• Set the following registry entry (or create if it does not exist):
KeepAliveTime

Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Value type: REG_DWORD – Time in milliseconds
Range: 1 – 0xFFFFFFFF
Default value: 7.200.000 (two hours)
For example this value can be set to 180000 (decimal), equals 3 minutes.

• Reboot the computer.

• Oracle SQL*Net settings the tnsnames.ora file:

Add the line “(ENABLE=BROKEN)” as shown below:

TESTDB =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTDB)
)
)

This is also useful for WinCC OA and RDB-Manager.

Allgemein

Oracle 12c unified audit trail fills up SYSAUX tablespace (schema AUDSYS)


-- clean audit trail for the current container
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => FALSE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT );
END;

-- drop purge job
BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_PJ');
END;
/

-- create purge job for current container
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => FALSE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/
/

Oracle

Oracle query session locks and objects….

SELECT
s.inst_id,
p.spid process_id,
a.oracle_username,
a.os_user_name,
a.locked_mode,
b.object_name,
b.object_type,
s.sid,
s.serial#,
s.program
from
gv$locked_object a, dba_objects b, gv$session s, gv$process p
where
a.object_id = b.object_id AND
a.process = s.process AND
a.SESSION_ID = s.SID AND
p.addr = s.paddr AND
p.inst_id = s.inst_id

In a cluster database use gv$ instead of v$ !


alter system [disconnect|kill] session 'sid,serial#,@inst_id' immediate;

kill locked sessions
SELECT 'alter system kill session '''||b.sid||','||b.SERIAL#||''';'
FROM v$lock a, v$session b, dba_objects o1, dba_objects o2
WHERE a.SID = b.SID
AND a.id1 = o1.object_id (+)
AND a.id2 = o2.object_id (+)
AND b.blocking_session IS NOT NULL
AND username IS NOT NULL

query blocking sessions:
=======================================================
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2

=======================================================
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l1.id2 = l2.id2 ;

=======================================================
select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;