drop2.sql

来自「本人在工程上收集的比较常用的oracle sql语句 有问题请发email:l」· SQL 代码 · 共 43 行

	SET ECHO      OFF;
	SET FEEDBACK  OFF;
	SET VERIFY    OFF;
	SET PAGESIZE  0;
	SET TERMOUT   ON;
	SET HEADING   OFF;
	SET LINESIZE  255;

        --shutdown;
 	--startup mount;
	spool result.sql;
	
        select 'SET ECHO      ON;' from dual;
       --select 'shutdown;' from dual;
       --select 'startup mount;' from dual;

	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 'drop user dyt cascade;' from dual;
 
	select 'drop tablespace '||t.name || ' INCLUDING CONTENTS;'
	from v$datafile d , v$tablespace t 
	where 
	d.ts#=t.ts# and upper(d.name) like '%MSUSER%'; 

	select 'quit;' from dual;
	spool off;


	spool result.bat;
	
        select 'sqlplus system/manager@dytsms @result.sql' from dual;
        select 'rm  $ORACLE_BASE/oradata/dytsms/msuser/*' from dual;
 
	spool off;
quit