Sql script for exporting schema of an instances in Oracle

set serveroutput on
set trimspool on
set line 500
set head off
set feed off

spool exp_test.sql

declare
username varchar2(30);
dba_users varchar2(30);
user_id varchar2(30);
begin

select username,user_id
from dba_users;

dbms_output.put_line('spool exp.'||username||'.'||
to_char(sysdate, 'ddMonyy')||'.log');


-- Loop through schemas
for c1 in (select username us
from sys.dba_users)
loop
dbms_output.put_line(exp 'sys/||sys||'owner='||c1.us||' file=testdump.dmp log='||username||'.log');

end loop;
dbms_output.put_line('spool off');
end;
/

No comments:

Post a Comment

Please Provide your feedback here