tablespace usage sql

I want to add diskgroup usage and file system usage.

--tablespace usage

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

-- tablespace usage including autoextend

SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)",AUTOEXTENSIBLE
FROM
(SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,
(SELECT FILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME,FILE_ID) E,
(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_DATA_FILES) F
WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)",AUTOEXTENSIBLE
FROM
(SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,
(SELECT FILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME,FILE_ID) E,
(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_TEMP_FILES) F
WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)
ORDER BY TABLESPACE_NAME,FILE_NAME

-- extends based usage
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)",
Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;

alter database datafile ... offline drop

Dropping datafiles
Datafiles were not designed to be dropped. (See also Metalink note 111316.1) This is true even though there is alter database datafile offline drop.
However, if the datafile is the only datafile in its tablespace, it can be removed together with the tablespace:
DROP TABLESPACE ts_data INCLUDING CONTENTS;
With Oracle 10g Release 2, it's finally possible to drop a datafile as long as
it's not the only datafile within the tablespace, and
the tablespace is only and in read-write status
:
alter tablespace ts_something drop datafile '/path/to/datafile.dbf'

Fuzzy datafiles
A datafile that contains a block whose SCN is more recent than the SCN of its header is called a fuzzy datafile.

Initialize Oracle Security (revoke)

Oracle keeping waring you in Enterprise Manager DBControl.

revoke execute on utl_file from public;
revoke execute on utl_http from public;
revoke execute on utl_smtp from public;
revoke execute on utl_tcp from public;
revoke execute on dbms_random from public;

grant execute on utl_tcp to sysman;
grant execute on utl_smtp to sysman;
grant execute on utl_http to sysman;
grant execute on utl_file to sysman;
grant execute on dbms_random to sysman;

Oracle Killing Sessions Version 10.2

kill oracle sessions scrit (with sqlplus)

select s.sid, s.serial#, s.username, spid, 
osuser, s. program, schemaname 
from v$session s, v$process p 
where machine like '%ta%' and s.program like '%%' and s.paddr = p.addr
/

select 'alter system kill session ''' 
|| s.sid || ',' || s.serial# || '''; -- ' 
|| s.machine || ',' || s.program  
from v$session s, v$process p 
where machine like '%%' and s.program like '%%' and s.paddr = p.addr
/

---

Oracle Killing Sessions
Version 10.2

UNIX
Killing sessions in the UNIX environment ps -ef | grep ora to find Oracle processes. Be sure to get the process id of the session you are trying to kill.

kill -1
or
kill -9
kill -9 5745
All in one kill ps -ef | grep pmon_$ORACLE_SID | awk '{print $2}' | xargs kill -9

Windows
Killing sessions in the Windows environment with ORAKILL. orakill
SELECT instance_name
FROM gv$instance;

col program format a30

SELECT spid, osuser, s.program, schemaname
FROM gv$process p, gv$session s
WHERE p.addr = s.paddr;

c:\oracle\product\ora102\bin> orakill orabase spid

All
Killing sessions from inside the database To kill sessions within the database requires the ALTER SYSTEM privilege and the sid and serial# of the session to be killed

GRANT alter system TO ;

SELECT sid, serial#, username, schemaname, osuser
FROM gv$session
WHERE username = ;

ALTER SYSTEM KILL SESSION '< sid>,';
conn / as sysdba

GRANT alter system TO aqadmin;

conn aqadmin/aqadmin

SELECT sid, serial# , username, schemaname, osuser
FROM gv$session
WHERE username = 'AQUSER';

SQL> ALTER SYSTEM KILL SESSION '9,177' IMMEDIATE;

Kill All Sessions
Kill All Instance Sessions conn / as sysdba

set heading off
set termout off
set verify off
set echo off
set feedback off

ALTER SYSTEM enable restricted session;

ALTER SYSTEM checkpoint global;

spool kill_all.sql

SELECT 'execute kill_session('|| chr(39) || sid || chr(39) || ',' || chr(39) || serial# || chr(39) || ');'
FROM gv_$session
WHERE (username IS NOT NULL OR username <> 'SYS');

spool off

@kill_all

Session Kill Demo Procedure
An infinite loop for testing CREATE OR REPLACE PROCEDURE infinite_loop IS

BEGIN
LOOP
NULL;
END LOOP;
END infinite_loop;
/

SQL> exec infinite_loop