Oracle | PRVT_PARTREC

Oracle PRVT_PARTREC_NOPRIV Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Undocumented

AUTHID CURRENT_USER

Dependencies

ALL_CONSRAINTS	DBMS_LOB	KU$PARSED_ITEM
ALL_DIM_LEVELS	DBMS_METADATA	KU$PARSED_ITEMS
ALL_INDEXES	DBMS_STANDARD	PLITBLM
ALL_MVIEW_DETAIL_RELATIONS	KU$DDL	PRVT_ACCESS_ADVISOR
ALL_MVIEW_LOGS	KU$DDLS	PRVT_ADVISOR
DBMS_ASSERT

Documented No

First Available Not known

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvtsms.plb

GET_TABLE_DEFINITION

Writes the DDL for a heap table to create a partitioned table, insert the rows from the source table and collect table stats. Also performs a little renaming trick so that the partitioned table will appear to become the original table. prvt_partrec_nopriv.get_table_definition( l_owner IN VARCHAR2, l_table IN VARCHAR2, partition_clause IN CLOB, l_show_storage IN BINARY_INTEGER, -- 1 = generate the storage clause table_def OUT CLOB);

conn sys@pdbdev as sysdba

      

      CREATE TABLE uwclass.test (

      prof_history_id NUMBER(10),

      prof_hist_recrd VARCHAR2(200));

      

      DECLARE

       partClause CLOB;

       outVal     CLOB;

      BEGIN

        partClause := 'PARTITION BY HASH (prof_history_id) PARTITIONS 5';

      

        prvt_partrec_nopriv.get_table_definition('UWCLASS', 'TEST', partClause, 0, outVal);

        dbms_output.put_line(outVal);

      END;

      /

      

      SET SERVEROUTPUT ON

      SET ECHO ON

      

      Rem

      Rem Creating new partitioned table

      Rem

      CREATE TABLE "UWCLASS"."TEST1"

      ( "PROF_HISTORY_ID"

      NUMBER(10,0),

      "PROF_HIST_RECRD" VARCHAR2(200)

      )

      PARTITION BY HASH (prof_history_id) PARTITIONS 5;

      

      Rem

      Rem Populating new partitioned

      table with data from original table

      Rem

      INSERT /*+ APPEND */ INTO "UWCLASS"."TEST1"

      SELECT * FROM "UWCLASS"."TEST";

      COMMIT;

      

      begin

        dbms_stats.gather_table_stats('"UWCLASS"', '"TEST1"', NULL, dbms_stats.auto_sample_size);

      end;

      /

      

      Rem

      Rem Renaming tables to give new partitioned

      table the original table name

      Rem

      ALTER TABLE "UWCLASS"."TEST" RENAME TO "TEST11";

      ALTER TABLE "UWCLASS"."TEST1" RENAME TO "TEST";

      

      

      PL/SQL procedure successfully completed.

      

      DECLARE

       partClause CLOB;

       outVal     CLOB;

      BEGIN

        partClause := 'PARTITION BY HASH (prof_history_id) PARTITIONS 5';

      

        prvt_partrec_nopriv.get_table_definition('UWCLASS', 'TEST', partClause, 1, outVal);

        dbms_output.put_line(outVal);

      END;

      /

      

      SET SERVEROUTPUT ON

      SET ECHO ON

      

      Rem

      Rem Creating new partitioned table

      Rem

      CREATE TABLE "UWCLASS"."TEST1"

      ( "PROF_HISTORY_ID"

      NUMBER(10,0),

      "PROF_HIST_RECRD" VARCHAR2(200)

      ) SEGMENT CREATION DEFERRED

      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

      NOCOMPRESS

      NOLOGGING

      TABLESPACE "EXAMPLE"

      PARTITION BY HASH (prof_history_id) PARTITIONS 5;

      

      Rem

      Rem Populating new partitioned table with data from

      original table

      Rem

      INSERT /*+ APPEND */ INTO "UWCLASS"."TEST1"

      SELECT * FROM "UWCLASS"."TEST";

      COMMIT;

      

      begin

        dbms_stats.gather_table_stats('"UWCLASS"', '"TEST1"', NULL, dbms_stats.auto_sample_size);

      end;

      /

      

      Rem

      Rem Renaming tables to give new partitioned

      table the original table name

      Rem

      ALTER TABLE "UWCLASS"."TEST" RENAME TO "TEST11";

      ALTER TABLE "UWCLASS"."TEST1" RENAME TO "TEST";

      

      PL/SQL procedure successfully completed.