Data share - Part III

In this blogpost we will see about how to create, populate, and publish the data share. A data share is a named entity in the provider instance and It can be a group of datasets shared as a single entity. The share is a logical container that contains objects such as tables/view that we can share with the data recipient. An authorized data share recipient can access the share and all tables in it.
 
 
As a share provider create a named data share DEMO_SHARE using the DATA_SHARE_STORAGE_LINK refers to the storage link created in the previous blog post.
 
share-provider@ATP19C> begin
  2     dbms_share.create_share(
  3             share_name => 'DEMO_SHARE',
  4             share_type => 'VERSIONED',
  5             storage_link_name => 'DATA_SHARE_STORAGE_LINK');
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
share-provider@ATP19C> select share_name, current_version
  2  from user_shares
  3  where share_name ='DEMO_SHARE';
 
SHARE_NAME           CURRENT_VERSION
-------------------- ---------------
DEMO_SHARE
 
before we publish the share, the CURRENT_VERSION will be null, after we publish the share, the column CURRENT_VERSION will be incremental value.
 
Add data source to the data share.
 
share-provider@ATP19C> begin
  2     dbms_share.add_to_share(
  3             share_name => 'DEMO_SHARE',
  4             table_name => 'BIG_TABLE',
  5             owner => user,
  6             share_table_name => 'BIG_TABLE');
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
share-provider@ATP19C> select share_table_name, table_name, table_owner
  2  from user_share_tables
  3  where share_name ='DEMO_SHARE';
 
SHARE_TABLE_NAME     TABLE_NAME           TABLE_OWNER
-------------------- -------------------- --------------------
BIG_TABLE            BIG_TABLE            SHARE_PROVIDER
 
Upto this point, share and its table are stored in database only. the call to publish_share API will offload data to OCI buckets and make it accessible to recipients.
 
admin@ATP19C> exec dbms_share.update_default_share_property('JOB_TYPE','DBMS_CLOUD');
 
PL/SQL procedure successfully completed.
 
share-provider@ATP19C> exec dbms_share.publish_share( share_name => 'DEMO_SHARE' );
 
PL/SQL procedure successfully completed.
 
Call to the PUBLISH_SHARE API will offloads data to the Cloud Store and makes it accessible to recipients that you define and authorize later.
 
share-provider@ATP19C> select share_version, status
  2  from user_share_versions
  3  where share_name ='DEMO_SHARE';
 
SHARE_VERSION STATUS
------------- -----------
            1 EXPORTING
 
If the status shows exporting, then publishing process is not yet complete, we need to wait for few mins.
when we publish a "VERSIONED" share, the tool generates and stores the data share as parquet files
in the specified object storage bucket, any authenticated data share recipients can directly access the share in the bucket. And you don’t need to proceed further till the EXPORTING status change to CURRENT status.
 
share-provider@ATP19C> select share_version, status
  2  from user_share_versions
  3  where share_name ='DEMO_SHARE';
 
SHARE_VERSION STATUS
------------- -----------
            1 CURRENT
 
share-provider@ATP19C> variable uri varchar2(95)
share-provider@ATP19C> exec :uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/DATA_SHARE_BUCKET/o/';
 
PL/SQL procedure successfully completed.
 
share-provider@ATP19C>
share-provider@ATP19C> col object_name for a85
share-provider@ATP19C> select object_name, bytes
  2      from dbms_cloud.list_objects(
  3                 credential_name => 'SHARE_BUCKET_CREDENTIAL',
  4                 location_uri => :uri );
 
OBJECT_NAME                                                                                BYTES
------------------------------------------------------------------------------------- ----------
DEMO_SHARE_528631/SHARE_PROVIDER/BIG_TABLE/V1_1S_1_20240408T043228502295Z.parquet       24899814
 
This tool generates and stores the data share as parquet files in the specified bucket & any authenticated data share recipient can directly access the share in the bucket.