批量获取表的DDL语句

今天老外有一个需求，要求将某一个用户下的所有表的创建脚本抓出来，并且每个表保存为一个文件，以下为脚本：

set feedback off
set pagesize 0
set heading off
set verify off
set linesize 200
set serveroutput on size 1000000
set trimspool on
define outputpath
accept outputpath char default ‘c:\’ prompt ‘pls input output path:’
define runfile = ‘c:\runfile.sql’

spool &runfile
prompt set feedback off
prompt set pagesize 0
prompt set heading off
prompt set verify off
prompt set linesize 500
prompt set trimspool on
prompt SET serveroutput on long 999999
prompt EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’STORAGE’,false);

declare
cursor cur1 is select table_name from user_tables;
mytable user_tables.table_name%TYPE;
begin
open cur1;
loop
fetch cur1 into mytable;
exit when cur1%notfound;
dbms_output.put_line(’spool &outputpath’||mytable||’.sql’);
dbms_output.put_line(’prompt drop table ‘||mytable||’;;’);
dbms_output.put_line(’select dbms_metadata.get_ddl(”TABLE”,”’||mytable||”’) from dual;’);
dbms_output.put_line(’prompt ;;’);
dbms_output.put_line(’spool off’);
end loop;
close cur1;
end;
/
spool off
@&runfile