C:\App\sqldeveloper\sqldeveloper.exe --AddVMOption=-Duser.language=en
All posts by vogler
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 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 ;
WinCC OA Proxy on a separate host …
config file entries:
Host 1 => WinCC OA Server Name: winccoa
# nothing is needed
Host 2 => Proxyhost where the proxy runs: scada
[general]
dataHost = "winccoa"
eventHost = "winccoa"
[proxy]
server = "winccoa:4897"
server = "winccoa:4998"
Host 3 => Client
[general]
data = "winccoa"
event = "winccoa"
mxProxy = "winccoa scada:5678 cert"
if you do not add the [proxy] configuration at the proxy host then you will get following error when you try to connect from the client:
WCCILproxy (1), 2014.05.27 21:09:21.486, SYS, INFO, 222, Accept SSL connection from host ::ffff:192.168.1.125:1485.
WCCILproxy (1), 2014.05.27 21:09:26.078, PARAM,WARNING, 209, Cannot find the host in the list of the allowed-hosts: (winccoa:4897)
Oracle Pivot Query (row to column)…
SQL> col PARAMETER format a30
SQL> col "SESSION" format a30
SQL> col DATABASE format a30
SQL> col INSTANCE format a30
SELECT * from
(SELECT 'SESSION' scope, nsp.* FROM nls_session_parameters nsp
union
SELECT 'DATABASE' scope, ndp.* FROM nls_database_parameters ndp
union
SELECT 'INSTANCE' scope, nip.* FROM nls_instance_parameters nip
) a
PIVOT (LISTAGG(VALUE) WITHIN GROUP (ORDER BY scope)
FOR scope
IN ('SESSION' as "SESSION",'INSTANCE' as INSTANCE,'DATABASE' as DATABASE))
/
How to copy WinCC OA RDB-Manager schema and data to another database…
// create all tablespaces
create tablespace PVSSRDB_ALERT_00100000 datafile '+PVSS';
create tablespace PVSSRDB_ALERT_00100000I datafile '+PVSS';
create tablespace PVSSRDB_EVENT10_00100003 datafile '+PVSS';
create tablespace PVSSRDB_EVENT10_00100003I datafile '+PVSS';
create tablespace PVSSRDB_EVENT_00100001 datafile '+PVSS';
create tablespace PVSSRDB_EVENT_00100001I datafile '+PVSS';
create tablespace PVSSRDB_EXTERN_00100011 datafile '+PVSS';
create tablespace PVSSRDB_VALUES_00100002 datafile '+PVSS';
create tablespace PVSSRDB_VALUES_00100002I datafile '+PVSS';
create tablespace PVSSRDB_VALUES_00100006 datafile '+PVSS';
create tablespace TS_PVSSRDB datafile '+PVSS';
// create roles
create role R_APP_PVSSRDB;
// create database link to old database
create database link solaris connect to system identified by manager using 'solaris/db';
// create directory (in 12c with pluggable database the default data_pump_dir directory does not work)
create directory data_pump as '/app/oracle/admin/CDB/dpdump';
// import data with database link
impdp system/manager@database/pvss schemas=(PVSSRDB,PVSSAPP) network_link=solaris directory=data_pump parallel=2
// grant execute on sys.dbms_lock to pvssrdb
grant execute on dbms_lock to pvssrdb;
Windows 8 Move/Recover Offscreen(Hidden) Window…
Click the application in question on the task bar and press:
Windows-Key + Up ==> Maximize
Windows-Key + Left ==> place to left
Windows-Key + Right ==> place to right
Windows-Key + Down ==> Restore-Size
Oracle Apex RESTful Services…
To use RESTful Oracle Apex Services following grant has to be done:
alter user
Export Oracle APEX Applications of Workspace
How to export all applications of an Oracle APEX Workspace:
export CLASSPATH=.:/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5.jar:/app/apex/utilities
java oracle.apex.APEXExport -db localhost:1521:ORCL -user system -password
/app/apex ==> Oracle APEX Installation-Directory
ORA-28014: cannot drop administrative users (Oracle 12c)
SQL> drop user apex_040200 cascade;
drop user apex_040200 cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative users
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> drop user apex_040200 cascade;
User dropped.