Med Java

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Adjust tablespace size to free up space ======

===== Check how much space from datafiles are occupied for a specific tablespace =====

Replace <TBS_NAME> with the actual tablespace to check:

<code sql>
SELECT df.tablespace_name, df.file_name, df.bytes, SUM(e.bytes) AS used_bytes
FROM dba_data_files df
LEFT JOIN dba_extents e ON df.file_id = e.file_id
WHERE df.tablespace_name='<TBS_NAME>'
GROUP BY df.tablespace_name, df.file_name, df.bytes
</code>

Output:
<code>
DATA	D:\ORACLE\ORADATA\DATA1.DBF	32212254720	32211206144
DATA	D:\ORACLE\ORADATA\DATA2.DBF	32212254720	4697620480
...
</code>

===== Check existing tablespace size =====

<code sql>
 select a.file_name,round(a.bytes/1024/1024) totalsize,b.freesize
                from dba_data_files a, (select file_id,round(sum(bytes/1024/1024)) freesize
                                                               from dba_free_space
                                                             group by file_id
                                                           ) b
where a.file_id=b.file_id(+)
</code>

<code>
FILENAME                                                        TOTALSIZE   FREESIZE
/home/user/app/user/oradata/userorcl/pdborcl/USER_DATA_WORK.DBF	128	    103
/home/user/app/user/oradata/userorcl/pdborcl/USER_LOB_WORK.DBF	256	    247
...
</code>

===== Free up space =====

<code sql>
ALTER DATABASE DATAFILE '/home/user/app/user/oradata/userorcl/pdborcl/USER_DATA_WORK.DBF' RESIZE 50K
</code>