1.创建目录utl_dir用于存放导出的创建脚本
create or replace directory utl_dir as 'd:/'; 2.给所有用户赋予读写utl_dir的权限 grant write,read on directory utl_dir to public; 3.创建存储过程,用来导出单个对象的创建脚本 create or replace procedure exportddl(p_type varchar2,p_name varchar2,p_filename varchar2) is begin declare l_file utl_file.file_type; l_buffer varchar2(100); l_amount binary_integer := 100; l_pos integer := 1; l_clob clob; l_clob_len integer; begin select dbms_metadata.get_ddl(upper(p_type),p_name)||';' into l_clob from dual; l_clob_len := dbms_lob.getlength(l_clob); l_file := utl_file.fopen('UTL_DIR', p_filename||'.sql', 'a', 2000); while l_pos < l_clob_len loop dbms_lob.read(l_clob, l_amount, l_pos, l_buffer); utl_file.put_line(l_file, l_buffer); l_pos := l_pos + l_amount; end loop; utl_file.fclose(l_file); end; end exportddl; 4.创建存储过程,用来导出所有对象(表、索引、视图、同义词)的创建脚本 create or replace procedure exportddl_all(p_filename varchar2) is begin for x in (select table_name from user_tables) loop exportddl('TABLE',x.table_name,p_filename); end loop; for x in (select index_name from user_indexes) loop exportddl('INDEX',x.index_name,p_filename); end loop; for x in (select view_name from user_views) loop exportddl('VIEW',x.view_name,p_filename); end loop; for x in (select synonym_name from user_synonyms) loop exportddl('SYNONYM',x.synonym_name,p_filename); end loop; end exportddl_all; 5.使用过程导出scott用户所有对象的创建脚本 conn scott/tiger exec exportddl_all('123'); |
导出所有对象(表、索引、视图、同义词)的创建脚本
最新推荐文章于 2024-01-03 09:31:15 发布