create.sql

本人在工程上收集的比较常用的oracle sql语句 有问题请发email:[e-mail]

	SET ECHO      OFF;
	SET FEEDBACK  OFF;
	SET VERIFY    OFF;
	SET PAGESIZE  0;
	SET TERMOUT   ON;
	SET HEADING   OFF;
 	SET LINESIZE  255;
	spool offline.sql;
	
	SELECT 
  	'alter database datafile ''' || d.name || ''' offline;' || chr(10) || chr(10)
	from 
	v$datafile d , v$tablespace t 
	where 
	d.ts#=t.ts# and upper(d.name) like '%MSUSER%'; 

        select 'quit;' from dual;

	spool off;

	ACCEPT path CHAR PROMPT 'Enter new tablespace path(like c:\dytsms\): '; 
	spool rebuild.bat;
        select 'sqlplus dyt/sql@dytsms @offline.sql' from dual;
	select 'mkdir \a\b\c\d ' || '&path' from dual;

	select 'move '|| d.name || ' ' || '&path'
	from 
	v$datafile d , v$tablespace t 
	where 
	d.ts#=t.ts# and d.name like '%MSUSER%'; 

        select 'sqlplus dyt/sql@dytsms @online.sql' from dual;

	spool off;

	spool online.sql;
	
	SELECT 
  	'alter database rename file ''' || d.name || ''' to ''' || '&path' || t.name || '.ORA'';'
	from 
	v$datafile d , v$tablespace t 
	where 
	d.ts#=t.ts# and d.name like '%MSUSER%'; 

	SELECT 
  	'alter database datafile  ''' || '&path' || t.name || ''' online;' || chr(10) || chr(10)
	from 
	v$datafile d , v$tablespace t 
	where 
	d.ts#=t.ts# and d.name like '%MSUSER%'; 


        select 'quit;' from dual;

	spool off;
quit;