Upgrade of Oracle Multimedia Failed with ORA-02155: Invalid DEFAULT Tablespace Identifier

(Doc ID 1461031.1)

Last updated on JANUARY 22, 2014

Applies to:

Oracle Multimedia - Version to [Release 10.2 to 11.2]
Information in this document applies to any platform.


The upgrade of Oracle Multimedia failed with the following error when creating the ORDDATA user:

ORA-02155: invalid DEFAULT tablespace identifier

Below is the statement which failed:

-- create ORDDATA user
2   l_ord_tbs  varchar2(30);
3   ex exception;
4   -- ignore error ORA-01920, happens if the script is re-run
5   -- ORA-01920: user name 'ORDDATA' conflicts with another user or role name
6   pragma exception_init(ex, -1920);
7  begin
8    -- create orddata user and assign privs
9    -- create ORDData user in the same tablespace ORDSYS
11    select default_tablespace into l_ord_tbs
12    from sys.dba_users where username='ORDSYS';
14    begin
15      execute immediate
16         'create user orddata identified by orddata default tablespace '
17          || l_ord_tbs;
19      -- ORA-01920: user name 'ORDDATA' conflicts with another user
20      WHEN ex THEN
21        NULL;
23    end;
25    execute immediate
26     'alter user orddata account lock password expire';
28  end;
29  /
ERROR at line 1:
ORA-02155: invalid DEFAULT tablespace identifier
ORA-06512: at line 15

Oracle Multimedia has the status UPGRADED and the following objects are invalid:

These objects fail to compile with the error below:
PL/SQL: ORA-00942: table or view does not exist



Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms