Oracle DBMS_SNAP_INTERNAL
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
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

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_MVIEW
DBMS_SNAPSHOT
DBMS_SNAPSHOT_COMMON
DBMS_SNAPSHOT_KKXRCA
DBMS_SNAPSHOT_UTL
DBMS_SNAP_INTERNAL
DBMS_SNAP_REPAPI
DBMS_XRWMV
What's New In 19c
What's New In 20c-21c

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