-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