Oracle | PRVT_PARTREC

Oracle PRVT_PARTREC_NOPRIV Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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.