SET TERMOUT ON
SET ECHO OFF
SET HEADING OFF
SET VERIFY OFF
set linesize 300
set pagesize 50
 
ACCEPT Server PROMPT 'Enter a server alias/connection string (MAGICASH): ' DEFAULT MAGICASH 
ACCEPT SYSPsw PROMPT 'Enter MAGICASH  SYS PASSWORD: ' DEFAULT NCTSYS

CONNECT SYS/&SYSpsw@&Server AS SYSDBA

spool imp_user_ALL.bat

---  Вывод команд на импорт
 

SELECT 'imp.exe userid=''sys' || '/' || '&SYSPsw' || '@' ||
       (SELECT NAME FROM v$database) || ' as sysdba''' || ' file=exp_' ||
       t.USERNAME || '_' || to_char(sysdate, 'dd_mm_yyyy') || '.dmp ' ||
       'log=imp_' || t.USERNAME || '_' || to_char(sysdate, 'dd_mm_yyyy') ||
       '.log ' || 'FROMUSER=' || t.USERNAME || ' TOUSER=' || t.USERNAME ||
       ' GRANTS=y ' || 'INDEXES=y ' || 'ROWS=y ' || 'CONSTRAINTS=y' ||
       ' FEEDBACK=5000000' "1"
  FROM DBA_USERS t,
       (SELECT owner,
               TO_CHAR(ROUND(SUM(BYTES) / 1024 / 1024, 1)) || ' Mb' sch_size
          FROM dba_segments t1
         GROUP BY owner) objsize
 WHERE objsize.owner = t.username
   and ACCOUNT_STATUS = 'OPEN'
   and DEFAULT_TABLESPACE not in ('SYSTEM', 'SYSAUX')
 order by t.USERNAME asc;



spool off
 



SET TERMOUT ON
SET ECHO ON
EXIT
