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
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 18c
What's New In 19c

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