DBA Hub

📋Steps in this guide1/2

Running Commands in Different Schemas and Pluggable Databases

This article explains how to run commands in a different schema using the DBMS_SCHEDULER and DBMS_SQL packages.

oracle miscconfigurationintermediate
by OracleDba
12 views
1

DBMS_SCHEDULER Approach

The package can be used to run jobs as different users. In the example below we use the procedure to create a job owned by the schema to drop a database link called . The parameters to pay attention to are as follows. - : Set to a fully qualified name made up as a combination of the username and a random name generated by the function. - : We then put the command that needs to run as the SCOTT user in a PL/SQL block. In this case we use an to perform the DDL statement. - : We want the job to run. - : The job will be dropped immediately on completion. This saves us clearing up lots of one-off jobs.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE
  l_user     VARCHAR2(30) := 'SCOTT';
  l_db_link  VARCHAR2(30) := 'my_link';
BEGIN
  DBMS_SCHEDULER.create_job(
    job_name   => l_user || '.' || DBMS_SCHEDULER.generate_job_name,
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN
                     EXECUTE IMMEDIATE ''DROP DATABASE LINK ' || l_db_link || ''';
                   END;',
    enabled    => TRUE,
    auto_drop  => TRUE
  );
END;
/
2

DBMS_SQL Approach

The procedure of DBMS_SQL package can be used to run commands as other users. In the example below we use it to drop a database link called owned by the SCOTT schema. The parameters to pay attention to are as follows. - : We then put the command that needs to run as the user in a PL/SQL block. - : The name of the user we want to run this command as. The procedure can also accepts a parameter to allow a statement to run in the specified container. For more information see: 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
23
24
25
26
27
28
29
30
31
32
33
34
DECLARE
  l_user     VARCHAR2(30) := 'SCOTT';
  l_db_link  VARCHAR2(30) := 'my_link';
  
  l_cursor   PLS_INTEGER;
BEGIN
  l_cursor := sys.DBMS_SQL.open_cursor(security_level => 2);

  sys.DBMS_SQL.parse(c             => l_cursor,
                     statement     => 'DROP DATABASE LINK ' || l_db_link,
                     language_flag => sys.DBMS_SQL.native,
                     schema        => l_user);
                     
  sys.DBMS_SQL.close_cursor(c => l_cursor);
END;
/

DECLARE
  l_user     VARCHAR2(30) := 'SCOTT';
  l_db_link  VARCHAR2(30) := 'my_link';
  
  l_cursor   PLS_INTEGER;
BEGIN
  l_cursor := sys.DBMS_SQL.open_cursor(security_level => 2);
  
  sys.DBMS_SQL.parse(c             => l_cursor,
                     statement     => 'DROP DATABASE LINK ' || l_db_link,
                     language_flag => sys.DBMS_SQL.native,
                     schema        => l_user,
                     container     => 'PDB1');
                     
  sys.DBMS_SQL.close_cursor(c => l_cursor);
END;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!