How to solve the problem with ORA-00997: illegal use of LONG datatype when quering the dba_tab_partitions

If you need to selectively find partitions based on their upper-bound value, like this:

select partition_name,high_value
from dba_tab_partitions
where table_name='MYTABLE'
and high_value='201805';

you will run into the following error:

ORA-00997: illegal use of LONG datatype

One solution I found was to create a temporary table, like this:

CREATE GLOBAL TEMPORARY TABLE PARTITION_HIGH_VAL
ON COMMIT PRESERVE ROWS
AS
select 
     PARTITION_NAME,
     to_lob(high_value) as text_clob
     from dba_tab_partitions 
     where TABLE_OWNER='SH'
     and table_name='SALES';

Then, use the table PARTITION_HIGH_VAL to view the upper bounds of each partition:

select PARTITION_NAME,TO_CHAR(TEXT_CLOB) "high value" 
from partition_high_Val;

PARTITION_NAME	high value
SYS_P79451	201806
SYS_P71617	201805
SYS_P70243	201803
SYS_P70231	201804
SYS_P70112	201802

Since this is a temporary table, your rows will be deleted upon your session exit because of the directive ON COMMIT PRESERVE ROWS.
Obviously, you could create a heap-organized table to store these results in, too, if you need to store your data for a longer period.

How to setup Oracle Network Integrity

Encryption will protect your data from being read in clear text, when in transition.
However, data can still be tampered with and arrive modified at the receiver's end.

To protect against modification, you need to ensure the integrity of the data being sent over the network.

Data integrity protection from Oracle works independently from the encryption process. In other words, you can use both encryption and checksuming together or either one separately.

To implement network integrity, add the following to your server’s sqlnet.ora file:

SQLNET.CRYPTO_CHECKSUM_SERVER = requested
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (SHA256, SHA384, SHA512, SHA1, MD5)

All of the hash algorithms in the list create a checksum that changes if the data is altered in any way.

The value "requested" in the CRYPTO_CHECKSUM_SERVER instructs the Oracle server that calculating checksums when sending network packets to the client is desirable, but not required. Other valid settings are "required", "accepted" and "rejected". See the documentation for details.

The list of available encryption algorithms are stated in the CRYPTO_CHECKSUM_TYPE_SERVER, and the first one common to both client and server in the list will be selected. During my tests, the SHA256 was selected.

More information is to be found in the official documentation

How to create a materialized view with query rewrite enabled

In this example, I am creating a materialized view to support queries against a table with 20 million rows, which are looking at the maximum sequence number within a range of dates.

Oracle describes the benefits of Materialized views like this:

"One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables."

Since I want the materialized view to be refreshed every time a new row is commited to the base table, I need to specify that the mview should use "fast refresh".

First, create a materialized view log to store changed rows in the base tables that your materialized views are built on. This is required when using FAST REFRESH:

CREATE MATERIALIZED VIEW LOG ON DATE_TO_SEQNO
TABLESPACE DATA1
CACHE
ENABLE ROW MOVEMENT
WITH ROWID (SEQNO, RDATE)
INCLUDING NEW VALUES;

Then, create the materialized view. Query rewrites are disabled by default, so it must be explicitly stated:

CREATE MATERIALIZED VIEW SEQ_AGGR_INFO
SEGMENT CREATION IMMEDIATE
TABLESPACE DATA1
CACHE
ENABLE ROW MOVEMENT
ROW STORE COMPRESS ADVANCED
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
select RDATE, count(SEQNO) as antall, max(SEQNO) as makssekvens
from DATE_TO_SEQNO
group by dato
order by 1, 2;

Verify the mview's existence:

SELECT mview_name,rewrite_enabled,refresh_mode,refresh_method, last_refresh_type, last_refresh_date,last_refresh_end_time,staleness,compile_state
FROM USER_MVIEWS;

MVIEW_NAME	REWRITE_ENABLED	REFRESH_MODE	REFRESH_METHOD	LAST_REFRESH_TYPE	LAST_REFRESH_DATE	LAST_REFRESH_END_TIME	STALENESS	COMPILE_STATE
SEQ_AGGR_INFO	Y	COMMIT	FAST	COMPLETE	18.01.2019 13:58:24	18.01.2019 13:58:24	FRESH	VALID

Verify the mview log's existence:

SELECT master,log_table,rowids, primary_key,filter_columns, include_new_values
FROM USER_MVIEW_LOGS;

MASTER	LOG_TABLE	ROWIDS	PRIMARY_KEY	FILTER_COLUMNS	INCLUDE_NEW_VALUES
DATE_TO_SEQNO	MLOG$_DATE_TO_SEQNO	YES	NO	YES	YES

The following query will now use the materialized view, due to the QUERY REWRITE directive used in the creation of the mview:

var B1 VARCHAR2(10);
EXEC :B1 := to_date('01.01.2018','dd.mm.yyyy');
set lines 200
set autot on
set timing on
select max(seqno)
from DATE_TO_SEQNO
where rdate = :B1;

The explain plan verifies it:

MAX(SEKVENSNUMMER)
------------------
          18580449

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1856567295

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |     1 |    14 |   243   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                        |     1 |    14 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| SEQ_AGGR_INFO          |     1 |    14 |   243   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SEQ_AGGR_INFO"."RDATE"=:B1)

To drop the materialized view and its log object:

drop materialized view log on DATE_TO_SEQNO;
drop materialized view SEQ_AGGR_INFO;

Sources:
Create Materialized View Log

Create Materialized view

Datawarehousing guide

How to generate a script to rebuild unusable indexes

set trimspool on
set lines 200
set pages 0
set heading off
set verify off
set feedback off
set echo off
spool rebuild_stmts.sql

select 'spool rebuild_stmts.log' from dual;
SELECT 'alter index ' || idx.table_owner || '.' || idx.index_name || ' rebuild ' || ' ONLINE;'
FROM dba_indexes idx
where idx.status = 'UNUSABLE'
ORDER BY idx.table_owner, idx.index_name
/
select 'exit' from dual;

exit

How to find the version of postgres installed on a server

$ su - postgres
Last login: Tue Dec 18 23:03:07 CET 2018
$ pg_config --version
PostgreSQL 11.1

Using psql:

[postgres@til0drift-dbteam-sandbox-pgsql01 ~]$ psql
psql (11.2)
Type "help" for help.

postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

How to send a file to a specific directory using wget

Here demonstrated while getting a file from our RH satellite server:

wget -P /home/oracle/myfiles http://satellite/oracle/cloningscripts/post_clone.sql

If the directory /home/oracle/mfiles is not present, it will be created.

How to create the SCOTT user

To create the user SCOTT, run the script

$ORACLE_HOME/rdbms/admin/utlsampl.sql

Observe that user SCOTT will be created with the following statement:

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;

Note that if the default profile is using a password verification function, the creation will fail.

To work around, exchange default password "tiger" with something different that will honor the verification function, for example "T123G456R".

Remember to set

SET TERMOUT ON
SET ECHO ON

at the top, if you want to see the script output.