How to use yum to list installed packages

yum list package_name

For example:

[root@myserver ~]# yum list compat-libcap1
Loaded plugins: product-id, rhnplugin, search-disabled-repos, security
This system is receiving updates from RHN Classic or RHN Satellite.
Installed Packages
compat-libcap1.x86_64                                                   1.10-1                                                   @test-rhel-x86_64-server-6
Available Packages
compat-libcap1.i686                                                     1.10-1    

See also "yum command cheat sheet" from RH for a good overview.

How to clear your bash shell's history

history -c && history -w

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