Oracle DBMS_HS_PARALLEL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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 2856
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

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