Differences

This shows you the differences between two versions of the page.

--- informatica:oracle:comandos [2013/07/08 10:57] – created jose
+++ informatica:oracle:comandos [2024/06/19 09:46] (current) – jose
@@ Line 1: / Line 1: @@
+<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>