Desativação de um Schema

--VERIFICAR SE TEM ALGUM USUÁRIO USANDO O SISTEMA

SELECT SADDR, SID, USERNAME, LOGON_TIME, STATUS, OSUSER, MACHINE, PROGRAM
FROM V$SESSION
WHERE USERNAME LIKE 'USER%';

--VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

SELECT USERNAME FROM DBA_USERS
WHERE USERNAME LIKE 'USER%';

--VERIFICA ATRIBUTOS DO USUÁRIO

SELECT * FROM DBA_USERS
WHERE USERNAME LIKE 'USER%';

SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTOR LIKE 'USER%';

--VERIFICA PREVILEGIOS DO USUÁRIO

SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE LIKE 'USER%';

SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE LIKE 'USER%';

--CONTA OBJETOS DO SCHEMA

SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER LIKE LIKE 'USER%'
GROUP BY OBJECT_TYPE;

--DESATIVA USUÁRIO

ALTER USER USER ACCOUNT LOCK;
ALTER USER USER PASSWORD EXPIRE;

--VERIFICA STATUS DA CONTA

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS
WHERE USERNAME LIKE 'USER%';