Author Archives: james huang

ORA-02065: illegal option for ALTER SYSTEM

In an old 10g database, when tried 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, … Continue reading

Posted in GI and RAC, Configuration, ORA- ERRORS | Tagged , | Leave a comment

ORA-02158: invalid CREATE INDEX option

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. … Continue reading

Posted in ORA- ERRORS, oracle advanced compression | Tagged , , | Leave a comment

ORA-14451: unsupported feature with temporary table

It looks like temporary table can nor be compressed by using Oracle Advanced Compression Features in 12.1.0.2. SQL> alter session set current_schema=jamesh; SQL> CREATE GLOBAL TEMPORARY TABLE TEST_TBL ( id number, name varchar(20), CONSTRAINT TEST_TBL_PK PRIMARY KEY (id) ENABLE ) … Continue reading

Posted in ORA- ERRORS, oracle advanced compression | Tagged , | Leave a comment

ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

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 … Continue reading

Posted in ORA- ERRORS, SQL Scripts For DBA | Tagged , , , , , , , , , | Leave a comment

Oracle Database Time Zone

1) dbtimezone only shows the database creation time TZ offset from OS environment. For AEST, it could be either of the below depending on OS environment variable: 00:00 +10:00 ( non DST ) +11:00 ( DST ) SQL> select dbtimezone … Continue reading

Posted in Configuration, Globalization | Tagged , , , , , , , , , , , , | Leave a comment

Change the ADR retention

The default retention for SHORTP_POLICY is 720 ( HOURS ) /30 days, LONGP_POLICY is 8760 ( HOURS ) or 1 year.  The details are described below: Attribute Name Description SHORTP_POLICY Number of hours after which to purge ADR contents that have a short life. Default is … Continue reading

Posted in ADR | Tagged , , , , , , , , | Leave a comment

Tablespace Usage History Report

Please make sure you are licensed to run sqls against DBA_HIST_*, like  dba_hist_tbspc_space_usage tblusage. The length of the report depends on  AWR retention. Please refer to  “Change AWR Retention & Interval” for how to change AWR retention. SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS … Continue reading

Posted in AWR, SQL Scripts For DBA | Tagged , , , , , , , | Leave a comment

Change the ORMB DB User Password

After the DB user password is changed by DBA, or after the ORMB schema is refreshed from other environment and need keep the password of old one. The DB user password needs to be changed as per Oracle Doc. For … Continue reading

Posted in ORMB | Tagged , , , , , | Leave a comment

ORA-01758: table must be empty to add mandatory (NOT NULL) column

While adding NOT NULL column into a table, the below error occurred: SQL> Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12) NOT NULL; Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12) NOT NULL             * ERROR at line 1: ORA-01758: table must be empty … Continue reading

Posted in ORA- ERRORS | Tagged , | Leave a comment

ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

When importing a schema exported from one 12cR1 AL32UTF8 database to another database  with same AL32UTF8 characterset, this ORA -error was generated : ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT The error message clearly shows the problem data is from … Continue reading

Posted in DataPump, ORA- ERRORS | Tagged , , , , , , , , | Leave a comment