Script para listar objetos dependentes – SQL Server

Neste artigo irei mostrar um script que mostra todos os objetos e suas respectivas dependências em um database.

Este script é útil para algumas situações, por exemplo: você irá realizar uma manutenção em uma procedure, e para isto você precisa saber quais são os objetos ‘dependetes’ desta mesma procedure a fim de verificar se estes objetos dependentes poderão ser impactados. Outro exemplo: você irá adicionar um novo campo em um tabela X, com este script você pode verificar a quais procedures, views ou functions utilizam esta tabela, entre outros.

O scritpt retorna os seguintes campos:

– DBName: nome do database;
– Object_Schema: o schema do objeto pai;
– Object_Name: o nome do objeto pai;
– Child_Schema: o schema do objeto dependente (filho);
– Child_Name: o nome do objeto dependente (filho);
– Child_Type: o tipo do objeto, por exemplo: USER_TABLE, SQL_STORED_PROCEDURE, etc.

O script analisa as views sys.objects e sysdepends.

Script

Use <Database>
SELECT
	 DB_Name() As DBName 
	,Referencing.DBObject_Schema As Object_Schema
	,Referencing.DBObject As [Object_Name]
	,Referenced.Child_DBBObject_Schema As [Child_Schema]
	,Referenced.Child_DBObject As [Child_Name]
	,Referenced.Child_DBObject_Type As [Child_Type]
FROM
(
	SELECT
		 [Object_ID] As DBObject_Id
		,Schema_Name([Schema_ID]) As DBObject_Schema
		,Name As DBObject
		,Type_Desc As DBObject_Type
	FROM
		sys.objects
	WHERE
		Type Not In('D','IT','PK','SQ','UQ','U','S','TR')
) Referencing
LEFT OUTER JOIN
(
	SELECT
		 Id As Parent_DBObject_Id
		,DepId As Child_DBObject_Id
		,Schema_Name([Schema_ID]) As Child_DBBObject_Schema
		,Name As Child_DBObject
		,Type_Desc As Child_DBObject_Type
	FROM
		sysdepends 
	JOIN	
		sys.objects
	ON
		[Object_ID] = DepId
	GROUP BY
		Id, DepId, [Schema_Id], Name, Type_Desc
) Referenced
ON
	Referencing.DBObject_Id = Referenced.Parent_DBObject_Id

Se você quiser obter as dependências de um objeto especifico, a cláusula WHERE deve ficar desta maneira:

(...)
ON
	Referencing.DBObject_Id = Referenced.Parent_DBObject_Id
WHERE 
	Referencing.DBObject = 'ObjectName'

Ou caso você queria obter os objetos pais de um objeto especifico (são referenciados), a cláusula WHERE deve ficar desta maneira:

(...)
ON
	Referencing.DBObject_Id = Referenced.Parent_DBObject_Id
WHERE 
	Referenced.Child_DBObject = 'ObjectName'
Anúncios

Verifica coleta de estatísticas das Tabelas e Índices – Oracle

Neste post irei mostrar um script que verifica a data da última coleta de estatísticas dos índices e tabelas no Oracle.

Antes de mostrar o script, irei deixar alguns links de conteúdo caso você ainda tenha alguma duvida, ou também não compreenda muito bem como funciona a coleta de estatísticas no Oracle, ou como fazer uma coleta de estatísticas, etc. São eles:

Gerenciando Estatísticas do Otimizador;
Coletando estatísticas para o otimizador de queries do Oracle;
Entendendo o Otimizador de Coleta de Estatísticas (conteúdo excelente!);
ALL_IND_STATISTICS.

Ambos scripts retornam os seguintes campos:
Schema: a qual schema (dono) o objeto faz parte;
Objeto: o nome do objeto;
Tipo do Objeto: qual é o tipo do objeto, neste caso: tabela ou índice;
Qtd. de Linhas: retorna o número de registros que o objeto possui;
Ultima Coleta: mostra a data e hora que fora realizada a última analise (coleta) no objeto.

Verifica a coleta de estatísticas das tabelas

SELECT
   S.Owner As "Schema"
  ,S.Table_Name As "Objeto"
  ,S.Object_Type As "Tipo do Objeto"
  ,S.Num_Rows As "Qtd. de Linhas"
  ,To_Char(S.Last_Analyzed, 'yyyy-MM-dd HH24:mm:ss') As "Ultima Coleta"
FROM
  sys.dba_tab_statistics S
WHERE
  S.Owner Not In('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
ORDER BY
  S.Last_Analyzed;

Verifica a coleta de estatísticas dos índices

SELECT
   S.Owner As "Schema"
  ,S.Table_Name As "Objeto"
  ,S.Object_Type As "Tipo do Objeto"
  ,S.Num_Rows As "Qtd. de Linhas"
  ,To_Char(S.Last_Analyzed, 'yyyy-MM-dd HH24:mm:ss') As "Ultima Coleta"
FROM
  sys.dba_ind_statistics S
WHERE
  S.Owner Not In('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
ORDER BY
  S.Last_Analyzed;

Tamanho do Banco de Dados em MB – Oracle

Neste artigo irei mostrar um script que mostra o tamanho da base de dados em uma instância do Oracle.

O script retorna os seguintes campos: Data, Undo, Redo, Temp, Livre e Total. Para entendimento de alguns campos retornados no script, segue alguns links para melhor entendimento do significado e a importância de cada um:

Arquivos de Data (Datafiles, tablespaces);
Undo;
Redo;
Temp.

Script:

SET FEED OFF

prompt
prompt TAMANHO DO BANCO DE DADOS EM MB
prompt ===============================

col dados for a10
col undo  for a12
col redo  for a12
col temp  for a12
col livre for a12
col total for a12

select to_char(sum(dados) / 1048576, 'fm99g999g990') dados,
       to_char(sum(undo) / 1048576, 'fm99g999g990') undo,
       to_char(sum(redo) / 1048576, 'fm99g999g990') redo,
       to_char(sum(temp) / 1048576, 'fm99g999g990') temp,
       to_char(sum(free) / 1048576, 'fm99g999g990') livre,
       to_char(sum(dados + undo + redo + temp) / 1048576, 'fm99g999g990') total
from (
  select sum(decode(substr(t.contents, 1, 1), 'P', bytes, 0)) dados,
         sum(decode(substr(t.contents, 1, 1), 'U', bytes, 0)) undo,
         0 redo,
         0 temp,
         0 free
  from dba_data_files f, dba_tablespaces t
  where f.tablespace_name = t.tablespace_name
  union all
  select 0 dados,
         0 undo,
         0 redo,
         sum(bytes) temp,
         0 free
  from dba_temp_files f, dba_tablespaces t
  where f.tablespace_name = t.tablespace_name(+)
  union all
  select 0 dados,
         0 undo,
         sum(bytes * members) redo,
         0 temp,
         0 free
  from v$log
  union all
  select 0 dados,
         0 undo,
         0 redo,
         0 temp,
         sum(bytes) free
  from dba_free_space f, dba_tablespaces t
  where f.tablespace_name = t.tablespace_name and
        substr(t.contents, 1, 1) = 'P'
);

prompt

SET FEED ON

Movendo Arquivos dos Databases – SQL Server

Neste artigo irei mostrar um método para alterar o caminho dos arquivos de log e data dos databases.

Este método pode ser utilizado do SQL Server 2000 até a mais atual, o SQL Server 2012.

Este método contém os seguintes passos:
• Obter o diretório atual dos arquivos;
• Alterar o database para SINGLE_USER, para fechar todas as conexões no banco;
• Alterar o status do database para OFFLINE para que ele não leia mais os arquivos;
• Realizar o comando T-SQL para fazer o “detach” no database, ou seja, durante este processo o db engine ignora a existência dessa base;
• Mover os arquivos para o novo diretório;
• Realizar o comando T-SQL para fazer o “attach” no database;
• Alterar o status do database para ONLINE para subir o database e para ler os arquivos;
• Alterar o database para MULTI_USER para que outras conexões possam ser abertas;
• Verificar se o diretório foi modificado.

Antes de mostrar um exemplo prático, é necessário estar atento à alguns detalhes:
– Você precisa copiar os arquivos para o caminho novo antes de realizar o comando para efetuar a troca, pois isso dará erro, e mesmo com o erro se você tentar subir o database, o mesmo não achará os arquivos, ou seja, o database não irá subir;
– Apesar de ser bem simples o passo-a-passo, tenha muito cuidado ao escrever o nome da pasta e dos arquivos nos comandos;
– O caminho novo (diretório) precisa que o usuário, da qual é executado o Serviço do SQL Server, tenha permissão para escrever, ler e apagar quaisquer arquivos.

Vamos ao exemplo prático:

Neste exemplo criei um database chamado ALAN_TESTE. Os arquivos de data e log do database (ALAN_TESTE) estão no Dir1. Vou mudar o caminho do arquivo de data (mdf) para outro diretório: \Dir2\Data, e também vou mudar o caminho do arquivo de log (ldf) para outro diretório: \Dir2\Log.

1. Obter o diretório atual dos arquivos

-- ROWS = Arquivos de Data
-- LOG  = Arquivos de Log
SELECT
	Type_Desc, Name, Physical_Name, State_Desc
FROM
	sys.master_files 
WHERE
	database_id = DB_ID(N'ALAN_TESTE')

O resultado:

movedb01

2. Alterar o database para SINGLE_USER

Use master 
GO
ALTER DATABASE ALAN_TESTE
	SET SINGLE_USER
GO

3. Alterar o status do database para OFFLINE

Use master 
GO
ALTER DATABASE ALAN_TESTE
	SET OFFLINE 
GO

4. Realizar o comando T-SQL para fazer o “detach” no database

Use master 
GO
sp_detach_db 'ALAN_TESTE'
GO

5. Mover os arquivos para o novo diretório

Mova o arquivo C:\Dir1\ALAN_TESTE.mdf para o diretório C:\Dir2\Data. E também mova o arquivo C:\Dir1\ALAN_TESTE_log.ldf para o diretório C:\Dir2\Log.

6. Realizar o comando T-SQL para fazer o “attach” no database

Use master 
GO
sp_attach_db 'ALAN_TESTE'
	,'C:\Dir2\Data\ALAN_TESTE.mdf'
	,'C:\Dir2\Log\ALAN_TESTE_log.ldf'
GO

7. Alterar o status do database para ONLINE

Use master 
GO
ALTER DATABASE ALAN_TESTE
	SET ONLINE
GO

8. Alterar o database para MULTI_USER

Use master 
GO
ALTER DATABASE ALAN_TESTE
	SET MULTI_USER
GO

9. Verificar se o diretório foi modificado

-- ROWS = Arquivos de Data
-- LOG  = Arquivos de Log
SELECT
	Type_Desc, Name, Physical_Name, State_Desc
FROM
	sys.master_files 
WHERE
	database_id = DB_ID(N'ALAN_TESTE')

Veja que os diretórios foram alterados com sucesso:

movedb02

Não tem muito segredo, mas é importante fazer com cuidado, seguindo passo-a-passo com calma.
É isso!

Reorganizando Indexes – SQL Server

Neste artigo irei mostrar um script que reorganiza todos os Indexes dos databases de usuário (nenhum de sistema).

Links Úteis
O que são Indexes?
Como criar um Index
Boas práticas para criação de Indexes (em inglês)

Script

DECLARE
	 @Database		varchar(255)
	,@Table			varchar(255)
	,@Cmd			nvarchar(500)
	,@ReadOnly		bit
	,@TimeStart		datetime
	,@TimeEnd		datetime

DECLARE DatabaseCursor CURSOR FOR
	SELECT
		Name, Is_Read_Only
	FROM
		master.sys.databases 
	WHERE
		database_id > 4 -- Ele apenas irá selecionar os databases dos usuários (não mostra os do sistema)
	ORDER BY
		Name

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database, @ReadOnly

While @@FETCH_STATUS = 0
Begin
	If @ReadOnly = 1
	Begin
		Set @Cmd = 'ALTER DATABASE ' + @Database + ' SET READ_WRITE WITH NO_WAIT;'
		Exec(@Cmd)
	End
	
	Set @Cmd = 
			'DECLARE TableCursor CURSOR FOR 
					SELECT 
						''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName 
					FROM 
						[' + @Database + '].INFORMATION_SCHEMA.TABLES 
					WHERE 
						Table_Type = ''BASE TABLE'''
	
	Set @TimeStart = GetDate()
	
	PRINT 'Início do processo para reorganizar todos os indexes: '
		+ @Database + ': ' + Convert(varchar(25), @TimeStart, 121)
		
	Exec(@Cmd)
	
	OPEN TableCursor
	FETCH NEXT FROM TableCursor INTO @Table
	
	While @@FETCH_STATUS = 0
	Begin
		Set @Cmd = 'ALTER INDEX ALL ON ' + @Table + ' REORGANIZE'
		Exec(@Cmd)
		FETCH NEXT FROM TableCursor INTO @Table
	End
	
	CLOSE TableCursor
	DEALLOCATE TableCursor
	
	Set @TimeEnd = GetDate()
	
	PRINT 'Encerramento do processo para reorganizar todos os indexes: '
		+ @Database + ': ' + Convert(varchar(25), @TimeStart, 121)
		
	PRINT 'Processo de execução: '
		+ Convert(varchar(100), DateDiff(ss, @TimeStart, @TimeEnd)) + ' segundos.'
	PRINT ''
		
	If @ReadOnly = 1
	Begin
		Set @Cmd = 'ALTER DATABASE ' + @Database + ' SET READ_ONLY WITH NO_WAIT'
		Exec(@Cmd)
	End
	
	FETCH NEXT FROM DatabaseCursor INTO @Database, @ReadOnly
End

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Scripts Grant – Oracle

Neste artigo irei mostrar 5 scripts para dar os grants de SELECT, UPDATE, INSERT, DELETE e EXECUTE para qualquer usuário no Oracle respectivamente.

O script cria um arquivo.sql através do comando spool, e neste script é realizado um SELECT para listar os objetos. Estes objetos retornados no SELECT é são concatenados com o comando GRANT. Logo após, o script irá rodar o arquivo.sql criado com os comandos para dar os GRANTS.

Grant para DELETE:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

Spool Temp_Delete.sql

SELECT 'GRANT DELETE ON "' || U.Table_Name || '" TO nome_do_usuario;'
FROM   User_Tables U
WHERE  Not Exists (SELECT '1'
                   FROM   All_Tab_Privs A
                   WHERE  A.Grantee    = UPPER('&1')
                   AND    A.Privilege  = 'DELETE'
                   AND    A.Table_Name = U.Table_Name);

Spool Off

-- Linha comentada para evitar de executar imediatamente
@Temp_Delete.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

Grant para SELECT:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

Spool Temp_Select.sql

SELECT 'GRANT SELECT ON "' || u.object_name || '" TO nome_do_usuario;'
FROM   User_Objects U
WHERE  U.Object_Type In ('TABLE','VIEW','SEQUENCE')
AND    Not Exists (SELECT '1'
                   FROM   All_Tab_Privs A
                   WHERE  A.Grantee    = UPPER('&1')
                   AND    A.Privilege  = 'SELECT'
                   AND    A.Table_Name = U.Object_Name);

Spool Off

-- Linha comentada para evitar de executar imediatamente
@Temp_Select.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

Grant para EXECUTE:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

Spool Temp_Execute.sql

SELECT 'GRANT EXECUTE ON "' || U.Object_Name || '" TO nome_do_usuario;'
FROM   USer_Objects U
WHERE  U.Object_Type In ('PACKAGE','PROCEDURE','FUNCTION')
AND    Not Exists (SELECT '1'
                   FROM   All_Tab_Privs A
                   WHERE  A.Grantee    = UPPER('&1')
                   AND    A.Privilege  = 'EXECUTE'
                   AND    A.Table_Name = U.Object_Name);

Spool Off

-- Linha comentada para evitar de executar imediatamente
@Temp_Execute.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

Grant para UPDATE:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

Spool Temp_Update.sql

SELECT 'GRANT UPDATE ON "' || U.Table_Name || '" TO nome_do_usario;'
FROM   User_Tables U
WHERE  NOT EXISTS (SELECT '1'
                   FROM   All_Tab_Privs A
                   WHERE  A.Grantee    = UPPER('&1')
                   AND    A.Privilege  = 'UPDATE'
                   AND    A.Table_Name = U.Table_Name);

Spool Off

-- Linha comentada para evitar de executar imediatamente
@Temp_Update.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

Grant para INSERT:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

Spool Temp_Insert.sql

SELECT 'GRANT INSERT ON "' || u.table_name || '" TO nome_do_usuario;'
FROM   User_Tables U
WHERE  NOT EXISTS (SELECT '1'
                   FROM   All_Tab_Privs A
                   WHERE  A.Grantee    = UPPER('&1')
                   AND    A.Privilege  = 'INSERT'
                   AND    A.Table_Name = U.Table_Name);

Spool Off

-- Linha comentada para evitar de executar imediatamente
@Temp_Insert.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

Sessões em Espera – Oracle

Neste artigo irei mostrar um simples script responsável por listar todas as sessões que estão com o ‘status’ de espera, ou seja, ainda estão na fila para poder conectar-se no banco.

O Oracle fornece várias views para facilitar a monitoração, neste post iremos utilizar duas views importantes chamado: v$session_wait e v$system_event. Estas views nos fornecem informações detalhadas sobre o estado de espera das sessões. É sempre importante verificar se há muitas sessões em espera, pois isso pode causar algum gargalo no sistema ou afetar alguns objetos individualmente.

Este script retorna os seguintes campos:

UserName: nome do usuário no Oracle;
SID: o id da sessão;
Serial#: o serial number da sessão, na qual é utilizado apenas para identificar a sessão dos objetos utilizados;
Event: o evento para a qual sessão está em espera;
Wait_Class: nome da classe para o evento em espera;
Wait_Time: se a sessão está atualmente aguardando, então o valor é 0. Se a sessão não está em uma espera e, em seguida, o valor é o seguinte: > 0-valor é a duração da última aguardar em centésimos de segundo -1 – duração da última espera foi menos de um centésimo de um segundo–2 – TIMED_STATISTICS parâmetro foi definido como false;
Seconds_In_Wait: mostra em segundos o tempo que a sessão está como espera;
State: o status da sessão.

Script:

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN UserName FORMAT A20
COLUMN Event FORMAT A30
COLUMN Wait_Class FORMAT A15

SELECT 
	  NVL(s.UserName, '(oracle)') As UserName
	, s.SID
	, s.Serial#
	, sw.Event
	, sw.Wait_class
	, sw.Wait_Time
	, sw.Seconds_In_Wait
	, sw.State
FROM   
	v$session_wait sw, v$session s
WHERE  
	s.SID = sw.SID
ORDER BY 
	sw.Seconds_In_Wait DESC;