Comandos basicos oracle

OBS.: Vale lembrar que são somente comandos basicos, podendo variar de ambiente para ambiente.

 

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%’;

 

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

 

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

 

Listagem 38: ALTERA A SENHA

ALTER USER USER IDENTIFIED BY ‘SENHA‘;Listagem 39: VERIFICAR OS USUÁRIOS DO SISTEMA

 

Listagem 39: VERIFICAR OS USUÁRIOS DO SISTEMA

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