// GERAR O SQL PARA DELETAR TODAS AS TABELAS DE UM USUÁRIO
select 'drop table '||table_name||' cascade constraints;' from user_tables;
// grant select para usuário específico
grant select on dono_da_tabela.tabela to usuario_que_recebe_a_permissao;
// grant synonym para usuário específico
grant create synonym to usuario_que_recebe_a_permissao;// criar sinônimo
create or replace synonym nome_do_sinonimo for dono_da_tabela.tabela;
// consulta lock
set heading on
set pages 70
set lines 150
col wu format A18 head "USER aguardando..."
col ws format A17 head "SID Aguardando... "
col ws1 format A17 head "Serial Aguardando... "
col wp format A17 head "PID Aguardando... "
col hu format A17 head "-- USER Lockador --"
col hs format A17 head "-- SID Lockador -- "
col hs1 format A17 head "-- Serial Lockador -- "
col hp format A17 head "-- PID Lockador --"
SELECT /*+ RULE */
rpad(substr(A.USERNAME, 1, 15), 15, ' ') wu,
rpad(substr(to_char(W.SID), 1, 12), 12, ' ') ws,
rpad(substr(to_char(a.serial#
rpad(substr(to_char(p1.spid), 1, 12), 12, ' ') wp,
rpad(substr(B.USERNAME, 1, 15), 15, ' ') hu,
rpad(substr(to_char(H.SID), 1, 12), 12, ' ') hs,
rpad(substr(to_char(b.serial#
rpad(substr(to_char(p2.spid), 1, 12), 12, ' ') hp
FROM GV$SESSION B,
GV$LOCK H,
GV$LOCK W,
GV$SESSION A,
GV$process p1,
GV$process p2
WHERE H.LMODE <> 0
AND H.LMODE <> 1
AND W.REQUEST <> 0
AND H.CTIME > 60
AND B.USERNAME NOT IN ('PERFANALYSIS')
AND H.TYPE = W.TYPE
AND H.ID1 = W.ID1
AND H.ID2 = W.ID2
AND B.SID = H.SID
AND B.INST_ID = H.INST_ID
AND W.SID = A.SID
AND W.INST_ID = A.INST_ID
AND A.paddr = P1.addr
AND B.paddr = P2.addr
ORDER BY H.ctime desc;
//LISTAR PERMISSÕES USUÁRIOS ORACLE
select * from (
select 'GRANT '||privilege||' TO '||grantee||';' from dba_sys_privs
where grantee in (SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP'))
union all
select 'grant '||privilege||' on '||grantor||'.'||table_name||' to '||grantee||';' from dba_tab_privs
where grantee in (SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP'))
union all
select 'GRANT '||GRANTED_ROLE||' TO '||grantee||';' from dba_role_privs
where grantee in (SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP')));
// VERIFICA INSTANCIA
SELECT * FROM GLOBAL_NAME;
// VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA
SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE ‘USER%‘;
//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%';
