Oracle DBMS_SMB
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 SQL Management Base Utilities
AUTHID CURRENT_USER
Constants SMB object type constants moved to DBMS_SMB_INTERNAL 2/28/07
Dependencies
DBMS_AUTO_INDEX DBMS_SQLDIAG_INTERNAL DBMS_STATS_INTERNAL
DBMS_PRIV_CAPTURE DBMS_SQLQ DBMS_SYS_ERROR
DBMS_SMB_INTERNAL DBMS_SQLTCB_INTERNAL PLITBLM
DBMS_SPD DBMS_SQLTUNE SQLSET_ROW
DBMS_SPM DBMS_SQLTUNE_INTERNAL SQL_PLAN_TABLE_TYPE
DBMS_SPM_INTERNAL DBMS_SQLTUNE_UTIL0 WRI$_REPT_SPMEVOLVE
DBMS_SQLCONTROL_INTERNAL DBMS_SQLTUNE_UTIL2 XMLTYPE
DBMS_SQLDIAG DBMS_STANDARD  
Documented No
Exceptions
Error Code Reason
ORA-19384 Cannot pack into staging table from previous version
ORA-19385 Staging table is empty
ORA-38171 Insufficient privileges for SQL management object operation
First Available 2006-7
Security Model Owned by SYS with no privileges_granted.
Source {ORACLE_HOME}/rdbms/admin/prvssmb.plb
Subprograms
 
CHECK_SMB_PRIV
Raises an exception if the identified privilege is inadequate for SMB use dbms_smb.check_smb_priv(priv_name IN VARCHAR2);
SQL> exec dbms_smb.check_smb_priv('DBA');
BEGIN dbms_smb.check_smb_priv('DBA'); END;
*
ERROR at line 1:
ORA-38171: Insufficient privileges for SQL management object operation
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SMB", line 116
ORA-06512: at line 1



SQL> exec dbms_smb.check_smb_priv('SYSDBA');

PL/SQL procedure successfully completed.
 
CREATE_STGTAB
Creates an SMB staging table dbms_smb.create_stgtab(
table_name      IN VARCHAR2,
table_owner     IN VARCHAR2,
tablespace_name IN VARCHAR2);
exec dbms_smb.create_stgtab('SMBSTGTAB', 'UWCLASS', 'SYSTEM');

PL/SQL procedure successfully completed.

desc uwclass.smbstgtab
 
GET_VSN_STGTAB_SMO
Undocumented but appears to return the number of rows in the staging table dbms_smb.get_vsn_stgtab_smo(qualified_stgtab_name IN VARCHAR2) RETURN NUMBER;
SELECT dbms_smb.get_vsn_stgtab_smo('UWCLASS.SMBSTGTAB')
FROM dual;

DBMS_SMB.GET_VSN_STGTAB_SMO('UWCLASS.SMBSTGTAB')
-----------------------------------------------
                                              1
 
OPEN_STGTAB_CURSOR
Opens the staging table ref cursor dbms_smb.open_stgtab_cursor(
qualified_stgtab_name IN VARCHAR2
sql_handle            IN  VARCHAR2,
obj_name              IN  VARCHAR2,
obj_type              IN  VARCHAR2,
sql_text              IN  CLOB,
creator               IN  VARCHAR2,
origin                IN  VARCHAR2,
enabled               IN  VARCHAR2,
accepted              IN  VARCHAR2,
fixed                 IN  VARCHAR2,
module                IN  VARCHAR2,
action                IN  VARCHAR2,
category              IN  VARCHAR2,
open_cur_flags        IN  BINARY_INTEGER,
stgtab_version        IN  NUMBER,
CUR                   OUT REF CURSOR);
TBD
 
PACK_STGTAB
Load staging table dbms_smb.pack_stgtab(
staging_table_name  IN VARCHAR2,
staging_table_owner IN VARCHAR2,
sql_handle          IN VARCHAR2,
obj_name            IN VARCHAR2,
obj_type            IN VARCHAR2,
sql_text            IN CLOB,
creator             IN VARCHAR2,
origin              IN VARCHAR2,
enabled             IN VARCHAR2,
accepted            IN VARCHAR2,
fixed               IN VARCHAR2,
module              IN VARCHAR2,
action              IN VARCHAR2,
category            IN VARCHAR2,
commit_rows         IN NUMBER);
RETURN NUMBER;
TBD
 
UNPACK_STGTAB
Undocumented dbms_smb.unpack_stgtab(
staging_table_name  IN VARCHAR2,
staging_table_owner IN VARCHAR2,
sql_handle          IN VARCHAR2,
obj_name            IN VARCHAR2,
obj_type            IN VARCHAR2,
sql_text            IN CLOB,
creator             IN VARCHAR2,
origin              IN VARCHAR2,
enabled             IN VARCHAR2,
accepted            IN VARCHAR2,
fixed               IN VARCHAR2,
module              IN VARCHAR2,
action              IN VARCHAR2,
category            IN VARCHAR2,
replace             IN BOOLEAN,
impcallout          IN BOOLEAN)
RETURN NUMBER;
TBD
 
UPGRADE_STGTAB
Upgrades the named staging table dbms_smb.upgrade_stgtab(
staging_table_owner IN VARCHAR2,
staging_table_name  IN VARCHAR2);
exec dbms_smb.upgrade_stgtab('ORDSYS', 'SMBSTGTAB');
 
Related Queries
Retrieve SMB parameters
col parameter_name format a35
col last_updated format a20
col updated_by format a20
col parameter_data format a20

SELECT parameter_name, parameter_value, parameter_data
FROM smb$config
ORDER BY 1;

PARAMETER_NAME                             PARAMETER_VALUE PARAMETER_DATA
------------------------------------- -------------------- --------------------
AUTO_CAPTURE_PARSING_SCHEMA_NAME                         0 <filters></filters>
AUTO_CAPTURE_MODULE                                      0 <filters></filters>
AUTO_CAPTURE_ACTION                                      0 <filters></filters>
AUTO_CAPTURE_sql_text                                    0 <filters></filters>
AUTO_INDEX_COMPRESSION                                   0 OFF
AUTO_INDEX_DEFAULT_TABLESPACE                            0
AUTO_INDEX_MODE                                          0 OFF
AUTO_INDEX_REPORT_RETENTION                             31
AUTO_INDEX_RETENTION_FOR_AUTO                            0 373
AUTO_INDEX_RETENTION_FOR_MANUAL                          0
AUTO_INDEX_SCHEMA                                        0 <filters></filters>
AUTO_INDEX_SPACE_BUDGET                                 50
AUTO_SPM_EVOLVE_TASK                                     0 OFF
AUTO_SPM_EVOLVE_TASK_INTERVAL                         3600
AUTO_SPM_EVOLVE_TASK_MAX_RUNTIME                      1800
PLAN_RETENTION_WEEKS                                    53
SPACE_BUDGET_PERCENT                                    10
SPM_TRACING                                              0
_AUTO_INDEX_ABSDIFF_THRESHOLD                          100
_AUTO_INDEX_CONCURRENCY                                  1
_AUTO_INDEX_CONTROL                                      0
_AUTO_INDEX_DERIVE_STATISTICS                            0 ON
_AUTO_INDEX_IMPROVEMENT_THRESHOLD                       20
_AUTO_INDEX_REBUILD_COUNT_LIMIT                          5
_AUTO_INDEX_REBUILD_TIME_LIMIT                          30
_AUTO_INDEX_REGRESSION_THRESHOLD                        10
_AUTO_INDEX_REVERIFY_TIME                               30
_AUTO_INDEX_SPA_CONCURRENCY                              1
_AUTO_INDEX_STS_CAPTURE_TASK                             0 OFF
_AUTO_INDEX_TASK_INTERVAL                              900
_AUTO_INDEX_TASK_MAX_RUNTIME                          3600
_AUTO_INDEX_TRACE                                        0

Related Topics
Built-in Functions
Built-in Packages
DBMS_SMB_INTERNAL
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