move_datafiles_in_tablespace


set echo off
set head off
set heading off
set pagesize 0
set linesize 500
set verify off
set trimspool on
set feedback off

ACCEPT T1 Prompt 'Tablespace Name >>> '
ACCEPT T2 Prompt 'Database Name   >>> '

spool &t1..sql

select 'alter tablespace &t1 read only;' from dual ;

SELECT 'host cp -ip '||file_name||'  &t3/oradata/&t2/'||SUBSTR(file_name,instr(file_name,'/',-1)+1,40)
FROM dba_data_files WHERE tablespace_name='&t1' order by file_name;

select 'alter tablespace &t1 offline;' from dual ;

SELECT 'alter database rename file '||''''||file_name||''''||' to  '||''''||'&t3/oradata/&t2/'||SUBSTR(file_name,instr(file_name,'/',-1)+1,40)||''''||'; '
FROM dba_data_files WHERE tablespace_name='&t1' order by file_name;

select 'alter tablespace &t1 online;' from dual ;

Select  'select file_name from dba_data_files WHERE tablespace_name= '||''''||'&t1'||''''||';' from dual;

Select ' ACCEPT T4 Prompt '||''''||' Read Write yapmak igin Entera basiniz '||'''' from dual;

select ' alter tablespace &t1 read write;' from dual ;

spool off

ACCEPT T5 Prompt 'Please press  to Enter   '

-- start &t1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: