Explain PLAN ...en dos simples pasos..

DELETE PLAN_TABLE;
EXPLAIN PLAN FOR SELECT * FROM mierda;

Para ver el resultado:

Copiar

set pagesize 1000
set linesize 150
col objeto format a40
col operacion format a60

SELECT 
  SUBSTR (LPAD(' ', LEVEL-1) || OPERATION || ' (' || OPTIONS || ')',1,30 ) "OPERACION", 
  OBJECT_NAME "OBJETO"
FROM PLAN_TABLE 
START WITH ID = 0 
CONNECT BY PRIOR ID=PARENT_ID;

Tamaño de tabla en ORACLE

Dejo consulta para averiguar cuanto pesa una tabla en ORACLE

select tablespace_name Tablespace, segment_name Tabla,     
       trunc(sum(bytes)/(1024*1024*1024)) Gigas
  from dba_extents
 where segment_type='TABLE'
  group by segment_name, tablespace_name 
order by 3 desc;

gracias!!

Ubicacion de la WALLET en TDE !!

Ademas de fijarse en el archivo sqlnet.ora par confirmar la ubicacion de la wallet de TDE podemos hacer este query:

select * from V$ENCRYPTION_WALLET;

Espera por sesion. ORACLE

Script:

prompt spool waits.rpt col p1text for a30 col p2text for a30 col p3text for a30 col program for a40 col sid for 9999 col event for a57 col wait_class for a15 col "OS User" for a9 col "User" for a20 col "Status" for a10 col "Program" for a40 col "Module" for a38 col "Terminal" for a15 col "Client Info" for a15 col machine for a25 col "SID/Serial" for a15 set feed off accept x_user prompt 'User : ' accept x_sid prompt 'SID : ' accept x_NoSqlNet prompt 'Ignore SQL*Net client waits? [Y] ' default 'Y' prompt prompt Current sessions by username prompt select i.instance_name "instance", s.osuser "OS User", s.sid || ',' || s.serial# "SID/Serial", r.spid "OS PID", s.username "User", s.status "Status", s.logon_time "Logon", s.machine "Machine", s.program "Program", s.module "Module", s.client_info "Client Info" from gv$session s, gv$process r, gv$instance i where s.username like upper( '%' || '&&x_user' || '%' ) and s.sid = nvl( '&x_sid', s.sid ) and i.inst_id = s.inst_id and r.inst_id = s.inst_id and r.addr (+)= s.paddr order by s.username, s.terminal, s.program, s.logon_time; prompt prompt By event, username, inst_id, wait_class, sid prompt select b.event, a.username, a.inst_id, c.wait_class, b.sid, c.total_waits, c.time_waited, b.seconds_in_wait, decode( b.wait_time, 0, 'Waiting', -1, 'Last Wait minimal', -2, 'Waited Unknown', decode( sign( b.wait_time ), -1, 'Wrapped', 1, 'Last Wait', 'Unknown' ) ) "Wait status", a.osuser, a.program, b.p1text, b.p1, b.p1raw, b.p2text, b.p2, b.p2raw, b.p3text, b.p3, b.p3raw, b.seq# from gv$session a, gv$session_wait b, gv$session_wait_class c where a.username like upper( '%&x_user%' ) and b.sid = decode( '&&x_sid', null, b.sid, 0, b.sid, '&&x_sid' ) and b.wait_time > -2 and a.sid = b.sid and ( upper( nvl( '&x_NoSqlNet', 'N' ) ) <> 'Y' or instr( b.event, 'SQL*Net message from client' ) = 0 ) and b.inst_id = a.inst_id and c.inst_id = a.inst_id and c.sid = a.sid and c.serial# = a.serial# order by b.event, username, inst_id, wait_class, sid; prompt prompt By seconds_in_wait, total_waits prompt select b.seconds_in_wait, c.total_waits, a.inst_id, a.username, b.sid, c.wait_class, c.time_waited, decode( b.wait_time, 0, 'Waiting', -1, 'Last Wait minimal', -2, 'Waited Unknown', decode( sign( b.wait_time ), -1, 'Wrapped', 1, 'Last Wait', 'Unknown' ) ) "Wait status", b.event, a.osuser, a.program, b.p1text, b.p1, b.p1raw, b.p2text, b.p2, b.p2raw, b.p3text, b.p3, b.p3raw, b.seq# from gv$session a, gv$session_wait b, gv$session_wait_class c where a.username like upper( '%&x_user%' ) and b.sid = decode( '&&x_sid', null, b.sid, 0, b.sid, '&&x_sid' ) and b.wait_time > -2 and a.sid = b.sid and ( upper( nvl( '&x_NoSqlNet', 'N' ) ) <> 'Y' or instr( b.event, 'SQL*Net message from client' ) = 0 ) and b.inst_id = a.inst_id and c.inst_id = a.inst_id and c.sid = a.sid and c.serial# = a.serial# order by b.seconds_in_wait, c.total_waits; select event, inst_id, count(*) from gv$session_wait group by event, inst_id order by event, inst_id; prompt prompt ITL waits prompt col statistic_name for a15 select a1.statistic_name, a1.owner, a1.object_type, a1.object_name, a1.value, a1.subobject_name, a1.inst_id, a1.tablespace_name, a1.ts#, a1.obj#, a1.dataobj#, a1.statistic# from gv$segment_statistics a1 where statistic_name = 'ITL waits' and owner = nvl( upper( '&&x_user' ), owner ) and value > 0 order by value desc; prompt prompt ITL waits: summary prompt select a1.statistic_name, a1.owner, a1.object_type, a1.object_name, sum( a1.value ) "Combined Value", a1.subobject_name, a1.tablespace_name, a1.ts#, a1.obj#, a1.dataobj#, a1.statistic# from gv$segment_statistics a1 where statistic_name = 'ITL waits' and owner = nvl( upper( '&&x_user' ), owner ) and value > 0 group by a1.statistic_name, a1.owner, a1.object_type, a1.object_name, a1.subobject_name, a1.tablespace_name, a1.ts#, a1.obj#, a1.dataobj#, a1.statistic# order by "Combined Value"; spool off

_______________________________________________________________

EJEMPLO DE SALIDA.

User : SID : Ignore SQL*Net client waits? [Y] Current sessions by username instance OS User SID/Serial OS PID User Status Logon Machine Program Module Client Info ---------------- --------- --------------- ------------------------ -------------------- ---------- ------------------- ------------------------- ---------------------------------------- -------------------------------------- --------------- FERD oracle 753,12935 16291 APEX_PUBLIC_USER INACTIVE 30/09/2015 14:32:45 axdevap05 httpd.worker@xxdevjj05 (TNS V1-V3) FERD oracle 502,1 24866 DBSNMP INACTIVE 25/09/2015 19:48:40 blx24ax01 JDBC Thin Client JDBC Thin Client FERD oracle 352,2133 32351 DBSNMP INACTIVE 30/09/2015 11:38:45 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database FERD oracle 552,1905 18784 DBSNMP INACTIVE 30/09/2015 14:18:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database FERD oracle 6,883 17550 DBSNMP INACTIVE 30/09/2015 14:48:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database FERD oracle 302,3427 17532 DBSNMP INACTIVE 30/09/2015 14:48:44 blx24ax01 JDBC Thin Client emagent_SQL_oracle_database FERD peterm 702,18145 24137 UTILITIES_DEV01 ACTIVE 30/09/2015 13:52:28 M10000_HG98A sqlplusw.exe SQL*Plus By event, username, inst_id, wait_class, sid EVENT USERNAME INST_ID WAIT_CLASS SID TOTAL_WAITS TIME_WAITED SECONDS_IN_WAIT Wait status OSUSER PROGRAM P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW SEQ# --------------------------------------------------------- ------------------------------ ---------- --------------- ----- ----------- ----------- --------------- ----------------- ---------- ---------------------------------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ---------- SQL*Net message to client UTILITIES_DEV01 1 Idle 702 51 351732 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142 SQL*Net message to client UTILITIES_DEV01 1 Network 702 52 0 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142 SQL*Net message to client UTILITIES_DEV01 1 System I/O 702 14 0 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142 SQL*Net message to client UTILITIES_DEV01 1 User I/O 702 25 8 0 Last Wait minimal u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 142 By seconds_in_wait, total_waits SECONDS_IN_WAIT TOTAL_WAITS INST_ID USERNAME SID WAIT_CLASS TIME_WAITED Wait status EVENT OSUSER PROGRAM P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW SEQ# --------------- ----------- ---------- ------------------------------ ----- --------------- ----------- ----------------- --------------------------------------------------------- ---------- ---------------------------------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ------------------------------ ---------- ---------------- ---------- 0 14 1 UTILITIES_DEV01 702 System I/O 0 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148 0 25 1 UTILITIES_DEV01 702 User I/O 8 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148 0 54 1 UTILITIES_DEV01 702 Idle 351737 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148 0 55 1 UTILITIES_DEV01 702 Network 0 Last Wait minimal SQL*Net message to client u758223 sqlplusw.exe driver id 1413697536 0000000054435000 #bytes 1 0000000000000001 0 00 148 EVENT INST_ID COUNT(*) --------------------------------------------------------- ---------- ---------- DIAG idle wait 1 2 EMON slave idle wait 1 5 SQL*Net message from client 1 6 SQL*Net message to client 1 1 Space Manager: slave idle wait 1 1 Streams AQ: emn coordinator idle wait 1 1 Streams AQ: qmn coordinator idle wait 1 1 Streams AQ: qmn slave idle wait 1 1 Streams AQ: waiting for time management or cleanup tasks 1 1 VKTM Logical Idle Wait 1 1 pmon timer 1 1 rdbms ipc message 1 13 smon timer 1 1 ITL waits STATISTIC_NAME OWNER OBJECT_TYPE OBJECT_NAME VALUE SUBOBJECT_NAME INST_ID TABLESPACE_NAME TS# OBJ# DATAOBJ# STATISTIC# --------------- -------------------- ------------------ ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ---------- ---------- ---------- ITL waits SYS INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST ########## 1 SYSAUX 1 500 500 15 ITL waits: summary STATISTIC_NAME OWNER OBJECT_TYPE OBJECT_NAME Combined Value SUBOBJECT_NAME TABLESPACE_NAME TS# OBJ# DATAOBJ# STATISTIC# --------------- -------------------- ------------------ ------------------------------ -------------- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ITL waits SYS INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 1 SYSAUX 1 500 500 15

Backup historico de RMAN

prompt accept x_days prompt "Days [7]: " default 7 prompt col "Time" for a10 col "In" for a20 col "Out" for a20 col "Day" for a4 col status for a12 col command_id for a20 select command_id, input_type, to_char( start_time, 'Dy' ) "Day", start_time, end_time, status, time_taken_display "Time", input_bytes_display "In", output_bytes_display "Out", output_device_type from v$rman_backup_job_details where start_time > sysdate - &x_days order by start_time; prompt prompt Non Full: select command_id, input_type, to_char( start_time, 'Dy' ) "Day", start_time, end_time, status, time_taken_display "Time", input_bytes_display "In", output_bytes_display "Out", output_device_type from v$rman_backup_job_details where start_time > sysdate - &x_days and input_type <> 'DB FULL' order by start_time; prompt prompt Full: select command_id, input_type, to_char( start_time, 'Dy' ) "Day", start_time, end_time, status, time_taken_display "Time", input_bytes_display "In", output_bytes_display "Out", output_device_type from v$rman_backup_job_details where start_time > sysdate - &x_days and input_type = 'DB FULL' order by start_time;

---

Example output:

SQL> @rmans Days [7]: COMMAND_ID INPUT_TYPE Day START_TIME END_TIME STATUS Time In Out OUTPUT_DEVICE_TYP -------------------- ------------- ---- ------------------- ------------------- ------------ ---------- -------------------- -------------------- ----------------- DEV2R2.online DB FULL Mon 28/09/2015 20:25:19 28/09/2015 20:28:32 COMPLETED 00:03:13 16.79G 8.92G SBT_TAPE DEV2R1.archlogs ARCHIVELOG Mon 28/09/2015 22:41:43 28/09/2015 22:43:06 COMPLETED 00:01:23 443.93M 444.25M SBT_TAPE DEV2R2.archlogs ARCHIVELOG Wed 30/09/2015 02:08:10 30/09/2015 02:11:14 COMPLETED 00:03:04 594.39M 594.75M SBT_TAPE DEV2R2.archlogs ARCHIVELOG Wed 30/09/2015 23:53:33 01/10/2015 00:00:58 COMPLETED 00:07:25 316.18M 316.50M SBT_TAPE DEV2R1.online DB FULL Thu 01/10/2015 20:25:58 01/10/2015 20:30:20 COMPLETED 00:04:22 16.79G 8.93G SBT_TAPE DEV2R2.archlogs ARCHIVELOG Thu 01/10/2015 22:42:19 01/10/2015 22:43:33 COMPLETED 00:01:14 432.98M 433.50M SBT_TAPE DEV2R1.archlogs ARCHIVELOG Fri 02/10/2015 22:41:43 02/10/2015 22:44:17 COMPLETED 00:02:34 438.50M 438.75M SBT_TAPE DEV2R2.archlogs ARCHIVELOG Sat 03/10/2015 22:43:07 03/10/2015 22:44:42 COMPLETED 00:01:35 450.88M 451.50M SBT_TAPE DEV2R1.online DB FULL Sun 04/10/2015 20:32:45 04/10/2015 20:43:00 COMPLETED 00:10:15 16.79G 8.93G SBT_TAPE DEV2R1.archlogs ARCHIVELOG Sun 04/10/2015 22:41:50 04/10/2015 22:43:25 COMPLETED 00:01:35 465.95M 466.25M SBT_TAPE 10 rows selected. Non Full: COMMAND_ID INPUT_TYPE Day START_TIME END_TIME STATUS Time In Out OUTPUT_DEVICE_TYP -------------------- ------------- ---- ------------------- ------------------- ------------ ---------- -------------------- -------------------- ----------------- DEV2R1.archlogs ARCHIVELOG Mon 28/09/2015 22:41:43 28/09/2015 22:43:06 COMPLETED 00:01:23 443.93M 444.25M SBT_TAPE DEV2R2.archlogs ARCHIVELOG Wed 30/09/2015 02:08:10 30/09/2015 02:11:14 COMPLETED 00:03:04 594.39M 594.75M SBT_TAPE DEV2R2.archlogs ARCHIVELOG Wed 30/09/2015 23:53:33 01/10/2015 00:00:58 COMPLETED 00:07:25 316.18M 316.50M SBT_TAPE DEV2R2.archlogs ARCHIVELOG Thu 01/10/2015 22:42:19 01/10/2015 22:43:33 COMPLETED 00:01:14 432.98M 433.50M SBT_TAPE DEV2R1.archlogs ARCHIVELOG Fri 02/10/2015 22:41:43 02/10/2015 22:44:17 COMPLETED 00:02:34 438.50M 438.75M SBT_TAPE DEV2R2.archlogs ARCHIVELOG Sat 03/10/2015 22:43:07 03/10/2015 22:44:42 COMPLETED 00:01:35 450.88M 451.50M SBT_TAPE DEV2R1.archlogs ARCHIVELOG Sun 04/10/2015 22:41:50 04/10/2015 22:43:25 COMPLETED 00:01:35 465.95M 466.25M SBT_TAPE 7 rows selected. Full: COMMAND_ID INPUT_TYPE Day START_TIME END_TIME STATUS Time In Out OUTPUT_DEVICE_TYP -------------------- ------------- ---- ------------------- ------------------- ------------ ---------- -------------------- -------------------- ----------------- DEV2R2.online DB FULL Mon 28/09/2015 20:25:19 28/09/2015 20:28:32 COMPLETED 00:03:13 16.79G 8.92G SBT_TAPE DEV2R1.online DB FULL Thu 01/10/2015 20:25:58 01/10/2015 20:30:20 COMPLETED 00:04:22 16.79G 8.93G SBT_TAPE DEV2R1.online DB FULL Sun 04/10/2015 20:32:45 04/10/2015 20:43:00 COMPLETED 00:10:15 16.79G 8.93G SBT_TAPE 3 rows selected.

Ver las auditorias actuales de la base ORACLE

-- Name : audits.sql prompt prompt Purpose : auditorias actuales de la base ORACLE prompt -- -- Oracle Version: 11g -- Other Oracle versions: should work on 9i and later ---------------------------------------------------------------------------------------- spool audits.rpt prompt col value for a20 col display_value for a50 col update_comment for a50 col policy_text for a30 prompt Instances prompt ---------- select * from gv$instance order by inst_id; prompt Audit parameters prompt ----------------- col name for a20 col value for a40 break on inst_id skip 1 select * from gv$parameter where upper( name ) like '%AUDIT%' order by inst_id; clear breaks prompt prompt DBA_STMT_AUDIT_OPTS prompt -------------------- select * from sys.dba_stmt_audit_opts order by nvl( user_name, '0' ), audit_option; prompt prompt DBA_PRIV_AUDIT_OPTS prompt -------------------- select * from sys.dba_priv_audit_opts order by privilege; prompt prompt DBA_OBJ_AUDIT_OPTS prompt ------------------- prompt S=Session, A=Access prompt S/- = audit whenever successful prompt -/S = audit whenever unsuccessful col owner for a30 select * from sys.dba_obj_audit_opts where ALT <> '-/-' or AUD <> '-/-' or COM <> '-/-' or DEL <> '-/-' or GRA <> '-/-' or IND <> '-/-' or INS <> '-/-' or LOC <> '-/-' or REN <> '-/-' or SEL <> '-/-' or UPD <> '-/-' or REF <> '-/-' or EXE <> '-/-' or CRE <> '-/-' or REA <> '-/-' or WRI <> '-/-' order by owner, object_name; -- default settings prompt prompt ALL_DEF_AUDIT_OPTS prompt ------------------- select * from all_def_audit_opts; prompt prompt DBA_AUDIT_TRAIL (breakdown) prompt ---------------------------- select action_name, returncode, count(*) from dba_audit_trail group by action_name, returncode order by action_name, returncode; prompt prompt DBA_AUDIT_POLICIES prompt ------------------- select * from dba_audit_policies; prompt prompt SYS.FGA_LOG$ prompt declare cursor csr_main_cursor is select * from SYS.DBA_AUDIT_POLICIES order by policy_name; begin dbms_output.put_line( 'Details from SYS.DBA_AUDIT_POLICIES' ); dbms_output.put_line( '-----------------------------------' ); for i in csr_main_cursor loop dbms_output.put_line( 'Object Schema : ' || i.object_schema ); dbms_output.put_line( 'Object Name : ' || i.object_name ); dbms_output.put_line( 'Policy Name : ' || i.policy_name ); dbms_output.put_line( 'Policy Text : ' || i.policy_text ); dbms_output.put_line( 'Policy Column : ' || i.policy_column ); dbms_output.put_line( 'Pf Schema : ' || i.pf_schema ); dbms_output.put_line( 'Pf Package : ' || i.pf_package ); dbms_output.put_line( 'Pf Function : ' || i.pf_function ); dbms_output.put_line( 'Enabled : ' || i.enabled ); dbms_output.put_line( 'Sel : ' || i.sel ); dbms_output.put_line( 'Ins : ' || i.ins ); dbms_output.put_line( 'Upd : ' || i.upd ); dbms_output.put_line( 'Del : ' || i.del ); dbms_output.put_line( 'Audit Trail : ' || i.audit_trail ); dbms_output.put_line( 'Policy Column Options : ' || i.policy_column_options ); dbms_output.put_line( chr(10) ); dbms_output.put_line( rpad( '-', 80, '-' ) ); end loop; end; / col obj$name for a30 prompt prompt SYS.FGA_LOG$ summary prompt --------------------- select policyname, obj$schema, obj$name, stmt_type, count(*) from sys.fga_log$ group by policyname, obj$schema, obj$name, stmt_type order by policyname, obj$schema, obj$name, stmt_type; prompt prompt SYS.GV_$XML_AUDIT_TRAIL summary prompt ------------------------------- col db_user for a20 col os_user for a20 col "Audit Type" for a25 col "Action (Code)" for a20 select a.db_user, a.os_user, b.name || ' (' || a.action || ')' "Action (Code)", a.returncode, a.audit_type || decode( a.audit_type, 1, ' Standard XML Audit', 2, ' Fine Grained XML Audit', 4, ' SYS XML Audit', 8, ' Mandatory XML Audit', a.audit_type || ' ????' ) "Audit Type", count(*) from SYS.GV_$XML_AUDIT_TRAIL a, audit_actions b where b.action (+)= a.action group by a.db_user, a.os_user, b.name || ' (' || a.action || ')', a.returncode, a.audit_type || decode( a.audit_type, 1, ' Standard XML Audit', 2, ' Fine Grained XML Audit', 4, ' SYS XML Audit', 8, ' Mandatory XML Audit', a.audit_type || ' ????' ) order by 1, 2; select sql_text, count(*) from gv$xml_audit_trail group by sql_text order by upper( sql_text ); spool off

___________________________________________________________

SALIDA

Instances --------- INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO ---------- --------------- ---------------- -------------------- ----------------- ------------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- --- 1 1 DEV1 linux01a 11.2.0.3.0 25/09/2015 19:41:47 OPEN YES 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 2 2 DEV2 linux01b 11.2.0.3.0 25/09/2015 20:01:08 OPEN YES 2 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO 2 rows selected. Audit parameters ---------------- INST_ID NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION UPDATE_COMMENT HASH ---------- ---------- -------------------- ---------- ---------------------------------------- -------------------------------------------------- --------- ----- --------- ----- ---------- ----- ----- ----- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------- 1 1677 audit_sys_operations 1 FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable sys auditing 2991425711 1840 audit_file_dest 2 /opt/oracle/admin/admin/DEV0A/adump /opt/oracle/admin/admin/DEV0A/adump FALSE FALSE DEFERRED TRUE FALSE FALSE FALSE FALSE Directory in which auditing files are to reside 2188534560 1841 audit_syslog_level 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Syslog facility and level 1189300432 1869 audit_trail 2 DB DB FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable system auditing 4289193100 2 1677 audit_sys_operations 1 FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable sys auditing 2991425711 1840 audit_file_dest 2 /opt/oracle/admin/admin/DEV0A/adump /opt/oracle/admin/admin/DEV0A/adump FALSE FALSE DEFERRED TRUE FALSE FALSE FALSE FALSE Directory in which auditing files are to reside 2188534560 1841 audit_syslog_level 2 TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Syslog facility and level 1189300432 1869 audit_trail 2 DB DB FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE enable system auditing 4289193100 8 rows selected. DBA_STMT_AUDIT_OPTS ------------------- USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- ALTER ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS 28 rows selected. DBA_PRIV_AUDIT_OPTS ------------------- USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- ALTER ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS AUDIT SYSTEM BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS 23 rows selected. DBA_OBJ_AUDIT_OPTS ------------------ S=Session, A=Access S/- = audit whenever successful -/S = audit whenever unsuccessful OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK ------------------------------ ------------------------------ ----------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- DEV_0001 COUNTRIES TABLE -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- CODE_002 PROC_UPDATE_TEAMS PROCEDURE -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- 2 rows selected. ALL_DEF_AUDIT_OPTS ------------------ ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- 1 row selected. DBA_AUDIT_TRAIL (breakdown) --------------------------- ACTION_NAME RETURNCODE COUNT(*) ---------------------------- ---------- ---------- DELETE 0 1045 LOGOFF 0 697 LOGOFF BY CLEANUP 0 475 LOGON 0 1188 LOGON 28000 1 LOGON 28001 2 6 rows selected. DBA_AUDIT_POLICIES ------------------ no rows selected SYS.FGA_LOG$ Details from SYS.DBA_AUDIT_POLICIES ----------------------------------- PL/SQL procedure successfully completed. SYS.FGA_LOG$ summary -------------------- no rows selected SYS.GV_$XML_AUDIT_TRAIL summary ------------------------------ no rows selected no rows selected

resultado del ADDM

Description:

Show the results of the latest ADDM run(s)

---

Script:

prompt prompt ADDM (Automatic Database Diagnostic Monitor) recommendations and suggestions for possible problems prompt declare -- find latest ADDM finding that actually produced recommendations -- can be more than 1 cursor c_latest_recommends is select * from dba_advisor_tasks where execution_end = ( select max( execution_end ) from dba_advisor_tasks dat, dba_advisor_recommendations dar where dat.task_name = dar.task_name ); cursor c_problems ( i_task in varchar2 ) is select * from dba_advisor_findings where task_name = i_task and type = 'PROBLEM'; cursor c_symptoms ( i_task in varchar2, i_parent_id in integer) is select * from dba_advisor_findings where task_name = i_task and type = 'SYMPTOM' and parent = i_parent_id; cursor c_recommendations ( i_task in varchar2, i_problem_id in integer) is select * from dba_advisor_recommendations where task_name = i_task and finding_id = i_problem_id order by rec_id; cursor c_actions ( i_task in varchar2, i_rec_id in integer) is select * from dba_advisor_actions where task_name = i_task and rec_id = i_rec_id; cursor c_objects ( i_task in varchar2, i_obj_id in integer) is select * from dba_advisor_objects where task_name = i_task and object_id = i_obj_id; begin dbms_output.put_line( 'Latest Sets of Recommendations' ); dbms_output.put_line( '------------------------------' || chr(10) ); for i in c_latest_recommends loop dbms_output.put_line( '' ); dbms_output.put_line( 'Task Name: ' || i.task_name ); dbms_output.put_line( rpad( '-', length( 'Task Name: ' || i.task_name ), '-' ) ); dbms_output.put_line( 'Task Started: ' || to_char( i.execution_start, 'DD-MON-YYYY HH24:MI:SS') ); dbms_output.put_line( '' ); dbms_output.put_line( 'For full details connect as ' || i.owner || ' and run:' ); dbms_output.put_line( '' ); dbms_output.put_line( 'select dbms_advisor.get_task_report(''' || i.task_name || ''', ''TEXT'', ''TYPICAL'' ) from dual;'); dbms_output.put_line( '' ); for j in c_problems( i.task_name ) loop dbms_output.put_line( ' ' || j.type || ': ' || j.message ); dbms_output.put_line( ' ' || rpad( '-', length( j.type || ': ' || j.message ), '-' ) ); -- Symptoms dbms_output.put_line( '' ); dbms_output.put_line( ' Symptoms:' ); dbms_output.put_line( ' ---------' ); for k in c_symptoms( i.task_name, j.finding_id) loop dbms_output.put_line( ' ' || k.message ); end loop; -- Recommendations for l in c_recommendations( i.task_name, j.finding_id ) loop dbms_output.put_line( '' ); dbms_output.put_line( ' Recommendation:' ); dbms_output.put_line( ' ---------------' ); dbms_output.put_line( ' ' || l.type ); -- Actions for recommendation dbms_output.put_line( '' ); dbms_output.put_line( ' Recommended action:' ); dbms_output.put_line( ' -------------------' ); for m in c_actions( i.task_name, l.rec_id ) loop dbms_output.put_line( ' ' || m.message ); -- Object attributes dbms_output.put_line( '' ); dbms_output.put_line( ' Attributes:' ); dbms_output.put_line( ' -------------------' ); for n in c_objects( i.task_name, m.object_id ) loop dbms_output.put_line( ' ' || n.attr3 || ' ' || n.attr4 || ' ' || n.attr5); end loop; end loop; end loop; dbms_output.put_line( chr(10) ); end loop; dbms_output.put_line( chr(10) ); end loop; end; /

---

Example output:

SQL> @addm ADDM (Automatic Database Diagnostic Monitor) recommendations and suggestions for possible problems Latest Sets of Recommendations ------------------------------ Task Name: ADDM:2594484505_29821 -------------------------------- Task Started: 01-OCT-2015 23:00:30 For full details connect as SYS and run: select dbms_advisor.get_task_report('ADDM:2594484505_29821', 'TEXT', 'TYPICAL' ) from dual; PROBLEM: Individual database segments responsible for significant "User I/O" and "Cluster" waits were found. ------------------------------------------------------------------------------------------------------------ Symptoms: --------- Wait class "User I/O" was consuming significant database time. Recommendation: --------------- Segment Tuning Recommended action: ------------------- Run "Segment Advisor" on INDEX "OCEAN.PK_LOCATION_LINKS" with object ID 116737. Attributes: ------------------- Investigate application logic involving I/O on INDEX "DEPUTY.ALIAS_INDEX_01" with object ID 116737. Attributes: ------------------- Recommendation: --------------- Segment Tuning Recommended action: ------------------- Investigate application logic involving I/O on TABLE "DEPUTY.ALIAS" with object ID 114499. Attributes: ------------------- Look at the "Top SQL Statements" finding for SQL statements consuming significant I/O on this segment. For example, the INSERT statement with SQL_ID "8szmfgm7fysa3" is responsible for 100% of "User I/O" and "Cluster" waits for this segment. Attributes: ------------------- PROBLEM: PL/SQL execution consumed significant database time. ------------------------------------------------------------- Symptoms: --------- Recommendation: --------------- SQL Tuning Recommended action: ------------------- Tune the entry point PL/SQL "SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC" of type "PACKAGE" and ID 7945. Refer to the PL/SQL documentation for addition information. Attributes: ------------------- PROBLEM: SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement. ---------------------------------------------------------------------------------------------------------------------------------------------- Symptoms: --------- Recommendation: --------------- SQL Tuning Recommended action: ------------------- Run SQL Tuning Advisor on the INSERT statement with SQL_ID "8szmwam7fysa3". Attributes: ------------------- insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE')) Recommendation: --------------- SQL Tuning Recommended action: ------------------- Run SQL Tuning Advisor on the SELECT statement with SQL_ID "7wgks43wrjtrz". Attributes: ------------------- SELECT U.SPACE_USED, U.SPACE_ALLOCATED FROM TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF( :B1 , :B2 , :B3 , 0, :B4 , 'TRUE', :B5 )) U