Oracle DBMS_HS_PARALLEL
Version 19.3

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 2551
ORA-06512: at line 4
General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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);
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object SYS.DBMS_HS_PASSTHROUGH@CONN_USER
ORA-00604: error occurred at recursive SQL level 2
ORA-12154: TNS:could not resolve the connect identifier specified
ORA-06512: at "SYS.DBMS_HS_PARALLEL", line 2845
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 4296
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 3960
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);
 
TABLE_SAMPLING (new 19c)
Undocumented

Relocated from the DBMS_HS_PARALLEL_METADATA package
dbms_hs_parallel.table_sampling(
remote_schema     IN VARCHAR2,
remote_table_name IN VARCHAR2,
database_link     IN VARCHAR2,
hs_remote_tab_typ IN VARCHAR2,
p_degree          IN NUMBER,
row_count         IN NUMBER,
ora_user          IN VARCHAR2,
oracle_table_name IN VARCHAR2,
pt_col_names      IN HSBLKNamLst,
pt_col_types      IN HSBLKNamLst ,
col_names         IN HSBLKNamLst,
col_types         IN HSBLKNamLst) ;
TBD

Related Topics
Built-in Functions
Built-in Packages
Database Links
DBMS_HS_PARALLEL_METADATA
What's New In 18cR3
What's New In 19cR3

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