Ĉ¿
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
Leia mais em: SQLs mais usados no Oracle http://www.devmedia.com.br/sqls-mais-usados-no-oracle/12817#ixzz45cQZwcoW
