====== Oracle DB select samples ======

===== Table space size =====

<cli prompt='>'>
[oracle@aixdb01]/home/oracle> sqlplus / as sysdba
SQL> SELECT a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

TABLESPACE_NAME                BYTES_USED BYTES_FREE    LARGEST PERCENT_USED
------------------------------ ---------- ---------- ---------- ------------
SYSTEM                         1216348160    3997696    3145728        99.67
SYSAUX                         1195376640   86966272    1048576        92.72
TS_TOTO                         2.1475E+10 1.4934E+10 4160749568        30.46
USERS                             5242880    4194304    4194304           20
UNDOTBS1                       1840250880 1798635520 1271922688         2.26
</cli>

List table space files
<cli prompt='>'>
[oracle@aixdb01]/home/oracle> sqlplus / as sysdba
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/DATAFILE/TOTODEV/system01.dbf
/DATAFILE/TOTODEV/sysaux01.dbf
/DATAFILE/TOTODEV/undotbs01.dbf
/DATAFILE/TOTODEV/users01.dbf
/DATAFILE/TOTODEV/ts_FRS.dbf
</cli>