eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« DBA警世录:无知者不可无畏 | Blog首页 | 北京798 大山子艺术区参观记 »

DBA Scripts:获取用户创建语句
modb.pro

今天新开一个分类: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
IDENTIFIED BY VALUES 'E066D214D5421CCC'
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;

CREATE USER eygle
IDENTIFIED BY VALUES 'B726E09FE21F8E83'
DEFAULT TABLESPACE EYGLE
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;

CREATE USER outln
IDENTIFIED BY VALUES '4A3BA55E08595C81'
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;

CREATE USER perfstat
IDENTIFIED BY VALUES 'AC98877DE1297365'
DEFAULT TABLESPACE PERFSTAT
TEMPORARY TABLESPACE TEMP
QUOTA -1 ON PERFSTAT
PROFILE DEFAULT;

CREATE USER test
IDENTIFIED BY VALUES '7A0F2B316C212D67'
DEFAULT TABLESPACE TEST
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;

CREATE USER wmsys
IDENTIFIED BY VALUES '7C9BA362F8314299'
DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;

 

 


历史上的今天...
    >> 2013-07-15文章:
    >> 2011-07-15文章:
    >> 2009-07-15文章:
    >> 2008-07-15文章:
    >> 2007-07-15文章:
           摄影习作-花之灵

By eygle on 2006-07-15 13:09 | Comments (0) | FAQ | 831 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com