Legido Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<code>
SELECT 'DROP TABLE ' || owner || '.' || table_name || ' CASCADE CONSTRAINTS;' AS drop_command
FROM all_tables
WHERE owner = 'WZC_AUTHORIZATION_OWN';
</code>

<code>
set sqlformat insert;
select * from WZC_AUTHORIZATION_OWN.AUTHORIZATION_CONFIGURATION;
set sqlformat default;
</code>

====== Crear Usuario ======

  CREATE USER usuari IDENTIFIED BY contrasenya

  GRANT CREATE SESSION TO usuari



====== Comprobar usuario bloqueado: ======

<code>
select username,account_status,profile from dba_users where username='jose'

USERNAME		       ACCOUNT_STATUS			PROFILE
------------------------------ -------------------------------- ------------------------------
jose			       LOCKED(TIMED)			DEFAULT
</code>

Desbloquear usuario:
  ALTER USER username ACCOUNT UNLOCK;

Comprobar parámetros perfil usuario:
<code>
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
</code>

====== 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 ======
<code>
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
/
</code>