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

Webcenter Content - Cache Configuration for JdbcFileStore configuration

The configuration values below, control the temporarily cached files keep in FileCache table. FsCacheThreshold Specifies the maximum cache size, in megabytes (default=100) Once the threshold is met, Content Server starts deleting files that are older than the minimum age, as specified by the FsMinimumFileCacheAge parameter FsMaximumFileCacheAge The age at which files are deleted, expressed in days. The default is 365 days FsMinimumFileCacheAge The minimum age at which cached files can be deleted This parameter is used in conjunction with the FsCacheThreshold parameter to determine when to delete cached files FsCleanUpCacheDuringIndexing This parameter activates vault cache cleaning, while FsCacheThreshold, FsMaximumFileCacheAge and FsMinimumFileCacheAge are controling its behaviour.  Expect, when this is enabled on a system that currently has a large number of files in the FileCache table, the first cycle will take a long time to complete. How to Confi...

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

Logical Backup Using dbms_datapump

Sample scripts to make a logical backup using dbms_datapump -- BACKUP BY SCHEMA declare h1 NUMBER; begin h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'TESTE2', version => 'COMPATIBLE'); dbms_datapump.set_parallel(handle => h1, degree => 1); dbms_datapump.add_file(handle => h1, filename => 'BKP_SCHEMAS.LOG', directory => 'BKP_DATA_PUMP', filetype => 3); dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0); dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''DENILSON'')'); dbms_datapump.add_file(handle => h1, filename => 'BKP_SCHEMAS.DMP', directory => 'BKP_DATA_PUMP', filetype => 1); dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); dbms_datapump.set_parameter(handle => h1, name ...