ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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;
/
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);
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);
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
dbms_redefinition_internal.find_dep_idx(
o_bt_owner IN VARCHAR2,
o_bt_name IN VARCHAR2,
o_idx_owner IN VARCHAR2,
o_idx_name IN VARCHAR2,
i_bt_owner IN VARCHAR2,
i_bt_name IN VARCHAR2,
i_idx_owner IN VARCHAR,
i_idx_name OUT VARCHAR2)
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;
DECLARE
c CLOB;
BEGIN
c := TO_CLOB(dbms_redefinition_internal.get_default_value('UWCLASS', 'DEFAULT_TEST', 'CREATED_ON'));
dbms_output.put_line(c);
END;
/
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;
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;
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;
/
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
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);
-- 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
-- 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;
/
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
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;
conn sys@pdbdev as sysdba
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
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
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;
/
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;
/
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
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;
/
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
dbms_redefinition_internal.is_join_idx(
towner IN VARCHAR2,
tname IN VARCHAR2,
idx_owner IN VARCHAR2,
idx_name IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
IF dbms_redefinition_internal.is_join_idx('UWCLASS', 'T', 'UWCLASS', 'BJI_T') THEN
dbms_output.put_line('Is A Bitmap Join Index');
ELSE
dbms_output.put_line('Not A Bitmap Join Index');
END IF;
END;
/
dbms_redefinition_internal.is_nest_iot(
towner IN VARCHAR2,
tname IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
IF dbms_redefinition_internal.is_join_idx('UWCLASS', 'NESTIOT',) THEN
dbms_output.put_line('Is A Nested IOT');
ELSE
dbms_output.put_line('Not A Nested IOT');
END IF;
END;
/
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
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
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
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;
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;
/ An IOT PK
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;
-- 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
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;
/ Composite Partitioned Table
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;
/ Name Is Taken Unused Table Name
dbms_redefinition_internal.is_table_object( tname IN VARCHAR2, towner IN VARCHAR2) RETURN BOOLEAN;
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;
/ F
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, is_obj_tab IN BOOLEAN)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba
BEGIN
IF dbms_redefinition_internal.is_table_partitioned('TAB$', 'SYS', FALSE) THEN
dbms_output.put_line('Partitioned Table');
ELSE
dbms_output.put_line('Non-Paritioned Table');
END IF;
END;
/ Non-Paritioned Table
dbms_redefinition_internal.is_tab_analyzed( towner IN VARCHAR2, tname IN VARCHAR2) RETURN BOOLEAN;
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;
/ T
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
BEGIN
IF dbms_redefinition_internal.nest_tab_name_exist('PROPERTIES_TAB', 'SYS') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
IF dbms_redefinition_internal.nest_tab_name_exist('DBFS_SFS$_TAB', 'SYS') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/ T F
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
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
dbms_redefinition_internal.register_dep_cons(
rid IN BINARY_INTEGER,
o_bt_owner IN VARCHAR2,
o_bt_name IN VARCHAR2,
i_bt_owner IN VARCHAR2,
i_bt_name IN VARCHAR2);
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);
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
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
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
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
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
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
dbms_redefinition_internal.test_dml_ref_trigger(
uname IN VARCHAR2,
tname IN VARCHAR2,
snap IN VARCHAR2,
colmap IN VARCHAR2,
options IN BINARY_INTEGER);
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
