Oracle | DBMS

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	OPEN_STGTAB_CURSOR	UNPACK_STGTAB
CREATE_STGTAB	PACK_STGTAB	UPGRADE_STGTAB
GET_VSN_STGTAB_SMO

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