今天新开一个分类:Scripts,用来收集和记录一些DBA经常使用的脚本。
这些脚本有的来自网络，有的来自自己编写，记录在这里供大家参考，同时也给自己一个重新熟悉的过程。
很多时候我们在作数据库迁移时需要进行重建用户等工作,这时就需要获得用户信息,本脚本就用于获取用户的创建语句,具体脚本如下,来源Metalink:
SET verify off;
SET termout off;
SET feedback off;
SET echo off;
SET pagesize 0;
SET timeing off;SET termout on
SELECT 'Creating user build script...' FROM DUAL;
SET termout off;CREATE TABLE usr_temp( lineno NUMBER,usr_name VARCHAR2(30),text VARCHAR2(80))
/DECLARE
CURSOR usr_cursor
IS
SELECT username, PASSWORD, default_tablespace, temporary_tablespace,
PROFILE
FROM SYS.dba_users
WHERE username != 'SYS' AND username != 'SYSTEM'
ORDER BY username;CURSOR qta_cursor (c_usr VARCHAR2)
IS
SELECT tablespace_name, max_bytes
FROM SYS.dba_ts_quotas
WHERE username = c_usr;lv_username SYS.dba_users.username%TYPE;
lv_password SYS.dba_users.PASSWORD%TYPE;
lv_default_tablespace SYS.dba_users.default_tablespace%TYPE;
lv_temporary_tablespace SYS.dba_users.default_tablespace%TYPE;
lv_profile SYS.dba_users.PROFILE%TYPE;
lv_tablespace_name SYS.dba_ts_quotas.tablespace_name%TYPE;
lv_max_bytes SYS.dba_ts_quotas.max_bytes%TYPE;
lv_string VARCHAR2 (80);
lv_lineno NUMBER := 0;PROCEDURE write_out (p_line INTEGER, p_name VARCHAR2, p_string VARCHAR2)
IS
BEGIN
INSERT INTO usr_temp
(lineno, usr_name, text
)
VALUES (p_line, p_name, p_string
);
END;
BEGIN
OPEN usr_cursor;LOOP
FETCH usr_cursor
INTO lv_username, lv_password, lv_default_tablespace,
lv_temporary_tablespace, lv_profile;EXIT WHEN usr_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := ('CREATE USER ' || LOWER (lv_username));
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;IF lv_password IS NULL
THEN
lv_string := 'IDENTIFIED EXTERNALLY';
ELSE
lv_string := ('IDENTIFIED BY VALUES ''' || lv_password || '''');
END IF;write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'DEFAULT TABLESPACE ' || lv_default_tablespace;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TEMPORARY TABLESPACE ' || lv_temporary_tablespace;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;OPEN qta_cursor (lv_username);
LOOP
FETCH qta_cursor
INTO lv_tablespace_name, lv_max_bytes;EXIT WHEN qta_cursor%NOTFOUND;
lv_lineno := lv_lineno + 1;IF lv_max_bytes IS NULL
THEN
lv_string := 'QUOTA UNLIMITED ON ' || lv_tablespace_name;
ELSE
lv_string :=
'QUOTA ' || lv_max_bytes || ' ON ' || lv_tablespace_name;
END IF;write_out (lv_lineno, lv_username, lv_string);
END LOOP;CLOSE qta_cursor;
lv_string := ('PROFILE ' || lv_profile || ';');
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := ' ';
write_out (lv_lineno, lv_username, lv_string);
END LOOP;CLOSE usr_cursor;
END;
/SPOOL create_users.sql
SET heading off
SET recsep off
COL test format a80 word_wrap
SELECT text
FROM usr_temp
ORDER BY usr_name, lineno;SPOOL off;
DROP TABLE usr_temp;
EXIT
运行该脚本后会产生一个create_users.sql的输出文件,输出内容参考范例如下:
CREATE USER dbsnmp CREATE USER eygle CREATE USER outln CREATE USER perfstat CREATE USER test CREATE USER wmsys |
