Desenvolvedor: SQL's de apoio do Oracle

Ĉ¿

Alguns comando citados por Emerson Martins, que ajuda no dia a dia

Listagem 1: VARIÁVEIS DE AMBIENTE

CONN SYSTEM @INSTANCIAS POOL
mailto:SYSTEM@INSTANCIAS POOL "> SYSTEM@INSTANCIAS POOL>
C:\LOGSSET ECHO ONSET TIMING ONSET LINES 1000SET SQLBL ON
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD/MM/YYYY HH24:MI:SS’;
SELECT SYSDATE FROM DUAL;
SHOW USER

Listagem 2: VERIFICA INSTANCIA

SELECT * FROM GLOBAL_NAME;
DUMP

Listagem 3: VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

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

Listagem 4: CONTA OBJETOS DO SCHEMA

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

Listagem 5: 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*

Listagem 6: 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%‘;

Listagem 7: VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

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

Listagem 8: VERIFICA ATRIBUTOS DO USUÁRIO

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

Listagem 9: VERIFICA PREVILEGIOS DO USUÁRIO

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

Listagem 10: CONTA OBJETOS DO SCHEMA

SELECT COUNT(OBJECT_TYPE), OBJECT_TYPEFROM DBA_OBJECTSWHERE OWNER LIKE LIKE ‘USER%‘GROUP BY OBJECT_TYPE;

Listagem 11: DESATIVA USUÁRIO

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

Listagem 12: VERIFICA STATUS DA CONTA

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

Listagem 13: VERIFICAR SE TEM ALGUM USUÁRIO USANDO O SISTEMA

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

Listagem 14: VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

SELECT USERNAME FROM DBA_USERS ;

Listagem 15: VERIFICA ATRIBUTOS DO USUÁRIO

SELECT * FROM DBA_USERS;
SELECT * FROM DBA_TAB_PRIVS;

Listagem 16: VERIFICA PREVILEGIOS DO USUÁRIO

SELECT * FROM DBA_SYS_PRIVS;

Listagem 17: VERIFICA PREVILEGIOS DE ROLE

SELECT * FROM DBA_ROLE_PRIVS;

Listagem 18: CONTA OBJETOS DO SCHEMA

SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE FROM DBA_OBJECTSGROUP BY OBJECT_TYPE;

Listagem 19: NO TERMINAL

EXPORT ORACLE_SID=INSTANCE
SQLPLUS / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE;

]

Listagem 20: VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA

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

Listagem 21: 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’;

Listagem 22: CONTA OBJETOS DO SCHEMA

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

Listagem 23: CONTA OBJETOS INVALIDOS

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

Listagem 24: VERIFICA OBJETOS INVALIDOS

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

Listagem 25: EXECUTA O SCRIPT

CONN USER@INSTANCE
@C:\CAMINHO\SCRIPT.SQL
CONN SYSTEM@INSTANCE
mailto:SYSTEM@INSTANCE">SYSTEM@INSTANCE

Listagem26: 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’;

Listagem 27: CONTA OBJETOS DO SCHEMA

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

Listagem 28: CONTA OBJETOS INVALIDOS

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

Listagem 29: VERIFICA OBJETOS INVALIDOS

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

Listagem 30: GERA SCRIPTS DOS OBJETOS INVALIDOS

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

Listagem 31: VERIFICA OBJETOS INVALIDOS

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

Listagem 32: - VERIFICAR SE EXISTE ESSE USUÁRIO NO SISTEMA

SELECT USERNAME FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;

Listagem 33: 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;

Listagem 33: 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%‘;

Listagem 34: CRIAR O USUÁRIO

CREATE USER USERIDENTIFIED BY ‘SENHA‘DEFAULT TABLESPACE INSTANCE_SCHEMA_01TEMPORARY TABLESPACE TEMP;

Listagem 34: APLICA GRANT

GRANT RESOURCE, CONNECT TO USER;

Listagem 35: VERIFICAR SE O USUÁRIO FOI CRIADO

SELECT * FROM DBA_USERSWHERE USERNAME LIKE ‘USER%‘;

Listagem 36: VERIFICA ROLES DO USUARIO CRIADO

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

Listagem 37: VERIFICAR OS USUÁRIOS DO SISTEMA

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

Listagem 38: ALTERA A SENHA

ALTER USER USER IDENTIFIED BY ‘SENHA‘;

Listagem 39: VERIFICAR OS USUÁRIOS DO SISTEMA

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

SQL's de apoio do Oracle

Comandos git do dia a dia