Script to create tablespace from existing Database

---Script to create tablespace from existing Database.

SET LONG 20000000
SET PAGESIZE 0
set head off
set echo off
column filename new_val filename
select ora_database_name || '_CREATE_TABLESPACES.sql' filename from dual;
spool &filename
select 'create BIGFILE TABLESPACE ' || df.tablespace_name
 || ' datafile ''' || '+DATA_R1' || ''' size ' || sum(bytes)
 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
 || sum(maxbytes))
 || ' ;' "Script To Recreate Tablespaces"
 from dba_data_files df, dba_tablespaces t
 where df.tablespace_name=t.tablespace_name
 and df.tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS2','UNDOTBS1','TOOLS','ORA_AUDIT','USERS')
 group by df.tablespace_name,autoextensible;

spool off;

---List datafiles

SET LONG 20000000
SET PAGESIZE 0
SET LINESIZE 200
set heading on
set echo on
col TABLESPACE_NAME format a20
col FILE_NAME format a60
select TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024,maxbytes/1024/1024 from dba_data_files order by TABLESPACE_NAME;

---List Tablespace

SET LONG 20000000

SET PAGESIZE 0

SET LINESIZE 200

set heading on

set echo on

col TABLESPACE_NAME format a20

col FILE_NAME format a60

select TABLESPACE_NAME, sum(BYTES)/1024/1024,sum(maxBYTES)/1024/1024 from dba_data_files  group by TABLESPACE_NAME order by TABLESPACE_NAME;