Oracle Tablespace metadata

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

select tablespace_name,dbms_metadata.get_ddl(object_type => 'TABLESPACE', name=> tablespace_name) metadata from dba_tablespaces;

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON





TABLESPACE_NAME                METADATA
------------------------------ ----------------------------------------------------------------------------------------------------
SYSTEM
                                 CREATE TABLESPACE "SYSTEM" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/system01.dbf' SIZE 524288000
                                 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT MANUAL
                                  ALTER DATABASE DATAFILE
                                 '/opt/app/oracle/oradata/orcl/system01.dbf' RESIZE 880803840


SYSAUX
                                 CREATE TABLESPACE "SYSAUX" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/sysaux01.dbf' SIZE 419430400
                                 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
                                  ALTER DATABASE DATAFILE
                                 '/opt/app/oracle/oradata/orcl/sysaux01.dbf' RESIZE 870318080


TEMP
                                 CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
                                 '/opt/app/oracle/oradata/orcl/temp01.dbf' SIZE 341835776
                                 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
                                 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576


USERS
                                 CREATE TABLESPACE "USERS" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
                                 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
                                  ALTER DATABASE DATAFILE
                                 '/opt/app/oracle/oradata/orcl/users01.dbf' RESIZE 672399360


EXAMPLE
                                 CREATE TABLESPACE "EXAMPLE" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/example01.dbf' SIZE 104857600
                                 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
                                 NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


ANUJTEST
                                 CREATE TABLESPACE "ANUJTEST" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/anujtest.dbf' SIZE 10485760
                                 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


TSAPEXF
                                 CREATE TABLESPACE "TSAPEXF" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/tsapexf01.dbf' SIZE 52428800
                                 AUTOEXTEND ON NEXT 10485760 MAXSIZE 314572800
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


TSAPEXU
                                 CREATE TABLESPACE "TSAPEXU" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/tsapexu01.dbf' SIZE 52428800
                                 AUTOEXTEND ON NEXT 10485760 MAXSIZE 314572800
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
                                  ALTER DATABASE DATAFILE
                                 '/opt/app/oracle/oradata/orcl/tsapexu01.dbf' RESIZE 115343360


TEST
                                 CREATE TABLESPACE "TEST" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/test.dbf' SIZE 20971520
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


RMAN
                                 CREATE TABLESPACE "RMAN" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/rman.dbf' SIZE 52428800
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


DROP1
                                 CREATE TABLESPACE "DROP1" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/drop.dbf' SIZE 2097152
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


PERFSTAT
                                 CREATE TABLESPACE "PERFSTAT" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf' SIZE 1048576000
                                 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO


UNDOTBR
                                 CREATE UNDO TABLESPACE "UNDOTBR" DATAFILE
                                 '/opt/app/oracle/oradata/orcl/undotbR.dbf' SIZE 524288000
                                 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
                                 BLOCKSIZE 8192
                                 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

CREATE TABLESPACE tbs_anuj DATAFILE '+DATA' SIZE 1M;
alter tablespace TBS_ANUJ add datafile '+DATA' size 32767m;



   FILE_ID BLOCK_SIZE TABLESPACE_NAME             File Name                                            SIZE_GB     MAX_GB AUTOEXTENSIBLE     BIGFILE         ENCRYPTED  COMPRESS_F CONTENTS              STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
        95       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3779.1202914077       31.99          0 NO                 NO              NO                    PERMANENT             AVAILABLE
        94       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527           0          0 NO                 NO              NO                    PERMANENT             AVAILABLE



alter database datafile '+DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527' autoextend on maxsize unlimited;



   FILE_ID BLOCK_SIZE TABLESPACE_NAME             File Name                                            SIZE_GB     MAX_GB AUTOEXTENSIBLE     BIGFILE         ENCRYPTED  COMPRESS_F CONTENTS              STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
        95       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3779.1202914077       31.99          0 NO                 NO              NO                    PERMANENT             AVAILABLE
        94       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527           0      31.99 YES                NO              NO                    PERMANENT             AVAILABLE




undefine tablespace_name
define tablespace_name='TBS_ANUJ'
set pages 500 lines 400 term off
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
col file_name           for a60
col AUTOEXTENSIBLE      for a18
col BIGFILE             for a15
col ENCRYPTED           for a10
col TABLESPACE_NAME     for a27
col COMPRESS_FOR        for a10
col file_name 	        for &fname   heading "File Name"
SELECT FILE_ID, b.BLOCK_SIZE, a.TABLESPACE_NAME,a.FILE_NAME, trunc(a.BYTES/(1024*1024*1024),2) SIZE_GB, trunc(a.MAXBYTES/(1024*1024*1024),2) Max_GB , a.AUTOEXTENSIBLE,BIGFILE,ENCRYPTED,COMPRESS_FOR,CONTENTS,a.STATUS from DBA_DATA_FILES a ,DBA_TABLESPACES b 
where 1=1  
and a.TABLESPACE_NAME=b.TABLESPACE_NAME
--and a.TABLESPACE_NAME in ('ENCRYPT_256')
and a.tablespace_name = upper( decode('&&tablespace_name',null,a.tablespace_name,'&&tablespace_name'))
--and FILE_ID=31
order by 2;



   FILE_ID BLOCK_SIZE TABLESPACE_NAME             File Name                                            SIZE_GB     MAX_GB AUTOEXTENSIBLE     BIGFILE         ENCRYPTED  COMPRESS_F CONTENTS              STATUS
---------- ---------- --------------------------- ------------------------------------------------- ---------- ---------- ------------------ --------------- ---------- ---------- --------------------- ---------
        94       8192 TBS_ANUJ                    +DATA/VIHCDBD8/DATAFILE/tbs_anuj.3713.1202913527           0          0 NO                 NO              NO                    PERMANENT             AVAILABLE