Oracle PRVT_PARTREC_NOPRIV
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Undocumented
AUTHID DEFINER
Dependencies
ALL_CONSRAINTS DBMS_ASSERT KU$PARSED_ITEM
ALL_DIM_LEVELS DBMS_LOB KU$PARSED_ITEMS
ALL_INDEXES DBMS_METADATA PLITBLM
ALL_MVIEW_DETAIL_RELATIONS KU$DDL PRVT_ACCESS_ADVISOR
ALL_MVIEW_LOGS KU$DDLS PRVT_ADVISOR
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
Packages

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-2014 Daniel A. Morgan All Rights Reserved