Comandos e dicas para Oracle.

oracle.sql

-- Configura formato de data padrão
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

-- Verificar as constraints de uma tabela
SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = '<TABELA>'

-- Alterando o valor atual de uma sequence
alter sequence <SEQUENCE> increment by 64711;
select <SEQUENCE>.NEXTVAL from dual;
alter sequence <SEQUENCE> increment by 1;



-- Verifica se existe espaço disponível no arquivo de dados (datafiles) e se eles são extensíveis 
select
a.file_id,
b.file_name,
b.autoextensible,
b.bytes/1024/1024 t1,
sum(a.bytes)/1024/1024 t2
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by
a.file_id,b.file_name, autoextensible, b.bytes/1024/1024


-- Mostra quem está lockando o que
select 
  oracle_username
  os_user_name,
  locked_mode,
  object_name,
  object_type
from 
  v$locked_object a,dba_objects b
where 
  a.object_id = b.object_id

-- Comentários no oracle
SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = '<NOME_DA_TABELA>'

-- Recupera select em formato XML
select dbms_xmlgen.getxml('select employee_id, first_name,2 last_name, phone_number from employees where rownum < 6') from dual

-- Procura todas as referências a uma tabela
select *
from dba_dependencies
where referenced_name = '<TABELA>' and owner = '<owner>'
order by referenced_owner, TYPE, NAME;


-- Verifica quem está bloqueando
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;


-- Mostra todos os usuários logados
SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;


-- Mostra objetos locados
select 
distinct to_name object_locked
from 
v$object_dependency 
where 
to_address in
(
select /*+ ordered */
w.kgllkhdl address
from 
dba_kgllock w, 
dba_kgllock h, 
v$session w1, 
v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
);


-- Busca por conexões lentas
select
   b.machine         host,
   b.username        username,
   b.server,
   b.osuser          os_user,
   b.program         program,
   a.tablespace_name ts_name,
   row_wait_file#    file_nbr,
   row_wait_block#   block_nbr,
   c.owner,
   c.segment_name,
   c.segment_type
from
   dba_data_files a,
   v$session      b,
   dba_extents    c 
where b.row_wait_file# = a.file_id
and c.file_id = row_wait_file#
and row_wait_block#  between c.block_id and c.block_id + c.blocks - 1
and row_wait_file# <> 0
and type='USER';



-- Recuperar todos os privilégios sobre um objeto
SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv
FROM dba_tab_privs
WHERE table_name = '<TABLE_NAME>' and grantee IN (
  SELECT role
  FROM dba_roles)
GROUP BY table_name, grantee;

-- Backup de banco de dados apenas os metadados
expdp backup/backup content=metadata_only directory=expdp dumpfile=expdp_metadata.dmp logfile=expdpmetadata.log