Performing common system tasks for Oracle DB instances

Following, you can find how to perform certain common DBA tasks related to the system on your Amazon RDS DB instances running Oracle. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances, and restricts access to certain system procedures and tables that require advanced privileges.

Disconnecting a session

To disconnect the current session by ending the dedicated server process, use the Amazon RDS procedure rdsadmin.rdsadmin_util.disconnect. The disconnect procedure has the following parameters.

The following example disconnects a session.

begin
    rdsadmin.rdsadmin_util.disconnect(
        sid    => sid, 
        serial => serial_number);
end;
/

To get the session identifier and the session serial number, query the V$SESSION view. The following example gets all sessions for the user AWSUSER.

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'AWSUSER';

The database must be open to use this method. For more information about disconnecting a session, see ALTER SYSTEM in the Oracle documentation.

Terminating a session

To terminate a session, use the Amazon RDS procedure rdsadmin.rdsadmin_util.kill. The kill procedure has the following parameters.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE

To get the session identifier and the session serial number, query the V$SESSION view. The following example gets all sessions for the user AWSUSER.

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'AWSUSER';

The following example terminates a session.

BEGIN
    rdsadmin.rdsadmin_util.kill(
        sid    => sid, 
        serial => serial_number,
        method => 'IMMEDIATE');
END;
/

The following example terminates the processes associated with a session.

BEGIN
    rdsadmin.rdsadmin_util.kill(
        sid    => sid, 
        serial => serial_number,
        method => 'PROCESS');
END;
/

Canceling a SQL statement in a session

To cancel a SQL statement in a session, use the Amazon RDS procedure rdsadmin.rdsadmin_util.cancel.

The cancel procedure has the following parameters.

The following example cancels a SQL statement in a session.

begin
    rdsadmin.rdsadmin_util.cancel(
        sid    => sid, 
        serial => serial_number,
        sql_id => sql_id);
end;
/

To get the session identifier, the session serial number, and the SQL identifier of a SQL statement, query the V$SESSION view. The following example gets all sessions and SQL identifiers for the user AWSUSER.

select SID, SERIAL#, SQL_ID, STATUS from V$SESSION where USERNAME = 'AWSUSER';

Enabling and disabling restricted sessions

To enable and disable restricted sessions, use the Amazon RDS procedure rdsadmin.rdsadmin_util.restricted_session. The restricted_session procedure has the following parameters.

The following example shows how to enable and disable restricted sessions.

/* Verify that the database is currently unrestricted. */

SELECT LOGINS FROM V$INSTANCE;
 
LOGINS
-------
ALLOWED

/* Enable restricted sessions */

EXEC rdsadmin.rdsadmin_util.restricted_session(p_enable => true);
 

/* Verify that the database is now restricted. */

SELECT LOGINS FROM V$INSTANCE;
 
LOGINS
----------
RESTRICTED
 

/* Disable restricted sessions */

EXEC rdsadmin.rdsadmin_util.restricted_session(p_enable => false);
 

/* Verify that the database is now unrestricted again. */

SELECT LOGINS FROM V$INSTANCE;
 
LOGINS
-------
ALLOWED

Flushing the shared pool

To flush the shared pool, use the Amazon RDS procedure rdsadmin.rdsadmin_util.flush_shared_pool. The flush_shared_pool procedure has no parameters.

The following example flushes the shared pool.

EXEC rdsadmin.rdsadmin_util.flush_shared_pool;

Flushing the buffer cache

To flush the buffer cache, use the Amazon RDS procedure rdsadmin.rdsadmin_util.flush_buffer_cache. The flush_buffer_cache procedure has no parameters.

The following example flushes the buffer cache.

EXEC rdsadmin.rdsadmin_util.flush_buffer_cache;

Flushing the database smart flash cache

To flush the database smart flash cache, use the Amazon RDS procedure rdsadmin.rdsadmin_util.flush_flash_cache. The flush_flash_cache procedure has no parameters. The following example flushes the database smart flash cache.

EXEC rdsadmin.rdsadmin_util.flush_flash_cache;

For more information about using the database smart flash cache with RDS for Oracle, see Storing temporary data in an RDS for Oracle instance store.

Granting SELECT or EXECUTE privileges to SYS objects

Usually you transfer privileges by using roles, which can contain many objects. To grant privileges to a single object, use the Amazon RDS procedure rdsadmin.rdsadmin_util.grant_sys_object. The procedure grants only privileges that the master user has already been granted through a role or direct grant.

The grant_sys_object procedure has the following parameters.

The following example grants select privileges on an object named V_$SESSION to a user named USER1.

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'USER1',
        p_privilege => 'SELECT');
end;
/

The following example grants select privileges on an object named V_$SESSION to a user named USER1 with the grant option.

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'USER1',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

To be able to grant privileges on an object, your account must have those privileges granted to it directly with the grant option, or via a role granted using with admin option. In the most common case, you may want to grant SELECT on a DBA view that has been granted to the SELECT_CATALOG_ROLE role. If that role isn't already directly granted to your user using with admin option, then you can't transfer the privilege. If you have the DBA privilege, then you can grant the role directly to another user.

The following example grants the SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE to USER1. Since the with admin option is used, USER1 can now grant access to SYS objects that have been granted to SELECT_CATALOG_ROLE.

GRANT SELECT_CATALOG_ROLE TO USER1 WITH ADMIN OPTION; 
GRANT EXECUTE_CATALOG_ROLE to USER1 WITH ADMIN OPTION;

Objects already granted to PUBLIC do not need to be re-granted. If you use the grant_sys_object procedure to re-grant access, the procedure call succeeds.

Revoking SELECT or EXECUTE privileges on SYS objects

To revoke privileges on a single object, use the Amazon RDS procedure rdsadmin.rdsadmin_util.revoke_sys_object. The procedure only revokes privileges that the master account has already been granted through a role or direct grant.

The revoke_sys_object procedure has the following parameters.

The following example revokes select privileges on an object named V_$SESSION from a user named USER1.

begin
    rdsadmin.rdsadmin_util.revoke_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_revokee   => 'USER1',
        p_privilege => 'SELECT');
end;
/

Managing RDS_X$ views for Oracle DB instances

You might need to access SYS.X$ fixed tables, which are only accessible by SYS. To create SYS.RDS_X$ views on eligible X$ tables, use the procedures in the rdsadmin.rdsadmin_util package. Your master user is automatically granted the privilege SELECT … WITH GRANT OPTION on the RDS_X$ views.

The rdsadmin.rdsadmin_util procedures are available in the following database engine versions:

List X$ fixed tables eligible for use in RDS_X$ views

To list X$ tables that are eligible for use in RDS_X$ views, use the RDS procedure rdsadmin.rdsadmin_util.list_allowed_sys_x$_views. This procedure accepts no parameters. The following statements lists all eligible X$ tables (sample output included).

SQL> SET SERVEROUTPUT ON
SQL> SELECT * FROM TABLE(rdsadmin.rdsadmin_util.list_allowed_sys_x$_views);

'X$BH'
'X$K2GTE'
'X$KCBWBPD'
'X$KCBWDS'
'X$KGLLK'
'X$KGLOB'
'X$KGLPN'
'X$KSLHOT'
'X$KSMSP'
'X$KSPPCV'
'X$KSPPI'
'X$KSPPSV'
'X$KSQEQ'
'X$KSQRS'
'X$KTUXE'
'X$KQRFP'

The list of eligible X$ tables can change over time. To make sure that your list of eligible X$ fixed tables is current, rerun list_allowed_sys_x$_views periodically.

Creating SYS.RDS_X$ views

To create an RDS_X$ view on an eligible X$ table, use the RDS procedure rdsadmin.rdsadmin_util.create_sys_x$_view. You can only create views for the tables listed in the output of rdsadmin.rdsadmin_util.list_allowed_sys_x$_views. The create_sys_x$_view procedure accepts the following parameters.

The following example creates the SYS.RDS_X$KGLOB view on the table X$KGLOB. The format for the view name is RDS_X$tablename.

SQL> SET SERVEROUTPUT ON
SQL> EXEC rdsadmin.rdsadmin_util.create_sys_x$_view('X$KGLOB');

PL/SQL procedure successfully completed.

The following data dictionary query lists the view SYS.RDS_X$KGLOB and shows its status. Your master user is automatically granted the privilege SELECT ... WITH GRANT OPTION on this view.

SQL> SET SERVEROUTPUT ON
SQL> COL OWNER FORMAT A30 
SQL> COL OBJECT_NAME FORMAT A30
SQL> COL STATUS FORMAT A30
SQL> SET LINESIZE 200
SQL> SELECT OWNER, OBJECT_NAME, STATUS 
FROM DBA_OBJECTS 
WHERE OWNER = 'SYS' AND OBJECT_NAME = 'RDS_X$KGLOB';

OWNER                          OBJECT_NAME                    STATUS
------------------------------ ------------------------------ ------------------------------
SYS                            RDS_X$KGLOB                    VALID

Listing SYS.RDS_X$ views

To list existing RDS_X$ views, use the RDS procedure rdsadmin.rdsadmin_util.list_created_sys_x$_views. The procedure lists only views that were created by the procedure create_sys_x$_view. The following example lists X$ tables that have corresponding RDS_X$ views (sample output included).

SQL> SET SERVEROUTPUT ON
SQL> COL XD_TBL_NAME FORMAT A30
SQL> COL STATUS FORMAT A30
SQL> SET LINESIZE 200
SQL> SELECT * FROM TABLE(rdsadmin.rdsadmin_util.list_created_sys_x$_views);

XD_TBL_NAME                    STATUS
------------------------------ ------------------------------
X$BH                           VALID
X$K2GTE                        VALID
X$KCBWBPD                      VALID

3 rows selected.

Dropping RDS_X$ views

To drop a SYS.RDS_X$ view, use the RDS procedure rdsadmin.rdsadmin_util.drop_sys_x$_view. You can only drop views listed in the output of rdsadmin.rdsadmin_util.list_allowed_sys_x$_views. The drop_sys_x$_view procedure accepts the following parameter.

The following example drops the RDS_X$KGLOB view, which was created on the table X$KGLOB.

SQL> SET SERVEROUTPUT ON
SQL> EXEC rdsadmin.rdsadmin_util.drop_sys_x$_view('X$KGLOB');

PL/SQL procedure successfully completed.

The following example shows that the view SYS.RDS_X$KGLOB has been dropped (sample output included).

SQL> SET SERVEROUTPUT ON
SQL> COL OWNER FORMAT A30 
SQL> COL OBJECT_NAME FORMAT A30
SQL> COL STATUS FORMAT A30
SQL> SET LINESIZE 200
SQL> SELECT OWNER, OBJECT_NAME, STATUS 
FROM DBA_OBJECTS 
WHERE OWNER = 'SYS' AND OBJECT_NAME = 'RDS_X$KGLOB';

no rows selected

Granting privileges to non-master users

You can grant select privileges for many objects in the SYS schema by using the SELECT_CATALOG_ROLE role. The SELECT_CATALOG_ROLE role gives users SELECT privileges on data dictionary views. The following example grants the role SELECT_CATALOG_ROLE to a user named user1.

GRANT SELECT_CATALOG_ROLE TO user1;

You can grant EXECUTE privileges for many objects in the SYS schema by using the EXECUTE_CATALOG_ROLE role. The EXECUTE_CATALOG_ROLE role gives users EXECUTE privileges for packages and procedures in the data dictionary. The following example grants the role EXECUTE_CATALOG_ROLE to a user named user1.

GRANT EXECUTE_CATALOG_ROLE TO user1;

The following example gets the permissions that the roles SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE allow.

  SELECT * 
    FROM ROLE_TAB_PRIVS  
   WHERE ROLE IN ('SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE') 
ORDER BY ROLE, TABLE_NAME ASC;

The following example creates a non-master user named user1, grants the CREATE SESSION privilege, and grants the SELECT privilege on a database named sh.sales.

CREATE USER user1 IDENTIFIED BY PASSWORD;
GRANT CREATE SESSION TO user1;
GRANT SELECT ON sh.sales TO user1;

Creating custom functions to verify passwords

You can create a custom password verification function in the following ways:

The create_verify_function procedure

You can create a custom function to verify passwords by using the Amazon RDS procedure rdsadmin.rdsadmin_password_verify.create_verify_function. The create_verify_function procedure is supported for version 12.1.0.2.v5 and all higher major and minor versions of RDS for Oracle.

The create_verify_function procedure has the following parameters.

You can create multiple password verification functions.

There are restrictions on the name of your custom function. Your custom function can't have the same name as an existing system object. The name can be no more than 30 characters long. Also, the name must include one of the following strings: PASSWORD, VERIFY, COMPLEXITY, ENFORCE, or STRENGTH.

The following example creates a function named CUSTOM_PASSWORD_FUNCTION. The function requires that a password has at least 12 characters, 2 uppercase characters, 1 digit, and 1 special character, and that the password disallows the @ character.

begin
    rdsadmin.rdsadmin_password_verify.create_verify_function(
        p_verify_function_name => 'CUSTOM_PASSWORD_FUNCTION', 
        p_min_length           => 12, 
        p_min_uppercase        => 2, 
        p_min_digits           => 1, 
        p_min_special          => 1,
        p_disallow_at_sign     => true);
end;
/

To see the text of your verification function, query DBA_SOURCE. The following example gets the text of a custom password function named CUSTOM_PASSWORD_FUNCTION.

COL TEXT FORMAT a150

  SELECT TEXT 
    FROM DBA_SOURCE 
   WHERE OWNER = 'SYS' 
     AND NAME = 'CUSTOM_PASSWORD_FUNCTION' 
ORDER BY LINE;

To associate your verification function with a user profile, use alter profile. The following example associates a verification function with the DEFAULT user profile.

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION CUSTOM_PASSWORD_FUNCTION;

To see what user profiles are associated with what verification functions, query DBA_PROFILES. The following example gets the profiles that are associated with the custom verification function named CUSTOM_PASSWORD_FUNCTION.

SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD' AND LIMIT = 'CUSTOM_PASSWORD_FUNCTION';


PROFILE                    RESOURCE_NAME                     RESOURCE  LIMIT
-------------------------  --------------------------------  --------  ------------------------
DEFAULT                    PASSWORD_VERIFY_FUNCTION          PASSWORD  CUSTOM_PASSWORD_FUNCTION

The following example gets all profiles and the password verification functions that they are associated with.

SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION';

PROFILE                    RESOURCE_NAME                     RESOURCE  LIMIT
-------------------------  --------------------------------  --------  ------------------------
DEFAULT                    PASSWORD_VERIFY_FUNCTION          PASSWORD  CUSTOM_PASSWORD_FUNCTION
RDSADMIN                   PASSWORD_VERIFY_FUNCTION          PASSWORD  NULL

The create_passthrough_verify_fcn procedure

The create_passthrough_verify_fcn procedure is supported for version 12.1.0.2.v7 and all higher major and minor versions of RDS for Oracle.

You can create a custom function to verify passwords by using the Amazon RDS procedure rdsadmin.rdsadmin_password_verify.create_passthrough_verify_fcn. The create_passthrough_verify_fcn procedure has the following parameters.

The following example creates a password verification function that uses the logic from the function named PASSWORD_LOGIC_EXTRA_STRONG.

begin
    rdsadmin.rdsadmin_password_verify.create_passthrough_verify_fcn(
        p_verify_function_name => 'CUSTOM_PASSWORD_FUNCTION', 
        p_target_owner         => 'TEST_USER',
        p_target_function_name => 'PASSWORD_LOGIC_EXTRA_STRONG');
end;
/

To associate the verification function with a user profile, use alter profile. The following example associates the verification function with the DEFAULT user profile.

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION CUSTOM_PASSWORD_FUNCTION;

Setting up a custom DNS server

Amazon RDS supports outbound network access on your DB instances running Oracle. For more information about outbound network access, including prerequisites, see Configuring UTL_HTTP access using certificates and an Oracle wallet.

Amazon RDS Oracle allows Domain Name Service (DNS) resolution from a custom DNS server owned by the customer. You can resolve only fully qualified domain names from your Amazon RDS DB instance through your custom DNS server.

After you set up your custom DNS name server, it takes up to 30 minutes to propagate the changes to your DB instance. After the changes are propagated to your DB instance, all outbound network traffic requiring a DNS lookup queries your DNS server over port 53.

To set up a custom DNS server for your Amazon RDS for Oracle DB instance, do the following:

Setting and unsetting system diagnostic events

To set and unset diagnostic events at the session level, you can use the Oracle SQL statement ALTER SESSION SET EVENTS. However, to set events at the system level you can't use Oracle SQL. Instead, use the system event procedures in the rdsadmin.rdsadmin_util package. The system event procedures are available in the following engine versions:

para

Listing allowed system diagnostic events

To list the system events that you can set, use the Amazon RDS procedure rdsadmin.rdsadmin_util.list_allowed_system_events. This procedure accepts no parameters.

The following example lists all system events that you can set.

SET SERVEROUTPUT ON
EXEC rdsadmin.rdsadmin_util.list_allowed_system_events;

The following sample output lists event numbers and their descriptions. Use the Amazon RDS procedures set_system_event to set these events and unset_system_event to unset them.

604   - error occurred at recursive SQL level
942   - table or view does not exist
1401  - inserted value too large for column
1403  - no data found
1410  - invalid ROWID
1422  - exact fetch returns more than requested number of rows
1426  - numeric overflow
1427  - single-row subquery returns more than one row
1476  - divisor is equal to zero
1483  - invalid length for DATE or NUMBER bind variable
1489  - result of string concatenation is too long
1652  - unable to extend temp segment by  in tablespace
1858  - a non-numeric character was found where a numeric was expected
4031  - unable to allocate  bytes of shared memory ("","","","")
6502  - PL/SQL: numeric or value error
10027 - Specify Deadlock Trace Information to be Dumped
10046 - enable SQL statement timing
10053 - CBO Enable optimizer trace
10173 - Dynamic Sampling time-out error
10442 - enable trace of kst for ORA-01555 diagnostics
12008 - error in materialized view refresh path
12012 - error on auto execute of job
12504 - TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
14400 - inserted partition key does not map to any partition
31693 - Table data object  failed to load/unload and is being skipped due to error:

Setting system diagnostic events

To set a system event, use the Amazon RDS procedure rdsadmin.rdsadmin_util.set_system_event. You can only set events listed in the output of rdsadmin.rdsadmin_util.list_allowed_system_events. The set_system_event procedure accepts the following parameters.

The procedure set_system_event constructs and runs the required ALTER SYSTEM SET EVENTS statements according to the following principles:

The following example sets event 942 at level 3, and event 10442 at level 10. Sample output is included.

SQL> SET SERVEROUTPUT ON
SQL> EXEC rdsadmin.rdsadmin_util.set_system_event(942,3);
Setting system event 942 with: alter system set events '942 errorstack (3)'

PL/SQL procedure successfully completed.

SQL> EXEC rdsadmin.rdsadmin_util.set_system_event(10442,10);
Setting system event 10442 with: alter system set events '10442 level 10'

PL/SQL procedure successfully completed.

Listing system diagnostic events that are set

To list the system events that are currently set, use the Amazon RDS procedure rdsadmin.rdsadmin_util.list_set_system_events. This procedure reports only events set at system level by set_system_event.

The following example lists the active system events.

SET SERVEROUTPUT ON
EXEC rdsadmin.rdsadmin_util.list_set_system_events;

The following sample output shows the list of events, the event type, the level at which the events are currently set, and the time when the event was set.

942 errorstack (3) - set at 2020-11-03 11:42:27
10442 level 10 - set at 2020-11-03 11:42:41

PL/SQL procedure successfully completed.

Unsetting system diagnostic events

To unset a system event, use the Amazon RDS procedure rdsadmin.rdsadmin_util.unset_system_event. You can only unset events listed in the output of rdsadmin.rdsadmin_util.list_allowed_system_events. The unset_system_event procedure accepts the following parameter.

The following example unsets events 942 and 10442. Sample output is included.

SQL> SET SERVEROUTPUT ON
SQL> EXEC rdsadmin.rdsadmin_util.unset_system_event(942);
Unsetting system event 942 with: alter system set events '942 off'

PL/SQL procedure successfully completed.

SQL> EXEC rdsadmin.rdsadmin_util.unset_system_event(10442);
Unsetting system event 10442 with: alter system set events '10442 off'

PL/SQL procedure successfully completed.