Oracle PRVT_PARTREC_NOPRIV
Version 19c

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 DEFINER
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.

Related Topics
Built-in Functions
Built-in Packages
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx