Monday, April 20, 2009

Add a semi colon for dbms_metadata.get_ddl

execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

select 'SELECT dbms_metadata.get_ddl(''USER'','''||USERNAME||''') stmt FROM dual;'
from dba_users
where profile <> 'DEFAULT'
and lock_date < sysdate
and not exists (select object_name from dba_objects where owner = username)
order by username;