Showing posts with label Oracle Instances. Show all posts
Showing posts with label Oracle Instances. Show all posts

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;
/