CREATE OR REPLACE FUNCTION SCHEMA.DBLINK
RETURN CLOB
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
V_LOB CLOB;
BEGIN
-- Specify the object type.
  v_meta_handle := DBMS_METADATA.OPEN('TABLESPACE');
  v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 LOOP
    v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
    EXIT WHEN v_doc IS NULL;
    v_lob   := v_lob ||v_doc;
    v_lob   := v_lob || '/';
    -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
 END LOOP;
   v_lob                      := v_lob || '';
   return v_lob;
   end;

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

SQL> revoke  select_catalog_role from michel
  2  ;

Revoke succeeded.

SQL> CREATE OR REPLACE FUNCTION f
  2  RETURN CLOB
  3  AS
  4  v_meta_handle NUMBER;
  5  v_meta_handle_trans NUMBER;
  6  V_DOC CLOB;
  7  V_LOB CLOB;
  8  BEGIN
  9  -- Specify the object type.
 10    v_meta_handle := DBMS_METADATA.OPEN('TABLESPACE');
 11    v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 12   LOOP
 13      v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
 14      EXIT WHEN v_doc IS NULL;
 15      v_lob   := v_lob ||v_doc;
 16      v_lob   := v_lob || '/';
 17      -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
 18   END LOOP;
 19     v_lob                      := v_lob || '';
 20     return v_lob;
 21     end;
 22  /

Function created.

SQL> select f from dual;
F
--------------------------------------------------------------------------------------


1 row selected.

SQL> grant select_catalog_role to michel
  2  ;

Grant succeeded.

SQL> alter function f compile;

Function altered.

SQL> select f from dual;
F
--------------------------------------------------------------------------------------


1 row selected.

SQL> CREATE OR REPLACE FUNCTION f
  2  RETURN CLOB
  3  AUTHID CURRENT_USER
  4  AS
  5  v_meta_handle NUMBER;
  6  v_meta_handle_trans NUMBER;
  7  V_DOC CLOB;
  8  V_LOB CLOB;
  9  BEGIN
 10  -- Specify the object type.
 11    v_meta_handle := DBMS_METADATA.OPEN('TABLESPACE');
 12    v_meta_handle_trans:=DBMS_METADATA.ADD_TRANSFORM(v_meta_handle,'DDL');
 13   LOOP
 14      v_doc   := DBMS_METADATA.FETCH_CLOB(v_meta_handle);
 15      EXIT WHEN v_doc IS NULL;
 16      v_lob   := v_lob ||v_doc;
 17      v_lob   := v_lob || '/';
 18      -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
 19   END LOOP;
 20     v_lob                      := v_lob || '';
 21     return v_lob;
 22     end;
 23  /

Function created.

SQL> select f from dual;
F
--------------------------------------------------------------------------------------

  CREATE TABLESPACE "SYSTEM" DATAFILE
  'C:\ORACLE\BASES\MIKA\SYSTEM01.DBF' SIZE 524288000
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 1073741824
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
 /
  CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE
  'C:\ORACLE\BASES\MIKA\UNDOTBS01.DBF' SIZE 262144000
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 1073741824
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   ALTER DATABASE DATAFILE
  'C:\ORACLE\BASES\MIKA\UNDOTBS01.DBF' RESIZE 461373440
 /
  CREATE TABLESPACE "SYSAUX" DATAFILE
  'C:\ORACLE\BASES\MIKA\SYSAUX01.DBF' SIZE 209715200
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 1073741824
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  'C:\ORACLE\BASES\MIKA\SYSAUX01.DBF' RESIZE 304087040
 /
...