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)

How to import only users over a network link

Use the following parameter file to import only the user definitions from your source database to your test database:
Remember that the database link proddb01 must be correctly defined and working before you import the data.

userid='system/yourpassword
network_link=proddb1.mydomain.com
logfile=DATA_PUMP_DIR:user_definitions.log
full=Y
include=USER

To import a specific user only:

INCLUDE=USER:"='SCOTT'"

To include a specific user and remap it to another user name during import:

REMAP_SCHEMA=SCOTT:JIM
INCLUDE=USER:"='SCOTT'"

Remember to check if the SCOTT user is assigned a default tablespace that doesn't exist in the target database.
If this is the case, you need to use remap_schema directive to make it work:

REMAP_TABLESPACE=SCOTT_DATA:JIM_DATA

... or potentiall pre-create the tablespace SCOTT_DATA in the target database, but this may not be what you wish.