Oracle DBMS_GSM_UTILITY
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 supporting the Global Services Manager
AUTHID DEFINER
Constants
Name Data Type Value
 AQ Parameter Lengths
max_param_len NUMBER 4000
 Catalog Requests
chunk_move NUMBER 1
chunk_copy NUMBER 2
chunk_drop NUMBER 3
chunk_move_atomic NUMBER 4
chunk_move_atm_int NUMBER 5
 Catalog Version
catvers_lookup tvers_lookup_t tvers_lookup_t(
tvers_rec('12.1.0.1', 1),
tvers_rec('12.1.0.2', 2),
tvers_rec('12.2.0.0', 3),
tvers_rec('12.2.0.1', 3),
tvers_rec('18.0.0.0', 4),
tvers_rec('19.0.0.0', 5);
tvers_rec('20.0.0.0', 6),
tvers_rec('21.0.0.0', 7));
 Database Deployment Status
not_deployed NUMBER 0
deploy_requested NUMBER 1
replication_configured (DataGuard) NUMBER 2
chunks_deployed NUMBER 3
ddl_deployed NUMBER 4
ogg_rep_configured NUMBER 5
 Database Version
dbvers_lookup tvers_lookup_t tvers_lookup_t(
tvers_rec('12.1.0.1', 1),
tvers_rec('12.1.0.2', 2),
tvers_rec('12.2.0.0', 3),
tvers_rec('12.2.0.1', 3),
tvers_rec('18.0.0.0', 4),
tvers_rec('20.0.0.0', 6),
tvers_rec('21.0.0.0', 7));
 DDL Operation Types
ddl_alter CHAR 'A'
ddl_create CHAR 'C'
ddl_drop CHAR 'D'
ddl_grant CHAR 'G'
ddl_split CHAR 'L'
new_shardspace CHAR 'P'
ddl_revoke CHAR 'R'
sync_signal CHAR 'S'
ddl_truncate CHAR 'T'
user_sql CHAR 'U'
ddl_password CHAR 'W'
ddl_ignore_err CHAR 'Z'
 Default Names
default_cloud_name VARCHAR2(10) 'oradbcloud'
 Failure Codes
source_failed -- cannot cleanup source NUMBER 97
target_failed -- chunk not live on target NUMBER 98
chunk_move_failed -- actual move failed NUMBER 99
 GDSCTL Version
gdsctlvers_lookup tvers_lookup_t tvers_lookup_t(
tvers_rec('12.1.0.1', 1),
tvers_rec('12.1.0.2', 2),
tvers_rec('12.2.0.0', 3),
tvers_rec('12.2.0.1', 3),
tvers_rec('18.0.0.0', 4),
tvers_rec('20.0.0.0', 6),
tvers_rec('21.0.0.0', 7));
 GDSCTL Catalog Version
gdsctl_catalog_lookup vers_lookup_t vers_lookup_rec( 1, vers_list(1,2,3,4,5)),
vers_lookup_rec( 2, vers_list(2,3,4,5)),
vers_lookup_rec( 3, vers_list(3,4,5)),
tvers_lookup_rec(4, vers_list(4,5)),
tvers_lookup_rec(5, vers_list(5));
 GSM Version
gsmvers_lookup tvers_lookup_t tvers_lookup_t(
tvers_rec('12.1.0.1', 1),
tvers_rec('12.1.0.2', 2),
tvers_rec('12.2.0.0', 3),
tvers_rec('12.2.0.1', 3),
tvers_rec('18.0.0.0', 4),
tvers_rec('19.0.0.0', 5);
tvers_rec('20.0.0.0', 6),
tvers_rec('21.0.0.0', 7));
 GSM Catalog Version
gsm_catalog_lookup vers_lookup_t vers_lookup_t(
vers_lookup_rec( 1, vers_list(1,2,3)),
vers_lookup_rec( 2, vers_list(2,3)),
vers_lookup_rec( 3, vers_list(3)));
 Maximum Length of AQ Parameters
max_param_len NUMBER 4000
 Maximum Number of Table Families
default_cloud_name NUMBER 20
 Reference Table Flags
is_ref_table CHAR D
is_root_table CHAR R
 Runtime Database Status
db_down NUMBER to_number('00000000','xxxxxxxx')
db_up NUMBER to_number('00000001','xxxxxxxx')
is_primary NUMBER to_number('00000002','xxxxxxxx')
failed_source
-- move failed on source db
NUMBER to_number('00000004','xxxxxxxx')
failed_target -- move failed on target db NUMBER to_number('00000008','xxxxxxxx')
green_field
-- deploy requested on green filed DB
NUMBER to_number('00000010','xxxxxxxx')
waiting_for_sync
-- waiting for GSM to run sync
NUMBER to_number('00000020','xxxxxxxx')
failed_ogg
-- move failed on OGG operation
NUMBER to_number('00000040','xxxxxxxx')
 States of Catalog Request for Atomic Move
move_st_init NUMBER 10
move_st_dcpy NUMBER 11
move_st_mfnt NUMBER 12
move_st_dcfl NUMBER 13
move_st_mvcn NUMBER 14
move_st_srrl NUMBER 15
move_st_srfl NUMBER 16
move_st_trfl NUMBER 17
move_st_term NUMBER 18
move_st_ftrm NUMBER 19
 Cloud Deploy State
no_deploy -- no deploy running NUMBER 0
in_deploy -- deploy in progress NUMBER 1
deploy_chunks
-- request chunk deployment
NUMBER 2
 Catalog Requests Status
req_pending; -- waiting to start NUMBER 0
in_gsm1; -- sent to GSM NUMBER 1
chunk_on_target -- GSM moved to target NUMBER 3
target_done -- target confirmed move NUMBER 4
move_suspended NUMBER 5
 Shard Group Status
sg_undeployed NUMBER 0
sg_deployed NUMBER 1
 Shard Space Status
ss_undeployed NUMBER 0
ss_chunks (request sent to deploy chunks) NUMBER 1
ss_deployed NUMBER 2
 Shard Status
gws_undeployed NUMBER 0
gws_deployed NUMBER 1
 Actions For updateMoveChunk
restart_move NUMBER 0
suspend_move NUMBER 1
remove_chunks NUMBER 2
 Database Service Name Lengths
max_service_name_len NUMBER 64
max_net_service_name_len NUMBER 250
 Instance Name Lengths
max_inst_name_len NUMBER 16
 Maximum Number of VNCRs
max_vncr_number NUMBER 1000
 Maximum Number of Services
max_services NUMBER 1000
 Maximum Number of Database Pools
max_dbpools NUMBER 200
 Database Deployment States
not_depl (not deployed) NUMBER 0
gds_setup (GSM installed DB will register) NUMBER 1
db_depl (deployment complete) NUMBER 2
 _gws_shard_shard Values
not_sharded NUMBER 0
sh_system (system-managed sharding) NUMBER 1
sh_userdef (user-defined sharding) NUMBER 2
sh_composite (composite sharding) NUMBER 3
 "adminId" Values for generateChangeLogEntry()
cloud_admin NUMBER 1
pool_admin NUMBER 2
ddl_admin (generated by DDL in SQL) NUMBER 3
 Number of Targets
all_databases NUMBER 0
in_payload (usually a single target) NUMBER 1
 Special gsm_requests Sequence ID For Pending GDSCTL Warnings
pendingWarning NUMBER -1
 changeID Values for generateChangeLogEntry()
add_gsm NUMBER 1
modify_gsm NUMBER 2
drop_gsm NUMBER 3
start_gsm NUMBER 4
stop_gsm NUMBER 5
add_region NUMBER 10
modify_region NUMBER 11
drop_region NUMBER 12
add_database_pool NUMBER 20
modify_database_pool NUMBER 21
drop_database_pool NUMBER 22
replace_database NUMBER 23
 ChangeID Values for generateChangeLogEntry()
add_database NUMBER 30
modify_database NUMBER 31
drop_database NUMBER 32
start_database NUMBER 33
stop_database NUMBER 34
drop_db_phys NUMBER 35
add_broker_config NUMBER 36
mod_db_status NUMBER 37
add_db_done NUMBER 38
sync_database NUMBER 39
mod_db_runtime NUMBER 40
modify_dg_db_property NUMBER 41
modify_dg_bk_property NUMBER 42
deploy_primary NUMBER 43
deploy_standby NUMBER 44
modify_broker_config NUMBER 45
add_broker NUMBER 46
remove_broker_config NUMBER 47
add_service NUMBER 50
modify_service NUMBER 51
drop_service NUMBER 52
relocate_service NUMBER 53
start_service NUMBER 54
stop_service NUMBER 55
enable_service NUMBER 56
disable_service NUMBER 57
add_service_to_dbs NUMBER 58
move_service_to_db NUMBER 59
make_dbs_preferred NUMBER 60
modify_service_config NUMBER 61
modify_service_on_db NUMBER 62
update_service_state NUMBER 63
add_vncr NUMBER 70
remove_vncr NUMBER 71
drop_service_ph NUMBER 72
drop_broker_config NUMBER 73
drop_bc_phys NUMBER 74
sync_broker_config NUMBER 75
mod_db_vers NUMBER 76
 ChangeID Special Number for Warning Message for generateChangeLogEntry()
plsql_warning NUMBER 77
 ChangeID DDL Request Message for generateChangeLogEntry()
ddl_request NUMBER 80
ddl_ignore NUMBER 81
ddl_recover NUMBER 82
add_shardgroup NUMBER 83
x`remove_shardgroup NUMBER 84
finalize_deploy NUMBER 85
deploy_brokers NUMBER 86
move_chunk NUMBER 87
move_complete NUMBER 88
move_abort NUMBER 89
split_chunk NUMBER 90
 GoldenGate Message for generateChangeLogEntry()
ogg_rep_setup NUMBER 91
gen_multi_target NUMBER 92
ogg_multi_target NUMBER 93
 Start Observer Message for generateChangeLogEntry()
start_observer NUMBER 94
 DDL Refetch Due To Password Change for generateChangeLogEntry()
ddl_refetch NUMBER 95
 DDL Offset New position of the start of DDL sequence
ddl_offset NUMBER 96
 Catalog Rollback Identifiers for generateChangeLogEntry()
rb_drop_service NUMBER 152
rb_modify_service_on_db NUMBER 162
 GenerateChangeLogEntry Constants
updateFalse NUMBER 0
updateTrue NUMBER 1
 Release Lock Constants
releaseLockCommit NUMBER 1
releaseLockRollback NUMBER 2
 Keep in sync with dbmsggsharding.sqlLock Constants
chunk_op_step3 NUMBER 1003
chunk_op_step4 NUMBER 1004
chunk_op_step5 NUMBER 1005
 Operations for AQ92 (gen_multi_target)
exec_stmt NUMBER 1
exec_syncschema NUMBER 2
 Operations for AQ97 (gen_multi_target_fix)
exec_stmt_fix NUMBER 1
exec_deletebc NUMBER 2
msg_message NUMBER 0
msg_start NUMBER 1
msg_end NUMBER 2
msg_warning NUMBER 3
msg_info NUMBER 4
Dependencies
ALL_USERS DBMS_SESSION OBJ$
CHUNKS DBMS_SQL PARTITION_SET
CHUNK_LOC DBMS_SYS_ERROR PLITBLM
CLOUD DBMS_SYS_SQL REGION
DATABASE DDLID$ SHARDKEY_COLUMNS
DATABASE_POOL DDL_REQUESTS SHARD_GROUP
DBA_CONS_COLUMNS DDL_REQUESTS_PWD SHARD_SPACE
DBA_PART_TABLES DUAL SHARD_TS
DBA_SYS_PRIVS EXECASUSER SHA_DATABASES
DBA_TABLESPACES EXECUTEDDL TABLESPACE_SET
DBA_TAB_COLUMNS EXEC_SHARD_PLSQL TABLE_FAMILY
DBA_USERS GDSCTL_MESSAGES TS_SET_TABLE
DBMS_APPLICATION_INFO GLOBAL_TABLE TVERS_LOOKUP_T
DBMS_AQ GSM TVERS_REC
DBMS_ASSERT GSM_CHANGE_MESSAGE T_SHDCOL_ROW
DBMS_GSM_CLOUDADMIN GSM_REQUESTS T_SHDCOL_TAB
DBMS_GSM_COMMON GV$SESSION UTL_RAW
DBMS_GSM_DBADMIN GV_$LOCK V$INSTANCE
DBMS_GSM_FIXED GV_$SESSION VERS_LIST
DBMS_GSM_NOPRIV INSTANCE_LIST VERS_LOOKUP_REC
DBMS_GSM_POOLADMIN MESSAGE_PARAM_LIST VERS_LOOKUP_T
DBMS_GSM_UTILITY_LIB MSG_SEQUENCE V_$PARAMETER2
DBMS_LOCK_ALLOCATED NUMBER_LIST V_$VERSION
Documented No
Exceptions

Review the source script in /rdbms/admin for the full listing
Error Code Reason
ORA-02667 Root table required to perform this operation was not found
ORA-03739 The specified database is not configured to be a catalog
ORA-44900 Identifier <name_string> is too long.
ORA-45558 There are no database pools in this catalog
First Available 12.1
Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);
Security Model Owned by GSMADMIN_INTERNAL with EXECUTE granted to DATAPUMP_IMP_FULL_DATABASE, GGSYS, GSMADMIN_ROLE, GSMROOTUSER_ROLE. GSMUSER_ROLE, GSM_POOLADMIN_ROLE
Source {ORACLE_HOME}/rdbms/admin/dbmsgwmut.sql
Subprograms
 
ALT_CSHDBLINK
Undocumented dbms_gsm_utility.alt_Cshdblink (
user_name IN VARCHAR2,
passwd    IN VARCHAR2,
isCat     IN BOOLEAN);
TBD
 
CLEANUPDDL
Undocumented dbms_gsm_utility.cleanupDDL;
exec gsmadmin_internal.dbms_gsm_utility.cleanupDDL;

PL/SQL procedure successfully completed.
 
COMPATIBLEVERSION
Determines if provided versions are compatible with each other dbms_gsm_utility.compatibleVersion(
gdsctl_version  IN NUMBER DEFAULT NULL,
catalog_version IN NUMBER DEFAULT NULL,
gsm_version     IN NUMBER DEFAULT NULL,
db_version      IN NUMBER DEFAULT NULL)
RETURN BOOLEAN;
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.compatibleVersion THEN
    dbms_output.put_line('Compatible');
  ELSE
    dbms_output.put_line('Not Compatible');
  END IF;
END;
/
Compatible

PL/SQL procedure successfully completed.
 
CRT_CSHDBLINK
Undocumented dbms_gsm_utility.crt_cshDBLink(
user_name IN VARCHAR2,
passwd    IN VARCHAR2,
conn_str  IN VARCHAR2,
iscat     IN BOOLEAN);
TBD
 
DATABASEPOOLEXISTS
Checks if the specified database pool exists in the cloud catalog dbms_gsm_utility.databasePoolExists(database_pool_name IN VARCHAR2)
RETURN BOOLEAN;
SQL> BEGIN
  2    IF gsmadmin_internal.dbms_gsm_utility.databasePoolExists('UWDBPOOL') THEN
  3      dbms_output.put_line('F');
  4    ELSE
  5      dbms_output.put_line('F');
  6    END IF;
  7  END;
  8  /
F

PL/SQL procedure successfully completed
.
 
DBVERSREVLOOKUP
Returns the database version string given version number (reverse lookup) dbms_gsm_utility.DBVersRevLookup(dbvers IN NUMBER)
RETURN VARCHAR2;
SELECT gsmadmin_internal.dbms_gsm_utility.DBVersRevLookup(21.1)
FROM dual;

GSMADMIN_INTERNAL.DBMS_GSM_UTILITY.DBVERSREVLOOKUP(21.1)
---------------------------------------------------------
-1
 
DROP_TABLE
Undocumented dbms_gsm_utility.drop_table(table_id IN NUMBER);
TBD
 
DROP_TABLES_IN_TSET
Undocumented dbms_gsm_utility.drop_tables_in_tset(
ts_name     IN VARCHAR2,
user_id     IN NUMBER,
ddl_enabled IN BOOLEAN,
prop_ddl    IN BOOLEAN);
TBD
 
DRP_CREATED_TS
Undocumented dbms_gsm_utility.drp_created_ts(name IN VARCHAR2);
TBD
 
DRP_CSHDBLINK
Undocumented dbms_gsm_utility.drp_Cshdblink(
user_name IN VARCHAR2,
isCat     IN BOOLEAN);
TBD
 
FEDINSERTCATALOGDDL (new 21c)
Undocumented dbms_gsm_utility.fedInsertCatalogDDL(
ddl_text       IN  CLOB,
schema_name    IN  VARCHAR2 DEFAULT NULL,
object_name    IN  VARCHAR2 DEFAULT NULL,
operation_type IN  CHAR,
object_type    IN  VARCHAR2,
ddl_num        OUT NUMBER);
TBD
 
FEDUPDHETCOL (new 21c)
Undocumented dbms_gsm_utility.fedUpdHetCol(
owner         IN VARCHAR2,
table_name    IN VARCHAR2,
column_name   IN VARCHAR2,
heterogeneous IN BOOLEAN);
exec gsmadmin_internal.dbms_gsm_utility.fedUpdHetCol(USER, 'T', 'TESETCOL', TRUE);

PL/SQL procedure successfully completed.
 
GDSCTLVERSLOOKUP
Returns the numeric version of GDSCTL given version string dbms_gsm_utility.GDSCTLVersLookup(gdsctl_vers IN VARCHAR2) RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.GDSCTLVersLookup('3')
FROM dual;

GSMADMIN_INTERNAL.DBMS_GSM_UTILITY.GDSCTLVERSLOOKUP('3')
--------------------------------------------------------
                                                      -1
 
GENERATECHANGELOGENTRY
Generates a description of a change and puts it into the change log queue

Overload 1
dbms_gsm_utility.generateChangeLogEntry(
adminId            IN NUMBER,
changeId           IN NUMBER,
target             IN VARCHAR2,
poolName           IN VARCHAR2      DEFAULT NULL,
params             IN VARCHAR2      DEFAULT NULL,
updateRequestTable IN NUMBER        DEFAULT updateTrue,
old_instances      IN instance_list DEFAULT NULL,
ddl_num            IN NUMBER        DEFAULT NULL,
databases          IN number_list   DEFAULT NULL)
ACCESSIBLE BY (PACKAGE dbms_gsm_dbadmin,
  PACKAGE dbms_gsm_pooladmin,
  PACKAGE dbms_gsm_common,
  PACKAGE dbms_gsm_cloudadmin,
  PACKAGE ggsys.ggsharding);
TBD
Overload 2 dbms_gsm_utility.generateChangeLogEntry(
adminId            IN  NUMBER,
changeId           IN  NUMBER,
target             IN  VARCHAR2,
poolName           IN  VARCHAR2      DEFAULT NULL,
params             IN  VARCHAR2      DEFAULT NULL,
updateRequestTable IN  NUMBER        DEFAULT updateTrue,
old_instances      IN  instance_list DEFAULT NULL,
ddl_num            IN  NUMBER        DEFAULT NULL,
databases          IN  number_list   DEFAULT NULL,
parent_id          IN  NUMBER        DEFAULT NULL,
seq_id             OUT NUMBER)
ACCESSIBLE BY (PACKAGE dbms_gsm_dbadmin,
  PACKAGE dbms_gsm_pooladmin,
  PACKAGE dbms_gsm_common,
  PACKAGE dbms_gsm_cloudadmin,
  PACKAGE ggsys.ggsharding);
TBD
 
GETCATALOGLOCK
Gets the catalog lock prior to making a change to the cloud catalog dbms_gsm_utility.getCatalogLock(currentChangeSeq OUT NUMBER);
DECLARE
 outVal NUMBER;
BEGIN
  gsmadmin_internal.dbms_gsm_utility.getCatalogLock(outVal);
  dbms_output.put_line(TO_CHAR(outVal));
END;
/
DECLARE
*
ERROR at line 1:
ORA-03739: The specified database is not configured to be a catalog.
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 266
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 170
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 261
ORA-06512: at line 4
 
GETCATALOGLOCKPRVT
Gets the catalog lock prior to making a change to the cloud catalog dbms_gsm_utility.getCatalogLockPrvt(
currentChangeSeq OUT NUMBER,
privs            IN  NUMBER,
gdsctl_version   IN  VARCHAR2 DEFAULT NULL,
gsm_version      IN  VARCHAR2 DEFAULT NULL,
gsm_name         IN  VARCHAR2 DEFAULT NULL,
catalog_vers     OUT NUMBER,
update_mode      IN  NUMBER);
TBD
 
GETCATALOGVERSION
Returns the version of this catalog dbms_gsm_utility.getCatalogVersion RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.getCatalogVersion
FROM dual;

GETCATALOGVERSION
-----------------
                3
 
GETCATINFO
Undocumented dbms_gsm_utility.getCatInfo(
html_port         OUT NUMBER,
registration_pass OUT VARCHAR2,
cat_host          OUT VARCHAR2);
DECLARE
 portVal NUMBER;
 regVal  dbms_id;
 hostVal dbms_id;
BEGIN
  gsmadmin_internal.dbms_gsm_utility.getCatInfo(portVal, regVal, hostVal);
  dbms_output.put_line(portVal);
  dbms_output.put_line(portVal);
  dbms_output.put_line(portVal);
END;
/
8080
8080
8080

PL/SQL procedure successfully completed.
 
GETCHUNKID
Return chunk id for the shard keys provided if the chunk exists at the current shard dbms_gsm_utility.getChunkID(
tablefamily_id IN BINARY_INTEGER,
keys           IN VARARG)
RETURN NUMBER;
TBD
 
GETCHUNKUNIQUEID
Return chunk unique id (not chunk id) for the supershard and shard keys provided if the chunk exists dbms_gsm_utility.getChunkUniqueID((keys IN VARARG) RETURN NUMBER;
TBD
 
GETCHUNKUNIQUEIDBYTEXT
Return chunk unique id (not chunk number) for the given shardspace with keys provided if the chunk exists dbms_gsm_utility.getChunkUniqueIDbyText(
sharding_key  IN VARCHAR2,
shardspace_id IN NUMBER DEFAULT NULL,
b64flag       IN NUMBER DEFAULT 0)
RETURN NUMBER;
TBD
 
GETDBVERSION
Returns the version of this cloud database dbms_gsm_utility.getDBVersion RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.getDBVersion
FROM dual;

GETDBVERSION
------------
           3
 
GETFIELDSIZE
Used internally by PL/SQL to verify the size of input strings and returns the size of a database field

The demo at right is from 19.3
This function returns null in 21.1
dbms_gsm_utility.getFieldSize(
tab_name IN VARCHAR2,
col_name IN VARCHAR2)
RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.getFieldSize('VIEW$', 'AUDIT$')
FROM dual;

GSMADMIN_INTERNAL.DBMS_GSM_UTILITY.GETFIELDSIZE('VIEW$','AUDIT$')
-----------------------------------------------------------------
                                                               38
 
GETGDSONSCONFIG
Obtains the GDS ONS config string based on region. This is used by the mid-tier routing Java library to get ONS subscription information when connected to the catalog local service dbms_gsm_utility.getGDSONSConfig(region_name IN VARCHAR2 default NULL)
RETURN VARCHAR2;
SELECT gsmadmin_internal.dbms_gsm_utility.getGDSONSConfig
FROM dual;

GETGDSONSCONFIG
----------------
 
 
GETREPTYPE
Returns the replication type dbms_gsm_utility.getRepType(dbname IN VARCHAR2) RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.getRepType('ORABASE2')
FROM dual;

GSMADMIN_INTERNAL.DBMS_GSM_UTILITY.GETREPTYPE('ORABASE')
--------------------------------------------------------
 
 
GETSESSIONKEYRAW
Returns a binary (KDK) representation of the current session key dbms_gsm_utility.getSessionKeyRaw(
colidx IN NUMBER,
klevel IN NUMBER DEFAULT 0)
RETURN RAW;
SELECT gsmadmin_internal.dbms_gsm_utility.getSessionKeyRaw(42, 0)
FROM dual;
SELECT gsmadmin_internal.dbms_gsm_utility.getSessionKeyRaw(42, 0)
*
ERROR at line 1:
ORA-02667: Root table required to perform this operation was not found.
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 2619
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 2637
ORA-06512: at line 1
 
GETSESSIONKEYTEXT
Returns a human readable representation (not reversible) of the current session key dbms_gsm_utility.getSessionKeyTexT(klevel IN NUMBER DEFAULT 0)
RETURN VARCHAR2;
SELECT gsmadmin_internal.dbms_gsm_utility.getSessionKeyText
FROM dual;
SELECT gsmadmin_internal.dbms_gsm_utility.getSessionKeyText
*
ERROR at line 1:
ORA-02667: Root table required to perform this operation was not found.
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 2969
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 2982
ORA-06512: at line 1
 
GETSHARDCOL
Undocumented dbms_gsm_utility.getShardCol(object_id IN NUMBER)
RETURN gsmadmin_internal.t_shdcol_tab;
TBD
 
GETSHARDSPACEIDBYTEXT
Return shardspace id for the supershard key provided if the shardspace exists dbms_gsm_utility.getShardSpaceIDbyText(
supersharding_key IN VARCHAR2,
b64flag           IN NUMBER DEFAULT 0)
RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.getShardSpaceIDbyText('SecretKey', 0)
FROM dual;
SELECT gsmadmin_internal.dbms_gsm_utility.getShardSpaceIDbyText('SecretKey', 0)
*
ERROR at line 1:
ORA-02667: Root table required to perform this operation was not found.
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 2573
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 2582
ORA-06512: at line 1
 
GETVERSIONSTRING
Returns the current RDBMS version string dbms_gsm_utility.getVersionString RETURN VARCHAR2;
SELECT gsmadmin_internal.dbms_gsm_utility.getVersionString
FROM dual;

GETVERSIONSTRING
-----------------
21.0.0.0.0
 
GSMVERSLOOKUP
Returns the numeric version of the GSM given version string

Returns -1 with any value other than that shown at right
dbms_gsm_utility.gsmVersLookup(gsm_vers IN VARCHAR2)
RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.gsmVersLookup('12.2.0.1')
FROM dual;

GSMADMIN_INTERNAL.DBMS_GSM_UTILITY.GSMVERSLOOKUP('12.2.0.1')
------------------------------------------------------------
                                                           3

SELECT gsmadmin_internal.dbms_gsm_utility.gsmVersLookup('21.0.0.0')
FROM dual;

GSMADMIN_INTERNAL.DBMS_GSM_UTILITY.GSMVERSLOOKUP('21.0.0.0')
------------------------------------------------------------
                                                           7
 
GSM_REQUESTS_UPDATE
Informs all subscribers of an updated gsm_requests tables dbms_gsm_utility.gsm_requests_update;
exec gsmadmin_internal.dbms_gsm_utility.gsm_requests_update;

PL/SQL procedure successfully completed.
 
ISFEDERATEDDB (new 21c)
Returns TRUE if the current database is federated dbms_gsm_utility.isFederatedDB RETURN BOOLEAN;
PRAGMA SUPPLEMENTAL_LOG_DATA(isFederatedDB, READ_ONLY);
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.isFederatedDB THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

PL/SQL procedure successfully completed.
 
ISGSMUP
Undocumented dbms_gsm_utility.isGSMUp RETURN BOOLEAN;
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.isGSMUp THEN
    dbms_output.put_line('GSM Is Up');
  ELSE
    dbms_output.put_line('GSM Is Down');
  END IF;
END;
/
GSM Is Down

PL/SQL procedure successfully completed.
 
ISGSMUPNUM (new 21c)
Wrapper over isGSMUP for usage in SQL statements dbms_gsm_utility.isGSMUpNum RETURN NUMBER;
PRAGMA SUPPLEMENTAL_LOG_DATA(isGSMUpNum, READ_ONLY);
SELECT gsmadmin_internal.dbms_gsm_utility.isGSMUpNum
FROM dual;

ISGSMUPNUM
----------
         0
 
ISLOCKEDBYMASTER
Determines if a master lock is already taken: Returns 1 if True, 0 if False dbms_gsm_utility.isLockedByMaster RETURN INTEGER
SELECT gsmadmin_internal.dbms_gsm_utility.isLockedByMaster
FROM dual;

ISLOCKEDBYMASTER
----------------
               0
 
ISSHARDEDCATALOG
Undocumented dbms_gsm_utility.isShardedCatalog(stype OUT NUMBER)
RETURN BOOLEAN;
DECLARE
 outVal NUMBER;
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.isShardedCatalog(outVal) THEN
    dbms_output.put_line('T');
    dbms_output.put_line(TO_CHAR(outVal));
  ELSE
    dbms_output.put_line('F');
    dbms_output.put_line(TO_CHAR(outVal));
  END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 309
ORA-06512: at line 4
 
MAXDBINSTANCES
Queries the database parameter setting for the maximum number of instances to reserve for a cloud database dbms_gsm_utility.maxDBInstances RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.maxDBInstances
FROM dual;

MAXDBINSTANCES
--------------
            10
 
NEW_DDL_REQUEST
Undocumented dbms_gsm_utility.new_ddl_request(
ddl_text       IN  CLOB,
orig_ddl_text  IN  CLOB,
schema_name    IN  VARCHAR2 DEFAULT NULL,
object_name    IN  VARCHAR2 DEFAULT NULL,
operation_type IN  VARCHAR2,
pwd_count      IN  NUMBER,
ddl_num        OUT NUMBER);
TBD
 
NEW_DDL_REQUEST_PWD
Undocumented dbms_gsm_utility.new_ddl_request_pwd(
e_pwd     IN VARCHAR2,
pwd_begin IN NUMBER,
ddl_num   IN NUMBER,
user_name IN VARCHAR2 DEFAULT NULL);
TBD
 
NEW_FAMILY
Undocumented dbms_gsm_utility.new_family(
family_name        IN VARCHAR2,
partition_set_type IN NUMBER,
shard_type         IN NUMBER);
TBD
 
NEW_KEYCOL
Undocumented dbms_gsm_utility.new_keycol(
family_name IN VARCHAR2,
column_name IN VARCHAR2,
klevel      IN NUMBER);
TBD
 
NEW_PARTITION_SET
Undocumented dbms_gsm_utility.new_partition_set(
set_name       IN VARCHAR2,
tsset_name     IN VARCHAR2,
family_name    IN VARCHAR2,
high_value_len IN NUMBER,
high_value     IN LONG,
bin_high_value IN BLOB,
low_value_len  IN NUMBER,
low_value      IN LONG,
bin_low_value  IN BLOB,
psorder        IN NUMBER DEFAULT NULL);
TBD
 
NEW_SHARD_TABLESPACE
Creates a new shard tablespace dbms_gsm_utility.new_shard_tablespace(
tablespace_name IN VARCHAR2,
chunk_NUMBER    IN NUMBER,
shardspace_name IN VARCHAR2 DEFAULT NULL);
TBD
 
NEW_TABLE
Undocumented dbms_gsm_utility.new_table(
table_name     IN VARCHAR2,
schema_name    IN VARCHAR2,
table_id       IN NUMBER,
parent_name    IN VARCHAR2 DEFAULT NULL,
ref_table_flag IN CHAR     DEFAULT NULL);
TBD
 
NEW_TABLESPACE_SET
Undocumented dbms_gsm_utility.new_tablespace_set(
set_name      IN VARCHAR2,
shardspace_id IN NUMBER,
partition_set IN VARCHAR2 DEFAULT NULL);
TBD
 
NEW_TS_SET_TABLE
Create a new table set table dbms_gsm_utility.new_ts_set_table(
ts_set_name IN VARCHAR2,
table_id    IN NUMBER,
parent_name IN VARCHAR2,
usage_flag  IN CHAR);
TBD
 
PREPAREDBPOOLNAME
Returns the database pool name to use on a dbms_gsm_* call when the database pool name in the routine call can be NULL dbms_gsm_utility.prepareDBPoolName(
input_name         IN  VARCHAR2,
database_pool_name OUT VARCHAR2,
shardgroup_name    IN  VARCHAR2 DEFAULT NULL,
shardspace_name    IN  VARCHAR2 DEFAULT NULL)
RETURN BOOLEAN;
DECLARE
 outVal VARCHAR2(128);
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.prepareDBPoolName('UWPool', outVal) THEN
    dbms_output.put_line('T: ' || outVal);
  ELSE
    dbms_output.put_line('F: ' || outVal);
  END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-45558: There are no database pools in this catalog.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 625
ORA-06512: at line 4
 
PREPARENAME
Verifies the length of a (service, GSM, etc) name and prepares it for use by the GSM package dbms_gsm_utility.prepareName(
in_name    IN  VARCHAR2,
out_name   OUT VARCHAR2,
max_length IN  NUMBER)
RETURN BOOLEAN;
DECLARE
 outVal VARCHAR2(128);
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.prepareName('SerVerSiNsTALLeD', outVal, 17) THEN
    dbms_output.put_line('T: ' || outVal);
  ELSE
    dbms_output.put_line('F: ' || outVal);
  END IF;
END;
/
T: serversinstalled

PL/SQL procedure successfully completed.
 
PREPAREREGIONNAME
Returns the region name to use on a dbms_gsm_* call when the region name in the routine call can be NULL dbms_gsm_utility.prepareRegionName(
input_name  IN  VARCHAR2,
region_name OUT VARCHAR2)
RETURN BOOLEAN;
DECLARE
 outVal VARCHAR2(128);
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.prepareRegionName('SerVerS', outVal) THEN
    dbms_output.put_line('T: ' || outVal);
  ELSE
    dbms_output.put_line('F: ' || outVal);
  END IF;
END;
/
T: servers  -- region names are always lower case

PL/SQL procedure successfully completed.
 
RAISE_GSM_ERROR (new 21c)
Causes a warning message to display on GDSCTL session. Use when error doesn't come directly from GDSCTL session but from another session generated by AQ dbms_gsm_utility.raise_gsm_error(
errmesg    IN VARCHAR2,
parent_req IN NUMBER DEFAULT NULL);
exec gsmadmin_internal.dbms_gsm_utility.raise_gsm_error('ORA-01400', 999);
BEGIN gsmadmin_internal.dbms_gsm_utility.raise_gsm_error('TestMsg', 999); END;
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("GSMADMIN_INTERNAL"."GSM_REQUESTS"."CHANGE_SEQ#")
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 2263
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 2244
ORA-06512: at line 1
 
RAISE_GSM_WARNING
During catalog processing only causes a warning message to display on GDSCTL session dbms_gsm_utility.raise_gsm_warning(
message_id IN NUMBER,
parms      IN message_param_list DEFAULT message_param_list());
TBD
 
REGIONEXISTS
Checks if the specified region exists in the cloud catalog dbms_gsm_utility.regionExists(
region_name IN  VARCHAR2,
region_num  OUT NUMBER)
RETURN BOOLEAN;
DECLARE
 outVal NUMBER;
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.regionExists('ZZYZX', outVal) THEN
    dbms_output.put_line('The region number is: ' || TO_CHAR(outVal));
  ELSE
    dbms_output.put_line('Region Not Found');
  END IF;
END;
/
Region Not Found

PL/SQL procedure successfully completed.
 
RELEASECATALOGLOCK
Releases the catalog lock and commits or rolls back the changes made under the lock dbms_gsm_utility.releaseCatalogLock(
action    IN  NUMBER DEFAULT releaseLockCommit,
changeSeq OUT NUMBER);
DECLARE
 outVal VARCHAR2(30);
BEGIN
  gsmadmin_internal.dbms_gsm_utility.releaseCatalogLock(changeSeq => outVal);
  dbms_output.put_line(TO_CHAR(outVal));
END;
/
DECLARE
*
ERROR at line 1:
ORA-03739: The specified database is not configured to be a catalog.
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 297
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 170
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 286
ORA-06512: at line 4
 
RELEASECATALOGLOCKPRVT
Releases the catalog lock and commits or rolls back the changes made under the lock dbms_gsm_utility.releaseCatalogLockPrvt(
action    IN  NUMBER DEFAULT releaseLockCommit,
changeSeq OUT NUMBER);
DECLARE
outVal VARCHAR2(30);
BEGIN
  gsmadmin_internal.dbms_gsm_utility.releaseCatalogLockPrvt(changeSeq => outVal);
  dbms_output.put_line(TO_CHAR(outVal));
END;
/
DECLARE
*
ERROR at line 1:
ORA-03739: The specified database is not configured to be a catalog.
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 3608
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 170
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 3595
ORA-06512: at line 4
 
REMOVESTALEREQUESTS
Removes stale entries from gsm_requests (stale is defined as 10 minutes) dbms_gsm_utility.removeStaleRequests;
exec gsmadmin_internal.dbms_gsm_utility.removeStaleRequests;
 
RESERVEINSTNUMS
Reserve reserve_count database numbers from cloud: Only useful for PL/SQL calls, current value should be known already otherwise this function has no good use. dbms_gsm_utility.reserveInstNums(reserve_count IN NUMBER);
exec gsmadmin_internal.dbms_gsm_utility.reserveInstNums(3);

PL/SQL procedure successfully completed.
 
RESERVENEXTDBNUM
Reserves the next available DB number for use, updates the cloud table, but does not commit dbms_gsm_utility.reserveNextDBNum(reserve_count IN NUMBER DEFAULT NULL)
RETURN NUMBER;
SQL> DECLARE
  2    retVal NUMBER;
  3  BEGIN
  4      retVal := gsmadmin_internal.dbms_gsm_utility.reserveNextDBNum(3);
  5    dbms_output.put_line(retVal);
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_UTILITY", line 1817
ORA-06512: at line 4
 
RSAENCODER
Encrypt input string to byte array using PKCS dbms_gsm_utility.rsaEncoder(
input   IN  VARCHAR2,
keybyte IN  RAW,
output  OUT RAW);
DECLARE
 kbyte  RAW(32767) := utl_raw.cast_to_raw('ABC');
 outVal RAW(32767);
BEGIN
  gsmadmin_internal.dbms_gsm_utility.rsaEncoder('SuperSecret', kbyte, outVal);
  dbms_output.put_line(TO_CHAR(outVal));
END;
/

PL/SQL procedure successfully completed.
 
SEND_GDSCTL_MSG
Causes a message to display on GDSCTL session by adding a new record to gdsctl_messages dbms_gsm_utility.send_GDSCtl_msg(
message      IN VARCHAR2,
gdsctl_sid   IN NUMBER,
message_type IN NUMBER DEFAULT msg_message);
TBD
 
SETCOMMITMODE (new 21c)
Toggles the commit Mode On/Off dbms_gsm_utility.setCommitMode(noCommit BINARY_INTEGER); -- noCommit = 1 (commit ) = 0 
exec gsmadmin_internal.dbms_gsm_utility.setCommitMode(0);

PL/SQL procedure successfully completed.
 
SETSESSIONKEY
Set the current session key as text value, columns separated by comma. Base64 TEXT escaping for each column value is supported, ^ as the first character, e.g. : 1,abc,3 can be represented as ^MQ==,^YWJj,^Mw== dbms_gsm_utility.setSessionKey(
sharding_k      IN VARCHAR2,
supersharding_k IN VARCHAR2);
TBD
 
SHARDGROUPEXISTS
Returns TRUE if the shard group exists dbms_gsm_utility.shardGroupExists(shardgroup_name IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF gsmadmin_internal.dbms_gsm_utility.shardGroupExists('ZZYZX') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F
 
SHARDSPACEEXISTS
Checks to see if the specified shard space exists in the cloud catalog dbms_gsm_utility.shardSpaceExists(
shardspace_name IN  VARCHAR2,
shardspace_id   OUT NUMBER)
RETURN BOOLEAN;
TBD
 
UPDATE_DDL_DUPTBL
Undocumented dbms_gsm_utility.update_ddl_duptbl(
object_name IN VARCHAR2,
schema_name IN VARCHAR2);
exec gsmadmin_internal.dbms_gsm_utility.update_ddl_duptbl('TAB$', USER);

PL/SQL procedure successfully completed.
 
UPDATE_DDL_INCDEP
Undocumented dbms_gsm_utility.update_ddl_incdep;
exec gsmadmin_internal.dbms_gsm_utility.update_ddl_incdep;

PL/SQL procedure successfully completed.
 
WAIT_FOR_DDL
Wait for identified DDL statement to be applied on shards dbms_gsm_utility.wait_for_ddl(
in_ddl_id IN NUMBER := NULL,
p_timeout IN NUMBER := 3600);
exec gsmadmin_internal.dbms_gsm_utility.wait_for_ddl(NULL, 2400);

PL/SQL procedure successfully completed.
 
WAIT_FOR_DDL_COMP (new 21c)
Waits until GSM is completely done with DDL propagation dbms_gsm_utility.wait_for_ddl_comp(
in_ddl_id IN  NUMBER,
ddl_to    OUT NUMBER);
TBD
 
WAIT_FOR_DDL_NOEX
Wait for identified DDL statement to be applied on shards dbms_gsm_utility.wait_for_ddl_noex(
in_ddl_id IN NUMBER := NULL,
p_timeout IN NUMBER := 3600)
RETURN NUMBER;
SELECT gsmadmin_internal.dbms_gsm_utility.wait_for_ddl_noex(NULL, 4000)
FROM dual;

GSMADMIN_INTERNAL.DBMS_GSM_UTILITY.WAIT_FOR_DDL_NOEX(NULL,4000)
---------------------------------------------------------------
                                                              0
 
WAIT_FOR_GSM_REQUEST (new 21c)
Wait for a specific GSM request to be completed dbms_gsm_utility.wait_for_gsm_request(
change_seq IN NUMBER DEFAULT NULL,
timeout    IN NUMBER DEFAULT 3600)
RETURN NUMBER;
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_GSM_ALERTS
DBMS_GSM_CLOUDADMIN
DBMS_GSM_COMMON
DBMS_GSM_DBADMIN
DBMS_GSM_FIX
DBMS_GSM_FIXED
DBMS_GSM_NOPRIV
DBMS_GSM_POOLADMIN
DBMS_GSM_XDB
EXCHANGE
What's New In 21c
What's New In 23c

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