Wednesday, November 28, 2012

Importing the scott schema in Oracle on Amazon RDS

Oracle Amazon RDS doesn't allow you to connect as sysdba, and you don't have access to the local file system, so you can't run the rdbms/admin/scott.sql script, as you would otherwise do. Instead:
  1. Download the demo scripts
  2. As master user:
    1. Change password of the scott user1: alter user scott identified by password ;
    2. Grant some rights to scott:
      1. grant connect to scott;
      2. grant create table to scott;
      3. grant execute any type to scott;
      4. grant unlimited tablespace to scott;
      5. grant create any trigger to scott;
  3. Connect as scott/password
  4. Run SQL in scott.sql

1 The scott user exists by default in Oracle RDS instances, but I am not sure what the password is out of the box. (It isn't tiger.)