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
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