Oracle DBMS_REDEFINITION_INTERNAL
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 Internal support utilities for oneline table redefinition.
AUTHID DEFINER
Data Types CREATE OR REPLACE TYPE sys.ku$_ddl AS OBJECT (
ddltext     CLOB,                 -- The DDL text
parsedItems sys.ku$_parsed_items) -- the parsed items
/

CREATE OR REPLACE TYPE sys.ku$_ddls IS TABLE OF sys.ku$_ddl;
/
Dependencies
CCOL$ DBA_PART_TABLES DBMS_SYS_SQL
CDEF$ DBA_REDEFINITION_OBJECTS ICOL$
COL$ DBA_TABLES IND$
CON$ DBA_TAB_PARTITIONS KU$_DDLS
DBA_CONSTRAINTS DBA_TAB_SUBPARTITIONS KU$_SUBMITRESULTS
DBA_CONS_COLUMNS DBA_USERS OBJ$
DBA_INDEXES DBMS_METADATA REDEF_DEP_ERROR$
DBA_IND_COLUMNS DBMS_REDEFINITION REDEF_OBJECT$
DBA_LOBS DBMS_SNAPSHOT_LIB TRIGGER$
DBA_LOG_GROUPS DBMS_SQL TRIGGERDEP$
DBA_MVIEW_LOGS DBMS_SYS_ERROR USER$
Documented No
Exceptions
Error Code Reason
ORA-29304 tablespace '<string>' does not exist
First Available 11.2.0.3
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtbord.plb
Subprograms
 
ADD_DEP_ERROR
Undocumented but likely creates a dependency error record in redef_dep_error$ visualized in dba_redefinition_errors dbms_redefinition_internal.add_dep_error(
rid         IN BINARY_INTEGER,
otyp        IN BINARY_INTEGER,  -- object type
o_obj_owner IN VARCHAR2,        -- object_owner
o_obj_name  IN VARCHAR2,        -- object name
o_bt_owner  IN VARCHAR2,        -- base table owner
o_bt_name   IN VARCHAR2,        -- base table object name
ddltxt      IN CLOB);
TBD
 
DELETE_DEP_ERROR
Undocumented but likely removes a dependency error created by the ADD_DEP_ERROR procedure dbms_redefinition_internal.delete_dep_error(
rid         IN BINARY_INTEGER,
otyp        IN BINARY_INTEGER,  -- object type
o_obj_owner IN VARCHAR2,        -- object_owner
o_obj_name  IN VARCHAR2,        -- object name
o_bt_owner  IN VARCHAR2,        -- base table owner
o_bt_name   IN VARCHAR2,        -- base table object name
TBD
 
FETCH_DDL
Based on a handle returns associated DDL statements dbms_redefinition_internal.fetch_ddl(handle IN NUMBER) RETURN ku$ddls;
TBD
 
GET_MVLOG_NAME
Given a schema and table name returns the name of the associated materialized view log dbms_redefinition_internal.get_mvlog_name(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

CREATE MATERIALIZED VIEW LOG ON uwclass.servers;

set serveroutput on

DECLARE
 mvlog_name VARCHAR2(30);
BEGIN
  mvlog_name := dbms_redefinition_internal.get_mvlog_name('UWCLASS', 'SERVERS');
  dbms_output.put_line(mvlog_name);
  mvlog_name := dbms_redefinition_internal.get_mvlog_name('UWCLASS', 'SERVINST');
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('Table does not have a materialized view log');
END;
/

DROP MATERIALIZED VIEW LOG ON uwclass.servers;
 
GET_PARTITION_CNT
Returns the number of partitions in a partitioned table dbms_redefinition_internal.get_partition_cnt(
tab_name  IN VARCHAR2,
tab_owner IN VARCHAR2)
RETURN NUMBER;
CREATE TABLE uwclass.hash_part (
prof_history_id    NUMBER(10),
prof_hist_comments VARCHAR2(100))
PARTITION BY HASH (prof_history_id)
PARTITIONS 3
STORE IN (uwdata);

set serveroutput on

DECLARE
  pcount NUMBER;
BEGIN
  pcount := dbms_redefinition_internal.get_partition_cnt('HASH_PART', 'UWCLASS');
  dbms_output.put_line(TO_CHAR(pcount));
END;
/
 
GET_REDEF_ID
Returns a redefinition handle and information about the redefinition job dbms_redefinition_internal.get_redef_id(
c_uname       IN  VARCHAR2,
c_orig_table  IN  VARCHAR2,
redef_id      OUT NUMBER,          -- handle
int_obj_owner OUT VARCHAR2,        -- interim table owner
int_obj_name  OUT VARCHAR2,        -- interim table name
is_part_redef OUT BINARY_INTEGER,  -- is a partitioned object
has_mvlog     OUT BINARY_INTEGER,  -- has a materialized view log
TBD
 
GET_SUPPLEMENTAL_LOG_GRP_NAME
Can be used to obtain the name of a supplemental log group dbms_redefinition_internal.get_supplemental_log_grp_name(
grp_owner IN  VARCHAR2,
tab_name  IN  VARCHAR2,
grp_type  IN  VARCHAR2,
grp_name  OUT VARCHAR2);
TBD
 
GET_SUPPLEMENTAL_LOG_TYPE
Can be used to return the supplemental log type dbms_redefinition_internal.get_supplemental_log_type(
grp_owner IN  VARCHAR2,
grp_name  IN  VARCHAR2,
tab_name  IN  VARCHAR2,
grp_type  OUT VARCHAR2);
TBD
 
GET_TABLESPACE (new 12.1)
Given the owner and name of a non-partitioned table returns its tablespace dbms_redefinition_internal.get_tablespace(
tname  IN VARCHAR2,
towner IN VARCHAR2)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

SELECT dbms_redefinition_internal.get_tablespace('TAB$', 'SYS')
FROM dual;

CREATE TABLE list_part (
deptno NUMBER(10),
state  VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION q1_nw VALUES ('OR', 'WA') TABLESPACE system,
PARTITION q1_sc VALUES ('OK', 'TX') TABLESPACE sysaux);

SELECT dbms_redefinition_internal.get_tablespace('LIST_PART', 'SYS')
FROM dual;
 
GET_TABLE_OBJID
Given a schema and table name returns the object id from obj$ dbms_redefinition_internal.get_table_objid(
tab_name  IN VARCHAR2,
tab_owner IN VARCHAR2)
RETURN NUMBER;
set serveroutput on

DECLARE
 objID dba_objects.object_id%TYPE;
BEGIN
  objID := dbms_redefinition_internal.get_table_objid('SERVERS', 'UWCLASS');
  dbms_output.put_line(TO_CHAR(objID));
END;
/

SELECT owner, object_name
FROM dba_objects
WHERE object_id = 76978;
 
GET_USER_ID
Returns the user_id of a user dbms_redefinition_internal.get_user_id(user_name IN VARCHAR2) RETURN BINARY_INTEGER;
set serveroutput on

DECLARE
  uid dba_users.user_id%TYPE;
BEGIN
  uid := dbms_redefinition_internal.get_user_id('UWCLASS');
  dbms_output.put_line(TO_CHAR(uid));
END;
/

SELECT username
FROM dba_users
WHERE user_id = 90;
 
IS_CONTRAINT_ON_NTAB
Returns a value indicative that a constraint is on a nested table dbms_redefinition_internal.is_constraint_on_ntab(
cons_name  IN VARCHAR2,
cons_owner IN VARCHAR2)
RETURN NUMBER;
TBD
 
IS_IDX_FROM_CONSTRAINT (new 12.1)
  dbms_redefinition_internal.is_idx_from_constraint(
name  IN VARCHAR2,
owner IN VARCHAR2)
RETURN NUMBER;
SELECT dc.owner, dc.constraint_name, di.index_name
FROM dba_constraints dc, dba_indexes di
WHERE dc.owner = di.owner
AND dc.table_name = di.table_name
AND dc.constraint_name = di.index_name
AND dc.constraint_type = 'P'
AND rownum < 11
ORDER BY 1;

BEGIN
  IF dbms_redefinition_internal.is_idx_from_constraint('DEPENDENCIES_PK', 'SYS') THEN
    dbms_output.put_line('Index From Constraint');
  ELSE
    dbms_output.put_line('Index Not From A Constraint');
  END IF;
END;
/
 
IS_INDEX_ON_NESTED_TAB
Returns a value indicative that an index is on a nested table dbms_redefinition_internal.is_index_on_nested_tab(
idx_name  IN VARCHAR2,
idx_owner IN VARCHAR2)
RETURN NUMBER;
SELECT di.index_name, di.owner
FROM dba_indexes di, dba_nested_tables dnt
WHERE di.owner = dnt.owner
AND  di.table_name = dnt.table_name
ORDER BY 1,2;

SELECT dbms_redefinition_internal.is_index_on_nested_tab('XDB', 'SYS_C004624')
FROM dual;
 
IS_NOTNULL_CONSTRAINT
Returns a value indicative that a constraint is  a not null constraint dbms_redefinition_internal.is_notnull_constraint(
cons_name  IN VARCHAR2,
cons_owner IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT constraint_name
FROM dba_constraints
WHERE owner='SH'
AND constraint_type = 'C'

SELECT dbms_redefinition_internal.is_notnull_constraint('SH', 'SYS_C0010069')
FROM dual;
 
IS_PK_IOT (new 12.1)
Theoretically returns TRUE if the constraint is an IOT primary key ... except it doesn't. My suspicion is that some coder inside Oracle is using an exception or other means to exit without returning TRUE which is really bad coding. dbms_redefinition_internal.is_pk_iot(
cname  IN VARCHAR2,
cowner IN VARCHAR2)
RETURN BOOLEAN;
conn uwclass/uwclass@pdbdev

CREATE TABLE uwclass.labor_hour (
WORK_DATE   DATE,
EMPLOYEE_NO VARCHAR2(8),
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no))
ORGANIZATION INDEX;

BEGIN
  IF dbms_redefinition_internal.is_pk_iot('PK_LABOR_HOUR', 'UWCLASS') THEN
    dbms_output.put_line('An IOT PK');
  ELSE
    dbms_output.put_line('Not An IOT PK');
  END IF;
END;
/
 
IS_REG_DEP_OBJ
Returns true if the object has been registered as a dependent object of the table being redefined dbms_redefinition_internal.is_reg_dep_obj(
rid         IN BINARY_INTEGER,
otyp        IN BINARY_INTEGER,
o_obj_owner IN VARCHAR2,
o_obj_name  IN VARCHAR2,
o_bt_owner  IN VARCHAR2,
o_bt_name   IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
IS_TABLE_COMPOSITE_PARTITIONED (new 12.1)
Returns TRUE if a table is composite partitioned: Else FALSE dbms_redefinition_internal.is_table_composite_partitioned(
tname  IN VARCHAR2,
towner IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

SELECT DISTINCT table_owner, table_name
FROM dba_tab_subpartitions;

BEGIN
  IF dbms_redefinition_internal.is_table_composite_partitioned('WRI$_OPTSTAT_SYNOPSIS$', 'SYS') THEN
    dbms_output.put_line('Composite Partitioned Table');
  ELSE
    dbms_output.put_line('Not Composite Partitioned');
  END IF;
END;
/
 
IS_TABLE_NAME_TAKEN (new 12.1)
Returns TRUE if a name has been used to create a table: Otherwise FALSE dbms_redefinition_internal.is_table_name_taken(
tname  IN VARCHAR2,
towner IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_redefinition_internal.is_table_name_taken('TAB$', 'SYS') THEN
    dbms_output.put_line('Name Is Taken');
  ELSE
    dbms_output.put_line('Unused Table Name');
  END IF;

  IF dbms_redefinition_internal.is_table_name_taken('ZZYZX$', 'SYS') THEN
    dbms_output.put_line('Name Is Taken');
  ELSE
    dbms_output.put_line('Unused Table Name');
  END IF;
END;
/
 
IS_TABLE_PARTITIONED (new 12.1)
Returns FALSE if a non-partitioned table but does not return TRUE. My suspicion is that some coder inside Oracle is using an exception or other means to exit without returning TRUE which is really bad coding. dbms_redefinition_internal.is_table_partitioned(
tname  IN VARCHAR2,
towner IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_redefinition_internal.is_table_partitioned('TAB$', 'SYS') THEN
    dbms_output.put_line('Partitioned Table');
  ELSE
    dbms_output.put_line('Non-Paritioned Table');
  END IF;
END;
/

ALTER SESSION SET CONTAINER = PDBORCL;

BEGIN
  IF dbms_redefinition_internal.is_table_partitioned('SALES', 'SH') THEN
    dbms_output.put_line('Partitioned Table');
  ELSE
    dbms_output.put_line('Non-Paritioned Table');
  END IF;
END;
/
 
META_GET_XML (new 12.1)
Returns an objects metadata as XML dbms_redefinition_internal.meta_get_xml(
obj_type IN VARCHAR2,
name     IN VARCHAR2,
owner    IN VARCHAR2)
RETURN CLOB;
SELECT dbms_redefinition_internal.meta_get_xml('TABLE', 'SERVERS', 'UWCLASS')
FROM dual;
 
META_PUT (new 12.1)
Undocumented dbms_redefinition_internal.meta_put(
handle IN       NUMBER,
document IN     CLOB,
flags    IN     NUMBER,
results  IN OUT sys.ku$_submitresults)
RETURN BOOLEAN;
TBD
 
REGISTER_DEP_OBJ
Registers an object as dependent dbms_redefinition_internal.register_dep_obj(
rid         IN BINARY_INTEGER,
otyp        IN BINARY_INTEGER,
o_obj_owner IN VARCHAR2,
o_obj_name  IN VARCHAR2,
o_bt_owner  IN VARCHAR2,
o_bt_name   IN VARCHAR2,
i_obj_owner IN VARCHAR2,
i_obj_name  IN VARCHAR2
typflag     IN BINARY_INTEGER);
TBD
 
TEST_DML_REF_TRIGGER (new 12.1)
Undocumented dbms_redefinition_internal.test_dml_ref_trigger(
uname   IN VARCHAR2,
tname   IN VARCHAR2,
snap    IN VARCHAR2,
colmap  IN VARCHAR2,
options IN BINARY_INTEGER);
TBD
 
VALIDATE_TABLESPACE (new 12.1)
Returns an error if a tablespace does not exist dbms_redefinition_internal.validate_tablespace(tbs_name IN VARCHAR2);
exec dbms_redefinition_internal.validate_tablespace('ZZYZX');
*
ERROR at line 1:
ORA-29304: tablespace 'ZZYZX' does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION_INTERNAL", line 516
ORA-06512: at line 1


exec dbms_redefinition_internal.validate_tablespace('SYSAUX');

Related Topics
DBMS_METADATA
DBMS_REDEFINITION
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