SQLs mais usados no Oracle

VARIAVEIS DE AMBIENTE

CONN SYSTEM@INSTANCIASPOOLmailto:SYSTEM@INSTANCIASPOOL">SYSTEM@INSTANCIASPOOL>
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

– VERIFICA INSTANCIA

SELECT * FROM GLOBAL_NAME;
DUMP<!--[if !supportLineBreakNewLine]--><!--[endif]-->
– 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*
<!--[endif]-->

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

mailto:SYSTEM@INSTANCE">SYSTEM@INSTANCE