Oracle DBMS_REDEFINITION_INTERNAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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
ALL_CONS_COLUMNS DBA_SENSITIVE_DATA DBMS_UTILITY
ALL_TAB_COLUMNS DBA_TABLES EXPLAINMVARRAYTYPE
CCOL$ DBA_TAB_COLS EXPLAINMVMESSAGE
CDEF$ DBA_TAB_COLUMNS ICOL$
COL$ DBA_TAB_PARTITIONS IND$
CON$ DBA_TAB_SUBPARTITIONS KU$_DDLS
DBA_ALL_TABLES DBA_USERS KU$_SUBMITRESULTS
DBA_CONSTRAINTS DBMS_ASSERT KU$_SUBMITRESULTS
DBA_CONS_COLUMNS DBMS_IJOB OBJ$
DBA_INDEXES DBMS_LOB PLITBLM
DBA_IND_COLUMNS DBMS_METADATA REDEF$
DBA_IND_PARTITIONS DBMS_MVIEW REDEF_DEP_ERROR$
DBA_IND_SUBPARTITIONS DBMS_REDEFINITION REDEF_OBJECT$
DBA_LOBS DBMS_SCHEDULER SNAP$
DBA_LOB_PARTITIONS DBMS_SNAPSHOT_LIB SNAP_REFTIME$
DBA_LOG_GROUPS DBMS_SQL TAB$
DBA_MVIEW_LOGS DBMS_STATS TRIGGER$
DBA_NESTED_TABLES DBMS_SYSTEM TRIGGERDEP$
DBA_PART_TABLES DBMS_SYS_ERROR USER$
DBA_REDEFINITION_OBJECTS DBMS_SYS_SQL  
kDocumented No
Exceptions
Error Code Reason
ORA-01435 User does not exist
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_ERRMSG (new 12.2)
Undocumented dbms_redefinition_internal.add_dep_errMsg(
rid IN BINARY_INTEGER
otype       IN BINARY_INTEGER,
o_obj_owner IN VARCHAR2,
o_obj_name  IN VARCHAR2,
o_bt_owner  IN VARCHAR2,
o_bt_name   IN VARCHAR2,
ERRMSG      IN VARCHAR2);
TBD
 
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
 
CHECK_TRACE_EVENT (new 12.2)
Undocumented dbms_redefinition_internal.check_trace_event(
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
 
DO_REFRESH_DEP_MVS (new 12.2)
Undocumented dbms_redefinition_internal.do_refresh_dep_mvs(
owner IN VARCHAR2,
name  IN VARCHAR2);
exec dbms_redefinition_internal.do_refresh_dep_mvs('X', 'Y');
 
FETCH_DDL
Based on a handle returns associated DDL statements dbms_redefinition_internal.fetch_ddl(handle IN NUMBER) RETURN ku$ddls;
TBD
 
GENERATECOLMAP (new 12.2)
Generates a column map. Not sure what this would do with a 1000 column table where column names are 128 bytes and don't have enough interest to check ... but clearly the math wouldn't work out. dbms_redefinition_internal.generateColMap(
tableowner IN VARCHAR2,
tablename  IN VARCHAR2)
RETURN VARCHAR2;
SQL> SELECT dbms_redefinition_internal.generateColMap(USER, 'OBJ$')
  2  FROM dual;

DBMS_REDEFINITION_INTERNAL.GENERATECOLMAP(USER,'OBJ$')
----------------------------------------------------------------------------------------------------
SPARE7 SPARE7, SPARE8 SPARE8, SPARE9 SPARE9, DFLCOLLID DFLCOLLID, CREAPPID CREAPPID, CREVERID CREVERID, CREPATCHID CREPATCHID, MODAPPID MODAPPID, MODVERID MODVERID, MODPATCHID MODPATCHID, SPARE10 SPARE10, SPARE11 SPARE11, SPARE12 SPARE12, SPARE13 SPARE13, SPARE14 SPARE14, OBJ# OBJ#, DATAOBJ# DATAOBJ#, OWNER# OWNER#, NAME NAME, NAMESPACE NAMESPACE, SUBNAME SUBNAME, TYPE# TYPE#, CTIME CTIME, MTIME MTIME, STIME STIME, STATUS STATUS, REMOTEOWNER REMOTEOWNER, LINKNAME LINKNAME, FLAGS FLAGS, OID$ OID$, SPARE1 SPARE1, SPARE2 SPARE2, SPARE3 SPARE3, SPARE4 SPARE4, SPARE5 SPARE5, SPARE6 SPARE6, SIGNATURE SIGNATURE
 
GET_DEFAULTONNULL_COL (new 12.2)
Undocumented dbms_redefinition_internal.get_defaultOnNull_col(
cons_name  IN VARCHAR2,
cons_owner IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_DEFAULT_VALUE (new 12.2)
Returns the default value for a table column defined with a column default dbms_redefinition_internal.get_default_value(
ownername IN VARCHAR2,
tablename IN VARCHAR2,
colname   IN VARCHAR2)
RETURN LONG;
CREATE TABLE default_test (
active     VARCHAR2(1)  DEFAULT 'Y',
created_by VARCHAR2(30) DEFAULT USER,
created_on TIMESTAMP    DEFAULT SYSTIMESTAMP);

DECLARE
 c CLOB;
BEGIN
  c := TO_CLOB(dbms_redefinition_internal.get_default_value('UWCLASS', 'DEFAULT_TEST', 'CREATED_ON'));
  dbms_output.put_line(c);
END;
/
 
GET_IDX_NAME (new 12.2)
Returns the name of the index object supporting a Primary Key or Unique constraint dbms_redefinition_internal.get_idx_name(
cons_name  IN VARCHAR2,
cons_owner IN VARCHAR2)
RETURN VARCHAR2;
CREATE TABLE uwclass.t(
testcol VARCHAR2(20) PRIMARY KEY);

SELECT constraint_name
FROM dba_constraints
WHERE table_name = 'T';

CONSTRAINT_NAME
----------------
SYS_C0011510

SELECT dbms_redefinition_internal.get_idx_name('SYS_C0011510', 'UWCLASS')
FROM dual;
 
GET_IDX_SPARE4 (new 12.2)
Cannot tell for sure my suspect this returns the value from ind$.spare4 if it contains a value. SPARE4 persists the parameter string for domain indexes. dbms_redefinition_internal.get_idx_spare4(
iowner IN VARCHAR2,
iname  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_INTERIM_TAB_NAME (new 12.2)
Returns the name of the redefinition interim table dbms_redefinition_internal.get_interim_tab_name(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_INT_OBJ_NAME (new 12.2)
Returns the name of the redefinition interim object dbms_redefinition_internal.get_int_obj_name(
rid         IN BINARY_INTEGER
otyp        IN BINARY_INTEGER
o_obj_owner IN VARCHAR2
o_obj_name  IN VARCHAR2)
RETURN VARCHAR2;
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
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_TAB_PROPERTY (new 12.2)
Undocumented but appears to return a boolean for an Advanced Queuing, Clustered, Partitioned by System, Index Organized, or "pko" table dbms_redefinition_internal.get_tab_property(
towner           IN  VARCHAR2,
tname            IN  VARCHAR2,
is_aq_table      OUT BOOLEAN,
is_clu_table     OUT BOOLEAN,
is_syspart_table OUT BOOLEAN,
is_iot_table     OUT BOOLEAN,
is_pko_table     OUT BOOLEAN);
TBD
 
GET_USER (new 12.2)
Returns an exception if the input schema name does not exist dbms_redefinition_internal.get_user(towner IN VARCHAR2);
exec dbms_redefinition_internal.get_user('UWCLASS');

-- returns the following exception if the schema does not exist
*
ERROR at line 1:
ORA-01435: user does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION_INTERNAL", line 1632
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_REDEFINITION_INTERNAL", line 1628
ORA-06512: at line 1
 
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;
 
HAS_SDO_IDX (new 12.2)
In theory returns TRUE if the table has a spatial index but the demo, at right, returns FALSE dbms_redefinition_internal.has_sdo_idx(
tabowner IN VARCHAR2,
tabname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.cola_markets(
mkt_id   NUMBER PRIMARY KEY,
mkt_name VARCHAR2(32),
shape    mdsys.sdo_geometry);

-- this create index throws an exception but the demo still works
CREATE INDEX uwclass.cola_spatial_idx
ON uwclass.cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

BEGIN
  IF dbms_redefinition_internal.has_sdo_idx('UWCLASS', 'COLA_MARKET') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
HAS_VIRTUAL_COL (new 12.2)
Returns TRUE if the table has a virtual column dbms_redefinition_internal.has_virtual_column(
tableowner IN VARCHAR2,
tablename  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.vcol (
salary     NUMBER(8),
bonus      NUMBER(3),
total_comp NUMBER(10) GENERATED ALWAYS AS (salary+bonus));

BEGIN
  IF dbms_redefinition_internal.has_virtual_col('UWCLASS', 'VCOL') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
IS_BITMAP_IDX (new 12.2)
Returns TRUE if the index is a bitmap index; otherwise FALSE dbms_redefinition_internal.is_bitmap_idx(
towner    IN VARCHAR2,
tname     IN VARCHAR2,
idx_owner IN VARCHAR2,
idx_name  IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.is_bitmap_idx('UWCLASS', 'SERV_INST', 'UWCLASS', 'BIX_SERV_INST_WS_ID') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
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_CTX_IDX (new 12.2)
Returns TRUE if the index is a CONTEXT index; otherwise FALSE dbms_redefinition_internal.is_ctx_idx(
idxowner IN VARCHAR2,
idxname  IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.is_ctx_idx('CTXSYS', 'CTXCAT') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F
 
IS_IDX_ANALYZED (new 12.2)
Returns TRUE if the index has been analyzed with DBMS_STATS dbms_redefinition_internal.is_idx_analyzed(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.is_idx_analyzed('UWCLASS', 'SERV_INST') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
IS_IDX_FROM_CONSTRAINT
  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_IDX_FROM_PK_CONSTRAINT (new 12.2)
Returns TRUE if the index supports a primary key constraint; otherwise FALSE dbms_redefinition_internal.is_idx_from_pk_constraint(
name  IN VARCHAR2,
owner IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.is_idx_from_pk_constraint('PK_SERVERS', 'UWCLASS') THEN
    dbms_output.put_line('Index From PK Constraint');
  ELSE
    dbms_output.put_line('Index Not From A PK Constraint');
  END IF;
END;
/
 
IS_INDEX_COMPOSITE_PARTITIONED (new 12.2)
Returns TRUE if the index is composite partition; otherwise FALSE dbms_redefinition_internal.is_index_composite_partitioned(
idxowner IN VARCHAR2,
idxname  IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
IS_INDEX_ON_NESTED_TAB (new 12.2)
Returns TRUE if the index is on a nested table; otherwise FALSE dbms_redefinition_internal.is_index_on_nested_tab(
idx_name  IN VARCHAR2,
idx_owner IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
IS_INDEX_SINGLE_COL_KEY (new 12.2)
Returns TRUE if the key search is performed using a single column index dbms_redefinition_internal.is_index_single_col_key(
towner  IN VARCHAR2,
tname   IN VARCHAR2,
idxname IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.is_index_single_col_key('UWCLASS', 'SERVERS', 'PK_SERVERS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
IS_INDEX_XML
Returns TRUE if the input parameters match an XML index dbms_redefinition_internal.get_index_xml(
towner IN VARCHAR2,
tname  IN VARCHAR2,
idxname IN VARCHAR2)
RETURN BOOLEAN;
desc dba_xml_indexes

BEGIN
  IF dbms_redefinition_internal.get_index_xml('XDB', 'TESTTAB', 'IX_TESTTAB') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
IS_IOT_INDEX (new 12.2)
Returns true of the parameter "name" is the name of an index and returns TRUE if it is on an Index Organized Table; otherwise FALSE dbms_redefinition_internal.is_iot_index(
name  IN VARCHAR2,
owner IN VARCHAR2)
RETURN BOOLEAN;
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_iot_index('LABOR_HOUR', 'UWCLASS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F
BEGIN
  IF dbms_redefinition_internal.is_iot_index('PK_LABOR_HOUR', 'UWCLASS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
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_PARTITIONED_IDX (new 12.2)
Returns TRUE if the index on the partitioned table is a local index; otherwise FALSE dbms_redefinition_internal.is_partitioned_idx(
idxowner IN VARCHAR2,
idxname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.interval_part (
person_id   NUMBER(5) NOT NULL,
first_name  VARCHAR2(30),
last_name   VARCHAR2(30))
PARTITION BY RANGE (person_id)
INTERVAL (100) STORE IN (uwdata) (
PARTITION p1 VALUES LESS THAN (101))
TABLESPACE uwdata;

CREATE INDEX uwclass.idx_person
ON uwclass.interval_part(person_id);


BEGIN
  IF dbms_redefinition_internal.is_partitioned_idx('UWCLASS', 'IDX_PERSON') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

DROP INDEX uwclass.idx_person;

CREATE INDEX uwclass.idx_person
ON uwclass.interval_part(person_id)
LOCAL;


BEGIN
  IF dbms_redefinition_internal.is_partitioned_idx('UWCLASS', 'IDX_PERSON') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
IS_PART_VALID (new 12.2)
Returns TRUE if the partition is valid; otherwise FALSE dbms_redefinition_internal.is_part_valid(
owner     IN VARCHAR2,
tablename IN VARCHAR2,
partname  IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.is_part_valid('UWCLASS', 'INTERVAL_PART', 'P1') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
IS_PK_GENERATED_IDX (new 12.2)
Returns TRUE if the index named was generated by creation of a Primary Key or Unique constraint dbms_redefinition_internal.is_pk_generated_idx(
name  IN VARCHAR2,
owner IN VARCHAR2)
RETURN BOOLEAN;
???

BEGIN
  IF dbms_redefinition_internal.is_pk_generated_idx('PK_SERVERS', 'UWCLASS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
IS_PK_IOT
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_SDO_IDX (new 12.2)
Returns true if the object is an Oracle Spatial index dbms_redefinition_internal.is_sdo_idx(
idxowner IN VARCHAR2,
idxname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.cola_markets(
mkt_id   NUMBER PRIMARY KEY,
mkt_name VARCHAR2(32),
shape    mdsys.sdo_geometry);

-- this create index throws an exception but the demo still works
CREATE INDEX uwclass.cola_spatial_idx
ON uwclass.cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

BEGIN
  IF dbms_redefinition_internal.is_sdo_idx('UWCLASS', 'COLA_SPATIAL_IDX') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
IS_TABLE_AUTO_PARTITIONED (new 12.2)
Undocumented dbms_redefinition_internal.is_table_auto_partitioned(
tname  IN VARCHAR2,
towner IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
IS_TABLE_COMPOSITE_PARTITIONED
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
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_OBJECT (new 12.2)
Returns TRUE if the object is a table dbms_redefinition_internal.is_table_object(
BEGIN
  IF dbms_redefinition_internal.is_tab_analyzed('UWCLASS', 'PK_SERVERS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
IS_TABLE_PARTITIONED
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 = PDBDEV;

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;
/
 
IS_TAB_ANALYZED (new 12.2)
Returns TRUE if the table has been analyzed dbms_redefinition_internal.is_tab_analyzed(
BEGIN
  IF dbms_redefinition_internal.is_tab_analyzed('UWCLASS', 'SERVERS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
META_GET_XML
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
Undocumented dbms_redefinition_internal.meta_put(
handle   IN     NUMBER,
document IN     CLOB,
flags    IN     NUMBER,
results  IN OUT sys.ku$_submitresults)
RETURN BOOLEAN;
TBD
 
MV_FASTREFRESHABLE (new 12.2)
Returns TRUE if the target materialized view is a Fast Refresh MV; otherwise FALSE dbms_redefinition_internal.mv_fastRefreshable(
owner IN VARCHAR2,
name  IN VARCHAR2)
RETURN BOOLEAN;
CREATE MATERIALIZED VIEW LOG ON uwclass.servers
PCTFREE 0 PCTUSED 99
TABLESPACE uwdata
WITH PRIMARY KEY, ROWID, SEQUENCE;

CREATE MATERIALIZED VIEW uwclass.mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT * FROM servers;

BEGIN
  IF dbms_redefinition_internal.mv_fastRefreshable('UWCLASS', 'MV_SIMPLE') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
NEED_REFRESH_DEP_MVS (new 12.2)
Undocumented but appears to be checking whether the materialized views used by the DBMS_REDEFINITION package need to be refreshed dbms_redefinition_internal.need_refresh_dep_mvs(
owner IN VARCHAR2,
name  IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
NEST_TAB_NAME_EXIST
Returns TRUE if the identified nested table exists ... at least in theory. My tests do not bear this out as you can see in the demo at right. Whether this a bug or just the fact that this function is undocumented and possibly being used incorrectly is not known. dbms_redefinition_internal.nest_tab_name_exist(
tname  IN VARCHAR2,
towner IN VARCHAR2)
RETURN BOOLEAN;
SQL> SELECT table_name, parent_table_name
  2  FROM dba_nested_tables
  3  WHERE owner = 'SYS'
  4  ORDER BY 1,2;

TABLE_NAME             PARENT_TABLE_NAME
---------------------- -------------------------
HS_PARTITION_COL_NAME  HS$_PARALLEL_METADATA
HS_PARTITION_COL_TYPE  HS$_PARALLEL_METADATA
PROPERTIES_TAB         DBFS_SFS$_TAB
SCHEDULER$_FWQ_ANT     SCHEDULER_FILEWATCHER_QT
SCHEDULER$_RJQ_ANT     SCHEDULER$_REMDB_JOBQTAB
S_PROPS_TAB            DBFS$_MOUNTS
USR_PROPERTIES_TAB     DBFS_SFS$_FSTO

BEGIN
  IF dbms_redefinition_internal.nest_tab_name_exist('SYS', 'PROPERTIES_TAB') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;

  IF dbms_redefinition_internal.nest_tab_name_exist('SYS', 'DBFS_SFS$_TAB') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
NOCOMP_PART_LOB (new 12.2)
Undocumented dbms_redefinition_internal.nocomp_part_lob(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
PART_LOB (new 12.2)
Undocumented dbms_redefinition_internal.part_lob(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
PK_ON_IOT (new 12.2)
Returns the column names and TRUE if the Index Organized Table has a primary key dbms_redefinition_internal.pk_on_iot(
tname  IN  VARCHAR2,
towner IN  VARCHAR2,
cname  OUT VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.labor_hour (
WORK_DATE   DATE,
EMPLOYEE_NO VARCHAR2(8),
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no))
ORGANIZATION INDEX;

DECLARE
 cname_out VARCHAR2(30);
BEGIN
  IF dbms_redefinition_internal.pk_on_iot('LABOR_HOUR', 'UWCLASS', cname_out) THEN
    dbms_output.put_line(cname_out);
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
PK_LABOR_HOUR
 
PK_ON_TBL (new 12.2)
Returns the column names and TRUE if the table has a primary key dbms_redefinition_internal.pk_on_tbl(
tname  IN  VARCHAR2,
towner IN  VARCHAR2,
cname  OUT VARCHAR2)
RETURN BOOLEAN;
DECLARE
 cname_out VARCHAR2(30);
BEGIN
  IF dbms_redefinition_internal.pk_on_tbl('AIRPLANES', 'UWCLASS', cname_out) THEN
    dbms_output.put_line(cname_out);
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
PK_AIRPLANES
 
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
 
SET_PARAM_INT (new 12.2)
Used to set a parameter name:value pair. As this package is undocumented we do not know the names of valid parameters or their range of values. dbms_redefinition_internal.set_param_int(
redefinition_id IN VARCHAR2,
param_name      IN VARCHAR2,
param_value     IN VARCHAR2);
TBD
 
SKIP_ABORT_EVENT (new 12.2)
Undocumented dbms_redefinition_internal.skip_abort_event RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.skip_abort_event THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F
 
TAB_BEING_REDEFED (new 12.2)
Returns TRUE if a table is in the process of being redefined with DBMS_REDEFINITION dbms_redefinition_internal.tab_being_redefed(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.tab_being_redefed('UWCLASS', 'SERVERS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F
 
TAB_EXIST (new 12.2)
Returns TRUE if a table exists; otherwise FALSE dbms_redefinition_internal.tab_exists(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.tab_exist('UWCLASS', 'SERVERZ') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/

BEGIN
  IF dbms_redefinition_internal.tab_exist('UWCLASS', 'SERVERS') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
TAB_HAS_LOB (new 12.2)
Returns TRUE if the table has has a LOB column; otherwise FALSE dbms_redefinition_internal.tab_has_lob(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.lobcol(
pid   NUMBER);

BEGIN
  IF dbms_redefinition_internal.tab_has_lob('UWCLASS', 'LOBCOL') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

ALTER TABLE uwclass.lobcol ADD (newcol BLOB);

BEGIN
  IF dbms_redefinition_internal.tab_has_lob('UWCLASS', 'LOBCOL') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
TAB_HAS_LONG (new 12.2)
Returns TRUE if the table has has a column with the LONG data type; otherwise FALSE dbms_redefinition_internal.tab_has_long(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.longcol(
pid   NUMBER);

BEGIN
  IF dbms_redefinition_internal.tab_has_long('UWCLASS', 'LONGCOL') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

ALTER TABLE uwclass.longcol ADD (newcol LONG);

BEGIN
  IF dbms_redefinition_internal.tab_has_long('UWCLASS', 'LONGCOL') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
TAB_HAS_NESTAB (new 12.2)
Returns TRUE if the table has has a nested table; otherwise FALSE dbms_redefinition_internal.tab_has_nestab(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.nestedtab(
name     VARCHAR2(20),
director VARCHAR2(20),
office   VARCHAR2(20),
courses  VARCHAR2(20));

BEGIN
  IF dbms_redefinition_internal.tab_has_nestab('UWCLASS', 'NESTEDTAB') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

DROP TABLE uwclass.nestedtab PURGE;

CREATE OR REPLACE NONEDITIONABLE TYPE uwclass.CourseList AS TABLE OF VARCHAR2(64);
/

CREATE TABLE uwclass.nestedtab(
name     VARCHAR2(20),
director VARCHAR2(20),
office   VARCHAR2(20),
courses  uwclass.CourseList)
NESTED TABLE courses STORE AS courses_tab;

BEGIN
  IF dbms_redefinition_internal.tab_has_nestab('UWCLASS', 'NESTEDTAB') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T
 
TAB_HAS_PERIOD (new 12.2)
Undocumented but appears to be intended to identify tables with PERIOD definition hidden columns dbms_redefinition_internal.tab_has_period(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.perDef(
rid  NUMBER,
dob1 DATE,
dob2 DATE);

BEGIN
  IF dbms_redefinition_internal.tab_has_period('UWCLASS', 'PERDEF') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

ALTER TABLE uwclass.perDef ADD PERIOD FOR track_time(dob1, dob2);

BEGIN
  IF dbms_redefinition_internal.tab_has_period('UWCLASS', 'PERDEF') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F


-- it appears something in my code does not work to demonstrate this function
 
TAB_HAS_SENSITIVE_COL (new 12.2)
Returns TRUE if the table has one or more columns with transparent sensitive data management; otherwise FALSE dbms_redefinition_internal.tab_has_sensitive_col(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE uwclass.emp(
pid   NUMBER,
pname VARCHAR2(20),
ssn   VARCHAR2(11));

BEGIN
  IF dbms_redefinition_internal.tab_has_sensitive_col('UWCLASS', 'EMPLOYEES') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

exec dbms_tsdp_manage.add_sensitive_type('SSN_TYPE', 'Social Security Number');
exec dbms_tsdp_manage.add_sensitive_column('UWCLASS', 'EMP', 'SSN', 'SSN_TYPE', 'Personal SSN');

SQL> SELECT owner, table_name, column_name, sensitive_column
  2  FROM dba_tab_cols
  3  WHERE table_name = 'EMP'
  4* AND owner = 'UWCLASS';

OWNER    TABLE_NAME  COLUMN_NAME  SEN
-------- ----------- ------------ ---
UWCLASS  EMP         PID          NO
UWCLASS  EMP         PNAME        NO
UWCLASS  EMP         SSN          YES

BEGIN
  IF dbms_redefinition_internal.tab_has_sensitive_col('UWCLASS', 'EMPLOYEES') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F


-- it appears something in my code des not work to demonstrate this function
 
TAB_IS_LOGGING (new 12.2)
Returns TRUE if the table is in logging mode; otherwise FALSE dbms_redefinition_internal.tab_is_logging(
towner IN VARCHAR2,
tname  IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE logging(
testcol VARCHAR2(20))
LOGGING;

BEGIN
  IF dbms_redefinition_internal.tab_is_logging(USER, 'LOGGING') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/

ALTER TABLE logging NOLOGGING;

BEGIN
  IF dbms_redefinition_internal.tab_is_logging(USER, 'LOGGING') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
TEST_DML_REF_TRIGGER
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
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');
 
VORD_MONITOR_EVENT (new 12.2)
Undocumented dbms_redefinition_internal.vord_monitor_event RETURN BOOLEAN;
BEGIN
  IF dbms_redefinition_internal.vord_monitor_event THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_METADATA
DBMS_REDEFINITION
DBMS_TSDP_MANAGE
DBMS_TSDP_PROTECT
What's New In 12cR1
What's New In 12cR2

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