Year: 2017

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

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

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

Oracle Database Time Zone

Time Zone concept is critical to global transactions.

1) dbtimezone only shows the database creation time TZ offset from OS environment.
For AEST TZ, it could be either of the below depending on OS environment variable:

00:00
+10:00 ( non DST )
+11:00 ( DST )

SQL> select dbtimezone from dual;

DBTIME
------
+11:00


SQL> select created from v$database;

CREATED
-----------------
20161115-15:06:11    <------ DST

2) timezone file. For Australia, it needs to be greater than 14.

SQL> select * from v$timezone_file;

FILENAME              VERSION 
-------------------- ---------- 
timezlrg_18.dat       18

3) Database server time zone ( TIMESTAMP WITH TIME ZONE ):

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
-------------------------------------
24-MAY-17 04.12.15.769958 PM +10:00

4) Current session time zone :

SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
-------------------------------------------------
24-MAY-17 02.16.06.660376 PM +08:00 

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-------------------------------------------------
+08:00

5) Time zone example :

SQL> create table table_tz ( t1 TIMESTAMP WITH TIME ZONE, t2 TIMESTAMP WITH LOCAL TIME ZONE);

Table created.


SQL> insert into table_tz values (SYSTIMESTAMP,SYSTIMESTAMP);

1 row created.

SQL> select t1,t2 from table_tz ;

T1
----------------------------------------
T2
----------------------------------------
24-MAY-17 04.28.20.428944 PM +10:00
24-MAY-17 02.28.20.428944 PM

SQL> insert into table_tz values (LOCALTIMESTAMP,LOCALTIMESTAMP );

1 row created.

SQL> select t1,t2 from table_tz;

T1
-------------------------------------------------------------
T2
-------------------------------------------------------------
24-MAY-17 04.28.20.428944 PM +10:00
24-MAY-17 02.28.20.428944 PM

24-MAY-17 02.30.26.599416 PM +08:00
24-MAY-17 02.30.26.599416 PM

Change the ADR retention

It is a good practice to set proper SHORTP_POLICY & LONGP_POLICY for your environment. For example retention in TEST/DEV environment is shorter than in PROD.

The default retention for SHORTP_POLICY is 720 ( HOURS ) /30 days, LONGP_POLICY is 8760 ( HOURS ) or 1 year.  The details are described as below:

Attribute NameDescription
SHORTP_POLICYNumber of hours after which to purge ADR contents that have a short life. Default is 720 (30 days).A setting of 0 (zero) means that all contents that have a short life can be purged. The maximum setting is 35791394. If a value greater than 35791394 is specified, then this attribute is set to 0 (zero).

 


The ADR contents that have a short life include the following:

  • Trace files
  • Core dump files
  • Packaging information
LONGP_POLICYNumber of hours after which to purge ADR contents that have a long life. Default is 8760 (365 days).A setting of 0 (zero) means that all contents that have a long life can be purged. The maximum setting is 35791394. If a value greater than 35791394 is specified, then this attribute is set to 0 (zero).

 

The ADR contents that have a long life include the following:

  • Incident information
  • Incident dumps
  • Alert logs
SHORTP_POLICY & LONGP_POLICY definition

The following example illustrates how to change retention policies to 1 week ( 168 hours )  and 2 weeks ( 336 hours ) respectively:

oracle@racnode1:/u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace$ adrci

ADRCI: Release 12.1.0.2.0 - Production on Mon May 22 10:43:49 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show home
ADR Homes:
diag/rdbms/eactest/RACTEST1
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;

ADR Home = /u01/app/oracle/diag/rdbms/ractest/RACTEST1:
*************************************************************************exit

SHORTP_POLICY         LONGP_POLICY
-------------------- --------------------
720                   8760

1 rows fetched

adrci> set control (SHORTP_POLICY=168);
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;

ADR Home = /u01/app/oracle/diag/rdbms/ractest/RACTEST1:
*************************************************************************
SHORTP_POLICY         LONGP_POLICY
-------------------- --------------------
168                    8760

1 rows fetched

adrci> set control (LONGP_POLICY=336 );
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;

ADR Home = /u01/app/oracle/diag/rdbms/ractest/RACTEST1:
*************************************************************************
SHORTP_POLICY         LONGP_POLICY
-------------------- --------------------
168                   336

1 rows fetched


adrci> purge;
adrci>

To purge incident files older than 6 days ( 8640 MINUTES ) , you will need to prefer the following commands:

adrci> purge -age 8640 -type INCIDENT

Tablespace Usage History Report

Useful report to show tablespace daily usage and space usage increasing ratio.

Please make sure you are licensed to run sqls against DBA_HIST_*, like  dba_hist_tbspc_space_usage .

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS

NAME                            TYPE     VALUE
------------------------------- -------- -----------------------
control_management_pack_access  string   DIAGNOSTIC+TUNING

The length of the report depends on  AWR retention. Please refer to  “Change AWR Retention & Interval” for how to change AWR retention.

select THE_DATE,
       TABLESPACE,
       SIZE_IN_GB_TD,
       SIZE_IN_GB_YTD,
       SIZE_IN_GB_TD - SIZE_IN_GB_YTD  SIZE_IN_GB_INCREASED
from ( 
     select 
            to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') THE_DATE,
            tbl.name TABLESPACE,
            round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2) SIZE_IN_GB_TD,
            lag( round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2),1) over ( order by to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') ) SIZE_IN_GB_YTD 
     from 
          dba_hist_snapshot histsnap,
          dba_hist_tbspc_space_usage tblusage,
          v$tablespace tbl,
          v$parameter pmt
    where 
          histsnap.SNAP_ID = tblusage.SNAP_ID
      and tbl.ts# = tblusage.TABLESPACE_ID
      and tbl.name ='&TABLESPACENAME'
      and pmt.name ='db_block_size'
   group by 
          to_char( histsnap.begin_interval_time, 'YYYY-MM-DD'),
          tbl.name,
          pmt.value
 order by 
         to_char( histsnap.begin_interval_time, 'YYYY-MM-DD')
 );

Enter value for tablespacename: USERS
old 17: and tbl.name ='&TABLESPACENAME'
new 17: and tbl.name ='USERS'

THE_DATE   TABLESPACE SIZE_IN_GB_TD SIZE_IN_GB_YTD SIZE_IN_GB..ASED
---------- ---------- ------------- -------------- -----------------
2017-05-10 USERS        4.17
2017-05-11 USERS        4.17         4.17            0
2017-05-12 USERS        4.45         4.17             .28
2017-05-13 USERS        4.55         4.45             .1
2017-05-14 USERS        4.55         4.55            0
2017-05-15 USERS        4.56         4.55             .01
2017-05-16 USERS        4.84         4.56             .28
2017-05-17 USERS        4.96         4.84             .12
2017-05-18 USERS        5.14         4.96             .18
2017-05-19 USERS        5.14         5.14            0

10 rows selected.