Pl/Sql Script to generate DDL script to recreate all tablespaces in Oracle Database.

This Script is shared by my best colleague Ashfaq Ahmed.  

Login as DBA user and execute the following Script to generate DDL script to recreate all tablespaces in Oracle database.


set serveroutput on;

declare
  cursor c1 is select ts#,name from v$tablespace;
  a number;
  b varchar2(800);
  c number;
begin
  dbms_output.enable(60000);
  for c1_rec in c1 loop
      select count(*) into a from v$datafile where ts#=c1_rec.ts#;
        if a=1 then
           select name,round(bytes/1024/1024) into b,c from v$datafile where ts#=c1_rec.ts#;
           dbms_output.put_line('create tablespace '||c1_rec.name||' datafile '||''''||b||''''||' size '||c||'m autoextend on next 4m segment space management auto;');
        elsif a>1 then
           select name,round(bytes/1024/1024) into b,c from v$datafile where ts#=c1_rec.ts# and file#=(select min(file#) from v$datafile
                                                                          where ts#=c1_rec.ts#);
           dbms_output.put_line('create tablespace '||c1_rec.name||' datafile '||''''||b||''''||' size '||c||'m autoextend on next 4m segment space management auto;');
         
           for d_rec in (select name file_name,round(bytes/1024/1024) file_size from v$datafile where ts#=c1_rec.ts# )
           loop
           dbms_output.put_line('alter tablespace '||c1_rec.name||' add datafile '||''''||d_rec.file_name||''''||' size '||d_rec.file_size||'m;');      
           end loop;
         end if;
      end loop;
end;

Temp File issue :- ORA-01187: cannot read from file because it failed verification tests

ERROR:

ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/d01/ora10g/OraHome1/oradata/owcdsup/temp01.dbf'
01187. 00000 -  "cannot read from file %s because it failed verification tests"
*Cause:    The data file did not pass the checks to insure it is part of the
           database. Reads are not allowed until it is verified.
*Action:   Make the correct file available to the database. Then, either open
           the database, or execute ALTER SYSTEM CHECK DATAFILES.


Steps to Fix the issue.

Execute following queries to see above error.
select * from DBA_TEMP_FILES;

select file_name from dba_temp_files;

------------------

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;


Query to view existing temp file path.

Execute below query to find existing temp path .
select * from V$TEMPFILE;

Get temp file path and note it later is will be used to delete from OS and also to recreate the same file with same name.

Query to find temp tablespace name
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';


Query to change default temp tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Query to drop temp tablespace and datafiles
--drop tablespace temp2 including contents and datafiles;

Query to delete temp file
--ALTER DATABASE TEMPFILE '/d01/ora10g/OraHome1/oradata/temp01.dbf' DROP;
-- alter database tempfile '/d01/ora10g/OraHome1/oradata/temp01.dbf' drop including datafiles;

Also drop datafile from the OS path if it exists.

Query to add temp data file to temp tablespace.

ALTER TABLESPACE TEMP
   ADD TEMPFILE  '/d01/ora10g/OraHome1/oradata/temp01.dbf' SIZE 2G ;

Query to  Create temporary tablespace and add new datafile.
 create temporary tablespace TEMP
 tempfile '/d01/ora10g/OraHome1/oradata/temp01.dbf'
 SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED