欢迎关注微信公众号 JiekeXu DBA 之路
2 / 21
col ACCOUNT_STATUS for a18
select USERNAME,ACCOUNT_STATUS,CREATED,PROFILE,DEFAULT_TABLESPACE from dba_users
where account_status='OPEN' and DEFAULT_TABLESPACE not in ('SYSTEM','USERS','OGG_TBS')
order by CREATED asc;
USERNAME ACCOUNT_STATUS CREATED PROFILE
DEFAULT_TABLESPACE
------------------------- ------------------ --------- -------------------- ------------------------------
TEST_PY OPEN 25-NOV-20 DEFAULT
TEST_PY_DATA
DBA_BAK OPEN 08-JUN-22 DEFAULT
DBA_BAK_DATA
select USERNAME,ACCOUNT_STATUS,CREATED,PROFILE,DEFAULT_TABLESPACE from dba_users
where account_status='OPEN' and DEFAULT_TABLESPACE!='USERS' order by CREATED asc;
用户权限收集
DROP TABLE SCOTT.T_TMP_USER_JIEKE;
CREATE TABLE SCOTT.T_TMP_USER_JIEKE( ID NUMBER, USERNAME VARCHAR2(50), EXEC_SQL VARCHAR2(4000),CREATE_TYPE
VARCHAR2(20) );
DROP SEQUENCE SCOTT.S_T_TMP_USER_JIEKE;
CREATE SEQUENCE SCOTT.S_T_TMP_USER_JIEKE;
BEGIN
FOR CUR IN (SELECT D.USERNAME,
D.DEFAULT_TABLESPACE,
D.ACCOUNT_STATUS,
'create user ' || D.USERNAME || ' identified by ' ||
D.USERNAME || ' default tablespace ' ||
D.DEFAULT_TABLESPACE || ' TEMPORARY TABLESPACE ' ||
D.TEMPORARY_TABLESPACE || ';' CREATE_USER,
REPLACE(TO_CHAR(DBMS_METADATA.GET_DDL('USER',D.USERNAME)),CHR(10),'') CREATE_USER1
FROM DBA_USERS D
--WHERE D.USERNAME NOT IN
('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','DMSYS','DVSYS','EXFSYS','FLOWS_FILES',
文档被以下合辑收录
评论