check table space sql

SQL> select tablespace_name,
  2         sum(free_space) as free_space,
  3         sum(data_files) as data_files,
  4         round(sum(free_space)/sum(data_files)*100,2) as percent
  5  from
  6  (
  7  select tablespace_name, sum(bytes)/1048576 as free_space, 0 as data_files
  8  from dba_free_space
  9  group by tablespace_name
10  union all
11  select tablespace_name, 0, sum(bytes)/1048576 as data_files
12  from dba_data_files
13  group by tablespace_name
14  )
15  group by tablespace_name
16  order by 1
17  /

TABLESPACE_NAME                FREE_SPACE DATA_FILES    PERCENT
------------------------------ ---------- ---------- ----------
CHG_IDX_TS1                      9545.375      16136      59.16
CHG_IDX_TS2                      942.6875       8192      11.51
CHG_TS1                           5141.75      21504      23.91
CHG_TS2                            986.75       1024      96.36
CONV                           12425.0625      16384      75.84
COR_IDX_TS1                     1063.9375      25088       4.24
COR_IDX_TS2                       1366.75       6656      20.53
COR_IDX_TS3                       495.875      11364       4.36
COR_TS1                         1105.3125      25856       4.27
COR_TS2                           900.875       4096      21.99
COR_TS3                            778.75       3072      25.35

TABLESPACE_NAME                FREE_SPACE DATA_FILES    PERCENT
------------------------------ ---------- ---------- ----------
CWMLITE                          150.9375        300      50.31
ECC_TS1                           432.875       1024      42.27
FND_IDX_TS1                      518.4375       3584      14.47
FND_TS1                          936.3125       4096      22.86
GLO_IDX_TS1                     1259.0625       2560      49.18
GLO_TS1                            940.75       3072      30.62
HIS_TS1                            844.75       6784      12.45
OUTLN                              9.9375         10      99.38
PERF                            1570.6875       2304      68.17
SYSTEM                             731.25       1500      48.75
TOOLS                             27.9375         50      55.88

TABLESPACE_NAME                FREE_SPACE DATA_FILES    PERCENT
------------------------------ ---------- ---------- ----------
UNDOTBS_A                       6939.4375       7168      96.81
UNDOTBS_B                          6869.5       7168      95.84
UNDOTBS_C                       4667.4375       4768      97.89
USERS                            2455.625      14336      17.13
WOA_IDX_TS1                      353.1875       1024      34.49
WOA_TS1                         1964.0625       2048       95.9
WOI_IDX_TS1                      985.0625       2048       48.1
WOI_TS1                         1468.0625       2048      71.68
WOM_IDX_TS1                      519.4375       9984        5.2
WOM_TS1                          670.5625       8192       8.19

32 rows selected.


SQL> select df.NAME,dfs.BYTES,df.CREATE_BYTES From dba_free_space dfs,v$datafile
df,V$TABLESPACE ts
  2  where dfs.TABLESPACE_NAME = ts.NAME
  3  and ts.TS#=df.TS#;

NAME                                          BYTES CREATE_BYTES
---------------------------------------- ---------- ------------
/app/oradata/feng/system01.dbf             89718784    262144000
/app/oradata/feng/system01.dbf                65536    262144000
/app/oradata/feng/undotbs01.dbf           137297920    209715200
/app/oradata/feng/drsys01.dbf              20905984     20971520
/app/oradata/feng/indx01.dbf               26148864     26214400
/app/oradata/feng/tools01.dbf              10420224     10485760
/app/oradata/feng/users01.dbf              26083328     26214400
/app/oradata/feng/xdb01.dbf                20905984     20971520

8 rows selected.