Oracle DBMSOBJG2 Demos

Oracle DBMSOBJG2 Version 11.2.0.3

General Information

Purpose

Undocumented DDL generator

AUTHID

AUTHID DEFINER

Dependencies

DBA_INDEXES	DBA_TAB_COLUMNS	DBMS_SYS_SQL
DBA_IND_PARTITIONS	DBA_TAB_PARTITIONS	DBMS_UTILITY
DBA_IND_SUBPARTITIONS	DBA_TAB_SUBPARTITIONS	IND$
DBA_PART_INDEXES	DBMSOBJG	OBJ$
DBA_PART_KEY_COLUMNS	DBMS_REPCAT_OBJ_UTL	PARTOBJ$
DBA_PART_LOBS	DBMS_REPCAT_SQL_UTL	TAB$
DBA_PART_TABLES	DBMS_SQL	TS$
DBA_SUBPART_KEY_COLUMNS	DBMS_SYS_ERROR	USER$
DBA_TABLES

Security Model

Owned by SYS with no privileges granted

Source

{ORACLE_HOME}/rdbms/admin/prvthobg.plb

Subprograms

GENERATE_IND_PART_STORAGE

GENERATE_IOT_PART_STORAGE

GENERATE_TABLE_PART_STORAGE

GENERATE_IND_PART_STORAGE

Undocumented but appears to have a dependency or relationship with advanced replication

dbmsobjg2.generate_ind_part_storage(
sname          IN     VARCHAR2,
oname          IN     VARCHAR2,
use_tablespace IN     BOOLEAN,
create_storage IN     BOOLEAN,
ddltab         IN OUT dbms_sql.varchar2s,
ddlcnt         IN OUT NUMBER,
passedtspace   IN     BOOLEAN,
tstab          IN     dbmsobjg.tspace_tab,
tscnt          IN     NUMBER,
otype          IN     VARCHAR2);

CREATE TABLE iot_test (
WORK_DATE DATE,
EMPLOYEE_NO VARCHAR2(8),
HOURS NUMBER(4,2) NOT NULL,
CONSTRAINT pk_iot_test
PRIMARY KEY (work_date, employee_no))
ORGANIZATION INDEX
INCLUDING hours
OVERFLOW TABLESPACE uwdata
PARTITION BY RANGE (work_date) (
PARTITION yr09 VALUES LESS THAN (TO_DATE('01-JAN-2009', 'DD-MON-YYYY')) TABLESPACE uwdata,
PARTITION yr10 VALUES LESS THAN (TO_DATE('01-JAN-2010', 'DD-MON-YYYY')) TABLESPACE uwdata,
PARTITION yr11 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')) TABLESPACE uwdata,
PARTITION yr99 VALUES LESS THAN (MAXVALUE) TABLESPACE uwdata);

DECLARE
ddltab dbms_sql.varchar2s;
ddlcnt NUMBER;
tblsp_tab dbmsobjg.tspace_tab;
tblsp_cnt NUMBER := 1;
BEGIN
tblsp_tab(1) := 'UWDATA';
dbmsobjg2.generate_ind_part_storage(USER, 'IOT_TEST', TRUE, TRUE, ddltab, ddlcnt, TRUE, tblsp_tab, tblsp_cnt, 'TABLE');
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "SYS.DBMS_REPCAT_SQL_UTL", line 237
ORA-06512: at "SYS.DBMSOBJG2", line 1205
ORA-06512: at line 8

GENERATE_IOT_PART_STORAGE

Undocumented

dbmsobjg2.generate_iot_part_storage(
sname          IN     VARCHAR2,
oname          IN     VARCHAR2,
use_tablespace IN     BOOLEAN,
create_storage IN     BOOLEAN,
ddltab         IN OUT dbms_sql.varchar2s,
ddlcnt         IN OUT NUMBER,
passedtspace   IN     BOOLEAN,
tstab          IN     dbmsobjg.tspace_tab,
tscnt          IN     NUMBER,
flavor_columns IN     dbms_utility.name_array);

TBD

GENERATE_TABLE_PART_STORAGE

Undocumented

dbmsobjg2.generate_table_part_storage(
sname          IN     VARCHAR2,
oname          IN     VARCHAR2,
use_tablespace IN     BOOLEAN,
create_storage IN     BOOLEAN,
ddltab         IN OUT dbms_sql.varchar2s,
ddlcnt         IN OUT NUMBER,
passedtspace   IN     BOOLEAN,
tstab          IN     dbmsobjg.tspace_tab,
tscnt          IN     NUMBER,
flavor_columns IN     dbms_utility.name_array,
has_lob_col    IN     BOOLEAN);

TBD