Dicas ORACLE DBA

create or replace procedure recur_constraint(v_owner in varchar2, v_table_name varchar2, v_fk_constraint_name varchar2 default NULL, v_r_constraint_name varchar2 default NULL, v_r_status varchar2 default NULL, level number default 0) is i integer; v_constraint_name dba_constraints.constraint_name%type; v_status dba_constraints.status%type; cursor c_constraint is select owner, table_name, constraint_name, r_constraint_name, status from dba_constraints where dba_constraints.r_owner = v_owner and dba_constraints.r_constraint_name = v_constraint_name and dba_constraints.constraint_type = 'R'; begin select constraint_name, status into v_constraint_n...

Em alguns casos, você quer saber quais são as dependências de um objeto, por exemplo: - O que acontece se recompilar o objeto em questão - Objetos que evitam a compilação do objeto em questão (exemplo: data-dictionary e objeto no esquema errado) Uma outra questão pode ser que os objetos são dependentes de um objeto específico (dependências reversa), por exemplo: - Objetos que serão invalidados caso eu dropar objeto em questão. - Qual é a dependência em cascata para este objeto específico. Como a view dba_dependencies não tem as informações necessárias podemos utilizar as procedures abaixo (Lembre que para compilar, é necessário grant nas views dba_dependencies e dba_objects) -- Mostrar dependencias de um objeto: create or replace procedure denilson.recur_object(v_owner in varchar2, v_object_name varchar2, v_object_type varchar2, ...

STATUS_CODE column A - Waiting B - Resuming C - Normal D - Cancelled E - Error F - Scheduled G - Warning H - On Hold I - Normal M - No Manager Q - Standby R - Normal S - Suspended T - Terminating U - Disabled W - Paused X - Terminated Z - Waiting PHASE_CODE column C - Completed I - Inactive P - Pending R - Running

SELECT s.username, s.sid, s.serial#, t.used_ublk, t.used_urec, rs.segment_name, r.rssize, r.status, s.last_call_Et FROM v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs WHERE s.saddr = t.ses_addr AND t.xidusn = r.usn AND rs.segment_id = t.xidusn ORDER BY t.used_ublk DESC;

RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE BACKUP OPTIMIZATION OFF; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/b01/backup/%F'; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/b01/backup/%d_DB_%u_%s_%p'; CONFIGURE MAXSETSIZE TO UNLIMITED; CONFIGURE ENCRYPTION FOR DATABASE OFF; CONFIGURE ENCRYPTION ALGORITHM 'AES128'; CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/db10g/dbs/snapcf_control.f'; --Full {backup as compressed backupset incremental level 0 database plus archivelog delete input; crosscheck archivelog all; crosscheck backupset; delete noprom...

COLUMN sid FORMAT 999 COLUMN serial# FORMAT 9999999 COLUMN machine FORMAT A30 COLUMN progress_pct FORMAT 99999999.00 COLUMN elapsed FORMAT A10 COLUMN remaining FORMAT A10 SELECT s.sid, s.serial#, s.machine, TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed, TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM v$session s, v$session_longops sl WHERE s.sid = sl.sid AND s.serial# = sl.serial#;

SET SERVEROUTPUT ON SET VERIFY OFF DECLARE CURSOR cu_tables IS SELECT a.owner, a.table_name FROM all_tables a WHERE a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1')) AND a.owner = Upper('&&2'); op1 NUMBER; op2 NUMBER; op3 NUMBER; op4 NUMBER; op5 NUMBER; op6 NUMBER; op7 NUMBER; BEGIN Dbms_Output.Disable; Dbms_Output.Enable(1000000); Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK'); Dbms_Output.Put_Line('------------------------------ ------- --------------- ---------------'); FOR cur_rec IN cu_tables LOOP Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7); Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') LPad(op3,15,' ') LPad(op1,15,' ') LPad(Trunc(op1-op3-1),15,' ')); END LOOP; END; / SET VERIFY ON