RAPIDINHAS SQL ORACLE

// 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#), 1, 12), 12, ' ') ws1,

 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#), 1, 12), 12, ' ') hs1,

 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%';