← All posts tagged parus

pc
Oracle parus blocking locks select dbs.sid holding_sid, dbs.serial# holding_serial#, dbs.username holding_user,
(select count(sid) from v$session where blocking_session = dbs.sid) sessions_blocked,
dbs.sql_id holding_sql_id, dbs.wait_class holding_class, dbs.event holding_event, dbs.seconds_in_wait holding_secs,
dws.sid waiting_sid, dws.serial# waiting_serial#, dws.username waiting_user, dws.sql_id waiting_sql_id, dws.wait_class waiting_class, dws.event waiting_event, dws.seconds_in_wait waiting_secs
from v$session dbs, v$session dws
where dws.blocking_session = dbs.sid
order by dbs.sid, dws.seconds_in_wait desc
/
pc
Oracle SQL users create parus *import
set pagesize 0
set linesize 200
spool import_users.sql
SELECT 'CREATE USER '||u.username||' PROFILE "DEFAULT"'
||' IDENTIFIED BY VALUES '''||PASSWORD||''''
||' DEFAULT TABLESPACE "PARUS" TEMPORARY TABLESPACE "PRS_TMP" ACCOUNT '
||(case
when u.account_status='OPEN' then 'UNLOCK'
when u.account_status='LOCKED' then 'LOCK'
end)
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||'GRANT "CONNECT" TO "'||username||'"'
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||(case
when u.account_status='OPEN' then 'ALTER USER '||username||' ACCOUNT UNLOCK'
when u.account_status='LOCKED' then 'ALTER USER '||username||' ACCOUNT LOCK'
end)
||chr(13)||chr(10)||'/'||chr(13)||chr(10)
||'ALTER USER '||username
||' IDENTIFIED BY VALUES '''||PASSWORD||''''
||chr(13)||chr(10)||'/'||chr(13)||chr(10) "SQL"
FROM dba_users u
WHERE username NOT IN ('OUTLN','XDB','ANONYMOUS','DBSNMP','ORDPLUGINS','PARUS')
and username NOT LIKE '%SYS%'
union
select 'exit' "--"
from dual
order by 1
;
exit