Oracle 数据泵迁移用户创建 SQL语句

原创

wx62e28ac222a20 2022-08-05 12:19:17 ©著作权

文章标签 数据库 python mysql linux oracle 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.查看数据库中状态为open的用户

SQL> set pagesize 200
SQL> set linesize 200
SQL> col username for a20
SQL> col account_status for a20
SQL> col profile for a20
SQL> select username,account_status,created,PROFILE from dba_users where account_status='OPEN' order by created;

USERNAME             ACCOUNT_STATUS       CREATED            PROFILE
-------------------- -------------------- ------------------ --------------------
SYS                  OPEN                 2019:04:1700:56:32 DEFAULT
SYSTEM               OPEN                 2019:04:1700:56:33 DEFAULT
C##HBHE              OPEN                 2021:10:0722:04:07 DEFAULT

2.创建用户SQL

select 'create user ' || t.username || ' identified by values ' || chr(39) ||
       u.password || chr(39) || ' default tablespace ' ||
       t.default_tablespace || ' profile ' || p.name || ' Temporary TABLESPACE '|| TEMPORARY_TABLESPACE  ||';' create_user_withoutpass
  from dba_users t, sys.user$ u, sys.profname$ p, sys.user_astatus_map m
 where t.user_id = u.user#
   and u.resource$ = p.profile#
   and u.astatus = m.status#
   and t. username in ('C##HBHE');

CREATE_USER_WITHOUTPASS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create user C##HBHE identified by values '' default tablespace TBS01 profile DEFAULT Temporary TABLESPACE TEMP1;

3.授权

SQL> select 'GRANT connect,resource,unlimited tablespace,DBA to ' ||username|| ';' from dba_users where username in ('C##HBHE');

'GRANTCONNECT,RESOURCE,UNLIMITEDTABLESPACE,DBATO'||USERNAME||';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GRANT connect,resource,unlimited tablespace,DBA to C##HBHE;

4.如果是多个用户，则需要用”,”分开。

Linux,oracle