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 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

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
--
declare
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
10
11    select default_tablespace into l_ord_tbs
12    from sys.dba_users where username='ORDSYS';
13
14    begin
15      execute immediate
16         'create user orddata identified by orddata default tablespace '
17          || l_ord_tbs;
18      EXCEPTION
19      -- ORA-01920: user name 'ORDDATA' conflicts with another user
20      WHEN ex THEN
21        NULL;
22
23    end;
24
25    execute immediate
26     'alter user orddata account lock password expire';
27
28  end;
29  /
declare
*
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

 

Cause

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