XDB Installation Script catqm.sql is Failing With "ORA-00922: missing or invalid option" (Doc ID 1272686.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 9.2.0.3 to 11.2.0.2 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 02-Jan-2013***

Symptoms

You are manually installing XDB using the "catqm.sql" script. You have passed all the required parameters to the script correctly but it fails with following error -

SQL> @?/rdbms/admin/catqm.sql xdb sysaux temp YES
..
..
SQL> declare
2
3 SHARED_POOL_SIZE number := 6*1024*1024;
4 usrcnt number;
5 tbscomp varchar2(10);
6 spbytes number;
7 segmgmt varchar2(6);
8
9 e_user_exists exception;
10 e_comp_restbs exception;
11 e_comp_tmptbs exception;
12 e_low_sharedpool exception;
13 e_not_assm exception;
14
15 begin
16
17 /* check if XDB user already exists */
18 select count(*) into usrcnt from dba_users where username = 'XDB';
19 if usrcnt > 0 then
20 raise e_user_exists;
21 end if;
22
23 /* check if XDB tablespace is compressed */
24 select def_tab_compression into tbscomp from dba_tablespaces
25 where tablespace_name = upper('&res_tbs');
26 if tbscomp = 'ENABLED' then
27 raise e_comp_restbs;
28 end if;
29
30 select def_tab_compression into tbscomp from dba_tablespaces
31 where tablespace_name = upper('&temp_tbs');
32 if tbscomp = 'ENABLED' then
33 raise e_comp_tmptbs;
34 end if;
35
36 /* check shared pool size */
37 select bytes into spbytes from v$sgastat
38 where pool = 'shared pool' and name = 'free memory';
39 dbms_output.put_line ('spbytes ' || spbytes);
40 if spbytes < SHARED_POOL_SIZE then
41 raise e_low_sharedpool;
42 end if;
43
44 /* check if XDB tablespace is ASSM */
45 if (:usesecfiles = 'YES') then
46 select segment_space_management into segmgmt from dba_tablespaces
47 where tablespace_name = upper('&res_tbs');
48 if (segmgmt != 'AUTO') then
49 raise e_not_assm;
50 end if;
51 end if;
52
53 exception
54
55 when e_user_exists then
56 raise_application_error(-20000, 'XDB User already exists');
57 when e_comp_restbs then
58 raise_application_error(-20001, 'Compressed tablespace
&res_tbs' ||
59 ' cannot be used');
60 when e_comp_tmptbs then
61 raise_application_error(-20002, 'Compressed temporary
tablespace ' ||
62 '&temp_tbs cannot be used');
63 when e_low_sharedpool then
64 raise_application_error(-20003, 'Shared pool size too low');
65
66 when e_not_assm then
67 raise_application_error(-20004, 'Tablespace &res_tbs is not
ASSM ');
68
69 end;
70 /
declare
*
Error occurred at line 1.:
ORA-01403: no data found .
ORA-06512: Line24


SQL>
SQL> Rem Create XDB User.
SQL> create user xdb identified by &xdb_pass account lock password expire
2 default tablespace &res_tbs temporary tablespace &temp_tbs;
create user xdb identified by &xdb_pass account lock password expire
*
Error occurred at line 1.:
ORA-00922: missing or invalid option.

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