Labels

Friday, June 1, 2018

generate DDL of sequences from a user in oracle



-To generate the privileges for sequence
SELECT 'GRANT SELECT ON '||p.owner||'.'||p.table_name||' TO '||p.grantee||';' stmt FROM DBA_TAB_PRIVS p, DBA_SEQUENCES s WHERE p.owner = s.sequence_owner AND p.table_name = s.sequence_name AND s.sequence_owner ='';

-To get the metadata of sequence from a user
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('SEQUENCE', sequence_name, sequence_owner)
FROM   all_sequences
WHERE  sequence_owner = UPPER('&1')
AND    sequence_name  = DECODE(UPPER('&2'), 'ALL', sequence_name, UPPER('&2'));

No comments:

Post a Comment