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.

How to create a startup-trigger

In this example, I needed to write a simple trigger that opens the wallet upon startup of the instance:

create or replace trigger
   open_wallet
after startup on database
begin
   execute immediate 'ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "mysecretpassword"';
end;
/

How to solve ORA-01031: insufficient privileges when creating a materialized view

At first, I got the following error from the Oracle server when exeuting the "CREATE MATERIALIZED VIEW" script:

where    tab2.year >   to_number(to_char(sysdate, 'YYYY')) - 4
                                       *
ERROR at line 36:
ORA-01031: insufficient privileges

Solution:
If you try to create a materialized view based on tables in a different schema, you need the privilege

GLOBAL QUERY REWRITE

as well as

CREATE TABLE
CREATE MATERIALIZED VIEW

Grant the privileges:

grant GLOBAL QUERY REWRITE to scott;
grant CREATE TABLE to scott;
grant CREATE MATERIALIZED VIEW to scott;

Verify your users privileges:

connect scott/tiger

select * from user_sys_privs;


USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EKSTERN                        CREATE SESSION                           NO
EKSTERN                        CREATE TABLE                             NO
EKSTERN                        CREATE MATERIALIZED VIEW                 NO
EKSTERN                        GLOBAL QUERY REWRITE                     NO

This should fix your ORA-01031 problems.

Source: Oracle Support "Create Local Materialized View with Query Rewrite Option Fails with ORA-1031 Insufficient Privileges" (Doc ID 1079983.6)