Month: June 2017

ORA-02065: illegal option for ALTER SYSTEM

For 10.2.0.5, set up parameter value by :
alter system set sga_target= scope= sid=;

In 10g database, when trying to change SGA_TARGET parameter in spfile, “ORA-02065: illegal option for ALTER SYSTEM” occurred.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jun 13 15:56:37 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter sga_target

NAME_COL_PLUS_SHOW_PARAM TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------ ----------- --------------------------
sga_target               big integer 4G

SQL> alter system set sga_target=2g sid='RACTEST1' scope=spfile;
 alter system set sga_target=2g sid='RACTEST1' scope=spfile
 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

By swapping the order of “sid” and scope, it is working fine:

SQL> alter system set sga_target=2g scope=spfile sid='RACTEST1';

System altered.

SQL>

Advertisement

ORA-02158: invalid CREATE INDEX option

It seems Oracle Advanced Compression Feature does not apply to IOT tables.

Tried to apply Oracle Advanced Compression Feature onto IOT tables in 12.1.0.2, then got this error.

SQL> CREATE TABLE TEST_IOT_TBL
          ( id   number,
            sex  char(1),
           name  varchar2(20),
           CONSTRAINT TEST_IOT_TBL_PK PRIMARY KEY (id,sex ) ENABLE
         ) ORGANIZATION INDEX ;

Table created.

SQL> insert into TEST_IOT_TBL values ( 1,'M','test1');

1 row created.

SQL> insert into TEST_IOT_TBL values (2,'M','test2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table TEST_IOT_TBL move compress for all operations;
 alter table TEST_IOT_TBL move compress for all operations
 *
ERROR at line 1:
ORA-02158: invalid CREATE INDEX option

ORA-14451: unsupported feature with temporary table

Temporary table cannot be compressed by using Oracle Advanced Compression.

It looks like temporary table can not be compressed by using Oracle Advanced Compression Feature in 12.1.0.2.

SQL> alter session set current_schema=testuser;

SQL> CREATE GLOBAL TEMPORARY TABLE TEST_TBL 
        ( id number, name varchar(20), 
          CONSTRAINT TEST_TBL_PK PRIMARY KEY (id) ENABLE 
        ) ON COMMIT DELETE ROWS;

Table created.

SQL> insert into TEST_TBL values ( 1,'testuser');

1 row created.

SQL> select * from TEST_TBL;

ID NAME
---------- --------------------
 1 testuser

SQL> alter table TEST_TBL move compress for all operations;
 alter table TEST_TBL move compress for all operations
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

SQL> commit;

Commit complete.

SQL> select * from TEST_TBL;

no rows selected