Site icon An Oracle Spin by Alex Lima

How to grant view all DDL in an Oracle database to unprivilege user

SQL>grant select on dba_objects to <username>;

SQL>conn <username>/<password>

SET PAGESIZE 0
SET LONG 100000
SET LONGCHUNKSIZE 10000
SET LINESIZE 1000
–SET TERMINATOR ON
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,TRUE)
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = ‘PACKAGE’ AND OWNER = ‘POSITION_OWNER’
–SAME FOR OTHER OBJECTS E.G. TABLE, PROCEDURE, ETC
;

Exit mobile version