Scripts Úteis Para o Dia a Dia no Oracle (CANIVETE SUIÇO)

Ola Galera,

Bom pra quem é dba sabe que não se vive sem aqueles scripts que nos auxiliam no dia a dia, afinal não é todo mundo que tem toad ou alguma ferramenta gráfica por perto ou mesmo homologado pela empresa.

Então .. La vai um pequeno canivete suíço para lidar com o oracle.

VARIAVEIS DE AMBIENTE

CONN SYSTEM@INSTANCIA
SPOOL C:\LOGS

SET ECHO ON
SET TIMING ON
SET LINES 1000
SET SQLBL ON

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD/MM/YYYY HH24:MI:SS’;
SELECT SYSDATE FROM DUAL;
SHOW USER

— VERIFICA INSTANCIA

SELECT * FROM GLOBAL_NAME;

DUMP

— VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

SELECT USERNAME FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;

— CONTA OBJETOS DO SCHEMA

SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER LIKE ‘USER%
GROUP BY OBJECT_TYPE;
SPOOL OFF

— NO TERMINAL LINUX

$export ORACLE_SID=INSTANCE

$exp system@INSTANCE BUFFER=1000000 FILE=EXP_INSTANCE_USER_DATA.DMP LOG=EXP_INSTANCE_USER_DATA.LOG OWNER=USUÁRIOS LISTADOS CONSISTENT=Y

gzip EXP_INSTANCE_USER_DATA*

DESATIVAÇÃO DE UM SCHEMA

— 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%‘;

— VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

SELECT USERNAME FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;

— VERIFICA ATRIBUTOS DO USUÁRIO

SELECT * FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTOR LIKE ‘USER%‘;

— VERIFICA PREVILEGIOS DO USUÁRIO

SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE LIKE ‘USER%‘;
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE LIKE ‘USER%‘;

— CONTA OBJETOS DO SCHEMA

SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER LIKE LIKE ‘USER%
GROUP BY OBJECT_TYPE;

— DESATIVA USUÁRIO

ALTER USER USER ACCOUNT LOCK;
ALTER USER USER PASSWORD EXPIRE;

— VERIFICA STATUS DA CONTA

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%’;
SPOOL OFF

DESATIVAÇÃO DE UMA INSTANCIA

— VERIFICAR SE TEM ALGUM USUÁRIO USANDO O SISTEMA

SELECT SADDR, SID, USERNAME, LOGON_TIME, STATUS,
OSUSER, MACHINE, PROGRAM
FROM V$SESSION;

— VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

SELECT USERNAME FROM DBA_USERS ;

— VERIFICA ATRIBUTOS DO USUÁRIO

SELECT * FROM DBA_USERS;

SELECT * FROM DBA_TAB_PRIVS;

— VERIFICA PREVILEGIOS DO USUÁRIO

SELECT * FROM DBA_SYS_PRIVS;

— VERIFICA PREVILEGIOS DE ROLE

SELECT * FROM DBA_ROLE_PRIVS;

— CONTA OBJETOS DO SCHEMA

SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
GROUP BY OBJECT_TYPE;

— NO TERMINAL

EXPORT ORACLE_SID=INSTANCE

SQLPLUS / AS SYSDBA

SQL> SHUTDOWN IMMEDIATE;

EXECUÇÃO DE SCRIPT

— VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

SELECT USERNAME FROM DBA_USERS
WHERE USERNAME
LIKE ‘USER%;

— VERIFICA SE O OBJETOS JÁ EXISTE

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE,
CREATED, LAST_DDL_TIME, STATUS
FROM ALL_OBJECTS
WHERE OWNER LIKE ‘USER%
AND OBJECT_NAME = ‘OBJECT_NAME’;

— CONTA OBJETOS DO SCHEMA

SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER LIKE ‘USER%
GROUP BY OBJECT_TYPE;

— CONTA OBJETOS INVALIDOS

SELECT COUNT (*)
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;

— VERIFICA OBJETOS INVALIDOS

SELECT OBJECT_TYPE, OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;

— EXECUTA O SCRIPT

CONN USER@INSTANCE

@C:\CAMINHO\SCRIPT.SQL

CONN SYSTEM@INSTANCE

— VERIFICA SE O OBJETOS JÁ EXISTE

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE,
CREATED, LAST_DDL_TIME, STATUS
FROM ALL_OBJECTS
WHERE OWNER LIKE ‘USER%
AND OBJECT_NAME = ‘OBJECT_NAME’;
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTOR LIKE ‘USER%’;

— CONTA OBJETOS DO SCHEMA

SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER LIKE ‘USER%
GROUP BY OBJECT_TYPE;

— CONTA OBJETOS INVALIDOS

SELECT COUNT (*)
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;

— VERIFICA OBJETOS INVALIDOS

SELECT OBJECT_TYPE, OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;

— GERA SCRIPTS DOS OBJETOS INVALIDOS

SELECT ‘ALTER’||’ ‘|| OBJECT_TYPE ||’ ‘||OWNER ||’.’|| OBJECT_NAME || ‘ COMPILE;’
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;

— VERIFICA OBJETOS INVALIDOS

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;
SPOOL OFF

CRIACAO DE USUÁRIO

— VERIFICAR SE EXISTE ESSE USUÁRIO NO SISTEMA

SELECT USERNAME FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;

— CRIAR A TABLESPACE PARA O USUÁRIO

CREATE TABLESPACE INSTANCE_SCHEMA_01
DATAFILE ‘CAMINHO/INSTANCE_SCHEMA.DBF’ SIZE 64M
AUTOEXTEND ON NEXT 1M
SEGMENT SPACE MANAGEMENT AUTO;

— VERIFICA SE TEM ALGUMA ROLE PARA ESSE USUÁRIO

SELECT * FROM DBA_ROLES WHERE ROLE LIKE ‘%USER%‘;

SELECT * FROM DBA_SYS_PRIVS WHERE LIKE ‘%USER%‘;

SELECT * FROM DBA_TAB_PRIVS WHERE LIKE ‘%USER%‘;

— CRIAR O USUÁRIO

CREATE USER USER
IDENTIFIED BY ‘SENHA
DEFAULT TABLESPACE INSTANCE_SCHEMA_01
TEMPORARY TABLESPACE TEMP;

— APLICA GRANT

GRANT RESOURCE, CONNECT TO USER;

— VERIFICAR SE O USUÁRIO FOI CRIADO

SELECT * FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;

— VERIFICA ROLES DO USUARIO CRIADO

SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE LIKE ‘USER%‘;
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE LIKE ‘USER%‘;

ALTERAR SENHA

— VERIFICAR OS USUÁRIOS DO SISTEMA

SELECT * FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;

— ALTERA A SENHA

ALTER USER USER IDENTIFIED BY ‘SENHA‘;

— VERIFICAR OS USUÁRIOS DO SISTEMA

SELECT * FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;
SPOOL OFF

E era isso ai pessoal, espero que seja útil !!
Se alguém tiver mais algum script bacana só postar lá nos comentários !!

Abraços
Kenia Milene