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’

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;

DB Links in the database

SET LINESIZE 150

COLUMN db_link FORMAT A30
COLUMN host FORMAT A30

SELECT owner,
db_link,
username,
host
FROM dba_db_links
ORDER BY owner, db_link;

Oracle row length of table

SELECT a.table_name, a.num_rows, c.total_bytes sizeof_table,
CASE a.num_rows WHEN 0 THEN 0 ELSE c.total_bytes / a.num_rows END arl_wo, a.arl_woo, b.index_len,
a.arl_woo + b.index_len total_arl_woo, (a.arl_woo + b.index_len) * 1000000 for_million_records
FROM (SELECT table_name, avg_row_len arl_woo, num_rows
FROM user_tables) a,
(SELECT table_name, SUM (column_length) index_len
FROM user_ind_columns
GROUP BY table_name) b,
(SELECT SUM (BYTES) total_bytes, segment_name AS table_name
FROM user_extents
WHERE segment_type = ‘TABLE’
GROUP BY segment_name) c
WHERE a.table_name = b.table_name AND a.table_name = c.table_name;

Note: All values are in bytes.
NUM_ROW : Number or rows in table
SIZEOF_TABLE: Size of table
ARL_WO: Average row length with overhead
ARL_WOO: Average row length without overhead
INDEX_LEN: Bytes occupied by the index on table for each row
TOTAL_ARL_WOO: Total average row length with index included

Displays hidden parameters

SET VERIFY OFF
COLUMN parameter FORMAT a37
COLUMN description FORMAT a30 WORD_WRAPPED
COLUMN session_value FORMAT a10
COLUMN instance_value FORMAT a10

SELECT a.ksppinm AS parameter,
a.ksppdesc AS description,
b.ksppstvl AS session_value,
c.ksppstvl AS instance_value
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE ‘/_%’ ESCAPE ‘/’
AND a.ksppinm = DECODE(LOWER(‘&1’), ‘all’, a.ksppinm, LOWER(‘&1’))
ORDER BY a.ksppinm;