DBA Hub

📋Steps in this guide1/10

Oracle REST Data Services (ORDS) : Database API - Data Pump

The Oracle REST Data Services (ORDS) database API allows us to create Data Pump export and import jobs via REST web service calls.

oracle miscconfigurationintermediate
by OracleDba
12 views
1

Assumptions

This article assumes the following. We will use the following endpoint to perform the operations. If you are using a root container installation, you will have to adjust the URL to include the database service. In this example the PDB1 pluggable database service. There are also separate RPC-style export and import endpoints. I'm going to avoid them as they are not "REST", having their action determined by the URL, and all they do is allow us to omit the "operation" item from the payload.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
https://localhost:8443/ords/{schema-alias}/_/db-api/stable/database/datapump/jobs/

# Example
https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/

https://localhost:8443/ords/{database-service}/{schema-alias}/_/db-api/stable/database/datapump/jobs/

# Example
https://localhost:8443/ords/pdb1/dbapi_user/_/db-api/stable/database/datapump/jobs/

# REST
.../_/db-api/stable/database/datapump/jobs/

# RPC
.../_/db-api/stable/database/datapump/export
.../_/db-api/stable/database/datapump/import
2

Setup

We create a new database user for our testing. We create and populate a copy of the EMP table in the test user. We create a physical directory on the database server to use with the export/import operations. We create a Oracle directory object pointing to the physical location. Once a job is running, the status of the job is visible in the view and using the GET service shown below. You can see an example of this in the datapump_jobs.sql script.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
conn / as sysdba
alter session set container=pdb1;

--drop user testuser1 cascade;
create user testuser1 identified by testuser1
  default tablespace users quota unlimited on users;
  
grant create session, create table, create type to testuser1;

conn testuser1/testuser1@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;

mkdir -p /tmp/dp

conn / as sysdba
alter session set container=pdb1;

create or replace directory test_dir AS '/tmp/dp';
grant read, write on directory test_dir to testuser1;

column owner_name format a20
column job_name format a30
column operation format a10
column job_mode format a10
column state format a12

select owner_name,
       job_name,
       trim(operation) as operation,
       trim(job_mode) as job_mode,
       state,
       degree,
       attached_sessions,
       datapump_sessions
from   dba_datapump_jobs
order by 1, 2;
3

Get Data Pump Jobs

Making a HTTP GET method call to the "/database/datapump/jobs/" endpoint returns a list of Data Pump jobs, or a count of zero if no jobs are present. For each job in the "items" array there is a "self" link, allowing us to drill down and get more information about the job. In the following example we make a GET call to one of these links. The "links" array includes an "enclosure" link to return the resulting dump file, and a "related" link to return the log file.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
curl -ks -X GET \
     --user dbapi_user:DbApiUserPassword1 \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/ | python3 -mjson.tool
{
    "items": [
        {
            "owner_name": "DBAPI_USER",
            "job_name": "DATAPUMP_REST_EXPORT_20201001115524",
            "operation": "EXPORT                        ",
            "job_mode": "SCHEMA                        ",
            "state": "NOT RUNNING",
            "degree": 0,
            "attached_sessions": 0,
            "datapump_sessions": 0,
            "links": [
                {
                    "rel": "self",
                    "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001115524/"
                }
            ]
        }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 1,
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "edit",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "first",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        }
    ]
}

curl -ks -X GET \
     --user dbapi_user:DbApiUserPassword1 \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001115524/ | python3 -mjson.tool
{
    "job_name": "DATAPUMP_REST_EXPORT_20201001115524",
    "owner_name": "DBAPI_USER",
    "operation": "EXPORT",
    "job_mode": "SCHEMA",
    "state": "NOT RUNNING",
    "degree": 0,
    "attached_sessions": 0,
    "datapump_sessions": 0,
    "job_state": "COMPLETED",
    "job_comment": "Job \"DBAPI_USER\".\"DATAPUMP_REST_EXPORT_20201001115524\" successfully completed at Thu Oct 1 11:56:07 2020 elapsed 0 00:00:41",
    "links": [
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "enclosure",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001115524/EXPDAT01-20201001_11_55_26.DMP"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001115524/EXPDAT-2020-10-01-11_55_24.LOG"
        },
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001115524/"
        }
    ]
}
4

Table Export

The following example shows how to perform a table export of the table in the schema. We create a payload file containing the parameters for the export job. Notice we don't get to control the name of the dump file or the log file. We have to use the output links to identify them. We can use the "name_expressions", "schema_expressions" and "tablespace_expressions" options to define multiple expressions (IN, NOT IN, =, !=, LIKE, NOT LIKE etc.) if required. In the example above the "name_expressions" option includes the table and excludes the table. We could replace the "name_expressions" option with a comma-separated list of the tables to include using the "filer" option, which would give us the same result. We make a POST call to the "/database/datapump/jobs/" endpoint, passing the raw payload and setting the "Content-Type" header to "application/json". The "links" array includes a "related" link to the log file, and a "self" link to the job itself, where we can get the URL for the dump file, similar to what we saw in the GET operation above.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
cat > /tmp/payload.json <<EOF
{
  "operation": "EXPORT",
  "job_mode": "TABLE",
  "datapump_dir": "TEST_DIR",
  "name_expressions": [
    {"expression": "='EMP'"},
    {"expression": "!='DEPT'"}
  ],
  "schema_expressions": [
    {"expression": "= 'TESTUSER1'"}
  ]
}
EOF

cat > /tmp/payload.json <<EOF
{
  "operation": "EXPORT",
  "job_mode": "TABLE",
  "datapump_dir": "TEST_DIR",
  "filter": "EMP",
  "schema_expressions": [
    {"expression": "= 'TESTUSER1'"}
  ]
}
EOF

curl -ks -X POST \
     --user dbapi_user:DbApiUserPassword1 \
     --data-binary @/tmp/payload.json \
     --header "Content-Type:application/json" \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/ | python3 -mjson.tool
{
    "job_name": "DATAPUMP_REST_EXPORT_20201001122631",
    "owner_name": "DBAPI_USER",
    "operation": "EXPORT",
    "job_mode": "TABLE",
    "state": "EXECUTING",
    "degree": 1,
    "attached_sessions": 0,
    "datapump_sessions": 2,
    "job_state": "EXECUTING",
    "links": [
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001122631/EXPDAT-2020-10-01-12_26_31.LOG"
        },
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001122631/"
        }
    ]
}
5

Table Import

The following example shows how to perform a table import. We import the dump file produced by the export of the table. Before doing this we need to drop the existing EMP table. We create a payload file containing the parameters for the import job. We could replace the "name_expressions" option with a comma-separated list of the tables to include using the "filer" option, which would give us the same result. We make a POST call to the "/database/datapump/jobs/" endpoint, passing the raw payload and setting the "Content-Type" header to "application/json".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
drop table testuser1.emp purge;

cat > /tmp/payload.json <<EOF
{
  "operation": "IMPORT",
  "job_mode": "TABLE",
  "datapump_dir": "TEST_DIR",
  "file_name": "EXPDAT01-20201001_11_55_26.DMP",
  "name_expressions": [
    {"expression": "='EMP'"}
  ],
  "schema_expressions": [
    {"expression": "= 'TESTUSER1'"}
  ]
}
EOF

cat > /tmp/payload.json <<EOF
{
  "operation": "IMPORT",
  "job_mode": "TABLE",
  "datapump_dir": "TEST_DIR",
  "file_name": "EXPDAT01-20201001_11_55_26.DMP",
  "filter": "EMP",
  "schema_expressions": [
    {"expression": "= 'TESTUSER1'"}
  ]
}
EOF

curl -ks -X POST \
     --user dbapi_user:DbApiUserPassword1 \
     --data-binary @/tmp/payload.json \
     --header "Content-Type:application/json" \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/ | python3 -mjson.tool
{
    "job_name": "DATAPUMP_REST_IMPORT_20201001141930",
    "owner_name": "DBAPI_USER",
    "operation": "IMPORT",
    "job_mode": "TABLE",
    "state": "EXECUTING",
    "degree": 1,
    "attached_sessions": 0,
    "datapump_sessions": 2,
    "job_state": "EXECUTING",
    "links": [
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001141930/EXPDAT-2020-10-01-11_55_24.LOG"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001141930/IMPDAT-2020-10-01-14_19_30.LOG"
        },
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001141930/"
        }
    ]
}
6

Schema Export

The following example shows how to perform a schema export of the schema. We create a payload file containing the parameters for the export job. If we didn't need a complex expression to identify the schemas to export, we could replace the "schema_expressions" option with a comma-separated list of the schemas to include using the "filer" option, which would give us the same result. We make a POST call to the "/database/datapump/jobs/" endpoint, passing the raw payload and setting the "Content-Type" header to "application/json".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
cat > /tmp/payload.json <<EOF
{
  "operation": "EXPORT",
  "job_mode": "SCHEMA",
  "datapump_dir": "TEST_DIR",
  "schema_expressions": [
    {"expression": "= 'TESTUSER1'"}
  ]
}
EOF

cat > /tmp/payload.json <<EOF
{
  "operation": "EXPORT",
  "job_mode": "SCHEMA",
  "datapump_dir": "TEST_DIR",
  "filter": "TESTUSER1"
}
EOF

curl -ks -X POST \
     --user dbapi_user:DbApiUserPassword1 \
     --data-binary @/tmp/payload.json \
     --header "Content-Type:application/json" \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/ | python3 -mjson.tool
{
    "job_name": "DATAPUMP_REST_EXPORT_20201001142402",
    "owner_name": "DBAPI_USER",
    "operation": "EXPORT",
    "job_mode": "SCHEMA",
    "state": "EXECUTING",
    "degree": 1,
    "attached_sessions": 0,
    "datapump_sessions": 2,
    "job_state": "EXECUTING",
    "links": [
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001142402/EXPDAT-2020-10-01-14_24_02.LOG"
        },
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001142402/"
        }
    ]
}
7

Schema Import

The following example shows how to perform a schema import. We import the dump file produced by the export of the schema, but remap it to . We create a payload file containing the parameters for the import job. Alternatively we could use the "filter" option to identify the schemas to import. We make a POST call to the "/database/datapump/jobs/" endpoint, passing the raw payload and setting the "Content-Type" header to "application/json".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
cat > /tmp/payload.json <<EOF
{
  "operation": "IMPORT",
  "job_mode": "SCHEMA",
  "datapump_dir": "TEST_DIR",
  "file_name": "EXPDAT01-20201001_14_24_04.DMP",
  "schema_expressions": [
    {"expression": "= 'TESTUSER1'"}
  ],
  "remap_schemas": [
    {
      "source": "TESTUSER1",
      "target": "TESTUSER2"
    }
  ]
}
EOF

cat > /tmp/payload.json <<EOF
{
  "operation": "IMPORT",
  "job_mode": "SCHEMA",
  "datapump_dir": "TEST_DIR",
  "file_name": "EXPDAT01-20201001_14_24_04.DMP",
  "filter": "TESTUSER1",
  "remap_schemas": [
    {
      "source": "TESTUSER1",
      "target": "TESTUSER2"
    }
  ]
}
EOF

curl -ks -X POST \
     --user dbapi_user:DbApiUserPassword1 \
     --data-binary @/tmp/payload.json \
     --header "Content-Type:application/json" \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/ | python3 -mjson.tool
{
    "job_name": "DATAPUMP_REST_IMPORT_20201001143655",
    "owner_name": "DBAPI_USER",
    "operation": "IMPORT",
    "job_mode": "SCHEMA",
    "state": "EXECUTING",
    "degree": 1,
    "attached_sessions": 0,
    "datapump_sessions": 2,
    "job_state": "EXECUTING",
    "links": [
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001143655/EXPDAT-2020-10-01-14_24_02.LOG"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001143655/IMPDAT-2020-10-01-14_36_55.LOG"
        },
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001143655/"
        }
    ]
}
8

Database Export

The following example shows how to perform a full export of the database. We create a payload file containing the parameters for the export job. The full export will run in parallel by default. We can limit the parallelism using the "threads" option. We make a POST call to the "/database/datapump/jobs/" endpoint, passing the raw payload and setting the "Content-Type" header to "application/json".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
cat > /tmp/payload.json <<EOF
{
  "operation": "EXPORT",
  "job_mode": "FULL",
  "datapump_dir": "TEST_DIR"
}
EOF

cat > /tmp/payload.json <<EOF
{
  "operation": "EXPORT",
  "job_mode": "FULL",
  "datapump_dir": "TEST_DIR",
  "threads": 1
}
EOF

curl -ks -X POST \
     --user dbapi_user:DbApiUserPassword1 \
     --data-binary @/tmp/payload.json \
     --header "Content-Type:application/json" \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/ | python3 -mjson.tool
{
    "job_name": "DATAPUMP_REST_EXPORT_20201001145804",
    "owner_name": "DBAPI_USER",
    "operation": "EXPORT",
    "job_mode": "FULL",
    "state": "EXECUTING",
    "degree": 1,
    "attached_sessions": 0,
    "datapump_sessions": 2,
    "job_state": "EXECUTING",
    "links": [
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001145804/EXPDAT-2020-10-01-14_58_04.LOG"
        },
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_EXPORT_20201001145804/"
        }
    ]
}
9

Database Import

The following example shows how to perform a full import. We import the dump file produced by the export of the database. We create a payload file containing the parameters for the import job. The database export produced multiple dump files, so we include the "%U" wildcard in the "file_name" option pattern. We also remap one of the schemas. We make a POST call to the "/database/datapump/jobs/" endpoint, passing the raw payload and setting the "Content-Type" header to "application/json".

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
cat > /tmp/payload.json <<EOF
{
  "operation": "IMPORT",
  "job_mode": "FULL",
  "datapump_dir": "TEST_DIR",
  "file_name": "EXPDAT%U-20201001_14_58_04.DMP",
  "remap_schemas": [
    {
      "source": "TESTUSER1",
      "target": "TESTUSER3"
    }
  ]
}
EOF

curl -ks -X POST \
     --user dbapi_user:DbApiUserPassword1 \
     --data-binary @/tmp/payload.json \
     --header "Content-Type:application/json" \
     https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/ | python3 -mjson.tool
{
    "job_name": "DATAPUMP_REST_IMPORT_20201001152105",
    "owner_name": "DBAPI_USER",
    "operation": "IMPORT",
    "job_mode": "FULL",
    "state": "EXECUTING",
    "degree": 1,
    "attached_sessions": 0,
    "datapump_sessions": 2,
    "job_state": "EXECUTING",
    "links": [
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/metadata-catalog/"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001152105/EXPDAT-2020-10-01-14_58_04.LOG"
        },
        {
            "rel": "related",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001152105/IMPDAT-2020-10-01-15_21_05.LOG"
        },
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/dbapi_user/_/db-api/stable/database/datapump/jobs/DBAPI_USER,DATAPUMP_REST_IMPORT_20201001152105/"
        }
    ]
}
10

Thoughts

Here are some thoughts on the database APIs for Data Pump. - The APIs have very limited functionality. There are a lot of things missing compared to the command line utility and the package. - I think the lack of functionality will probably annoy some DBAs. They may prefer to wait for a future version, or use the package to automate Data Pump operations. - This functionality requires the ORDS enabled schema alias approach in version 20.2, but it should work with the default administrator approach in a future version. - I don't see a way to clean up old jobs, or to clean up the contents of the file system that relate to those jobs. If this API is to allow us to be far removed from the server, some sort of clean-up seems to be necessary. - Only being able to use the ORDS enabled schema approach is problematic, as you need to give out DBA credentials to use the API, and I can't see many DBAs using it. If a developer wants to use it, you will need to give them DBA credentials, which is not allowed in many companies. - On a more geenral level, I feel the ORDS Database APIs need a more fine-grained approach to which APIs can be accessed by a user. They should probably reflect the underlying privileges of the database user, rather than using ORDS-specific settings. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!