Script for generate the Tablespace and Alter Tablespace Useful for Export and Import(Exdp and impdp) and DB Migration using expdp and impdp

Script for generate the Tablespace and Alter Tablespace  Useful for Export and Import(exdp and impdp) and DB migration using expdp and impdp


-- User Case: Useful for Export and Import(Exdp and impdp)
-- DB Migration Using expdp and impdp

set echo off heading off termout off feed off pages 0 numwidth 10 linesize 80

spool create_ts.sql

select 'set echo off termout off feed off pages 0' || chr(10) ||

       'spool create_ts.lis'

from dual

/

--

-- 1st: For each tablespace, select the first datafile that was

-- created and create this again as first file for this (each) tablespace

--

select 'CREATE TABLESPACE ' || f1.tablespace_name || chr(10) ||

       ' DATAFILE ' || '''' || f1.file_name || '''' || ' SIZE ' || '2G' || chr(10) ||

       ' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED' ||

       ';'

from dba_tablespaces t1, dba_data_files f1

where f1.tablespace_name = t1.tablespace_name

and f1.tablespace_name not in('SYSTEM','USERS','UNDOTBS1','SYSAUX')

and f1.file_id = (select min(f2.file_id)

                    from dba_data_files f2

                   where f2.tablespace_name = f1.tablespace_name)

order by f1.file_id

/

--

-- 2nd: For each tablespace, create additional datafiles if any there.

--

select 'ALTER TABLESPACE ' || f1.tablespace_name || chr(10) ||

       ' ADD DATAFILE ' || '''' || f1.file_name || '''' || ' SIZE ' || '2G' || chr(10) || ' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED'||';'

from dba_data_files f1

where f1.tablespace_name not in('SYSTEM','USERS','UNDOTBS1','SYSAUX')

and f1.file_id > (select min(f2.file_id)

                    from dba_data_files f2

where f2.tablespace_name = f1.tablespace_name)

order by f1.file_id

/

--

-- 3rd: For each temporary tablespace, create additional tempfile if any there.

--

select 'CREATE TEMPORARY TABLESPACE ' || f1.tablespace_name || chr(10) ||

       ' TEMPFILE ' || '''' || f1.file_name || '''' || ' SIZE ' || '2G' || chr(10) ||

       ' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED' ||

       ';'

from dba_tablespaces t1, dba_temp_files f1

where f1.tablespace_name = t1.tablespace_name

and f1.tablespace_name not in('SYSTEM','USERS','UNDOTBS1','SYSAUX')

and f1.file_id = (select min(f2.file_id)

                    from dba_temp_files f2

                   where f2.tablespace_name = f1.tablespace_name)

order by f1.file_id

/

--

-- 4th: For each temporary tablespace, create additional tempfiles if any there.

--

select 'ALTER TABLESPACE ' || f1.tablespace_name || chr(10) ||

       ' ADD TEMPFILE ' || '''' || f1.file_name || '''' || ' SIZE ' || '2G' || chr(10) || ' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED'||';'

from dba_temp_files f1

where f1.tablespace_name not in('SYSTEM','USERS','UNDOTBS1','SYSAUX')

and f1.file_id > (select min(f2.file_id)

                    from dba_temp_files f2

where f2.tablespace_name = f1.tablespace_name)

order by f1.file_id

/

select 'spool off;' from dual

/

spool off;