Faz algum tempinho que copiei esse documento, não sei a fonte, caso o autor solicite, retiro do blog, mas como o íntuíto é ajudar, creio que isso não vai acontecer.
VARIAVEIS DE AMBIENTE
CONN SYSTEM@INSTANCIA
SPOOL C:\LOGS
SET ECHO ON
SET TIMING ON
SET LINES 1000
SET 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_USERS
WHERE USERNAME LIKE ‘USER%‘;
CONTA OBJETOS DO SCHEMA
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE 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, PROGRAM
FROM V$SESSION
WHERE USERNAME LIKE ‘USER%‘;
VERIFICAR QUAIS SÃO OS USUÁRIOS DO SISTEMA
SELECT USERNAME FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;
VERIFICA ATRIBUTOS DO USUÁRIO
SELECT * FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTOR LIKE ‘USER%‘;
VERIFICA PREVILEGIOS DO USUÁRIO
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE LIKE ‘USER%‘;
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE LIKE ‘USER%‘;
CONTA OBJETOS DO SCHEMA
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE 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_USERS
WHERE 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, PROGRAM
FROM 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_TYPE
FROM DBA_OBJECTS
GROUP 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_USERS
WHERE USERNAME LIKE ‘USER%‘;
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’;
CONTA OBJETOS DO SCHEMA
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER LIKE ‘USER%‘
GROUP BY OBJECT_TYPE;
CONTA OBJETOS INVALIDOS
SELECT COUNT (*)
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;
VERIFICA OBJETOS INVALIDOS
SELECT OBJECT_TYPE, OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE 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, STATUS
FROM ALL_OBJECTS
WHERE OWNER LIKE ‘USER%‘
AND OBJECT_NAME = ‘OBJECT_NAME’;
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTOR LIKE ‘USER%’;
CONTA OBJETOS DO SCHEMA
SELECT COUNT(OBJECT_TYPE), OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OWNER LIKE ‘USER%‘
GROUP BY OBJECT_TYPE;
CONTA OBJETOS INVALIDOS
SELECT COUNT (*)
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;
VERIFICA OBJETOS INVALIDOS
SELECT OBJECT_TYPE, OBJECT_NAME, STATUS
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;
GERA SCRIPTS DOS OBJETOS INVALIDOS
SELECT ‘ALTER’ OBJECT_TYPE OWNER OBJECT_NAME ‘ COMPILE;’
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;
VERIFICA OBJETOS INVALIDOS
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE STATUS=’INVALID’
AND OWNER LIKE ‘USER%‘;
SPOOL OFF
CRIACAO DE USUÁRIO
VERIFICAR SE EXISTE ESSE USUÁRIO NO SISTEMA
SELECT USERNAME FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;
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;
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 USER
IDENTIFIED BY ‘SENHA‘
DEFAULT TABLESPACE INSTANCE_SCHEMA_01
TEMPORARY TABLESPACE TEMP;
APLICA GRANT
GRANT RESOURCE, CONNECT TO USER;
VERIFICAR SE O USUÁRIO FOI CRIADO
SELECT * FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;
VERIFICA ROLES DO USUARIO CRIADO
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE LIKE ‘USER%‘;
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE LIKE ‘USER%‘;
VERIFICAR OS USUÁRIOS DO SISTEMA
SELECT * FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;
ALTERA A SENHA
ALTER USER USER IDENTIFIED BY ‘SENHA‘;
VERIFICAR OS USUÁRIOS DO SISTEMA
SELECT * FROM DBA_USERS
WHERE USERNAME LIKE ‘USER%‘;
SPOOL OFF
