For changing common user password, it should be done in CDB root container ( CDB$ROOT ).
$ sqlplus sys@pdb1 as sysdba
SQL> alter user system identified by “PasswordNew”;
alter user system identified by “PasswordNew”
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers
SQL> show con_name
CON_NAME
————
PDB1
SQL> connect / as sysdba
Connected.
SQL> alter user system identified by “PasswordNew”;
User altered.
SQL> show con_name
CON_NAME
————
CDB$ROOT
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>
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
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
Recycle the sequence to start from minimum value by “alter sequence cycle”.
SQL> CREATE SEQUENCE test_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 10;
Sequence created.
SQL> select test_seq.currval from dual; select test_seq.currval from dual * ERROR at line 1: ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session SQL> select test_seq.nextval from dual; NEXTVAL ---------- 1 SQL> / NEXTVAL ---------- 2 ... .. . NEXTVAL ---------- 9 SQL> / NEXTVAL ---------- 10 SQL> / select test_seq.nextval from dual * ERROR at line 1: ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
1)Increase maximum value:
SQL> alter sequence test_seq maxvalue 15; Sequence altered. SQL> select test_seq.nextval from dual; NEXTVAL ---------- 11 .. . SQL> select test_seq.nextval from dual; NEXTVAL ---------- 14 SQL> select test_seq.nextval from dual; NEXTVAL ---------- 15 SQL> select test_seq.nextval from dual; select test_seq.nextval from dual * ERROR at line 1: ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
2)Increment by -1.
SQL> alter sequence test_seq increment by -1; Sequence altered. SQL> select test_seq.nextval from dual; NEXTVAL ---------- 14 SQL> select test_seq.nextval from dual; NEXTVAL ---------- 13 ... .. . SQL> select test_seq.nextval from dual; NEXTVAL ---------- 2 SQL> select test_seq.nextval from dual; NEXTVAL ---------- 1 SQL> select test_seq.nextval from dual; select test_seq.nextval from dual * ERROR at line 1: ORA-08004: sequence TEST_SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated
3) Recycle the sequence starting from minimum value ( 1 ) by:
alter sequence cycle
SQL>select test_seq.nextval from dual;
NEXTVAL
----------
15
SQL> /
select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
SQL> select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,
INCREMENT_BY,CACHE_SIZE,CYCLE_FLAG
from dba_sequences
where SEQUENCE_NAME='TEST_SEQ';
SEQUENCE_NAM MIN_VALUE MAX_VALUE INCREMENT_BY CACHE_SIZE C
------------ ---------- ---------- ------------ ---------- -
TEST_SEQ 1 15 1 20 N
SQL> alter sequence TEST_SEQ cycle cache 10;
Sequence altered.
SQL> select test_seq.nextval from dual ;
NEXTVAL
----------
1
Make DBA Life Easy 