This is the create user statement to successfully create an Oracle 10g user for a content domain in SR2.

CREATE USER "PEPSI"  PROFILE "DEFAULT"
    IDENTIFIED BY "coke" DEFAULT TABLESPACE "USERS"
    ACCOUNT UNLOCK;
GRANT "CONNECT" TO "PEPSI";
GRANT "RESOURCE" TO "PEPSI";
GRANT CREATE TABLE TO "PEPSI";
GRANT CREATE VIEW TO "PEPSI";
ALTER USER PEPSI QUOTA UNLIMITED ON USERS