How to truncate a partition

ALTER TABLE MYTABLE TRUNCATE PARTITION P_2017_01;

For more details, check the documentation

How to work around ORA-12008 and ORA-01858 during an online redefinition

The following error occured during redefintion:

Move method (cons_use_pk or cons_use_rowid): cons_use_rowid
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.DBMS_REDEFINITION", line 75
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459
ORA-06512: at line 2

The reason:
One of the reasons why I wanted to redefine the table, was to make it partitioned.
One of the column in the interim table was therefore defined as DATE instead of VARCHAR2:

Original table:

CREATE TABLE SCOTT.MYTABLE
(
  SEQ_NUM              NUMBER Generated as Identity ( START WITH 9984 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) CONSTRAINT SYS_C0073650 NOT NULL,
  ENTRY_ID             VARCHAR2(50 BYTE)            NULL,
  ENAME                VARCHAR2(100 BYTE)           NULL,
  CREATED_DT           TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  ACTIVE_YEAR          INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  CONDITION            VARCHAR2(50 BYTE)            NULL,
  FN_ID                VARCHAR2(11 BYTE)            NULL
)
TABLESPACE USERS;

Interim table:

CREATE TABLE SCOTT.MYTABLE_INTERIM
(
  SEQ_NUM              NUMBER,
  ENTRY_ID             VARCHAR2(50 BYTE),
  ENAME                VARCHAR2(100 BYTE),
  CREATED_DT           TIMESTAMP(6),
  REVISION#            INTEGER,
  ACTIVE_YEAR          INTEGER,
  PERIOD               DATE,
  CONDITION            VARCHAR2(50 BYTE),
  FN_ID                VARCHAR2(11 BYTE)
)
PARTITION BY RANGE (PERIOD)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P_INIT VALUES LESS THAN (TO_DATE('2015-01', 'YYYY-MM'))
)
TABLESPACE USERS
;

The requirement from the Developers was that the partitions should be range partitioned on dates in the format 'YYYY-MM'.

When starting the online redefintion process, I hit the error at the top in this post.

I started to search for the root cause, and executed the following:

select seq_num,TO_DATE(PERIOD,'YYYY-MM') from Scott.mytable
fetch first 5 rows only;

which resulted in

ORA-01858: a non-numeric character was found where a numeric was expected

It turned out that the following SQL would return the data I wanted, in the correct format, defined with the correct data type:

select seq_num,to_date(to_char(TO_DATE(PERIOD),'YYYY-MM'),'YYYY-MM')
from Scott.mytable
fetch first 5 rows only;

This Expression had to go into the redefinition-command, like this:

begin
DBMS_REDEFINITION.start_redef_table(uname=>'EVENT',
orig_table=>'MYTABLE',
int_table=>'MYTABLE_INTERIM',
col_mapping =>'SEQ_NUM SEQ_NUM,ENTRY_ID ENTRY_ID,ENAME ENAME,CREATED_DT CREATED_DT,REVISION# REVISION#,ACTIVE_YEAR ACTIVE_YEAR,to_date(to_char(TO_DATE(PERIOD),''YYYY-MM''),''YYYY-MM'') PERIOD,CONDITION CONDITION,FN_ID FN_ID',
options_flag=>dbms_redefinition.cons_use_pk);
end;
/

After this change, the redefinition executed successfully.

How to create a logon trigger

In this particular example, I create a logon trigger that sets one of many available NLS session parameters, as well as sets an undocumentet parameter that I was recommended to set by Oracle Support services:

CREATE OR REPLACE TRIGGER logon_optimizer 
after logon on database
begin 
   if user in ('SCOTT','JACK','BOB')
   then 
      execute immediate 'alter session set NLS_LANGUAGE="GERMAN"';
      execute immediate 'alter session set "_optimizer_unnest_disjunctive_subq"= FALSE';
   end if; 
end;
/

Test to verify that it Works:

Connect scott/tiger
set lines 200
col parameter format a30
col value format a40
select * from nls_session_parameters;


PARAMETER                      VALUE
------------------------------ -----------
NLS_LANGUAGE                   GERMAN
NLS_TERRITORY                  NORWAY

Now generate an error:

select * from xxx
              *
ERROR at line 1:
ORA-00942: Tabelle oder View nicht vorhanden

How to generate CREATE TABLESPACE statements for both encrypted and non-encrypted tablespaces

I used the following statement when I was supposed to pre-create encrypted tablespaces as part of a migration project. We were supposed to move a database using TDE from one server to another, from AIX to Linux. The database wasn't big so import could be used.

The statement will pre-create all tablespaces as they were in the old database with the exception of SYSTEM, SYSAUX etc.

SELECT 'create bigfile tablespace ' ||T.NAME || ' datafile ''/u02/oradata/proddb01' || LOWER(T.NAME) || '.dbf'' size 256M autoextend on next 128M maxsize unlimited extent management local segment space management auto encryption using ''' || E.ENCRYPTIONALG || ''' default storage(encrypt);' 
FROM  V$TABLESPACE T, V$ENCRYPTED_TABLESPACES E
WHERE T.TS# = E.TS#
UNION
SELECT 'create bigfile tablespace ' ||T.NAME || ' datafile ''/u02/oradata/proddb01' || LOWER(F.NAME) || ''' size 256M autoextend on next 128M maxsize unlimited extent management local segment space management auto;' 
FROM  V$TABLESPACE T, V$DATAFILE F
WHERE T.TS# = F.TS#
AND T.NAME NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','USERS')
AND F.TS# NOT IN (SELECT TS# FROM V$ENCRYPTED_TABLESPACES);

How to find encrypted tablespaces

SELECT t.name, NVL(e.encryptionalg, 'Not encrypted') algorithm
FROM  v$tablespace t LEFT OUTER JOIN  v$encrypted_tablespaces e
ON(  t.ts# = e.ts# )
WHERE t.name not in ('SYSTEM','SYSAUX','UNDOTBS1','USERS');

To generate "CREATE TABLESPACE" statements for the tablespaces, see this post

How to enable incremental statistics gathering for a large partitioned table

What is incremental statistics?

It's a way to improve performance of global statisics gathering on large partitioned tables.

Incremental statistics allows the database to scan the table once to gather partition statistics and from there to derive global statistics by aggregating partition-level statistics.

During later statistics gathering, the database only needs to scan stale partitions and then update their statistics.
From there the database can derive global statistics from the fresh partition statistics, which in turn saves a full table scan.

To view the current settings:

SQL> select dbms_Stats.get_prefs('INCREMENTAL','SCOTT','EMP') "paramvalue" from dual;

paramvalue
--------------------
FALSE

To change the setting:

SQL> exec dbms_stats.set_table_prefs(ownname=>'SCOTT',tabname=>'EMP',pname=>'INCREMENTAL',pvalue=>'TRUE');

Verify:

SQL> select dbms_Stats.get_prefs('INCREMENTAL','SCOTT','EMP') "paramvalue" from dual;

paramvalue
--------------------
TRUE

For more information about incremental statistics and how it can benefit large table, read this from the Oracle Documentation

How to find the NLS_LENGTH_SEMANTICS used for your PL/SQL objects

Use the view DBA_PLSQL_OBJECT_SETTINGS:

SELECT OWNER,NAME,TYPE,NLS_LENGTH_SEMANTICS
FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE OWNER IN ('USER1','USER2');

Join with DBA_OBJECTS for creation time and last ddl time etc:

SELECT POS.OWNER,POS.NAME,POS.TYPE,POS.NLS_LENGTH_SEMANTICS, O.LAST_DDL_TIME,O.CREATED, O.STATUS
FROM DBA_PLSQL_OBJECT_SETTINGS POS JOIN DBA_OBJECTS O
ON (POS.NAME = O.OBJECT_NAME)
AND POS.OWNER = 'SCOTT'
ORDER BY NAME;

ORA-01775: Looping chain of synonyms during repository installation

During installation of a repository for an Oracle tool, the following error occurs

Select distinct mrc_name from schema_version_registry;

Select distinct mrc_name from schema_version_registry
                              *
ERROR on line 1:
ORA-01775: Looping chain of synonyms

Cause: There was only a synonym present in the database, and no underlying base object:

SQL> SELECT owner, SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from DBA_SYNONYMS where table_name='SCHEMA_VERSION_REGISTRY'

OWNER                SYNONYM_NAME               TABLE_OWNER          TABLE_NAME
-------------------- -------------------------- -------------------- ----------------------------------------
PUBLIC               SCHEMA_VERSION_REGISTRY    SYSTEM               SCHEMA_VERSION_REGISTRY

Solution is to drop the public synonym:

SQL> drop public synonym SCHEMA_VERSION_REGISTRY;

Synonym dropped.

Source: ORA-01775 or ORA-980 from Public Synonym when Base Table is Missing (Doc ID 392705.1)