julho 2020 – Rodrigo Silva e Silva

Query

select owner as schema_name,
       job_name,
       job_style,
       case when job_type is null 
                 then 'PROGRAM'
            else job_type end as job_type,  
       case when job_type is null
                 then program_name
                 else job_action end as job_action,
       start_date,
       case when repeat_interval is null
            then schedule_name
            else repeat_interval end as schedule,
       last_start_date,
       next_run_date,
       state
from sys.all_scheduler_jobs
order by owner,
         job_name;

select owner as schema_name,

job_name,

job_style,

case when job_type is null

then 'PROGRAM'

else job_type end as job_type,

case when job_type is null

then program_name

else job_action end as job_action,

start_date,

case when repeat_interval is null

then schedule_name

else repeat_interval end as schedule,

last_start_date,

next_run_date,

state

from sys.all_scheduler_jobs

order by owner,

job_name;

Columns schema_name – name of the schema job_name – name of the job job_style: REGULAR LIGHTWEIGHT job_type – inline job action type PLSQL_BLOCK STORED_PROCEDURE EXECUTABLE CHAIN SQL_SCRIPT BACKUP_SCRIPT EXTERNAL_SCRIPT PROGRAM job_action – PL/SQL code or program/routine name to call start_date – when job will be launched first time schedule – inline schedule…

Bom dia pessoal! Hoje me deparei com um cliente com a seguinte situação: Ao realizar o impdp várias TRIGGERS e PROCEDURES ficaram invalidadas. Resolvendo erro ORA-3902

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVATABLOG" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVA_TSIPARLOG" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."DESABILITA_TRG_TAB" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."HABILITA_TRG_TAB" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVATABLOG" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVA_TSIPARLOG" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."DESABILITA_TRG_TAB" created with compilation warnings
ORA-39082: Object type PROCEDURE:"NAME"."HABILITA_TRG_TAB" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFFIN_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_UPD_DLT_TGMTME_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFMBC_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFLIV_EXC" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_INS_TGWLOG" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_INS_TSILOG" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFITE" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFTEF_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_INC_UPD_DLT_TFPVPS_LOG" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFCAB" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_TSIPAR_LOG" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_UPD_DLT_TGMMSG_EXCLUSAO" created with compilation warnings
ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFNUM" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVATABLOG" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVA_TSIPARLOG" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."DESABILITA_TRG_TAB" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."HABILITA_TRG_TAB" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVATABLOG" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."STP_GRAVA_TSIPARLOG" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."DESABILITA_TRG_TAB" created with compilation warnings

ORA-39082: Object type PROCEDURE:"NAME"."HABILITA_TRG_TAB" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFFIN_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_UPD_DLT_TGMTME_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFMBC_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFLIV_EXC" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_INS_TGWLOG" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_INS_TSILOG" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFITE" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFTEF_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_INC_UPD_DLT_TFPVPS_LOG" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFCAB" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_TSIPAR_LOG" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_UPD_DLT_TGMMSG_EXCLUSAO" created with compilation warnings

ORA-39082: Object type TRIGGER:"NAME"."TRG_DLT_TGFNUM" created with compilation warnings

Concedendo permissões para o usuário NAME

GRANT SELECT ON V_$SESSION TO NAME 
/ 
GRANT SELECT ON DBA_TABLES  TO NAME 
/ 
GRANT CREATE SESSION TO NAME 
/ 
GRANT SELECT ON DBA_TAB_COLUMNS TO NAME 
/ 
GRANT SELECT ON DBA_CONSTRAINTS  TO NAME 
/ 
GRANT SELECT ON DBA_TRIGGERS TO NAME 
/ 
GRANT SELECT ON DBA_INDEXES TO NAME 
/ 
GRANT SELECT ON DBA_VIEWS  TO  NAME 
/ 
GRANT SELECT ON DBA_IND_COLUMNS TO NAME 
/ 
GRANT SELECT ON DBA_OBJECTS TO NAME  
/ 
GRANT EXP_FULL_DATABASE TO NAME 
/ 
GRANT IMP_FULL_DATABASE TO NAME

GRANT SELECT ON V_$SESSION TO NAME

GRANT SELECT ON DBA_TABLES TO NAME

GRANT CREATE SESSION TO NAME

GRANT SELECT ON DBA_TAB_COLUMNS TO NAME

GRANT SELECT ON DBA_CONSTRAINTS TO NAME

GRANT SELECT ON DBA_TRIGGERS TO NAME

GRANT SELECT ON DBA_INDEXES TO NAME

GRANT SELECT ON DBA_VIEWS TO NAME

GRANT SELECT ON DBA_IND_COLUMNS TO NAME

GRANT SELECT ON DBA_OBJECTS TO NAME

GRANT EXP_FULL_DATABASE TO NAME

GRANT IMP_FULL_DATABASE TO NAME

Rebuild objetos inválidos

EXEC DBMS_UTILITY.compile_schema(schema => 'NAME')

1	EXEC DBMS_UTILITY.compile_schema(schema => 'NAME')

Mês: julho 2020

Listando conteúdo Job

ORA-39082: Object type PROCEDURE: ORA-39082: Object type TRIGGER: