Rodrigo Silva e Silva

How To Install SQL Server 2016 SP2-Step 1 Download Media Sql Server

Publicado em 19 de março de 2021 by rodrigosilvaesilva

Acessar o Site da Microsoft https://www.microsoft.com/pt-br/evalcenter/evaluate-sql-server-2016 Preencha os Campos abaixo e clique em continuar Após o Download Execute como Administrador Escolha Opção Download Media Selecione o idioma compatível com o seu Windows. Escolha ISO Localização diretório Download Clique em Download Aguarde Finalizar o Download Download Concluído No próximo Post será realizado a instalação do SQL…

Listando conteúdo Job

Publicado em 13 de julho de 202019 de março de 2021 by rodrigosilvaesilva

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…

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

Publicado em 10 de julho de 2020 by rodrigosilvaesilva

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')

ORA-02304: invalid object identifier literal

Publicado em 11 de abril de 2020 by rodrigosilvaesilva

Ao realizar import me deparei com o erro: Erro ORA-39083: Object type TYPE failed to create with error: ORA-02304: invalid object identifier literal

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@DB directory=BKPDIR transform=disable_archive_logging:Y dumpfile=DB_FULL_09042020.DMP logfile=IMP_MIGRACAO_DB_09042020.log schemas=DB EXCLUDE=STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"SANKHYA" already exists
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE:"DB"."T_EXTRATO" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE TYPE "DB"."T_EXTRATO"   OID '117BA7FA64CE4D8D802965580CAA506B' AS OBJECT (
        DTMOV  DATE,
        COTACAO FLOAT,
        VLRANT FLOAT,
        VLRAPLICADO FLOAT
    )

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@DB directory=BKPDIR transform=disable_archive_logging:Y dumpfile=DB_FULL_09042020.DMP logfile=IMP_MIGRACAO_DB_09042020.log schemas=DB EXCLUDE=STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/USER

ORA-31684: Object type USER:"SANKHYA" already exists

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC

ORA-39083: Object type TYPE:"DB"."T_EXTRATO" failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE TYPE "DB"."T_EXTRATO" OID '117BA7FA64CE4D8D802965580CAA506B' AS OBJECT (

DTMOV DATE,

COTACAO FLOAT,

VLRANT FLOAT,

VLRAPLICADO FLOAT

)

Causa: Retorna o erro pois o Oracle não permite importar dois objetos com o mesmo OID. Cada objeto deve ter OID único no banco de dados TRANSFORM=OID:N parameter on the import….

Extrair DDL roles

Publicado em 11 de abril de 2020 by rodrigosilvaesilva

Extrair DDL ROLES

set heading off;
set echo off;
set pages 1000
set long 3000
spool extrair_roles_ddl.sql
select dbms_metadata.get_ddl('ROLE', ROLE) from dba_roles;
spool off



CREATE ROLE "CONNECT";

CREATE ROLE "RESOURCE";

CREATE ROLE "DBA";

CREATE ROLE "PDB_DBA";

CREATE ROLE "AUDIT_ADMIN";

CREATE ROLE "AUDIT_VIEWER";

CREATE ROLE "SELECT_CATALOG_ROLE";

set heading off;

set echo off;

set pages 1000

set long 3000

spool extrair_roles_ddl.sql

select dbms_metadata.get_ddl('ROLE', ROLE) from dba_roles;

spool off

CREATE ROLE "CONNECT";

CREATE ROLE "RESOURCE";

CREATE ROLE "DBA";

CREATE ROLE "PDB_DBA";

CREATE ROLE "AUDIT_ADMIN";

CREATE ROLE "AUDIT_VIEWER";

CREATE ROLE "SELECT_CATALOG_ROLE";

← Posts recentes Posts antigos →