set linesize 1234 pages 1234
col tablespace format a24
col free heading 'Free(Mb)' format 99999999.9
col total heading 'Total(Mb)' format 999999999.9
col used heading 'Used(Mb)' format 99999999.9
col pct_free heading 'Pct|Free' format 99999.9
col largest heading 'Largest(Mb)' format 99999.9
compute sum of total on report
compute sum of free on report
compute sum of used on report
break on report
select substr(a.tablespace_name,1,24) tablespace,
round(sum(a.total1)/1024/1024, 1) Total,
round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,
round(sum(a.sum1)/1024/1024, 1) free,
round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free,
round(sum(a.maxb)/1024/1024, 1) largest,
max(a.cnt) fragments
from
(select tablespace_name, 0 total1, sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files
group by tablespace_name
union
select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_temp_files
group by tablespace_name) a
group by a.tablespace_name
/

col file_name for a50;
select file_name,tablespace_name,bytes/1024/1024,status from dba_data_files where tablespace_name='&TABLESPACE_NAME';

BEGIN
FOR id IN 1..10000
loop
INSERT INTO SYS.testtable(id,name) VALUES(id,'dba');
END loop;
END;
/

alter tablespace PSAPB31 add datafile '/oracle/B31/sapdata18/b31_214/b31.data214.dbf' size 30720M;

ALTER DATABASE datafile '/oracle/NXD/sapdata10/sr3db_3/sr3db.data3' resize 15000M;

CREATE TABLESPACE tbspdb DATAFILE '/data/oracle/app/oracle/oradata/cdb/tbs_perm_pdb.dat' SIZE 10M ;

select file#, name, round(bytes/(1024*1024),2) "Temp file SIZE IN MB's" from v$tempfile;

alter tablespace TEMP add tempfile '/home/oracle/app/oracle/oradata/testdb/temp02.dbf' size 10M;

select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 from dba_Data_files;

alter database datafile '/data/app/oracle/ts_32k.dbf' autoextend on maxsize unlimited;

alter database datafile '/data/oracle/app/oracle/oradata/prim/pdbprim/users01.dbf' autoextend off;

CREATE TABLESPACE tbs_perm_02 DATAFILE 'tbs_perm_02.dat' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

CREATE BIGFILE TABLESPACE BIGTBS1 DATAFILE '/data/app/oracle/oradata/bigtbs01.dbf' SIZE 10M AUTOEXTEND ON NEXT 20M MAXSIZE 100G;

SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

TABLESPACE_NAME                BIG
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
BIGTBS1                        YES

select name,total_mb,free_mb, (free_mb/total_mb)*100 "%Free" from v$asm_diskgroup;