Abaixo alguns SQLs que são úteis no dia-a-dia do DBA.

Para executar kill user:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0ICdhbHRlciBzeXN0ZW0ga2lsbCBzZXNzaW9uICcnJyB8fCBiLlNpZCB8fCAnLCcgfHwgYi5TZXJpYWwjIHx8ICcnJyBpbW1lZGlhdGU7JwpGcm9tIFYkc2Vzc2lvbiBiLCBWJHByb2Nlc3MgYQpXaGVyZSBiLlBhZGRyID0gYS5BZGRyCkFuZCBUeXBlID0gJ1VTRVInCkFuZCBiLlVzZXJuYW1lIExpa2UgJyVOT01FX1VTVUFSSU8lJwpBbmQgYi5NYWNoaW5lIExpa2UgJyVOT01FX01BUVVJTkElJwpPcmRlciBCeSBiLkxvZ29uX1RpbWUsIGEuUGdhX1VzZWRfTWVtOw==” hover_enabled=”0″]U2VsZWN0ICdhbHRlciBzeXN0ZW0ga2lsbCBzZXNzaW9uICcnJyB8fCBiLlNpZCB8fCAnLCcgfHwgYi5TZXJpYWwjIHx8ICcnJyBpbW1lZGlhdGU7JwpGcm9tIFYkc2Vzc2lvbiBiLCBWJHByb2Nlc3MgYQpXaGVyZSBiLlBhZGRyID0gYS5BZGRyCkFuZCBUeXBlID0gJ1VTRVInCkFuZCBiLlVzZXJuYW1lIExpa2UgJyVOT01FX1VTVUFSSU8lJwpBbmQgYi5NYWNoaW5lIExpa2UgJyVOT01FX01BUVVJTkElJwpPcmRlciBCeSBiLkxvZ29uX1RpbWUsIGEuUGdhX1VzZWRfTWVtOw==[/et_pb_dmb_code_snippet]

Ou por usuário específico:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0ICdhbHRlciBzeXN0ZW0ga2lsbCBzZXNzaW9uICcnJyB8fCBiLlNpZCB8fCAnLCcgfHwgYi5TZXJpYWwjIHx8ICcnJyBpbW1lZGlhdGU7JwpGcm9tIFYkc2Vzc2lvbiBiCldoZXJlIFR5cGUgPSAnVVNFUicKQW5kIGIuVXNlcm5hbWUgPSAnTk9NRV9VU1VBUklPJzs=” hover_enabled=”0″]U2VsZWN0ICdhbHRlciBzeXN0ZW0ga2lsbCBzZXNzaW9uICcnJyB8fCBiLlNpZCB8fCAnLCcgfHwgYi5TZXJpYWwjIHx8ICcnJyBpbW1lZGlhdGU7JwpGcm9tIFYkc2Vzc2lvbiBiCldoZXJlIFR5cGUgPSAnVVNFUicKQW5kIGIuVXNlcm5hbWUgPSAnTk9NRV9VU1VBUklPJzs=[/et_pb_dmb_code_snippet]

obs.: Alterar o NOME_USUARIO e/ou NOME_MAQUINA para o que deseja, detalhe que tem que ser maiúsculos por é assim que está no dicionário de dados do Oracle.

Ver as sessões abertas:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0IFN1YnN0cihiLlNpZCwgMSwgNSkgU2lkLCBTdWJzdHIoYi5TZXJpYWwjLCAxLCA1KSBTZXIjLCBTdWJzdHIoYi5NYWNoaW5lLCAxLCA2KSBCb3gsClN1YnN0cihiLlVzZXJuYW1lLCAxLCA4ICkgIlVTRVIiLCBiLkxhc3RfQ2FsbF9FdCBTZWdfSW5hY3RpdmUsIFN1YnN0cihiLk9zdXNlciwgMSwgOCApIE9zX1VzZXIsClN1YnN0cihiLlByb2dyYW0sIDEsIDI3KSBQcm9ncmFtLCBiLkxvZ29uX1RpbWUKRnJvbSBWJHNlc3Npb24gYiwgViRwcm9jZXNzIGEKV2hlcmUgYi5QYWRkciA9IGEuQWRkcgpBbmQgVHlwZSA9ICdVU0VSJwpPcmRlciBCeSBiLkxvZ29uX1RpbWUsIFNlZ19JbmFjdGl2ZTs=” hover_enabled=”0″]U2VsZWN0IFN1YnN0cihiLlNpZCwgMSwgNSkgU2lkLCBTdWJzdHIoYi5TZXJpYWwjLCAxLCA1KSBTZXIjLCBTdWJzdHIoYi5NYWNoaW5lLCAxLCA2KSBCb3gsClN1YnN0cihiLlVzZXJuYW1lLCAxLCA4ICkgIlVTRVIiLCBiLkxhc3RfQ2FsbF9FdCBTZWdfSW5hY3RpdmUsIFN1YnN0cihiLk9zdXNlciwgMSwgOCApIE9zX1VzZXIsClN1YnN0cihiLlByb2dyYW0sIDEsIDI3KSBQcm9ncmFtLCBiLkxvZ29uX1RpbWUKRnJvbSBWJHNlc3Npb24gYiwgViRwcm9jZXNzIGEKV2hlcmUgYi5QYWRkciA9IGEuQWRkcgpBbmQgVHlwZSA9ICdVU0VSJwpPcmRlciBCeSBiLkxvZ29uX1RpbWUsIFNlZ19JbmFjdGl2ZTs=[/et_pb_dmb_code_snippet]

Para ver os Jobs:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0IFN1YnN0cihKb2IsIDEsIDMpIEpvYiwgU3Vic3RyKExhc3RfRGF0ZSB8fCAnICcgfHwgTGFzdF9TZWMsIDEsIDE5KSBMYXN0X0V4ZWMsClN1YnN0cihOZXh0X0RhdGUgfHwgJyAnIHx8IE5leHRfU2VjLCAxLCAxOSkgTmV4dF9FeGVjLCBCcm9rZW4sIEZhaWx1cmVzLCBTdWJzdHIoV2hhdCwgMSwgMzApIENvbWFuZG8sClN1YnN0cihJbnRlcnZhbCwgMSwgMjUpIEludGVydmFsCkZyb20gRGJhX0pvYnMKT3JkZXIgQnkgTGFzdF9FeGVjOw==” hover_enabled=”0″]U2VsZWN0IFN1YnN0cihKb2IsIDEsIDMpIEpvYiwgU3Vic3RyKExhc3RfRGF0ZSB8fCAnICcgfHwgTGFzdF9TZWMsIDEsIDE5KSBMYXN0X0V4ZWMsClN1YnN0cihOZXh0X0RhdGUgfHwgJyAnIHx8IE5leHRfU2VjLCAxLCAxOSkgTmV4dF9FeGVjLCBCcm9rZW4sIEZhaWx1cmVzLCBTdWJzdHIoV2hhdCwgMSwgMzApIENvbWFuZG8sClN1YnN0cihJbnRlcnZhbCwgMSwgMjUpIEludGVydmFsCkZyb20gRGJhX0pvYnMKT3JkZXIgQnkgTGFzdF9FeGVjOw==[/et_pb_dmb_code_snippet]

Para ver a ocupação dos Tablespaces:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0ICJUYWJsZXNwYWNlIE5hbWUiLCBTdW0oIlNpemUiKSAiU2l6ZSAoTSkiLCBTdW0oIlVzZWQiKSAiVXNlZCAoTSkiLCBTdW0oIkZyZWUiKSAiRnJlZSAoTSkiLApTdW0oIlVzZWQiKSAqIDEwMCAvIFN1bSgiU2l6ZSIpICJQZXJjZW50dWFsIFV0aWxpemFkbyAoJSkiCkZyb20gKFNlbGVjdCBTdWJzdHIoRGYuVGFibGVzcGFjZV9OYW1lLCAxLCAzNSkgIlRhYmxlc3BhY2UgTmFtZSIsIFJvdW5kKERmLkJ5dGVzIC8gMTAyNCAvIDEwMjQsIDIpICJTaXplIiwKUm91bmQoZS5Vc2VkX0J5dGVzIC8gMTAyNCAvIDEwMjQsIDIpICJVc2VkIiwgUm91bmQoZi5GcmVlX0J5dGVzIC8gMTAyNCAvIDEwMjQsIDIpICJGcmVlIgpGcm9tIERiYV9EYXRhX0ZpbGVzIERmLAooU2VsZWN0IEZpbGVfSWQsIFN1bShEZWNvZGUoQnl0ZXMsIE51bGwsIDAsIEJ5dGVzKSkgVXNlZF9CeXRlcwpGcm9tIERiYV9FeHRlbnRzCkdyb3VwIEJ5IEZpbGVfSWQpIGUsCihTZWxlY3QgTWF4KEJ5dGVzKSBGcmVlX0J5dGVzLCBGaWxlX0lkCkZyb20gRGJhX0ZyZWVfU3BhY2UKR3JvdXAgQnkgRmlsZV9JZCkgZgpXaGVyZSBlLkZpbGVfSWQoKykgPSBEZi5GaWxlX0lkCkFuZCBEZi5GaWxlX0lkID0gZi5GaWxlX0lkKCspCk9yZGVyIEJ5IERmLlRhYmxlc3BhY2VfTmFtZSkKR3JvdXAgQnkgIlRhYmxlc3BhY2UgTmFtZSIKT3JkZXIgQnkgIlBlcmNlbnR1YWwgVXRpbGl6YWRvICglKSI7″ hover_enabled=”0″]U2VsZWN0ICJUYWJsZXNwYWNlIE5hbWUiLCBTdW0oIlNpemUiKSAiU2l6ZSAoTSkiLCBTdW0oIlVzZWQiKSAiVXNlZCAoTSkiLCBTdW0oIkZyZWUiKSAiRnJlZSAoTSkiLApTdW0oIlVzZWQiKSAqIDEwMCAvIFN1bSgiU2l6ZSIpICJQZXJjZW50dWFsIFV0aWxpemFkbyAoJSkiCkZyb20gKFNlbGVjdCBTdWJzdHIoRGYuVGFibGVzcGFjZV9OYW1lLCAxLCAzNSkgIlRhYmxlc3BhY2UgTmFtZSIsIFJvdW5kKERmLkJ5dGVzIC8gMTAyNCAvIDEwMjQsIDIpICJTaXplIiwKUm91bmQoZS5Vc2VkX0J5dGVzIC8gMTAyNCAvIDEwMjQsIDIpICJVc2VkIiwgUm91bmQoZi5GcmVlX0J5dGVzIC8gMTAyNCAvIDEwMjQsIDIpICJGcmVlIgpGcm9tIERiYV9EYXRhX0ZpbGVzIERmLAooU2VsZWN0IEZpbGVfSWQsIFN1bShEZWNvZGUoQnl0ZXMsIE51bGwsIDAsIEJ5dGVzKSkgVXNlZF9CeXRlcwpGcm9tIERiYV9FeHRlbnRzCkdyb3VwIEJ5IEZpbGVfSWQpIGUsCihTZWxlY3QgTWF4KEJ5dGVzKSBGcmVlX0J5dGVzLCBGaWxlX0lkCkZyb20gRGJhX0ZyZWVfU3BhY2UKR3JvdXAgQnkgRmlsZV9JZCkgZgpXaGVyZSBlLkZpbGVfSWQoKykgPSBEZi5GaWxlX0lkCkFuZCBEZi5GaWxlX0lkID0gZi5GaWxlX0lkKCspCk9yZGVyIEJ5IERmLlRhYmxlc3BhY2VfTmFtZSkKR3JvdXAgQnkgIlRhYmxlc3BhY2UgTmFtZSIKT3JkZXIgQnkgIlBlcmNlbnR1YWwgVXRpbGl6YWRvICglKSI7[/et_pb_dmb_code_snippet]

Ver os Objetos Inválidos:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0IFN1YnN0cihPd25lciwgMSwgMTUpIEFzIE93bmVyLCBTdWJzdHIoT2JqZWN0X1R5cGUsIDEsIDM1KSBBcyBPYmplY3RfVHlwZSwKU3Vic3RyKE9iamVjdF9OYW1lLCAxLCAzMCkgQXMgT2JqZWN0X05hbWUsIFN0YXR1cwpGcm9tIERiYV9PYmplY3RzCldoZXJlIFN0YXR1cyA9ICdJTlZBTElEJwpPcmRlciBCeSBPd25lciwgT2JqZWN0X1R5cGUsIE9iamVjdF9OYW1lOw==” hover_enabled=”0″]U2VsZWN0IFN1YnN0cihPd25lciwgMSwgMTUpIEFzIE93bmVyLCBTdWJzdHIoT2JqZWN0X1R5cGUsIDEsIDM1KSBBcyBPYmplY3RfVHlwZSwKU3Vic3RyKE9iamVjdF9OYW1lLCAxLCAzMCkgQXMgT2JqZWN0X05hbWUsIFN0YXR1cwpGcm9tIERiYV9PYmplY3RzCldoZXJlIFN0YXR1cyA9ICdJTlZBTElEJwpPcmRlciBCeSBPd25lciwgT2JqZWN0X1R5cGUsIE9iamVjdF9OYW1lOw==[/et_pb_dmb_code_snippet]

obs.: Aqui ainda pode especificar os Object_Type (PACKAGE, PACKAGE BODY, SEQUENCE, SYNONYM, TRIGGER etc).

Listar usuários e suas permissões:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0IFVzZXJuYW1lLCBBY2NvdW50X1N0YXR1cywgRGVmYXVsdF9UYWJsZXNwYWNlLCBUZW1wb3JhcnlfVGFibGVzcGFjZSwgUHJvZmlsZSwgR3JhbnRlZF9Sb2xlLCBBZG1pbl9PcHRpb24sCkRlZmF1bHRfUm9sZQpGcm9tIFN5cy5EYmFfVXNlcnMgYSwgU3lzLkRiYV9Sb2xlX1ByaXZzIGIKV2hlcmUgYS5Vc2VybmFtZSA9IGIuR3JhbnRlZQpPcmRlciBCeSBVc2VybmFtZSwgQWNjb3VudF9TdGF0dXMsIERlZmF1bHRfVGFibGVzcGFjZSwgVGVtcG9yYXJ5X1RhYmxlc3BhY2UsIFByb2ZpbGUsIEdyYW50ZWRfUm9sZTsK” hover_enabled=”0″]U2VsZWN0IFVzZXJuYW1lLCBBY2NvdW50X1N0YXR1cywgRGVmYXVsdF9UYWJsZXNwYWNlLCBUZW1wb3JhcnlfVGFibGVzcGFjZSwgUHJvZmlsZSwgR3JhbnRlZF9Sb2xlLCBBZG1pbl9PcHRpb24sCkRlZmF1bHRfUm9sZQpGcm9tIFN5cy5EYmFfVXNlcnMgYSwgU3lzLkRiYV9Sb2xlX1ByaXZzIGIKV2hlcmUgYS5Vc2VybmFtZSA9IGIuR3JhbnRlZQpPcmRlciBCeSBVc2VybmFtZSwgQWNjb3VudF9TdGF0dXMsIERlZmF1bHRfVGFibGVzcGFjZSwgVGVtcG9yYXJ5X1RhYmxlc3BhY2UsIFByb2ZpbGUsIEdyYW50ZWRfUm9sZTsK[/et_pb_dmb_code_snippet]

Listas as auditorias ativas:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VsZWN0IFVzZXJfTmFtZSwgQXVkaXRfT3B0aW9uLCBTdWNjZXNzLCBGYWlsdXJlCkZyb20gRGJhX1N0bXRfQXVkaXRfT3B0czs=” hover_enabled=”0″]U2VsZWN0IFVzZXJfTmFtZSwgQXVkaXRfT3B0aW9uLCBTdWNjZXNzLCBGYWlsdXJlCkZyb20gRGJhX1N0bXRfQXVkaXRfT3B0czs=[/et_pb_dmb_code_snippet]

Espero postar outros em breve.

Att,
capin