Oracle DBA Cookbook

DBA_Cookbook.sql

---------------------------------------------------------------------------------------------
-- ORACLE DBA COOKBOOK ----------------------------------------------------------------------
---------------------------------------------------------------------------------------------

-- Set wide output in rlwrap
set lines 1000 pages 1000


-- Get privileges granted for certain table/object
SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME='TABLE_NAME';


-- Get Tablespaces status and associated datafiles
select t.tablespace_name  "Tablespace",  t.status "Estado",
    ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamano",
    ROUND((MAX(d.bytes)/1024/1024) -
    (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
    ROUND((ROUND((MAX(d.bytes)/1024/1024) - (SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2))*100/ROUND(MAX(d.bytes)/1024/1024,2
),2) "% Usado",
    ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
    d.AUTOEXTENSIBLE "Autoextensible",
    d.file_name "Fichero de datos"
from DBA_FREE_SPACE f, DBA_DATA_FILES d,  DBA_TABLESPACES t
where t.tablespace_name = d.tablespace_name(+)  AND
    f.tablespace_name(+) = d.tablespace_name
    AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
    d.file_name, d.AUTOEXTENSIBLE, t.status ORDER BY 1,3 DESC;


-- Get ASM diskgroup usage
select name,total_mb,free_mb,free_mb/total_mb*100 "% FREE", (total_mb-free_mb) "USED_MB", (total_mb-free_mb)/total_mb*100 "% USED" from v$asm_diskgroup;


-- Increase/modify datafile size
alter database datafile 'DATAFILE_NAME' resize 1024M;