-- Alter tablespace

-- Examples:
-- change default max extents of tablespace
ALTER TABLESPACE "DB_CUSTOMER_RECORD_DATA" DEFAULT STORAGE ( INITIAL 100K NEXT 100K MAXEXTENTS 10000 PCTINCREASE 20 );

-- add a datafile to a tablespace
-- NT
ALTER TABLESPACE USER_DATA 
   ADD DATAFILE 'D:\ORADATA\DBSTAR\DBSTAR_USR02.DBF' size 100M  
   AUTOEXTEND ON NEXT 30M MAXSIZE 1000M;

-- Unix
ALTER TABLESPACE CNV_DATA 
   ADD DATAFILE '/backup/oradata/DBSTARP1/DBSTARP1_cnv_data02.dbf' size 300M  
   AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;

-- alter temp tablespace/datafiles
select file_name, bytes from dba_temp_files;
alter database tempfile 'F:\ORACLE\ORADATA\PIDEV\TEMP01.DBF' resize 5G maxsize 20G;
alter database tempfile 'F:\ORACLE\ORADATA\PIDEV\TEMP01.DBF' autoextend on maxsize 20G;