AWS's Top 5 Commands: ALTER SYSTEM and Managing SYS Objects in RDS

1 min read

Jan 8, 2020 12:00:00 AM

We often manage services over EC2, this is not news. But when migrating to Amazon RDS, it's usual for new users to have issues with performing some administrative actions on the database, especially when it's done via internal Oracle procedures or when it requires privileges beyond the ones we have available on RDS credentials, even though they are part of DBA work. Amazon Web Services created internal procedures so we can still execute those actions, but in a different manner. I decided to list five daily basis commands / procedures that frequently cause questions for initial users. Here it goes: Kill sessions:

begin
 rdsadmin.rdsadmin_util.kill(
 sid => &sid,
 serial => &serial,
 method => 'IMMEDIATE');
 end;
 /

Flush shared_pool or buffer_cache:

exec rdsadmin.rdsadmin_util.flush_shared_pool;
 exec rdsadmin.rdsadmin_util.flush_buffer_cache;

Grant Privileges to SYS Objects: # Grant

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

# Grant with Grant Option

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

# Revoke

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

Hope this helps!

Database Administrator (DBA) Services

Are you ready to save up to 60% in operational costs?

Speak with our expert DBAs ->

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

AWS's Top 5 Commands: ALTER SYSTEM and Managing SYS Objects in RDS

Database Administrator (DBA) Services

Share this

Share this

More resources

AWS RDS: 5 Must-Know Actions for Oracle DBAs

Select vs Assign – How To Assign PL/SQL Variables

Tuning Pack 11g : Real-Time SQL Monitoring

Ready to unlock value from your data?