Tablespaces

ALTER TABLESPACE "PIN_D_EVENT_T_PART_4" ADD DATAFILE '/dev/vx/rdsk/l_oradg_D/rawdev_4096M_112' SIZE 4095M;

Resource(role) or unlimited tablespace(system privilege)

This query comes directly from Oracle Enterprise Manager

SELECT *
  FROM (SELECT a.status, a.NAME, a.TYPE, a.extent_management,
               TO_NUMBER (size_mb, '99999999.999') size_mb, a.used_mb,
               TO_NUMBER (used_perc, '99999999.999') used_perc
          FROM (SELECT d.status status, d.tablespace_name NAME,
                       d.CONTENTS TYPE, d.extent_management extent_management,
                       TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0),
                                '99,999,990.900'
                               ) size_mb,
                          TO_CHAR (  NVL (a.BYTES - NVL (f.BYTES, 0), 0)
                                   / 1024
                                   / 1024,
                                   '99999999.999'
                                  )
                       || '/'
                       || TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0),
                                   '99999999.999'
                                  ) used_mb,
                       TO_CHAR (NVL (  (a.BYTES - NVL (f.BYTES, 0))
                                     / a.BYTES
                                     * 100,
                                     0
                                    ),
                                '990.00'
                               ) used_perc
                  FROM SYS.dba_tablespaces d,
                       (SELECT   tablespace_name, SUM (BYTES) BYTES
                            FROM dba_data_files
                        GROUP BY tablespace_name) a,
                       (SELECT   tablespace_name, SUM (BYTES) BYTES
                            FROM dba_free_space
                        GROUP BY tablespace_name) f
                 WHERE d.tablespace_name = a.tablespace_name(+)
                   AND d.tablespace_name = f.tablespace_name(+)
                   AND NOT (    d.extent_management LIKE 'LOCAL'
                            AND d.CONTENTS LIKE 'TEMPORARY'
                           )
                UNION ALL
                SELECT d.status status, d.tablespace_name NAME,
                       d.CONTENTS TYPE, d.extent_management extent_management,
                       TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0),
                                '99,999,990.900'
                               ) size_mb,
                          TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024,
                                   '99999999.999'
                                  )
                       || '/'
                       || TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0),
                                   '99999999.999'
                                  ) used_mb,
                       TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0),
                                '990.00'
                               ) used_perc
                  FROM SYS.dba_tablespaces d,
                       (SELECT   tablespace_name, SUM (BYTES) BYTES
                            FROM dba_temp_files
                        GROUP BY tablespace_name) a,
                       (SELECT   tablespace_name, SUM (bytes_cached) BYTES
                            FROM v$temp_extent_pool
                        GROUP BY tablespace_name) t
                 WHERE d.tablespace_name = a.tablespace_name(+)
                   AND d.tablespace_name = t.tablespace_name(+)
                   AND d.extent_management LIKE 'LOCAL'
                   AND d.CONTENTS LIKE 'TEMPORARY') a)
--WHERE used_perc > 90

Add a new file to a tablespace

select * from (
  select file_name from dba_data_files
  union all
  select file_name from DBA_TEMP_FILES
  union all
  select member file_name from v$logfile
  union all
  select name from v$controlfile
) 
where file_name like '/dev/vx/rdsk/oradg_D/rawdev_4096M_007'
order by 1;

Custom procedure for locating unused raw devices
in the format /dev/vx/rdsk/oradg_D/rawdev_4096M_007
              /dev/vx/rdsk/oradg_D/rawdev_4096M_<progressive>

declare
fixedFinalDigits integer := 3;
firstFileDigit integer := 1;
lastDigit integer := -1;
lastFileName varchar2(250);
currDigit integer;
cursor c is
select * from (
  select file_name from dba_data_files
  union all
  select file_name from DBA_TEMP_FILES
  union all
  select member file_name from v$logfile
  union all
  select name from v$controlfile
) 
--where file_name like '%20%'
order by 1;
rc c%rowtype;
begin
  open c;
  loop
    fetch c into rc;
    exit when c%notfound;
	currDigit := substr(rc.file_name, length(rc.file_name)-fixedFinalDigits+1, fixedFinalDigits);
	if(currDigit-1 != lastDigit)and(currDigit!=firstFileDigit) then
	  dbms_output.put_line('Last is ' || lastDigit || ' - Current ' || currDigit);
	  dbms_output.put_line(rc.file_name);
	  dbms_output.put_line('Use: ' || substr(rc.file_name, 1, length(rc.file_name)-fixedFinalDigits) || to_char(currDigit-1, 'FM000'));
	end if;
	lastDigit := currDigit;
	lastFileName := rc.file_name;
  end loop;
  close c;
end;

How to have tablespaces with different block sizes
Suppose we have a db_block_size of 8192 and we want both a 32K and a 16K 
tablespace, suppose the db_cache_size is 100000. 
Place the db_32K_cache_size = 60000 and the db_16K_cache_size = 40000
    
Windows does not support 32K_cache_size

select name, value/1024/1024 MB from v$parameter where name like '%cache_size';

drop tablespace tbs_aldo including contents and datafiles;

You may experience a long wait, whit something similar to:
   delete from fet$ where file#=:1 and block#=:2 and ts#=:3
Don't worry, extents clean up is in progress, note 1056652.6
--Monitor drop progress:
select count(*) from fet$, v$tablespace
where fet$.ts# = v$tablespace.ts#
and name in ('TBS_ALDO')

CREATE TABLESPACE "tbs" 
    LOGGING 
    DATAFILE 'tbs.dbf' SIZE 100M 
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE 
    MANAGEMENT  AUTO;
    
CREATE TABLE prova (
  p varchar2(50)
)
TABLESPACE tbs
STORAGE (INITIAL 10M NEXT 20M);

select bytes/1024/1024 from dba_extents
where tablespace_name = 'TBS'

0,125

declare
  i integer; 
begin
	 for i in 0..10000 loop
	 	 insert into prova values ('porcellone');
	 end loop;
end;
/

select bytes/1024/1024 from dba_extents
where tablespace_name = 'TBS'

0,125
0,125

SELECT  table_owner||'.'|| table_name, tablespace_name, count(*) riut_tablespace
FROM 
DBA_TAB_PARTITIONS 
group by table_owner, table_name, tablespace_name 
having count(*)>1
order by 1