Oracle | DBMS_SNAP

Oracle DBMS_SNAP_INTERNAL Version 21c

General Information

Library Note

Morgan's Library Page Header

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 Utilities Support Snapshots and Materialized Views

AUTHID DEFINER

Dependencies

DBA_OBJECTS	DBMS_SNAPSHOT_UTL	MLOG$
DBA_TABLES	DBMS_SQL	OBJ$
DBMS_ASSERT	DBMS_STANDARD	PLITBLM
DBMS_FILE_TRANSFER	DBMS_SYSTEM	PROPS$
DBMS_INTERNAL_TRIGGER	DBMS_SYS_ERROR	SNAP$
DBMS_IREFRESH	DBMS_SYS_SQL	SNAPSHOT_ID$
DBMS_ITRIGGER_UTL	DBMS_UTILITY	TAB$
DBMS_LOCK	DUAL	USER$
DBMS_METADATA	GLOBAL_NAME	UTL_RAW
DBMS_REDEFINITION	KU$_DDL	V$PARAMETER
DBMS_SNAPSHOT_KKXRCA	KU$_DDLS	XS_DATA_SECURITY_UTIL
DBMS_SNAPSHOT_LIB	LINK$	XS_DATA_SECURITY_UTIL_INT

Documented No

First Available Not known

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvtsnps.plb
{ORACLE_HOME}/rdbms/admin/prvtsnap.plb

Subprograms

ALLOW_UPDATEABLE_MVS	GET_BACK_END_DB	SET_BACK_END_DB
BEGIN_TABLE_REORGANIZATION	GET_INDEX_DDL	SET_I_AM_AN_ACLMV
BIC	GET_LOGGING_STATUS	SET_I_AM_AN_ORACLE_REFRESH
BIS	GET_TABLE_ID	SET_LOGGING
BIT	GET_TABLE_XLOCK	SET_REDIRECTION
CANONICALIZE_IDENTIFIER	IAS_CREATE_MV_DDL	SYNC_BACK_END_DB
CANONICALIZE_MASTER	IAS_XLOCK_EVENT	SYNC_UP_LOG
CLEAR_REDIRECTION	ID_TO_TIMESTAMP	SYNC_UP_UPD
CONVERT_TO_SELECT_LIST	INSTANTIATE_OFFLINE	TEST_REDIRECTION
DELETE_REFRESH_OPERATIONS	IS_IAS	TIMESTAMP_TO_ID
END_TABLE_REORGANIZATION	I_AM_A_REFRESH	TRANSFORM_DDL
ENSURE_NWAY_MASTER_SITE	LOCAL_NAME_RESOLVE	UPDATEABLE_MVS_ALLOWED
ENSURE_ONE_WAY_MASTER_SITE	MODIFY_RO_ATTRIBUTE	VALIDATE_SQL
ENSURE_SNAPSHOT_SITE	PROHIBIT_UPDATEABLE_MVS	VEC_COLS_IN_TABLE
GENERATE_REFRESH_OPERATIONS	REMOVE_BACK_END_DB

ALLOW_UPDATEABLE_MVS

Undocumented dbms_snap_internal.allow_updateable_mvs;

TBD

BEGIN_TABLE_REORGANIZATION

Undocumented dbms_snap_internal.begin_table_reorganization( tabowner IN VARCHAR2, tabname IN VARCHAR2);

TBD

BIC

Undocumented dbms_snap_internal.bic( flag IN OUT RAW, bit_offset IN NUMBER);

TBD

BIS

Undocumented dbms_snap_internal.bis( flag IN OUT RAW, bit_offset IN NUMBER);

TBD

BIT

Undocumented

Overload 1 dbms_snap_internal.bit( flag IN RAW, byte IN NUMBER, bit_offset IN NUMBER) RETURN BOOLEAN;

TBD

Overload 2 dbms_snap_internal.bit( flag IN RAW, bit_offset IN NUMBER) RETURN BOOLEAN;

TBD

CANONICALIZE_IDENTIFIER

Undocumented dbms_snap_internal.canonicalize_identifier(name IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_snap_internal.canonicalize_identifier('SERVER')

      FROM dual;

      

      
      DBMS_SNAP_INTERNAL.CANONICALIZE_IDENTIFIER('SERVER')

      -----------------------------------------------------

      SERVER

      

      SELECT dbms_snap_internal.canonicalize_identifier('SER VER')

      FROM dual;

      

      DBMS_SNAP_INTERNAL.CANONICALIZE_IDENTIFIER('SER VER')

      ------------------------------------------------------

      SER

CANONICALIZE_MASTER

Undocumented dbms_snap_internal.canonicalize_master( master IN VARCHAR2, canon_master OUT VARCHAR2, check_on_illegal_char IN BOOLEAN, cuid IN NUMBER) RETURN NUMBER;

TBD

CLEAR_REDIRECTION

Undocumented dbms_snap_internal.clear_redirection;

exec dbms_snap_internal.clear_redirection;

      

      PL/SQL procedure successfully completed.

CONVERT_TO_SELECT_LIST

For a reason, not at all clear, drops the first and last character of each comma delimited item in the list.

But it could be really valuable if you had a use case. dbms_snap_internal.convert_to_select_list(list IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_snap_internal.convert_to_select_list('ALPHA,BETA,GAMMA,DELTAEPSILON')

      FROM dual;

      

      
      DBMS_SNAP_INTERNAL.CONVERT_TO_SELECT_LIST('ALPHA,BETA,GAMMA,DELTA')

      --------------------------------------------------------------------

      'LPH','ET','AMM','ELTAEPSILO'

DELETE_REFRESH_OPERATIONS

Undocumented dbms_snap_internal.delete_refresh_operations( snap_owner IN VARCHAR2, snap_name IN VARCHAR2);

TBD

END_TABLE_REORGANIZATION

Undocumented dbms_snap_internal.end_table_reorganization( tabowner IN VARCHAR2, tabname IN VARCHAR2);

TBD

ENSURE_NWAY_MASTER_SITE

Undocumented dbms_snap_internal.ensure_nway_master_site;

exec dbms_snap_internal.ensure_nway_master_site;

      

      PL/SQL procedure successfully completed.

ENSURE_ONE_WAY_MASTER_SITE

Undocumented dbms_snap_internal.ensure_one_way_master_site;

exec dbms_snap_internal.ensure_one_way_master_site;

      

      PL/SQL procedure successfully completed.

ENSURE_SNAPSHOT_SITE

Undocumented dbms_snap_internal.ensure_snapshot_site;

exec dbms_snap_internal.ensure_snapshot_site;

      

      PL/SQL procedure successfully completed.

GENERATE_REFRESH_OPERATIONS

Undocumented dbms_snap_internal.generate_refresh_operations( snap_owner IN VARCHAR2, snap_name IN VARCHAR2, which_ops IN BINARY_INTEGER);

TBD

GET_BACK_END_DB

Undocumented dbms_snap_internal.get_back_end_db RETURN VARCHAR2;

SELECT dbms_snap_internal.get_back_end_db

      FROM dual;

      

      GET_BACK_END_DB

      ----------------

GET_INDEX_DDL

Returns the DDL for an index by calling dbms_metadata.get_ddl dbms_snap_internal.get_index_ddl( index_name IN VARCHAR2, index_owner IN VARCHAR2, ddl IN OUT CLOB);

CREATE TABLE dsnap(

      testcol NUMBER);

      

      CREATE INDEX ix_dsnap

      ON dsnap(testcol);

      

      DECLARE

       outVal CLOB;

      BEGIN

        dbms_snap_internal.get_index_ddl('IX_DSNAP', USER, outVal);

        dbms_output.put_line(outVal);

      END;

      /

      CREATE INDEX "SYS"."IX_DSNAP" ON "SYS"."DSNAP" ("TESTCOL")

      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS

      2147483645

      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

      TABLESPACE "SYSTEM"

      

      PL/SQL procedure successfully completed.

GET_LOGGING_STATUS

Returns TRUE if the database is in archivelog mode, else FALSE dbms_snap_internal.get_logging_status RETURN BOOLEAN;

BEGIN

        IF dbms_snap_internal.get_logging_status THEN

          dbms_output.put_line('Archive Logging');

        ELSE

          dbms_output.put_line('No Archive Logging');

        END IF;

      END;

      /

      Archive Logging

      

      PL/SQL procedure successfully completed.

GET_TABLE_ID

Undocumented dbms_snap_internal.get_table_id( tabowner IN VARCHAR2, tabname IN VARCHAR2) RETURN NUMBER;

SELECT dbms_snap_internal.get_table_id(USER, 'DSNAP')

      FROM dual;

      

      DBMS_SNAP_INTERNAL.GET_TABLE_ID(USER,'DSNAP')

      ---------------------------------------------

                                            167287

GET_TABLE_XLOCK

Undocumented dbms_snap_internal.get_table_xlock( sql_cursor IN NUMBER, tabowner IN VARCHAR2, tabname IN VARCHAR2);

TBD

IAS_CREATE_MV_DDL

Undocumented dbms_snap_internal.ias_create_mv_ddl( downer IN VARCHAR2, dsnap IN VARCHAR2, rowner IN VARCHAR2, rsnap IN VARCHAR2, powner IN VARCHAR2, psnap IN VARCHAR2, ddl_txt1 IN OUT CLOB, ddl_txt2 IN OUT CLOB);

TBD

IAS_XLOCK_EVENT

Undocumented dbms_snap_internal.ias_xlock_event RETURN BOOLEAN;

BEGIN

        IF dbms_snap_internal.ias_xlock_event THEN

          dbms_output.put_line('XLock Event');

        ELSE

          dbms_output.put_line('Not An XLock Event');

        END IF;

      END;

      /

      Not An XLock Event

      

      PL/SQL procedure successfully completed.

ID_TO_TIMESTAMP

Undocumented dbms_snap_internal.id_to_timestamp(snapid IN BINARY_INTEGER) RETURN DATE;

TBD

INSTANTIATE_OFFLINE

Undocumented dbms_snap_internal.instantiate_offline( template_output_id IN BINARY_INTEGER, ddl_text IN CLOB, mowner OUT VARCHAR2, master OUT VARCHAR2, sowner OUT VARCHAR2, snapshot OUT VARCHAR2, pk_cols OUT sys.dbms_snapshot_utl.idens, updatable OUT BINARY_INTEGER, online IN BINARY_INTEGER);

TBD

IS_IAS

Undocumented dbms_snap_internal.is_ias RETURN BOOLEAN;

BEGIN

        IF dbms_snap_internal.is_ias THEN

          dbms_output.put_line('IAS');

        ELSE

          dbms_output.put_line('Not IAS');

        END IF;

      END;

      /

      Not IAS

      

      PL/SQL procedure successfully completed.

I_AM_A_REFRESH

Undocumented dbms_snap_internal.i_am_a_refresh RETURN BOOLEAN;

BEGIN

        IF dbms_snap_internal.i_am_a_refresh THEN

          dbms_output.put_line('I Am A Refresh');

        ELSE

          dbms_output.put_line('I Am Not A Refresh');

        END IF;

      END;

      /

      I Am Not A Refresh

      

      PL/SQL procedure successfully completed.

LOCAL_NAME_RESOLVE

Undocumented dbms_snap_internal.local_name_resolve( user IN VARCHAR2, name IN VARCHAR2, context IN BINARY_INTEGER, schema OUT VARCHAR2, part1 OUT VARCHAR2, part2 OUT VARCHAR2, dblink OUT VARCHAR2, part1_type OUT BINARY_INTEGER, object_number OUT BINARY_INTEGER);

TBD

MODIFY_RO_ATTRIBUTE

Undocumented dbms_snap_internal.modify_ro_attribute( owner IN VARCHAR2, tablename IN VARCHAR2, proc_mod IN BINARY_INTEGER);

TBD

PROHIBIT_UPDATEABLE_MVS

Undocumented dbms_snap_internal.prohibit_updateable_mvs;

exec dbms_snap_internal.prohibit_updateable_mvs;

      

      PL/SQL procedure successfully completed.

REMOVE_BACK_END_DB

Undocumented dbms_snap_internal.remove_back_end_db;

exec dbms_snap_internal.remove_back_end_db;

      

      PL/SQL procedure successfully completed.

SET_BACK_END_DB

Undocumented dbms_snap_internal.set_back_end_db(dblink IN VARCHAR2);

TBD

SET_I_AM_AN_ACLMV

Undocumented dbms_snap_internal.set_i_am_an_aclmv(value IN BINARY_INTEGER);

TBD

SET_I_AM_AN_ORACLE_REFRESH

Undocumented dbms_snap_internal.set_i_am_an_oracle_refresh(value IN BOOLEAN);

exec dbms_snap_internal.set_i_am_an_oracle_refresh(FALSE);

      

      PL/SQL procedure successfully completed.

SET_LOGGING

Undocumented dbms_snap_internal.set_logging(logging_on IN BOOLEAN);

exec dbms_snap_internal.set_logging(TRUE);

      

      PL/SQL procedure successfully completed.

SET_REDIRECTION

Undocumented dbms_snap_internal.set_redirection;

exec dbms_snap_internal.set_redirection;

      

      PL/SQL procedure successfully completed.

SYNC_BACK_END_DB

Undocumented dbms_snap_internal.sync_back_end_db;

exec dbms_snap_internal.sync_back_end_db;

      

      PL/SQL procedure successfully completed.

SYNC_UP_LOG

Undocumented dbms_snap_internal.sync_up_log( canon_sname IN VARCHAR2, canon_oname IN VARCHAR2);

TBD

SYNC_UP_UPD

Undocumented dbms_snap_internal.sync_up_upd( canon_sname IN VARCHAR2, canon_oname IN VARCHAR2);

TBD

TEST_REDIRECTION

Undocumented dbms_snap_internal.test_redirection RETURN BOOLEAN;

BEGIN

        IF dbms_snap_internal.test_redirection THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      F

      

      PL/SQL procedure successfully completed.

TIMESTAMP_TO_ID

Undocumented dbms_snap_internal.timestamp_to_id(snapshot IN DATE) RETURN BINARY_INTEGER;

SELECT dbms_snap_internal.timestamp_to_id(SYSDATE)

      FROM dual;

      

      DBMS_SNAP_INTERNAL.TIMESTAMP_TO_ID(SYSDATE)

      -------------------------------------------

                                               0

TRANSFORM_DDL

Undocumented dbms_snap_internal.transform_ddl( obj_type IN VARCHAR2, obj_owner IN VARCHAR2, obj_name IN VARCHAR2, new_objname IN VARCHAR2, obj_name2 IN VARCHAR2, new_objname2 IN VARCHAR2, par_name IN VARCHAR2, iotredefopt IN BOOLEAN, pk_name IN VARCHAR2, new_pkname IN VARCHAR2, ddl IN OUT CLOB);

TBD

UPDATEABLE_MVS_ALLOWED

Undocumented dbms_snap_internal.updateable_mvs_allowed(

TBD

VALIDATE_SQL

Undocumented dbms_snap_internal.validate_sql( sql_text IN CLOB, sql_type IN BINARY_INTEGER, user_name IN VARCHAR2) RETURN BINARY_INTEGER;

DECLARE

       retVal BINARY_INTEGER;

       sqlStr dbms_id := 'SELECT dummy FROM dual';

      BEGIN

        retVal := dbms_snap_internal.validate_sql(sqlStr, 1, USER);

        dbms_output.put_line(retVal);

      END;

      /

      0

      

      PL/SQL procedure successfully completed.

VEC_COLS_IN_TABLE

Undocumented dbms_snap_internal.vec_cols_in_table( table_owner IN VARCHAR2, mas_table IN VARCHAR2, log IN VARCHAR2, vec IN RAW, stime IN DATE) RETURN BINARY_INTEGER;

TBD