Oracle批量创建用户和导入导出的脚本

-- 在cmd下执行

SELECT 'exp ' || username || '/' || username || ' file=d:\' || username || '.dmp owner=' || username || ' log=d:\' ||
username || '.txt'
FROM dba_users
WHERE account_status = 'OPEN';

-- 在被导出库执行，获取语句后在导出库执行

SELECT 'create user ' || username ||' identified by ' || username || ' default tablespace ' || default_tablespace || ' temporary tablespace ' || temporary_tablespace || ';'
FROM dba_users
WHERE account_status = 'OPEN';

SELECT 'grant dba to ' || username || ';' FROM dba_users WHERE account_status = 'OPEN';

-- 在cmd下执行

SELECT 'imp ' || username || '/' || username || ' file=d:\' || username || '.dmp fromuser=' || username || ' touser=' ||
username || ' ignore=y log=d:\' || username || '_imp.txt'
FROM dba_users
WHERE account_status = 'OPEN';