List objects in the SYSTEM tablespace that doesn’t belong to SYS or SYSTEM

select *
from sys.dba_segments
where owner not in (‘PUBLIC’, ‘SYS’, ‘SYSTEM’)
and tablespace_name = ‘SYSTEM’

Advertisements

Tablespace Fragmentation

SELECT dfsc.tablespace_name tablespace_name,
DECODE (
dfsc.percent_extents_coalesced,
100,
(DECODE (GREATEST ((SELECT COUNT (1)
FROM dba_free_space dfs WHERE dfs.tablespace_name = dfsc.tablespace_name), 1),
1,
‘No Frag’,
‘Bubble Frag’
)
),
‘Possible Honey Comb Frag’
) fragmentation_status
FROM dba_free_space_coalesced dfsc
ORDER BY dfsc.tablespace_name;

List Unix users that can startup, shutdown and admin Databases

#!/bin/ksh
rem ———————————————————————–
rem Filename: sysdba.sh
rem Purpose: List Unix users that can startup, shutdown and admin Databases
rem Author: Frank Naude
rem ———————————————————————–

echo “Users that can startup, shutdown and admin Oracle Databases:”
echo

grep `grep ^dba /etc/group | cut -d: -f3` /etc/passwd

Users with DBA roles

select grantee, granted_role, admin_option
from sys.dba_role_privs
where granted_role in (‘DBA’, ‘AQ_ADMINISTRATOR_ROLE’,
‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’,
‘OEM_MONITOR’)
and grantee not in (‘SYS’, ‘SYSTEM’, ‘OUTLN’, ‘AQ_ADMINISTRATOR_ROLE’,
‘DBA’, ‘EXP_FULL_DATABASE’, ‘IMP_FULL_DATABASE’,
‘OEM_MONITOR’, ‘CTXSYS’, ‘DBSNMP’, ‘IFSSYS’,
‘IFSSYS$CM’, ‘MDSYS’, ‘ORDPLUGINS’, ‘ORDSYS’,
‘TIMESERIES_DBA’)

Analyzed table with unanalyzed index

— select distinct ‘analyze table ‘||i.table_name|| ‘ estimate statistics sample 25 percent;’
SELECT ‘Index ‘||i.index_name||’ not analyzed but table ‘||
i.table_name||’ is.’
FROM user_tables t, user_indexes i
WHERE t.table_name = i.table_name
AND t.num_rows IS NOT NULL
AND i.distinct_keys IS NULL

Rollback information on database sessions

SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Database Session Details

set pagesize 0
set linesize 180
col username for a25
col osuser for a12
col serial# for 9999999
col sid for 9999
col spid for a8
col module for a10 trunc
col start_time for a20
col machine for a20 trunc
select ‘Show users ordered by logon_time, username’ from dual;
select ‘OSUSER OSPID USERNAME SID SERIAL# LOGON_TIME STATUS MACHINE MODULE’ from dual;
select ‘———— ——– ———————— —– ——– ——————- ——— ——————– ———-‘ from dual;
select s.osuser,p.spid,s.username,s.sid,s.serial#,to_char(s.logon_time,’Dy dd Mon HH24:MI:SS’) start_time,s.status,s.machine,s.MODULE
from V$PROCESS p,V$SESSION s where s.paddr = p.addr and s.username is not null order by logon_time,1;