dbms_metadata.get_ddl – Rodrigo Silva e Silva

How to extract DDL for User including Privileges and Roles using dbms_metadata.get_ddl

Publicado em 10 de junho de 2021 by rodrigosilvaesilva

Connect Database and Execute with user DBA privilege Put username in uppercase References NOTE:207859.1 – Object types for DBMS_METADATANOTE:1460969.1 – How To Retrieve The Full Object Definition (DDL) From The DatabaseNOTE:1163383.1 – The dbms_metadata.get_granted_ddl Does Not Extract all System Privileges

Triggers enable/disable/recreate/ddl/status

Publicado em 25 de maio de 2021 by rodrigosilvaesilva

How to enable the trigger in Oracle How to disable the trigger in Oracle How to check Trigger status in Oracle How to Check Triggers TABLE_NAME How to recreate TRIGGER DDL How to enable all the trigger on a table How to list all disabled triggers How to List All Triggers in Tables

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";