Admin active sessions.sql
col username format a20
col osuser format a20
col schemaname format a20
set linesize 200
select sid, serial#, username, osuser, status, schemaname, to_char (logon_time,'DD-MON-YYYY HH24:MI:SS') logon_time
from v$session
where status = 'ACTIVE'
and username IS NOT NULL
order by logon_time
/
Admin all sessions.sql
col username format a15
col module format a30
col machine format a50
col program format a30
col osuser format a15
set linesize 300
select username,
status,
floor(last_call_et / 60) "Minutes Inactive",
sid,
serial#,
osuser,
machine,
substr(program,1,30) program,
substr(module,1,30) module
from v$session
where username not in (select username from dba_users where oracle_maintained='YES')
and username is not null
and username not in ('DBSNMP')
and type = 'USER'
order by status, username
/
Admin check dblinks.sql
SET SERVEROUT ON
prompt *** list of available database links ***
col owner format a15
col db_link format a20
col host format a30
select owner, db_link, host from dba_db_links order by 1;
prompt *** status of the private database links ***
prompt
prompt OWNER DATABASE LINK STATUS
prompt ------------------------------
BEGIN
FOR f IN ( SELECT *
FROM dba_db_links
WHERE OWNER <> 'PUBLIC'
ORDER BY owner, db_link)
LOOP
DBMS_SCHEDULER.create_job (
job_name => f.owner || '.CHECK_DBLINK_PRIVATE',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE '
|| ' X CHAR; '
|| 'BEGIN '
|| ' SELECT dummy into x from dual@'
|| f.db_link
|| ' ;'
|| ' DBMS_OUTPUT.put_line('''
|| f.owner
|| ' '
|| f.db_link
|| ' VALID'');'
|| 'END ; ');
BEGIN
DBMS_SCHEDULER.run_job (f.owner || '.CHECK_DBLINK_PRIVATE ', TRUE);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
f.owner
|| ' '
|| f.db_link
|| ' INVALID (ORA'
|| SQLCODE
|| ')');
END;
DBMS_SCHEDULER.drop_job (f.owner || ' . CHECK_DBLINK_PRIVATE ');
END LOOP;
END;
/
prompt *** status of the public database links ***
prompt
prompt OWNER DATABASE LINK STATUS
prompt ------------------------------
BEGIN
FOR f IN ( SELECT *
FROM dba_db_links
WHERE OWNER = 'PUBLIC'
ORDER BY db_link)
LOOP
DBMS_SCHEDULER.create_job (
job_name => 'system.CHECK_DBLINK_PUBLIC',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE '
|| ' X CHAR; '
|| 'BEGIN '
|| ' SELECT dummy into x from dual@'
|| f.db_link
|| ' ;'
|| ' DBMS_OUTPUT.put_line('''
|| 'SYSTEM'
|| ' '
|| f.db_link
|| ' VALID'');'
|| 'END ; ');
BEGIN
DBMS_SCHEDULER.run_job ( 'system.CHECK_DBLINK_PUBLIC ', TRUE);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'PUBLIC'
|| ' '
|| f.db_link
|| ' INVALID (ORA'
|| SQLCODE
|| ')');
END;
DBMS_SCHEDULER.drop_job ('system.CHECK_DBLINK_PUBLIC ');
END LOOP;
END;
/
