A semana passada estava pesquisando uns scripts de sql para Data Warehouse, o que estar sendo tema da minha monografia, porém em um blog encontrei uns scripts que me chamaram atenção, pois a rotina dos analistas de sistemas e DBA's que cuidam do banco de dados da empresa nem sempre dr tempo criar codigos do zero.Entao decidi publicar o conteudo interessante que pode facilitar a vida do DBA.
– VERIFICA INSTANCIA
SELECT * FROM GLOBAL_NAME;
DUMP
– VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA
SELECT USERNAME FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;
– CONTA OBJETOS DO SCHEMA
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPEFROM DBA_OBJECTSWHERE 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, PROGRAMFROM V$SESSIONWHERE USERNAME LIKE ‘USER%‘;
– VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA
SELECT USERNAME FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;
– VERIFICA ATRIBUTOS DO USUÁRIO
SELECT * FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;SELECT * FROM DBA_TAB_PRIVSWHERE GRANTOR LIKE ‘USER%‘;
– VERIFICA PREVILEGIOS DO USUÁRIO
SELECT * FROM DBA_SYS_PRIVSWHERE GRANTEE LIKE ‘USER%‘;SELECT * FROM DBA_ROLE_PRIVSWHERE GRANTEE LIKE ‘USER%‘;
– CONTA OBJETOS DO SCHEMA
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPEFROM DBA_OBJECTSWHERE 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_USERSWHERE 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, PROGRAMFROM 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_TYPEFROM DBA_OBJECTSGROUP 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_USERSWHERE USERNAME LIKE ‘USER%‘;
– VERIFICA SE O OBJETOS JÁ EXISTE
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE,CREATED, LAST_DDL_TIME, STATUSFROM ALL_OBJECTSWHERE OWNER LIKE ‘USER%‘AND OBJECT_NAME = ‘OBJECT_NAME’;
– CONTA OBJETOS DO SCHEMA
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPEFROM DBA_OBJECTSWHERE OWNER LIKE ‘USER%‘GROUP BY OBJECT_TYPE;
– CONTA OBJETOS INVALIDOS
SELECT COUNT (*)FROM DBA_OBJECTSWHERE STATUS=’INVALID’AND OWNER LIKE ‘USER%‘;
– VERIFICA OBJETOS INVALIDOS
SELECT OBJECT_TYPE, OBJECT_NAME, STATUSFROM DBA_OBJECTSWHERE 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, STATUSFROM ALL_OBJECTSWHERE OWNER LIKE ‘USER%‘AND OBJECT_NAME = ‘OBJECT_NAME’;
SELECT * FROM DBA_TAB_PRIVSWHERE GRANTOR LIKE ‘USER%’;
– CONTA OBJETOS DO SCHEMA
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPEFROM DBA_OBJECTSWHERE OWNER LIKE ‘USER%‘GROUP BY OBJECT_TYPE;
– CONTA OBJETOS INVALIDOS
SELECT COUNT (*)FROM DBA_OBJECTSWHERE STATUS=’INVALID’AND OWNER LIKE ‘USER%‘;
– VERIFICA OBJETOS INVALIDOS
SELECT OBJECT_TYPE, OBJECT_NAME, STATUSFROM DBA_OBJECTSWHERE STATUS=’INVALID’AND OWNER LIKE ‘USER%‘;
– GERA SCRIPTS DOS OBJETOS INVALIDOS
SELECT ‘ALTER’’ ‘ OBJECT_TYPE ’ ‘OWNER ’.' OBJECT_NAME ‘ COMPILE;’FROM DBA_OBJECTSWHERE STATUS=’INVALID’AND OWNER LIKE ‘USER%‘;
– VERIFICA OBJETOS INVALIDOS
SELECT OBJECT_NAME, OBJECT_TYPE, STATUSFROM DBA_OBJECTSWHERE STATUS=’INVALID’AND OWNER LIKE ‘USER%‘;SPOOL OFF
CRIACAO DE USUÁRIO
– VERIFICAR SE EXISTE ESSE USUÁRIO NO SISTEMA
SELECT USERNAME FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;
– CRIAR A TABLESPACE PARA O USUÁRIO
CREATE TABLESPACE INSTANCE_SCHEMA_01DATAFILE ‘CAMINHO/INSTANCE_SCHEMA.DBF’ SIZE 64MAUTOEXTEND ON NEXT 1MSEGMENT 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 USERIDENTIFIED BY ‘SENHA‘DEFAULT TABLESPACE INSTANCE_SCHEMA_01TEMPORARY TABLESPACE TEMP;
– APLICA GRANT
GRANT RESOURCE, CONNECT TO USER;
– VERIFICAR SE O USUÁRIO FOI CRIADO
SELECT * FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;
– VERIFICA ROLES DO USUARIO CRIADO
SELECT * FROM DBA_SYS_PRIVSWHERE GRANTEE LIKE ‘USER%‘;SELECT * FROM DBA_ROLE_PRIVSWHERE GRANTEE LIKE ‘USER%‘;
ALTERAR SENHA
– VERIFICAR OS USUÁRIOS DO SISTEMA
SELECT * FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;
– ALTERA A SENHA
ALTER USER USER IDENTIFIED BY ‘SENHA‘;
– VERIFICAR OS USUÁRIOS DO SISTEMA
SELECT * FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;SPOOL OFF
