Print Friendly, PDF & Email
How to extract metadata of users, tables, views, triggers, and other Oracle database objects by using the metadata API.
DEMO

DEMO: HOW TO RETRIEVE AN OBJECT DDL OR A USER DDL USING PL/SQL

RELATED

HOW TO FIND SYSTEM, ROLES AND OBJECTS PRIVILEGES THAT HAVE BEEN GRANTED TO A USER
HOW TO FIND ALL OBJECTS PRIVILEGES GRANTED TO A USER IN AN ORACLE DATABASE

QUERY

The DBT_RETRUSMETA is a PL/SQL anonymous block that retrieves metadata for a user.

------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 13:25 14-Nov-2021 (001)
--
-- DBT_RETRUSMETA - DATABASE TOOLS, RETRIEVE A USER METADATA
--
-- The DBT_RETRUSMETA PL/SQL anonymous block retrieves metadata for a user.
--
------------------------------------------------------------

SET LINES 500
SET PAGES 0
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET SQLNUMBER OFF
SET TRIMSPOOL ON 
SET SERVEROUTPUT ON FORMAT WRAPPED

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

DECLARE 
USERNAME VARCHAR2(40)  := UPPER('&USERNAME');
LINE     VARCHAR2(100) := CHR(10) || RPAD('-',40,'-') || CHR(10);
BEGIN
   
  BEGIN
    
    -- FETCHING A USER METADATA
    
    DBMS_OUTPUT.PUT_LINE(LINE || '-- FETCHING USER METADATA' || LINE);
    DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_DDL('USER',USERNAME));
    
      -- SYSTEM PRIVILEGIES 

      BEGIN
        DBMS_OUTPUT.PUT_LINE(LINE || '-- FETCHING SYSTEM PRIVILEGIES' || LINE);
        DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',USERNAME));
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE( 'The user ' || USERNAME || ' does NOT have any system privilegies.');
      END;
      
      -- ROLE PRIVILEGIES
      
      BEGIN
        DBMS_OUTPUT.PUT_LINE(LINE || '-- FETCHING ROLE PRIVILEGIES' || LINE);
        DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',USERNAME));
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('The user ' || USERNAME || ' does NOT have any role privilegies.');
      END;

      -- OBJECT PRIVILEGIES
      
      BEGIN
        DBMS_OUTPUT.PUT_LINE(LINE || '-- FETCHING OBJECT PRIVILEGIES' || LINE);
        DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',USERNAME));
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('The user ' || USERNAME || ' does NOT have any object privilegies.' || CHR(10));
      END;
      
  EXCEPTION
    WHEN OTHERS THEN
      IF USERNAME IS NULL THEN 
        DBMS_OUTPUT.PUT_LINE('The USERNAME is NOT defined. Try again.' || CHR(10));
      ELSE
        DBMS_OUTPUT.PUT_LINE('The user ' || USERNAME || ' does NOT exist.' || CHR(10));
       END IF;
  END;

END;
/

The following PL/SQL statement retrieves an object metadata.

SET PAGES 0
SET LINES 3000
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON 
SET SERVEROUTPUT ON SIZE 1000000
COLUMN TXT FORMAT a2500 WORD_WRAPPED

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

-- To retrieve an object DDL, specify a Type, a Name and an Owner of an object

SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OBJECT_OWNER') AS TXT FROM DUAL;

Written At
14 NOV 202113:40
OEL Release
7.9 x64
Database Release
19.12.0.0.0