钟祥信息网
科技
当前位置:首页 > 科技

DBA Scripts-获取用户创建语句

发布时间:2019-09-13 20:52:25 编辑:笔名

今天新开一个分类: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;

查看本文来源

血栓的前期症状有哪些
汉森四磨汤不适用人群
孩子脸色发黄怎么回事
小儿积食的原因