Using DBMS_METADATA to get table and index definitions

Below is a quick script I use to extract table and index definitions I may need from time to time. Enjoy…

set lines 255 trims on
set serveroutput on size unlimited
declare
  l_tab_clob clob := empty_clob();
  l_ind_clob clob := empty_clob();
begin
  for cur in (select table_name from user_tables) loop
    select dbms_metadata.get_ddl('TABLE',cur.table_name,user) into l_tab_clob from dual;
    dbms_output.put_line(l_tab_clob);
    for ind in (select index_name from user_indexes where table_name = cur.table_name) loop
      select dbms_metadata.get_ddl('INDEX',ind.index_name,user) into l_ind_clob from dual;
      dbms_output.put_line(l_ind_clob);
    end loop;
  end loop;    
end;
/