DBMS_CLOUD 包

本文介紹了

DBMS_CLOUD

包的基本用法。

自治資料庫中引入了 DBMS_CLOUD 包，以提供一種與資料庫中的對象存儲進行互動的簡單方法。它可用于版本 19c 和 21c 的本地安裝。它同樣适用于 AWS S3 存儲 buckets 或 Oracle Cloud Object Storage 存儲 buckets。

先決條件

本文中的大多數示例都使用

DBMS_CLOUD

包的本地安裝，但某些功能似乎隻能在自治資料庫上正常工作，過程中我将強調這些限制出現的地方。

DBMS_CLOUD 包預設存在于自治資料庫中，它未安裝在 Oracle 19c 或 21c 安裝中，是以必須手動安裝，此 MOS 說明中描述了安裝。

How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

可以參考下方的安裝示例：

DBMS_CLOUD : Installation on 19c and 21c On-Prem Databases

對于某些示例，我們需要一個對象存儲 buckets。這可以是 Oracle Cloud Object Store 存儲 buckets 或 AWS S3 存儲 buckets。以下文章介紹了如何建立 Oracle Cloud Object Store 存儲 buckets。

Oracle Cloud Infrastructure (OCI) : Create an Object Storage Bucket

安裝

我們建立一個測試使用者，確定使用者可以建立憑據，并授予其通路 DBMS_CLOUD 包的權限。

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;

grant create credential to testuser1;
grant execute on dbms_cloud to testuser1;

我們需要一個本地目錄對象來與資料庫檔案伺服器上的檔案進行互動，授予 test 使用者和

C##CLOUD$SERVICE

使用者通路此目錄的權限。

create or replace directory tmp_files_dir as '/tmp/files';
grant read, write on directory tmp_files_dir to testuser1, C##CLOUD$SERVICE;

外部表功能需要通路名為

DATA_PUMP_DIR

的目錄對象，是以在

PDB

中建立它并向測試使用者授予讀/寫通路權限。

alter session set "_oracle_script"=TRUE;
create or replace directory data_pump_dir as '/u01/app/oracle/admin/cdb1/dpdump/';
alter session set "_oracle_script"=FALSE;
grant read, write on directory data_pump_dir to testuser1;

連接配接到測試使用者并建立下表：

conn testuser1/testuser1@//localhost:1521/pdb1

create table emp (
  empno    number(4,0), 
  ename    varchar2(10 byte), 
  job      varchar2(9 byte), 
  mgr      number(4,0), 
  hiredate date, 
  sal      number(7,2), 
  comm     number(7,2), 
  deptno   number(2,0), 
  constraint pk_emp primary key (empno)
);
  
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

本文中所有 SQL 操作均由測試使用者執行，除非特殊說明。

conn testuser1/testuser1@//localhost:1521/pdb1

對象存儲 URI

對于本文中的許多示例，我們使用對象存儲 URI。對于 Oracle Cloud，URI 可以采用這些形式之一。

https://swiftobjectstorage.{region}.oraclecloud.com/v1/{namespace}/{bucket}/{objectname}

https://objectstorage.{region}.oraclecloud.com/n/{namespace}/b/{bucket}/o/{objectname}

本文主要使用

swiftobjectstorage

URI，但兩者都有效。在本文的其餘部分，我們将使用

swiftobjectstorage

URI。

AWS S3 和 Azure blob 存儲 URI 通常如下所示：

AWS S3: https://s3-{region}.amazonaws.com/{bucket}/{objectname}
Azure Blog Storage: https://{account}.blob.core.windows.net/{container}/{objectname}

對象存儲憑證

DBMS_CLOUD 包包含來自 DBMS_CREDENTIAL 包的過程的副本。這兩個包可以互換使用，因為它們做同樣的事情。

DBMS_CREDENTIAL : Persist Database and OS Credentials in Oracle Database 12c Release 1 (12.1)

使用

CREATE_CREDENTIAL

過程為您的對象存儲建立憑證。對于 Oracle 對象存儲桶，我們使用我們的 Oracle Cloud 電子郵件和我們生成的身份驗證令牌。

begin
  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => '[email protected]',
    password        => '{my-Auth-Token}'
  ) ;
end;
/

對于 AWS 存儲 buckets，我們使用我們的 AWS 通路密鑰和秘密通路密鑰。

begin
  dbms_cloud.create_credential (
    credential_name => 'obj_store_cred',
    username        => 'my AWS access key',
    password        => 'my AWS secret access key'
  );
end;
/

可以使用 USER_CREDENTIALS 視圖顯示有關憑證的資訊。

column credential_name format a25
column username format a20

select credential_name,
       username,
       enabled
from   user_credentials
order by credential_name;

CREDENTIAL_NAME           USERNAME             ENABL
------------------------- -------------------- -----
OBJ_STORE_CRED            [email protected]       TRUE

SQL>

DISABLE_CREDENTIAL 和 ENABLE_CREDENTIAL 過程分别禁用和啟用憑據。

begin
  dbms_credential.disable_credential('obj_store_cred');

  dbms_credential.enable_credential('obj_store_cred');
end;/

UPDATE_CREDENTIALS 過程允許我們編輯憑證的屬性。

begin
  dbms_credential.update_credential(
    credential_name => 'obj_store_cred',
    attribute       => 'username', 
    value           => '[email protected]');

  dbms_credential.update_credential(
    credential_name => 'obj_store_cred',
    attribute       => 'password', 
    value           => '{my-Auth-Token}');
end;
/

DROP_CREDENTIAL 過程删除命名憑據。

begin
  dbms_cloud.drop_credential(credential_name => 'obj_store_cred');
end;
/

以下示例需要有效憑據。

對象和檔案

有幾個例程可用于操作本地資料庫檔案系統上的檔案和雲對象存儲中的對象。

在資料庫伺服器檔案系統上建立一個檔案。

mkdir -p /tmp/files
echo "This is a test file" > /tmp/files/test1.txt

我們使用 PUT_OBJECT 過程将檔案從目錄對象位置傳輸到雲對象存儲。

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test1.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'test1.txt');
end;
/

PUT_OBJECT 過程有一個重載，用于将 BLOB 的内容傳輸到對象存儲。

declare
  l_file blob;
begin
  l_file := utl_raw.cast_to_raw('This is another test file');

  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt',
    contents        => l_file);
end;
/

LIST_OBJECTS 表函數列出對象存儲 URI 指向的位置中的對象。

set linesize 150
column object_name format a12
column checksum format a35
column created format a35
column last_modified format a35

select *
from   dbms_cloud.list_objects(
        credential_name => 'obj_store_cred',
        location_uri    => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket');

OBJECT_NAME       BYTES CHECKSUM                            CREATED                             LAST_MODIFIED
------------ ---------- ----------------------------------- ----------------------------------- -----------------------------------
test1.txt            20 5dd39cab1c53c2c77cd352983f9641e1                                        11-SEP-21 08.45.42.779000 AM +00:00
test2.txt            25 d0914057907f9d04dd9e68b1c1e180f0                                        11-SEP-21 08.45.54.148000 AM +00:00

SQL>

我們使用 GET_METADATA 函數傳回有關特定對象的資訊。

select dbms_cloud.get_metadata(
         credential_name => 'obj_store_cred',
         object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt') as metadata
from dual;

METADATA
--------------------------------------------------------------------------------
{"Content-Length":25}

SQL>

我們使用 GET_OBJECT 過程将對象從雲對象存儲傳輸到目錄對象位置。

begin
  dbms_cloud.get_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'test2.txt');
end;
/

有一個 GET_OBJECT 函數可以将對象從雲對象存儲傳輸到 BLOB。

declare
  l_file blob;
begin
  l_file := dbms_cloud.get_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt');
end;
/

DELETE_OBJECT 過程從雲對象存儲中删除對象。

begin
  dbms_cloud.delete_object(
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test1.txt');

  dbms_cloud.delete_object(
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/test2.txt');
end;
/

DELETE_FILE 過程從目錄對象位置删除檔案。

begin
  dbms_cloud.delete_file(
    directory_name => 'tmp_files_dir',
    file_name      => 'test1.txt');

  dbms_cloud.delete_file(
    directory_name => 'tmp_files_dir',
    file_name      => 'test2.txt');
end;
/

LIST_FILES 表函數列出指定 Oracle 目錄對象指向的位置中的檔案。文檔說它隻支援映射到 Oracle 檔案系統 (OFS) 或資料庫檔案系統 (DBFS) 檔案系統的目錄對象，是以我們不能将它用于正常檔案系統。它确實在自治資料庫上提供的位置工作。

select *
from   dbms_cloud.list_files(directory_name => 'data_pump_dir');

外部表

本節介紹基于雲對象存儲中的檔案建立外部表。

CREATE_EXTERNAL_TABLE

我們使用以下内容建立一個名為“emp.dat”的檔案并将其放入我們的對象存儲中。它是一個沒有标題的管道分隔檔案。

7369|"SMITH"|"CLERK"|7902|17-DEC-80|800||20
7499|"ALLEN"|"SALESMAN"|7698|20-FEB-81|1600|300|30
7521|"WARD"|"SALESMAN"|7698|22-FEB-81|1250|500|30
7566|"JONES"|"MANAGER"|7839|02-APR-81|2975||20
7654|"MARTIN"|"SALESMAN"|7698|28-SEP-81|1250|1400|30
7698|"BLAKE"|"MANAGER"|7839|01-MAY-81|2850||30
7782|"CLARK"|"MANAGER"|7839|09-JUN-81|2450||10
7788|"SCOTT"|"ANALYST"|7566|19-APR-87|3000||20
7839|"KING"|"PRESIDENT"||17-NOV-81|5000||10
7844|"TURNER"|"SALESMAN"|7698|08-SEP-81|1500|0|30
7876|"ADAMS"|"CLERK"|7788|23-MAY-87|1100||20
7900|"JAMES"|"CLERK"|7698|03-DEC-81|950||30
7902|"FORD"|"ANALYST"|7566|03-DEC-81|3000||20
7934|"MILLER"|"CLERK"|7782|23-JAN-82|1300||10

CREATE_EXTERNAL_TABLE 過程基于雲對象存儲中的檔案建立名為 EMP_EXT 的外部表。

--drop table emp_ext;

begin
  dbms_cloud.create_external_table(
    table_name      => 'emp_ext',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.dat',
    column_list     => 'empno     number(4),
                        ename     varchar2(10),
                        job       varchar2(9),
                        mgr       number(4),
                        hiredate  date,
                        sal       number(7,2),
                        comm      number(7,2),
                        deptno    number(2)',
    format          => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/

我們查詢外部表，它從雲對象存儲中讀取資料。

select * from emp_ext;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

FORMAT 參數允許我們調整加載過程以适應資料檔案内容。可以在此處找到格式選項的完整清單，以下示例适用于 CSV 檔案。

我們使用以下内容建立一個名為“emp.csv”的檔案并将其放入我們的對象存儲中。它是一個帶有标題行的 CSV 檔案。

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7839,"KING","PRESIDENT",,17-NOV-81,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-81,950,,30
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

CREATE_EXTERNAL_TABLE 過程基于雲對象存儲中的檔案建立名為 EMP_CSV_EXT 的外部表。

--drop table emp_csv_ext;

begin
  dbms_cloud.create_external_table(
    table_name      => 'emp_csv_ext',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
    column_list     => 'empno     number(4),
                        ename     varchar2(10),
                        job       varchar2(9),
                        mgr       number(4),
                        hiredate  date,
                        sal       number(7,2),
                        comm      number(7,2),
                        deptno    number(2)',
    format          => json_object('type' value 'csv', 'skipheaders' value '1')
 );
end;
/

select * from emp_csv_ext;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

VALIDATE_EXTERNAL_TABLE 過程允許我們檢查外部表的有效性。

begin
  dbms_cloud.validate_external_table('emp_csv_ext');
end;
/

CREATE_EXTERNAL_PART_TABLE

使用以下查詢建立四個 CSV 檔案：

set markup csv on quote on
set trimspool on linesize 1000 feedback off pagesize 0

spool /tmp/files/gbr1.txt
select 'GBR',
       object_id,
       owner,
       object_name
from   all_objects
where  object_id <= 2000
and    rownum <= 1000;
spool off

spool /tmp/files/gbr2.txt
select 'GBR',
       object_id,
       owner,
       object_name
from   all_objects
where  object_id BETWEEN 2000 AND 3999
and    rownum <= 1000;
spool off

spool /tmp/files/ire1.txt
select 'IRE',
       object_id,
       owner,
       object_name
from   all_objects
where  object_id <= 2000
and    rownum <= 1000;
spool off

spool /tmp/files/ire2.txt
select 'IRE',
       object_id,
       owner,
       object_name
from   all_objects
where  object_id BETWEEN 2000 AND 3999
and    rownum <= 1000;
spool off

set markup csv off
set trimspool on linesize 1000 feedback off pagesize 14

在上傳檔案之前，您可能需要稍微清理檔案的開頭和結尾。将檔案複制到對象存儲：

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'gbr1.txt');
end;
/

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'gbr2.txt');
end;
/

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'ire1.txt');
end;
/

begin
  dbms_cloud.put_object (
    credential_name => 'obj_store_cred',
    object_uri      => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt',
    directory_name  => 'tmp_files_dir',
    file_name       => 'ire2.txt');
end;
/

CREATE_EXTERNAL_PART_TABLE 過程基于雲對象存儲中的檔案建立名為 COUNTRY_PART_TAB_EXT 的外部分區表。

--drop table country_part_tab_ext;

begin
  dbms_cloud.create_external_part_table(
    table_name      => 'country_part_tab_ext',
    credential_name => 'obj_store_cred',
    format          => json_object('type' value 'csv', 'skipheaders' value '1'),
    column_list     => 'country_code  varchar2(3),
                        object_id     number,
                        owner         varchar2(128),
                        object_name   varchar2(128)',
    partitioning_clause => 'partition by list (country_code) (
                              partition part_gbr values (''GBR'') location (
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt'',
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt''
                              ),
                              partition part_ire values (''IRE'') location (
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt'',
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt''
                              )
                            )'
  );
end;
/

select country_code, count(*) as amount
from   country_part_tab_ext
group by country_code
order by country_code;

COU     AMOUNT
--- ----------
GBR       2000
IRE       2000

SQL>

VALIDATE_EXTERNAL_PART_TABLE 過程允許我們檢查外部分區表的有效性。

begin
  dbms_cloud.validate_external_part_table('country_part_tab_ext');
end;
/

CREATE_HYBRID_PART_TABLE

CREATE_HYBRID_PART_TABLE 過程基于雲對象存儲中的檔案建立一個名為 COUNTRY_HYBRID_PART_TAB_EXT 的外部混合分區表。

--drop table country_hybrid_part_tab_ext;

begin
  dbms_cloud.create_hybrid_part_table(
    table_name      => 'country_hybrid_part_tab_ext',
    credential_name => 'obj_store_cred',
    format          => json_object('type' value 'csv', 'skipheaders' value '1'),
    column_list     => 'country_code  varchar2(3),
                        object_id     number,
                        owner         varchar2(128),
                        object_name   varchar2(128)',
    partitioning_clause => 'partition by list (country_code) (
                              partition part_gbr values (''GBR'') external location (
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr1.txt'',
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/gbr2.txt''
                              ),
                              partition part_ire values (''IRE'') external location (
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire1.txt'',
                                ''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/ire2.txt''
                              ),
                              partition part_usa values (''USA'')
                            )'
  );
end;
/

我們在正常分區中插入一行。

insert into country_hybrid_part_tab_ext values ('USA', 123, 'banana', 'banana');
commit;

select country_code, count(*) as amount
from   country_hybrid_part_tab_ext
group by country_code
order by country_code;

COU     AMOUNT
--- ----------
GBR       2000
IRE       2000
USA          1

SQL>

VALIDATE_HYBRID_PART_TABLE 過程允許我們檢查外部混合分區表的有效性。

begin
  dbms_cloud.validate_hybrid_part_table('country_hybrid_part_tab_ext');
end;
/

COPY_DATA

COPY_DATA 過程允許我們将資料從雲對象存儲複制到現有表中。

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-81,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-81,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-81,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-81,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7839,"KING","PRESIDENT",,17-NOV-81,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-81,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-81,950,,30
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-82,1300,,10

我們截斷本地 EMP 表并使用 COPY_DATA 過程從雲對象存儲重新加載資料。

truncate table emp

begin
  dbms_cloud.copy_data(
    table_name      => 'emp',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
    format          => json_object('type' value 'csv', 'skipheaders' value '1')
  );
end;
/

我們查詢EMP表，可以看到資料已經加載完畢。

select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

與外部表示例類似，FORMAT 參數允許我們定制加載過程以适應資料檔案内容。

Export Data

EXPORT_DATA 過程擷取查詢生成的資料，并以請求的格式将其導出到雲對象存儲。這似乎不适用于 DBMS_CLOUD 包的本地版本，但适用于自治資料庫。

begin
  dbms_cloud.export_data (
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.json',
    query           => 'select * from emp',
    format          => '{"type" : "JSON"}'
  );
end;
/

begin
  dbms_cloud.export_data (
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/emp.csv',
    query           => 'select * from emp',
    format          => '{"type" : "CSV"}'
  );
end;
/

SODA Collections

Oracle 資料庫可用作使用簡單 Oracle 文檔通路 (SODA) 的文檔存儲。您可以在此處閱讀有關 SODA 的資訊。

我們建立一個名為“TestCollection1”的新集合。

set serveroutput on

declare
  l_collection  soda_collection_t;
begin
  l_collection := dbms_soda.create_collection('TestCollection1');

  if l_collection is not null then
    dbms_output.put_line('Collection ID : ' || l_collection.get_name());
  else
    dbms_output.put_line('Collection does not exist.');  
  end if;
end;
/
Collection ID : TestCollection1


PL/SQL procedure successfully completed.

SQL>

使用以下内容建立一個名為“fruit.json”的檔案并将其上傳到您的雲對象存儲。

{"fruit": "banana"}

COPY_COLLECTION 過程将資料從我們的雲對象存儲加載到集合中。

begin
  dbms_cloud.copy_collection(
    collection_name => 'TestCollection1',
    credential_name => 'obj_store_cred',
    file_uri_list   => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/my-namespace/ob-bucket/fruit.json',
    format          => json_object('unpackarrays' value 'true')
  );
end;
/

我們可以使用以下查詢檢視集合中的資料。

select json_document
from   "TestCollection1";

JSON_DOCUMENT
--------------------------------------------------------------------------------
{"fruit":"banana"}

SQL>

删除操作

許多 DBMS_CLOUD 操作會産生額外的檔案（日志檔案、壞檔案、臨時檔案等）。一旦操作完成，這些需要清理。上面顯示的幾個過程具有傳回操作 ID 值的重載，該值可與 DELETE_OPERATION 過程一起使用以清理額外的檔案。或者，可以使用 USER_LOAD_OPERATIONS 視圖顯示目前會話的操作。

SQL> desc user_load_operations
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER
 TYPE                                                  NOT NULL VARCHAR2(128)
 SID                                                   NOT NULL NUMBER
 SERIAL#                                               NOT NULL NUMBER
 START_TIME                                                     TIMESTAMP(6) WITH TIME ZONE
 UPDATE_TIME                                                    TIMESTAMP(6) WITH TIME ZONE
 STATUS                                                         VARCHAR2(9)
 OWNER_NAME                                                     VARCHAR2(128)
 TABLE_NAME                                                     VARCHAR2(128)
 PARTITION_NAME                                                 VARCHAR2(128)
 SUBPARTITION_NAME                                              VARCHAR2(128)
 FILE_URI_LIST                                                  VARCHAR2(4000)
 ROWS_LOADED                                                    NUMBER
 LOGFILE_TABLE                                                  VARCHAR2(128)
 BADFILE_TABLE                                                  VARCHAR2(128)
 TEMPEXT_TABLE                                                  VARCHAR2(128)

SQL>

我們使用 USER_LOAD_OPERATIONS 視圖傳回目前會話的操作。

column type format a10

select id, type
from   user_load_operations
order by 1;

        ID TYPE
---------- ----------
         1 COPY
        11 COPY

SQL>

DELETE_OPERATION 過程允許我們清除與特定操作相關的附加檔案。

begin
  dbms_cloud.delete_operation(1);
end;
/


select id, type
from   user_load_operations
order by 1;

        ID TYPE
---------- ----------
        11 COPY

SQL>

DELETE_ALL_OPERATIONS 過程允許我們清理所有操作的附加檔案，或者如果我們指定類型值，則清理特定類型操作的附加檔案。

begin
  dbms_cloud.delete_operation(1);
end;
/


select id, type
from   user_load_operations
order by 1;

        ID TYPE
---------- ----------
        11 COPY

SQL>

DBMS_CLOUD 包

先決條件

安裝

對象存儲 URI

對象存儲憑證

對象和檔案

外部表

CREATE_EXTERNAL_TABLE

CREATE_EXTERNAL_PART_TABLE

CREATE_HYBRID_PART_TABLE

COPY_DATA

Export Data

SODA Collections

删除操作

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List