1.查看数据库中状态为open的用户http://www.cndba.cn/hbhe0316/article/4903http://www.cndba.cn/hbhe0316/article/4903

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.创建用户SQLhttp://www.cndba.cn/hbhe0316/article/4903http://www.cndba.cn/hbhe0316/article/4903

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.授权http://www.cndba.cn/hbhe0316/article/4903http://www.cndba.cn/hbhe0316/article/4903

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.如果是多个用户，则需要用”,”分开。http://www.cndba.cn/hbhe0316/article/4903http://www.cndba.cn/hbhe0316/article/4903

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle