Oracle DBMS_HS_PARALLEL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Enables parallel processing for heterogeneous targets access. This package is designed to improve performance when retrieving data from a large foreign table.
AUTHID CURRENT_USER
Data Types type hs_part_rec is record (t hs_partition_obj);
type hs_partion_rec is record (t hs_part_obj);
type hs_sample_rec is record (t hs_sample_obj);
type hs_part_refcur_t is ref cursor return hs_part_rec;
type hs_partion_refcur_t is ref cursor return hs_partion_rec;
type hs_sample_refcur_t is ref cursor return hs_sample_rec;
Dependencies
DBMS_ASSERT HSBLKVALARY HS_PART_OBJ
DBMS_HS_PARALLEL_METADATA HS_BULK_SEQ HS_SAMPLE_OBJ
HSBLKNAMLST HS_PARTITION_OBJ PLITBLM
Documented Yes
Exceptions
Error Code Reason
ORA-00018 Maximum number of session exceeded
ORA-00942 Table or view does not exist
ORA-01733 Virtual column not allowed here
ORA-02019 Connection description for remote database not found
ORA-12801 Error signaled in parallel query server <query_server_name>
ORA-24277 no_dblink: The database link does not exist
ORA-24278 no_remote_table: The remote table does not exist
ORA-24279 no_view: The remote view does not exist
First Available Not known
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmshsld.sql
Subprograms
 
CREATE_OR_REPLACE_VIEW
Creates (or replaces) a read-only view to be referenced for retrieving the data from a remote table in parallel dbms_hs_parallel.create_or_replace_view(
remote_table    IN VARCHAR2,
database_link   IN VARCHAR2,
oracle_view     IN VARCHAR2 := NULL,
parallel_degree IN INTEGER  := NULL);
-- the following is not a heterogeneous link but will at least provide a link good enough to generate an exception useful in understanding the syntax

CREATE DATABASE LINK conn_user
USING 'conn_link';

exec dbms_hs_parallel.create_or_replace_view('M$DATA', 'CONN_USER', 'LOCALVIEW', 4);
BEGIN dbms_hs_parallel.create_or_replace_view('M$DATA', 'CONN_USER', 'LOCALVIEW', 4); END;

*
ERROR at line 1:
ORA-24277: invalid database link CONN_USER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_HS_PARALLEL_METADATA", line 53
ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 3851
ORA-06512: at line 1
 
CREATE_TABLE_TEMPLATE
Writes out a CREATE TABLE template based on information gathered from the remote table. You can use the information to add any optimal Oracle CREATE TABLE clauses dbms_hs_parallel.create_table_template(
remote_table                 IN  VARCHAR2,
database_link                IN  VARCHAR2,
oracle_table                 IN  VARCHAR2 := NULL,
create_table_template_string OUT VARCHAR2);
-- the error below is expected as the remote SQL Server database did not exist

DECLARE
 outVal user_tables.table_name%TYPE;
BEGIN
  dbms_hs_parallel.create_table_template('M$DATA', 'CONN_USER', 'ORCLDATA', outVal);
  dbms_output.put_line(outVal);
END;
/
DECLARE
*
ERROR at line 1:
ORA-24278: remote table M$DATA not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_HS_PARALLEL_METADATA", line 53
ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 4315
ORA-06512: at line 4
 
DROP_VIEW
Drops the view and internal objects created by the CREATE_OR_REPLACE_VIEW procedure. If the view has not already been created by the CREATE_OR_REPLACE_VIEW procedure, an error message is returned. dbms_hs_parallel.drop_view(oracle_view IN VARCHAR2);
-- even creating a readonly view does not trick Oracle that what was created is not a parallel access view

conn sys@pdbdev as sysdba

CREATE OR REPLACE VIEW uwclass.localview AS
SELECT * FROM servers
WITH READ ONLY;

exec dbms_hs_parallel.drop_view('UWCLASS.TEST');
*
ERROR at line 1:
ORA-24279: view UWCLASS.TEST is not a parallel access view
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_HS_PARALLEL_METADATA", line 53
ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 3975
ORA-06512: at line 1
 
LOAD_TABLE
Loads the data from a remote table to a local Oracle table in parallel. If the local Oracle table does not already exist, it is created automatically dbms_hs_parallel.load_table(
remote_table    IN  VARCHAR2 ,
database_link   IN  VARCHAR2 ,
oracle_table    IN  VARCHAR2 := NULL,
truncate        IN  BOOLEAN  := TRUE,
parallel_degree IN  INTEGER  := NULL,
row_count       OUT NUMBER);
DECLARE
 rcnt PLS_INTEGER;
BEGIN
  dbms_hs_parallel.load_table('M$DATA', 'CONN_USER', 'ORCLDATA', TRUE, 4, rcnt);
  dbms_output.put_line(TO_CHAR(rcnt));
END;
/
*
ERROR at line 1:
ORA-24278: remote table M$DATA not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_HS_PARALLEL_METADATA", line 53
ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 2560
ORA-06512: at line 4

Related Topics
Database Links
Packages
What's New In 12cR1
What's New In 12cR2

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