Pular para o conteúdo principal

GET DDL

accept OWNER char prompt 'OWNER: '
accept NAME char prompt 'ENTER THE NAME OF THE PROCEDURE: '
spool &&NAME..sql
set termout off
set linesize 1000
set trimspool on
set verify off
set feedback off
Set heading off
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from dba_source
where name = upper('&&NAME') and owner='&&OWNER'
order by type, line;
/

select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME ||' to '||GRANTEE||';'from dba_tab_privs where TABLE_NAME in (upper('&&NAME')) and OWNER='&&OWNER'
/
select 'create or replace synonym '||OWNER||'.'||SYNONYM_NAME||' for '||TABLE_OWNER||'.'||TABLE_NAME|| ';' from dba_synonyms where TABLE_NAME in
(upper('&&NAME')) and TABLE_OWNER in '&&OWNER' and owner !='PUBLIC'
/
select 'create or replace public synonym '||SYNONYM_NAME||' for '||TABLE_OWNER||'.'||TABLE_NAME|| ';' from dba_synonyms where TABLE_NAME in
(upper('&&NAME')) and TABLE_OWNER in '&&OWNER' and owner ='PUBLIC'
/
prompt
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100


OU:
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl(object_type => 'PACKAGE',name => 'PKG_TESTE',schema => 'DENILSON') from dual;

obs: a procedure dbms_metadata.set_transform_param coloca a "/" no final do comando para criar o objeto.

Comentários

Postagens mais visitadas deste blog

Assign Backup Policy for Boot and Block Volumes - OCI

To get Availability Domain oci iam availability-domain list  #!/bin/bash OCI_AD="hQsi:SA-SAOPAULO-1-AD-1" OCI_COMPARTMENT="ocid1.compartment.oc1..aaaaaaaavgyjpoffz5qliieawamjaksfb6juxejurtduclqjkvv66ookcxja" #Get Silver policy ocid funct_get_policy(){ OCI_POL_OCID=$(oci bv volume-backup-policy list | jq -r '.data[] | select(."display-name"|contains("silver"))' | jq -r '.id') } funct_boot_vol (){ #Create a file with all boot volume ocid oci bv boot-volume list --availability-domain ${OCI_AD} -c  ${OCI_COMPARTMENT} | jq -r '.data[].id' > boot-vol.txt while IFS="" read -r p || [ -n "$p" ] do   printf 'Seeting backup policy for %s\n' "$p"   oci bv volume-backup-policy-assignment create  --asset-id  $p  --policy-id ${OCI_POL_OCID} done < boot-vol.txt } funct_block_vol (){ #Create a file with all block volume ocid oci bv volume list  -c ${OCI_COMPARTMENT} | jq -r '.data[].id' ...

OHS 12c noprompt password when stop/start component

cd $OHS_DOMAIN/bin $ ./stopComponent.sh ohs1 Stopping System Component ohs1 ... Initializing WebLogic Scripting Tool (WLST) ... Welcome to WebLogic Server Administration Scripting Shell Type help() for help on available commands Reading domain from /u01/oracle/domains/ohs_domain Please enter your password : password Connecting to Node Manager ... Successfully Connected to Node Manager. Killing server ohs1 ... Successfully killed server ohs1 Successfully disconnected from Node Manager. Exiting WebLogic Scripting Tool. Done $ OHS_HOME/oracle_common/common/bin $ wlst.sh readDomain('/u01/oracle/domains/ohs_domain') ohs_domain>cd('/SecurityConfiguration/ohs_domain') set('NodeManagerUsername','weblogic') set('NodeManagerPasswordEncrypted','password') updateDomain() closeDomain() exit() $ ./startComponent.sh ohs1  Starting system Component ohs1 ... Initializing WebLogic Scripting Tool (WLST) ... Welco...

Configurar Tamanho de UNDO e tempo de RETENÇÃO (UNDO_RETENTION)

-- Tamanho Undo Atual SELECT SUM(a.bytes) "UNDO_SIZE" FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#; -- Blocos de UNDO por Seg. SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat; --Tamanho do Bloco SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]" FROM v$parameter WHERE name = 'db_block_size'; -- Select calcula UNDO RETENTION SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' ...