Database Administration (MOSC)

MOSC Banner

DBMS_METADATA for TABLE_STATISTICS generate code with "your_schema"."your_stats_table" (literally)

edited May 20, 2020 5:01AM in Database Administration (MOSC) 1 commentAnswered

When executing

  declare

    dmh number;

    th number;

    th2 number;

    c number := 0;

    stmt clob;

  begin

    dmh := dbms_metadata.open('TABLE_STATISTICS');

   

    dbms_metadata.set_filter(dmh, 'BASE_OBJECT_SCHEMA', 'ANDERS');

    dbms_metadata.set_filter(dmh, 'BASE_OBJECT_NAME', 'SOME_TABLE');

   

    th := dbms_metadata.add_transform(dmh, 'MODIFY');

    dbms_metadata.set_remap_param(th, 'REMAP_SCHEMA', 'ANDERS', 'NEW_SCHEMA');

     

    th2 := dbms_metadata.add_transform(dmh, 'DDL');

    dbms_metadata.set_transform_param(th2, 'PRETTY', true);

    dbms_output.put_line('---- output ----');

    loop

      stmt := dbms_metadata.fetch_clob(dmh);

      exit when stmt is null;

      dbms_output.put_line(stmt);

    end loop;

    dbms_metadata.close(dmh);

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center