CODEWITHSUNDEEP
oracle-databaseoracle11gpermissionspermission-deniedaws-dms
Drewry Pope
Drewry Pope

Reputation: 327

In Oracle 11g, what permissions would I need to `SELECT * FROM V$TRANSPORTABLE_PLATFORM;`?

I am using AWS DMS to migrate an on-site Oracle 11g database to an Amazon RDS for Oracle DB instance of the same major version. I recently received this error:

2019-11-27T18:54:08 [SOURCE_CAPTURE ]E: Cannot execute SQL statement 'select tp.endian_format, tp.platform_name from v$database d, v$transportable_platform tp where d.platform_id = tp.platform_id'. OCI status '-1' [1020401] (oradcdc_redoutil.c:976)
2019-11-27T18:54:08 [TASK_MANAGER ]E: OCI error 'ORA-00942: table or view does not exist'; Cannot execute SQL statement 'select tp.endian_format, tp.platform_name from v$database d, v$transportable_platform tp where d.platform_id = tp.platform_id'. OCI status '-1'; Error executing source loop; Stream component failed at subtask 0, component st_0_XGQVEPQ6RSAXZT44XOZF7ERABI ; Stream component 's

My normal administrative schema can SELECT * FROM V$TRANSPORTABLE_PLATFORM; without issue, however, my new 'migration' schema cannot. Both have SELECT ANY TABLE privileges which, as described in Grant Select on All VIEWS [current and future] in Schema X, will allow the user to query any table or view in any schema in the database.

What permission am I missing in my migration schema? How does V$TRANSPORTABLE_PLATFORM differ from table/view objects in the database? What query could I run in order to obtain the type of V$TRANSPORTABLE_PLATFORM or any other database object?

Upvotes: 1

Views: 406

Answers (1)

BLaZuRE
BLaZuRE

Reputation: 2406

A few things you may want to try:

  • Grant SELECT_CATALOG_ROLE to allow SELECT on system views
  • Add a manual GRANT to V_$TRANSPORTABLE_PLATFORM since V$TRANSPORTABLE_PLATFORM will reference that table
  • Ensure the schema is not ambiguous by adding SYS.

Upvotes: 1

Related Questions