该文档记录DBMS_CLOUD包的基本使用方法。DBMS_CLOUD包是自治数据库引入的,它提供了数据库与对象存储之间一种简单交互方式。该包可以在本地安装的19c和21c中安装。它能够搭配AWS S3存储或Oracle云对象存储使用。
1. 前提需求
该文档中的大多数案例是使用本地安装的DBMS_CLOUD包,但是一些功能似乎只在自治数据库中正常。在这些地方,我们将进行标注。
DBMS_CLOUD包默认是在自治数据库中使用的。在Oracle 19c或21c中并没有安装,所以需要手动安装。安装可以参考Note 2748362.1
当然也可以参考博客中的安装文档。
我们这里需要一个对象存储桶来做一些案例测试。可以是Oracle云对象存储桶或AWS S3桶。可以参考博客中的后续文档来创建Oracle云对象存储桶。
2. 设置
创建test用户,并授予用户创建认证权限和使用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;
创建一个本地directory对象,用于数据库文件服务器做交互。这里授予test用户和c##cloud$service用户访问该directory的权限。
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 directory对象,所以在PDB中创建,并授予test用户read/write权限。
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;
使用test用户连接,创建emp表,并插入数据。
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操作都是由test用户操作,否则会有对应的声明。
conn testuser1/testuser1@//localhost:1521/pdb1
3. 对象存储URIs
该文档中我们将使用对象存储URI。对于Oracle云,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存储URIs通常格式如下:
AWS S3: https://s3-{region}.amazonaws.com/{bucket}/{objectname}
Azure Blog Storage: https://{account}.blob.core.windows.net/{container}/{objectname}
很多procedures和函数都支持URI定义中使用通配符:
-
* - 表示多个字符
-
? - 表示单个字符
4. 对象存储认证
DBMS_CLOUD包含DBMS_CREDENTIAL包的副本。这两个包可以替换使用。
使用create_credential程序为对象存储创建一个认证。对于Oracle对象存储桶,我们将使用自己的Oracle Cloud email和生成的认证令牌。
begin
dbms_cloud.create_credential (
credential_name => 'obj_store_cred',
username => 'me@example.com',
password => '{my-Auth-Token}'
) ;
end;
/
对于AWS桶,我们使用自己的ASW访问密码和安全访问密码。
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 me@example.com TRUE
SQL>
程序disable_credential和enable_credential可以禁用和启用对应的认证。
begin
dbms_credential.disable_credential('obj_store_cred');
dbms_credential.enable_credential('obj_store_cred');
end;/
程序update_credential能够更新认证的属性设置。
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可以删除对应的认证。
begin
dbms_cloud.drop_credential(credential_name => 'obj_store_cred');
end;
/
以下案例中需要一个可用的认证。
5. 对象和文件
维护本地数据库文件系统中的文件和云对象存储中的对象有很多种方式。
在数据库服务器文件系统上创建一个目录。
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 directory对象中的文件。其他文档显示这只支持映射到Oracle File System(OFS)或DBFS文件系统的directory对象,因此不能在普通文件系统上使用。但是他在自治数据库上可以。
select *
from dbms_cloud.list_files(directory_name => 'data_pump_dir');
6. 外部表
该章节介绍使用云对象存储上的文件创建外部表。
6.1. 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能够跟踪加载程序来设置数据文件内容的格式。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;
/
6.2. CREATE_EXTERNAL_PART_TABLE
使用以下查询创建4个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;
/
6.3. 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;
/
7. 拷贝数据
copy_date程序能够从云对象存储中拷贝数据到已有表中。
使用以下内容创建文件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
truncate本地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能够让我们跟踪加载程序,设置数据文件内容格式。
8. 导出数据
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;
/
9. SODA集合
oracle数据库通过使用SODA(Simple Oracle Document Access,简易Oracle文档访问)可以作为文档使用。可以在后续文档中获取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>
10. 删除操作
一些dbms_cloud操作产生额外的文件(log文件、bad文件、temp文件等等)。当操作完成后,这些文件需要被清理。以上的很多程序都会返回操作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程序能够删除所有操作的额外文件或者特定类型操作的文件。
-- Delete only COPY operations.
begin
dbms_cloud.delete_all_operations('COPY');
end;
/
-- Delete all operations.
begin
dbms_cloud.delete_all_operations;
end;
/