Offloading Oracle Data to Object Storage and Loading as External Table in an Autonomous Database | Part 3: Oracle OCI Object Storage

Following up the posts here and here, we are explaining today how to make same thing using Oracle OCI Object Storage. The procedure and steps are quite similar so, let’s go and try!


Create a bucket

Upload file to bucket

Create an Auth Token for your user as follows:

In a sql editor, execute the following:

      credential_name => 'ocirfidcredential',     
      username => '',     
      password => 'laralilaralá'   
  ); END;

NOTE: If your user is federated in IdCS (not local) then prepend oracleidentitycloudservice/ to the username as follows:

      credential_name => 'ocirfidcredential',     
      username => 'oracleidentitycloudservice/',     
      password => 'borriquitocomotu,tururú'   

Construct a url with the following pattern:

Or get the url from here:

Execute the following putting the appropriate url of your object:

      table_name =>'ocirfid201901',
      credential_name =>'ocirfidcredential',
      file_uri_list =>'',
      format => json_object('delimiter' value '|', 'truncatecol' value 'True'),
      column_list => 'jsoncol varchar2(4000)'

If the validation ends up OK, then your table is ready for utilisation, let’s see.

set timing on
select count() from ocirfid201901
select count() from azurfid201901
select count(*) from s3rfid201901
Test #1
Test #2
Test #3

That’s all folks! Hope it helps 🙂

See also this and this

One Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.