DBA Hub

📋Steps in this guide1/7

Oracle REST Data Services (ORDS) : Delete ORDS Metadata

This article describes how to delete ORDS metadata that is no longer needed.

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Setup

In order to run the examples in this article we need to do the following setup. We create a new test user. We create an object in the test user. We enable ORDS for our test user, create a service that queries the test table and AutoREST enable a table. All operations are performed while connected to the test user, unless otherwise stated.

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
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

--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@//localhost:1521/freepdb1

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;

-- Enable ORDS for schema.
begin
  ords.enable_schema(
    p_enabled             => true,
    p_schema              => 'TESTUSER1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'hr',
    p_auto_rest_auth      => false
  );
    
  commit;
end;
/

-- Short-cut to define a module, template and handler.
begin
  ords.define_service(
    p_module_name    => 'rest-v1',
    p_base_path      => 'rest-v1/',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ords.source_type_collection_feed,
    p_source         => 'select * from emp',
    p_items_per_page => 0);

  commit;
end;
/

-- AutoREST enable an object.
begin
  ords.enable_object (
    p_enabled      => true,
    p_schema       => 'TESTUSER1',
    p_object       => 'EMP',
    p_object_type  => 'TABLE',
    p_object_alias => 'employees'
  );
    
  commit;
end;
/

conn testuser1/testuser1@//localhost:1521/freepdb1
2

ORDS Metadata

The ORDS metadata is stored in a schema called . We query the metadata using a variety of "DBA_%" and "USER_%" views. The "DBA_%" views show all metadata, and the "USER_%" views show the metadata relevant to the current user. The following query displays the "USER_%" views. We see the current schema has ORDS enabled. We see the modules, templates and handlers owned by the test user. The join to is not really necessary, as we know what schema we are connected to. They've been left in to make the transition to using the "DBA_%" views easier. We see the AutoREST enabled objects.

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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
conn testuser1/testuser1@//localhost:1521/freepdb1

column object_name format a30

select object_name
from   all_objects
where  object_name like 'USER_ORDS%'
and    object_type = 'VIEW'
order by 1;

OBJECT_NAME
------------------------------
USER_ORDS_APPROVALS
USER_ORDS_CLIENTS
USER_ORDS_CLIENT_PRIVILEGES
USER_ORDS_CLIENT_ROLES
USER_ORDS_ENABLED_OBJECTS
USER_ORDS_HANDLERS
USER_ORDS_JWT_PROFILE
USER_ORDS_MODULES
USER_ORDS_OBJECTS
USER_ORDS_OBJ_MEMBERS
USER_ORDS_PARAMETERS
USER_ORDS_PENDING_APPROVALS
USER_ORDS_PREDISPATCH_TASKS
USER_ORDS_PRIVILEGES
USER_ORDS_PRIVILEGE_MAPPINGS
USER_ORDS_PRIVILEGE_MODULES
USER_ORDS_PRIVILEGE_ROLES
USER_ORDS_PROPERTIES
USER_ORDS_ROLES
USER_ORDS_SCHEMAS
USER_ORDS_SERVICES
USER_ORDS_TEMPLATES

SQL>

set linesize 100
column parsing_schema format a20
column pattern format a30
column status format a10

select id,
       parsing_schema,
       pattern,
       status
from   user_ords_schemas
order by parsing_schema;

        ID PARSING_SCHEMA       PATTERN                        STATUS
---------- -------------------- ------------------------------ ----------
     10062 TESTUSER1            hr                             ENABLED

SQL>

column parsing_schema format a20
column module_name format a20
column uri_prefix format a20

select s.parsing_schema,
       m.id as module_id,
       m.name as module_name,
       m.uri_prefix,
       m.status
from   user_ords_modules m
       join user_ords_schemas s on s.id = schema_id
where  s.parsing_schema = 'TESTUSER1'
order by s.parsing_schema, m.name;

PARSING_SCHEMA        MODULE_ID MODULE_NAME          URI_PREFIX           STATUS
-------------------- ---------- -------------------- -------------------- ----------
TESTUSER1                 10075 rest-v1              /rest-v1/            PUBLISHED

SQL>


column name format a20
column uri_template format a20

select m.name as module_name,
       t.id as template_id,
       t.uri_template
from   user_ords_templates t
       join user_ords_modules m on m.id = t.module_id
       join user_ords_schemas s on s.id = m.schema_id
where  s.parsing_schema = 'TESTUSER1'
order by m.name, t.uri_template;

MODULE_NAME          TEMPLATE_ID URI_TEMPLATE
-------------------- ----------- --------------------
rest-v1                    10076 employees/

SQL>


set linesize 150
column source_type format a20
column source format a30

select m.name as module_name,
       t.uri_template,
       h.source_type,
       h.method,
       h.source
from   user_ords_handlers h
       join user_ords_templates t on t.id = h.template_id
       join user_ords_modules m on m.id = t.module_id
       join user_ords_schemas s on s.id = m.schema_id
where  s.parsing_schema = 'TESTUSER1'
order by m.name, t.uri_template;

MODULE_NAME          URI_TEMPLATE         SOURCE_TYPE          METHOD     SOURCE
-------------------- -------------------- -------------------- ---------- ------------------------------
rest-v1              employees/           json/collection      GET        select * from emp

SQL>

column parsing_schema format a20
column parsing_object format a20
column object_alias format a20

select parsing_schema,
       parsing_object,
       object_alias,
       status
from   user_ords_objects
order by 1;

PARSING_SCHEMA       PARSING_OBJECT       OBJECT_ALIAS         STATUS
-------------------- -------------------- -------------------- ------------------------------
TESTUSER1            EMP                  employees            ENABLED

SQL>
3

Delete Modules

If there are modules that need to be cleaned up we can delete them with a call to , passing the module name

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
begin
  ords.delete_module(p_module_name => 'rest-v1');
  commit;
end;
/


column parsing_schema format a20
column module_name format a20
column uri_prefix format a20

select s.parsing_schema,
       m.id as module_id,
       m.name as module_name,
       m.uri_prefix,
       m.status
from   user_ords_modules m
       join user_ords_schemas s on s.id = schema_id
where  s.parsing_schema = 'TESTUSER1'
order by s.parsing_schema, m.name;

no rows selected

SQL>
4

Delete AutoREST Objects

We could disable an AutoREST object by setting the flag to . The documentation claims the procedure deletes the AutoREST definition for the object, but it just seems to disable the definition, like in the previous example. We AutoREST enable an object. We call the procedure and can see the definition is still present, but it is marked as disabled.

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
begin
  ords.enable_object (
    p_enabled      => false,
    p_schema       => 'TESTUSER1',
    p_object       => 'EMP',
    p_object_type  => 'TABLE',
    p_object_alias => 'employees'
  );
    
  commit;
end;
/


column parsing_schema format a20
column parsing_object format a20
column object_alias format a20

select parsing_schema,
       parsing_object,
       object_alias,
       status
from   user_ords_objects
order by 1;

PARSING_SCHEMA       PARSING_OBJECT       OBJECT_ALIAS         STATUS
-------------------- -------------------- -------------------- ------------------------------
TESTUSER1            EMP                  employees            DISABLED

SQL>

begin
  ords.enable_object (
    p_enabled      => true,
    p_schema       => 'TESTUSER1',
    p_object       => 'EMP',
    p_object_type  => 'TABLE',
    p_object_alias => 'employees'
  );
    
  commit;
end;
/


column parsing_schema format a20
column parsing_object format a20
column object_alias format a20

select parsing_schema,
       parsing_object,
       object_alias,
       status
from   user_ords_objects
order by 1;

PARSING_SCHEMA       PARSING_OBJECT       OBJECT_ALIAS         STATUS
-------------------- -------------------- -------------------- ------------------------------
TESTUSER1            EMP                  employees            ENABLED

SQL>

begin
  ords.drop_rest_for_object(p_object => 'EMP');
  commit;
end;
/


select parsing_schema,
       parsing_object,
       object_alias,
       status
from   user_ords_objects
order by 1;<

PARSING_SCHEMA       PARSING_OBJECT       OBJECT_ALIAS         STATUS
-------------------- -------------------- -------------------- ------------------------------
TESTUSER1            EMP                  emp                  DISABLED

SQL>
5

Delete Authentication Metadata

The clean up of ORDS authentication metadata is described here. - Deleting OAuth Metadata (prior to 24.3) - Deleting OAuth Metadata (24.3 onward) - Deleting Roles and Privileges Metadata
6

Disable ORDS for a Schema

If we want to disable ORDS for a schema without destroying the ORDS definitions, we can set the flag to when calling the procedure.

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
begin
  ords.enable_schema(
    p_enabled             => false,
    p_schema              => 'TESTUSER1'
  );

  commit;
end;
/


set linesize 100
column parsing_schema format a20
column pattern format a30
column status format a10

select id,
       parsing_schema,
       pattern,
       status
from   user_ords_schemas
order by parsing_schema;

        ID PARSING_SCHEMA       PATTERN                        STATUS
---------- -------------------- ------------------------------ ----------
     10062 TESTUSER1            hr                             DISABLED

SQL>
7

Delete All Metadata for Schema

If we want to remove all metadata relating to a schema we can call the top-level procedure. For more information see: - ORDS PL/SQL Package Reference - Oracle REST Data Services (ORDS) : All Articles Hope this helps. Regards Tim...

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
begin
  ords.drop_rest_for_schema(p_schema => 'TESTUSER1');
  commit;
end;
/


set linesize 100
column parsing_schema format a20
column pattern format a30
column status format a10

select id,
       parsing_schema,
       pattern,
       status
from   user_ords_schemas
order by parsing_schema;

no rows selected

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!