Tablespace usage

Query to check 30% below:
-------------------------

set lines 1000
set pages 200
col host_name format a25
select a.tablespace_name,
(a.BYTES/1024)/1024 "Used Space(MB)",total "allocated size(MB)",
max "maximum allowable (MB)",max-(a.BYTES/1024)/1024 "effective free(MB)",round(((max-(a.BYTES/1024)/1024)/max)*100,2) "% FREE" from SM$TS_USED a,(select tablespace_name,sum((BYTES/1024)/1024) total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 6 desc;

Adding Space:
-------------
alter tablespace APPS_TS_TX_DATA add datafile '/test/data/data1.dbf' size 100M autoextend on maxsize 5g;

Temp tablespace usage:
----------------------
SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM V$SORT_SEGMENT;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP2                                3870336        4352     3865984
TEMP                                       0           0           0
TEMP1                                5181056        2848     5178208


Temporary Segments

The following query displays information about all sort segments in the database. (As a reminder, we use the term “sort segment” to refer to a temporary segment in a temporary tablespace.) Typically, Oracle will create a new sort segment the very first time a sort to disk occurs in a new temporary tablespace. The sort segment will grow as needed, but it will not shrink and will not go away after all sorts to disk are completed. A database with one temporary tablespace will typically have just one sort segment.

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TEMP1                                50480       22.5    50457.5
TEMP2                                40240         38      40202

ALTER TABLESPACE TEMP1 ADD TEMPFILE '/test/data/temp01.dbf' size 100M autoextend on maxsize 5g;