原文地址：https://oracle-base.com/articles/21c/dbms_cloud-package
原文作者：Tim Hall
本文介紹了 DBMS_CLOUD 包的基本用法。
目錄
CLOUD 包，以提供一種與數(shù)據(jù)庫中的對象存儲進(jìn)行交互的簡單方法。它可用于版本 19c 和 21c 的本地安裝。 它同樣適用于 AWS S3 存儲 buckets 或 Oracle Cloud Object Storage 存儲 buckets。先決條件
本文中的大多數(shù)示例都使用 DBMS_CLOUD 包的本地安裝，但某些功能似乎只能在自治數(shù)據(jù)庫上正常工作，過程中我將強(qiáng)調(diào)這些限制出現(xiàn)的地方。
DBMS_CLOUD 包默認(rèn)存在于自治數(shù)據(jù)庫中，它未安裝在 Oracle 19c 或 21c 安裝中，因此必須手動安裝，此 MOS 說明中描述了安裝。
可以參考下方的安裝示例：
對于某些示例，我們需要一個對象存儲 buckets。這可以是 Oracle Cloud Object Store 存儲 buckets 或 AWS S3 存儲 buckets。以下文章介紹了如何創(chuàng)建 Oracle Cloud Object Store 存儲 buckets。
安裝
我們創(chuàng)建一個測試用戶，確保用戶可以創(chuàng)建憑據(jù)，并授予其訪問 DBMS_CLOUD 包的權(quán)限。
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;
我們需要一個本地目錄對象來與數(shù)據(jù)庫文件服務(wù)器上的文件進(jìn)行交互，授予 test 用戶和 C##CLOUD$SERVICE 用戶訪問此目錄的權(quán)限。
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 中創(chuàng)建它并向測試用戶授予讀/寫訪問權(quán)限。
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;
連接到測試用戶并創(chuàng)建下表：
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 操作均由測試用戶執(zhí)行，除非特殊說明。
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 包的過程的副本。這兩個包可以互換使用，因為它們做同樣的事情。
使用 CREATE_CREDENTIAL 過程為您的對象存儲創(chuàng)建憑證。對于 Oracle 對象存儲桶，我們使用我們的 Oracle Cloud 電子郵件和我們生成的身份驗證令牌。
begin
dbms_cloud.create_credential (
credential_name => 'obj_store_cred',
username => 'me@example.com',
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 視圖顯示有關(guān)憑證的信息。
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 me@example.com TRUE
SQL>
DISABLE_CREDENTIAL 和 ENABLE_CREDENTIAL 過程分別禁用和啟用憑據(jù)。
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 => 'me@example.com');
dbms_credential.update_credential(
credential_name => 'obj_store_cred',
attribute => 'password',
value => '{my-Auth-Token}');
end;
/
DROP_CREDENTIAL 過程刪除命名憑據(jù)。
begin
dbms_cloud.drop_credential(credential_name => 'obj_store_cred');
end;
/
以下示例需要有效憑據(jù)。
對象和文件
有幾個例程可用于操作本地數(shù)據(jù)庫文件系統(tǒng)上的文件和云對象存儲中的對象。
在數(shù)據(jù)庫服務(wù)器文件系統(tǒng)上創(chuàng)建一個文件。
mkdir -p /tmp/files
echo "This is a test file" > /tmp/files/test1.txt
我們使用 PUT_OBJECT 過程將文件從目錄對象位置傳輸?shù)皆茖ο蟠鎯Α?/p>
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 的內(nèi)容傳輸?shù)綄ο蟠鎯Α?/p>
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 表函數(shù)列出對象存儲 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 函數(shù)返回有關(guān)特定對象的信息。
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 過程將對象從云對象存儲傳輸?shù)侥夸泴ο笪恢谩?/p>
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 函數(shù)可以將對象從云對象存儲傳輸?shù)?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 表函數(shù)列出指定 Oracle 目錄對象指向的位置中的文件。 文檔說它只支持映射到 Oracle 文件系統(tǒng) (OFS) 或數(shù)據(jù)庫文件系統(tǒng) (DBFS) 文件系統(tǒng)的目錄對象，所以我們不能將它用于常規(guī)文件系統(tǒng)。 它確實在自治數(shù)據(jù)庫上提供的位置工作。
select *
from dbms_cloud.list_files(directory_name => 'data_pump_dir');
外部表
本節(jié)介紹基于云對象存儲中的文件創(chuàng)建外部表。
CREATE_EXTERNAL_TABLE
我們使用以下內(nèi)容創(chuàng)建一個名為“emp.dat”的文件并將其放入我們的對象存儲中。 它是一個沒有標(biāo)題的管道分隔文件。
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 過程基于云對象存儲中的文件創(chuàng)建名為 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;
/
我們查詢外部表，它從云對象存儲中讀取數(shù)據(jù)。
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 參數(shù)允許我們調(diào)整加載過程以適應(yīng)數(shù)據(jù)文件內(nèi)容。 可以在此處找到格式選項的完整列表，以下示例適用于 CSV 文件。
我們使用以下內(nèi)容創(chuàng)建一個名為“emp.csv”的文件并將其放入我們的對象存儲中。 它是一個帶有標(biāo)題行的 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 過程基于云對象存儲中的文件創(chuàng)建名為 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;
/
我們查詢外部表，它從云對象存儲中讀取數(shù)據(jù)。
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
使用以下查詢創(chuàng)建四個 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
在上傳文件之前，您可能需要稍微清理文件的開頭和結(jié)尾。 將文件復(fù)制到對象存儲：
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 過程基于云對象存儲中的文件創(chuàng)建名為 COUNTRY_PART_TAB_EXT 的外部分區(qū)表。
--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;
/
我們查詢外部表，它從云對象存儲中讀取數(shù)據(jù)。
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 過程允許我們檢查外部分區(qū)表的有效性。
begin
dbms_cloud.validate_external_part_table('country_part_tab_ext');
end;
/
CREATE_HYBRID_PART_TABLE
CREATE_HYBRID_PART_TABLE 過程基于云對象存儲中的文件創(chuàng)建一個名為 COUNTRY_HYBRID_PART_TAB_EXT 的外部混合分區(qū)表。
--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;
/
我們在常規(guī)分區(qū)中插入一行。
insert into country_hybrid_part_tab_ext values ('USA', 123, 'banana', 'banana');
commit;
我們查詢外部表，它從云對象存儲中讀取數(shù)據(jù)。
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 過程允許我們檢查外部混合分區(qū)表的有效性。
begin
dbms_cloud.validate_hybrid_part_table('country_hybrid_part_tab_ext');
end;
/
COPY_DATA
COPY_DATA 過程允許我們將數(shù)據(jù)從云對象存儲復(fù)制到現(xiàn)有表中。
我們使用以下內(nèi)容創(chuàng)建一個名為“emp.csv”的文件并將其放入我們的對象存儲中。 它是一個帶有標(biāo)題行的 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
我們截斷本地 EMP 表并使用 COPY_DATA 過程從云對象存儲重新加載數(shù)據(jù)。
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表，可以看到數(shù)據(jù)已經(jīng)加載完畢。
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>
與外部表示例類似，F(xiàn)ORMAT 參數(shù)允許我們定制加載過程以適應(yīng)數(shù)據(jù)文件內(nèi)容。
Export Data
EXPORT_DATA 過程獲取查詢生成的數(shù)據(jù)，并以請求的格式將其導(dǎo)出到云對象存儲。 這似乎不適用于 DBMS_CLOUD 包的本地版本，但適用于自治數(shù)據(jù)庫。
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 數(shù)據(jù)庫可用作使用簡單 Oracle 文檔訪問 (SODA) 的文檔存儲。 您可以在此處閱讀有關(guān) SODA 的信息。
我們創(chuàng)建一個名為“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>
使用以下內(nèi)容創(chuàng)建一個名為“fruit.json”的文件并將其上傳到您的云對象存儲。
{"fruit": "banana"}
COPY_COLLECTION 過程將數(shù)據(jù)從我們的云對象存儲加載到集合中。
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;
/
我們可以使用以下查詢查看集合中的數(shù)據(jù)。
select json_document
from "TestCollection1";
JSON_DOCUMENT
--------------------------------------------------------------------------------
{"fruit":"banana"}
SQL>
刪除操作
許多 DBMS_CLOUD 操作會產(chǎn)生額外的文件（日志文件、壞文件、臨時文件等）。 一旦操作完成，這些需要清理。 上面顯示的幾個過程具有返回操作 ID 值的重載，該值可與 DELETE_OPERATION 過程一起使用以清理額外的文件。 或者，可以使用 USER_LOAD_OPERATIONS 視圖顯示當(dāng)前會話的操作。
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 視圖返回當(dāng)前會話的操作。
column type format a10
select id, type
from user_load_operations
order by 1;
ID TYPE
---------- ----------
1 COPY
11 COPY
SQL>
DELETE_OPERATION 過程允許我們清除與特定操作相關(guān)的附加文件。
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 過程允許我們清理所有操作的附加文件，或者如果我們指定類型值，則清理特定類型操作的附加文件。
-- Delete only COPY operations.
begin
dbms_cloud.delete_all_operations('COPY');
end;
/
-- Delete all operations.
begin
dbms_cloud.delete_all_operations;
end;
/
「喜歡這篇文章，您的關(guān)注和贊賞是給作者最好的鼓勵」
關(guān)注作者
【版權(quán)聲明】本文為墨天輪用戶原創(chuàng)內(nèi)容，轉(zhuǎn)載時必須標(biāo)注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權(quán)追究責(zé)任。如果您發(fā)現(xiàn)墨天輪中有涉嫌抄襲或者侵權(quán)的內(nèi)容，歡迎發(fā)送郵件至：contact@modb.pro進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，墨天輪將立刻刪除相關(guān)內(nèi)容。
