Oracle database management scripts-

Check how large size of the database

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
 from v$datafile
 union all
 select bytes
 from v$tempfile
 union all
 select bytes
 from v$log) used
, (select sum(bytes) as p
 from dba_free_space) free
group by free.p
/

Show the ten largest objects in the database

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
 select owner
 , segment_name
 , segment_type
 , bytes / 1024 / 1024 "MB"
 from dba_segments
 order by bytes desc
 )
where rownum < 11
/

Show all the connected users in database

set lines 100 pages 999
col ID format a15
select username
, sid || ',' || serial# "ID"
, status
, last_call_et "Last Activity"
from v$session
where username is not null
order by status desc
, last_call_et desc
/

Sort the session by logon time

set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/

Collect user info including os and pid

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/

Show a current sql for particular user

Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value 
 from v$session
 where username like '&username')
/

Select any long running operations

set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/

Select the List of open cursors per user

set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/

User Administration scripts:

set pages 999 lines 100
col username format a20
col status format a8
col tablespace format a20
col temp_ts format a20
select username
, account_status status
, created
, default_tablespace tablespace
, temporary_tablespace temp_ts
from dba_users
order by username
/

Lock and unlock a user

alter user <username> account lock;

alter user <username> account unlock;

Roles Management 

Find any role

select *
from dba_roles
where role like '&role'
/

Select what all roles are granted to any user

select grantee, granted_role, admin_option
from dba_role_privs
where grantee like upper('&username')
/

Select what all system privileges are granted to any role

select privilege,admin_option
from role_sys_privs
where role like '&role'
/

Select what all table privileges are granted to any role

select owner || '.' || table_name "TABLE"
, column_name , privilege, grantable
from role_tab_privs
where role like '&role'
/

ASM Administration script:

How to check if css is running

crsctl check cssd

Display all the disk-groups

set lines 100
col name format a10
col path format a30
select name
, group_number
, disk_number
, mount_status
, state
, path
from v$asm_disk
order by group_number
/

Select all disk space usage 

select name
, group_number
, disk_number
, total_mb
, free_mb
from v$asm_disk
order by group_number
/

Tablespace Management

Tablespace usage

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
 100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from dba_data_files group by tablespace_name union all
 select tablespace_name || ' **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/

Tablespaces which are >=80% full, and how much space need to add to make them again 80% –

set pages 999 lines 100
col "Tablespace" for a50
col "Size MB" for 999999999
col "%Used" for 999
col "Add (80%)" for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from dba_data_files group by tablespace_name) tsu
, (select ts.tablespace_name
 , nvl(sum(bytes)/1024/1024, 0) free_mb
 from dba_tablespaces ts, dba_free_space fs
 where ts.tablespace_name = fs.tablespace_name (+)
 group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/

User quotas allocated on all tablespaces

col quota format a10
select username
, tablespace_name
, decode(max_bytes, -1, 'unlimited'
 , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas
where tablespace_name not in ('TEMP')
/

Show all tablespaces used by any specific user

select tablespace_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name
/