To check
the tablespace in GB
select
b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB from
(select
tablespace_name, round(sum(bytes)/1024/1024/1024,1) as free_space
from
dba_free_space group by tablespace_name UNION
select
tablespace_name, round((free_space)/1024/1024/1024,1) as free_space from
dba_temp_free_space) a,
(select
tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size
from
dba_data_files group by tablespace_name UNION
select
tablespace_name, sum(bytes)/1024/1024/1024 tbs_size
from
dba_temp_files group by tablespace_name ) b where
a.tablespace_name(+)=b.tablespace_name;
Output
TABLESPACE_NAME
SIZEGB
FREEGB
------------------------------ ---------- ----------
APEX
6 1.8
APPS_TS_ARCHIVE
89
24.8
APPS_TS_INTERFACE
101.742188 38.3
APPS_TS_MEDIA
1025 54
To check
the tablespace in MB
SELECT
df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace)
"Free MB",
df.totalspace "Total MB",
ROUND(100 * ( (df.totalspace -
tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT ROUND(SUM(bytes)/(1024*1024))
totalusedspace,
2
3 4 tablespace_name
FROM dba_segments
GROUP BY tablespace_name
) tu
WHERE
df.tablespace_name = tu.tablespace_name;
Output
Tablespace Used MB Free MB
Total MB % Free
------------------------------
---------- ---------- ---------- ----------
APEX 4351 1793 6144 29
APPS_TS_ARCHIVE 65696 25440
91136 28
APPS_TS_INTERFACE 64938 39246
104184 38
APPS_TS_MEDIA 994267 55333
1049600 5
APPS_TS_NOLOGGING 18935 727661
746596 97
Checking datafile location
set lines 1000
set pages 1000
col file_name for
a60
col
tablespace_name for a25
col creation_time
for a20
select
ddf.file_id, ddf.file_name, ddf.tablespace_name, to_char(df.CREATION_TIME,'dd-MON-yyyy
hh24:mi:ss') creation_time,ddf.bytes/1024/1024/1024 "Size GB"
from
dba_data_files ddf, v$datafile df
where
ddf.file_id=df.file#
and tablespace_name='ranesh_DI_DATA'
order by
df.creation_time;
Output
FILE_ID FILE_NAME
TABLESPACE_NAME
CREATION_TIME Size GB
----------
------------------------------------------------------------
------------------------- -------------------- ----------
40 datafile/xxecs_dist_data01.dbf ranesh_DI_DATA 06-SEP-2007 19:03:23 30
65 /datafile/xxecs_dist_data02.dbf ranesh_DI_DATA 27-APR-2008 23:57:36 30
68 datafile/xxecs_dist_data03.dbf ranesh_DI_DATA 11-AUG-2008 13:31:06 30
Check the size of
particular tablespace.
set linesize 400;
set lines 200
col
"Name" format a20
col GB for 9999999
col file_name for
a100
select a.tbl
"Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100)
"Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select
tablespace_name tbl,sum(bytes)/1024/1024/1024 TSZ from dba_data_files
where tablespace_name = 'ranesh_DI_DATA' group by tablespace_name) a,
(select
tablespace_name tblsp,sum(bytes)/1024/1024/1024 FSZ from dba_free_space
where tablespace_name = 'ranesh_DI_DATA' group by tablespace_name) b
Where
a.tbl=b.tblsp;
Output
Name Total Size Free Space Pct Used
Pct Free
--------------------
---------- ---------- ---------- ----------
ranesh_DI_DATA 3608.07031 24.3586426 99 1
