DBA_TABLESPACES

DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO

 

Listing Tablespaces and Default Storage Parameters

 

SELECT TABLESPACE_NAME "TABLESPACE",INITIAL_EXTENT, NEXT_EXTENT ,MIN_EXTENTS,

MAX_EXTENTS ,PCT_INCREASEFROM DBA_TABLESPACES;

 

How to check tablespace usage in Oracle

select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"

from dba_segments a, (select tablespace_name, sum(bytes) total_space

                      from dba_data_files

                      group by tablespace_name) b

where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')

and a.tablespace_name = b.tablespace_name

group by a.tablespace_name, a.owner, b.total_space/1024/1024

order by a.tablespace_name, a.owner;