Table of Contents

SELECT 'DROP TABLE ' || owner || '.' || table_name || ' CASCADE CONSTRAINTS;' AS drop_command
FROM all_tables
WHERE owner = 'WZC_AUTHORIZATION_OWN';
set sqlformat insert;
select * from WZC_AUTHORIZATION_OWN.AUTHORIZATION_CONFIGURATION;
set sqlformat default;

Crear Usuario

CREATE USER usuari IDENTIFIED BY contrasenya
GRANT CREATE SESSION TO usuari

Comprobar usuario bloqueado:

select username,account_status,profile from dba_users where username='jose'

USERNAME		       ACCOUNT_STATUS			PROFILE
------------------------------ -------------------------------- ------------------------------
jose			       LOCKED(TIMED)			DEFAULT

Desbloquear usuario: 

ALTER USER username ACCOUNT UNLOCK;

Comprobar parámetros perfil usuario: 

SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

RESOURCE_NAME			 RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
COMPOSITE_LIMIT 		 KERNEL   UNLIMITED
SESSIONS_PER_USER		 KERNEL   UNLIMITED
CPU_PER_SESSION 		 KERNEL   UNLIMITED
CPU_PER_CALL			 KERNEL   UNLIMITED
LOGICAL_READS_PER_SESSION	 KERNEL   UNLIMITED
LOGICAL_READS_PER_CALL		 KERNEL   UNLIMITED
IDLE_TIME			 KERNEL   UNLIMITED
CONNECT_TIME			 KERNEL   UNLIMITED
PRIVATE_SGA			 KERNEL   UNLIMITED
FAILED_LOGIN_ATTEMPTS		 PASSWORD 10
PASSWORD_LIFE_TIME		 PASSWORD UNLIMITED
PASSWORD_REUSE_TIME		 PASSWORD UNLIMITED
PASSWORD_REUSE_MAX		 PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION	 PASSWORD NULL
PASSWORD_LOCK_TIME		 PASSWORD 1
PASSWORD_GRACE_TIME		 PASSWORD 7

Activar auditoria:

Una vez activada, se busca los errores con la consulta: 

select * from dba_audit_session where username='usuario' and returncode != 0 order by timestamp desc;

Para vaciar la tabla: 

truncate table aud$

Cambiar contraseña

alter user user_name identified by new_password;

Porcentaje tablespaces

COLUMN tsname FORMAT a30
COLUMN allocation_type FORMAT A4 TRU HEADING ALLO
COLUMN contents FORMAT A4 TRU HEADING MGMT
COLUMN Tot_Size FORMAT 999,999 HEADING "TOTAL(M)"
COLUMN Tot_Free FORMAT 999,999 HEADING "FREE(M)"
COLUMN Pct_Free FORMAT 999 HEADING "FREE %"
COLUMN Fragments FORMAT 99,999 HEADING "FRAGMTS"
COLUMN Large_Ext FORMAT 999,999 HEADING "LARG(M)"
set lines 120
set feedback off pages 999 trims on
SELECT a.tablespace_name TSNAME, SUM(a.tots)/1048576 Tot_Size,
SUM(a.sumb)/1048576 Tot_Free,
SUM(a.sumb)*100/sum(a.tots) Pct_Free,
SUM(a.largest)/1048576 Large_Ext, SUM(a.chunks) Fragments,
b.contents, b.allocation_type
FROM (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
MAX(bytes) largest, COUNT(*) chunks
FROM dba_free_space a
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
FROM dba_temp_files
GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY a.tablespace_name, b.contents, b.allocation_type
ORDER BY 4
/