Simple sed-command for exchanging a string in multiple files

sed -i "s/oldstring/newstring/g" 

For example

sed -i "s/11204/12102/g" /etc/oratab

MS word characters that have no representation in WE8ISO8859P1 and WE8ISO8859P15

During a globalization effort, I found the following interesting information about the difference between the WE8MSWIN1252 and the WE8ISO8859P15 character set

* 27 codepoints are NOT defined/used in WE8ISO8859P15 but are filled in / used in WE8MSWIN1252

(note that the WE8MSWIN1252 codepoints

* 91 = U+2018 : LEFT SINGLE QUOTATION MARK
* 92 = U+2019 : RIGHT SINGLE QUOTATION MARK
* 93 = U+201C : LEFT DOUBLE QUOTATION MARK
* 94 = U+201D : RIGHT DOUBLE QUOTATION MARK

are the default quotation marks of the Microsoft Word product, so if you have data that comes from Microsoft Office products you *need* a WE8MSWIN1252 database characterset.

Make sure you clients NLS_LANG is also correct: WE8ISO8859P15 is *not* correct as NLS_LANG for windows clients
Note 179133.1 The correct NLS_LANG in a Windows Environment


A more common problem is that in an environment using English and West European or Latin American ( French, Spanish, Portuguese, Dutch, Italian,...) windows clients. a lot of setups use a NLS_LANG set to WE8ISO8859P15 on the client side. For windows systems this is not correct and provokes in most cases that there are actually WE8MSWIN1252 codes stored in the WE8ISO8859P15 database. The most commonly seen characters are the € symbol and these qoutes: ‘’“” - these are the 1252 "smart qoutes" used in Microsoft Office. They look similar to the "normal" US7ASCII qoute " in most fonts, but are different characters often and result in confusion. The Courrier New font for example distinct them quite good visibly.


So watch out for cut-n-paste errors based on MS Word documents! They often result in characters that have no representation under the most commonly used non-Unicode character set.

What are the two options XDK and XML in DBA_REGISTRY anyway?

What are the two options XDK and XML in DBA_REGISTRY anyway?

You see them amongst other components in the DBA_REGISTRY view:

select comp_id,comp_name, version,status from dba_registry;

COMP_ID       COMP_NAME          VERSION        STATUS
-------------------- ---------------------------------------- ------------------------------ --------------------
XDB       Oracle XML Database        12.1.0.2.0       VALID
XML       Oracle XDK          12.1.0.2.0       VALID

So what are they, and how are they connected?

The XDK documentation states:

"Oracle XML Developer's Kit (XDK) is a versatile set of components that enables you to build and deploy C, C++, and Java software programs that process Extensible Markup Language (XML)."

In other words, XDK is a toolbox for developers of XML-driven applications.

The XML DB documentation states:
"Oracle XML DB is a set of Oracle Database technologies related to high-performance handling of XML data...Oracle XML DB and the XMLType abstract data type make Oracle Database XML-aware. Storing XML data as an XMLType column or table lets the database perform XML-specific operations on the content."

In other words, XML DB is the framework for allowing XML to be stored and retrieved in the database. It has been a part of the Oracle RDBMS since version 9.2.

The XDK

"supports Oracle XML DB, which is a set of technologies used for storing and processing XML in Oracle Database."

With XDK and XML DB you can

"build applications that run in Oracle Database. You can also use XDK independently of Oracle XML DB.
XDK is fully supported by Oracle and comes with a commercial redistribution license. The standard installation of Oracle Database includes XDK."

How to remove a non-used option marked as INVALID in dba_registry

If you have invalid components in your database table dba_registry, and those options are not used, you should mark them as removed to avoid misunderstandings.

set lines 200
col comp_name format a50
col version format a20
col status format a20
col parameter format a30
col value format a30
select comp_id, comp_name,version,status
from dba_registry;


COMP_ID                        COMP_NAME                                          VERSION              STATUS
------------------------------ -------------------------------------------------- -------------------- --------------------
RAC                            Oracle Real Application Clusters                   11.2.0.4.0           INVALID

16 rows selected.

Verify with v$option:

select value from v$option where parameter = 'Real Application Clusters';

VALUE
------
FALSE

To remove, execute

 exec dbms_registry.removed('RAC');

Registry is now updated:

COMP_ID                        COMP_NAME                                          VERSION              STATUS
------------------------------ -------------------------------------------------- -------------------- --------------------
RAC                            Oracle Real Application Clusters                   11.2.0.4.0           REMOVED

16 rows selected.

How to fix error ORA-04063: package body "OLAPSYS.CWM2_OLAP_METADATA_REFRESH" has errors when installing OLAP into a 11g database

You are installing OLAP.
You receive the following error at the end of the olap.sql script:

SQL> @?/olap/admin/olap.sql SYSAUX TEMP;

SQL> execute cwm2_olap_metadata_refresh.mr_refresh;
BEGIN cwm2_olap_metadata_refresh.mr_refresh; END;

*
ERROR at line 1:
ORA-04063: package body "OLAPSYS.CWM2_OLAP_METADATA_REFRESH" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"OLAPSYS.CWM2_OLAP_METADATA_REFRESH"
ORA-06512: at line 1

Solution:
Drop the OLAPSYS user and rerun the script:

$ sqlplus '/ as sysdba'
SQL> drop user OLAPSYS cascade
SQL> @?/olap/admin/olap.sql SYSAUX TEMP;
SQL> @?/rdbms/admin/utlrp.sql

Sources: Doc ID 726253.1 "Errors: ORA-04063, ORA-06508, ORA-06512 When Adding OLAP Using the Script 'olap.sql SYSAUX TEMP;'"

How to add a line feed to your spool file

If you want to spool out a file from you sqlplus session, and need a line feed in your string, you can use the function

char(int)

to concatenate a line feed into your string. Integer represents the decimal value of the character you'd like to send to the database.

Example:

set trimspool on
set lines 200
set pages 0
set heading off
set verify off
set echo off
set feedback off
spool rowcount.sql
select 'spool rowcount.out' from dual;
select 'prompt rowcount for ' || owner || '.' || table_name || chr(10)|| 'select count(*) from ' || owner || '.'  || table_name || ';'
from dba_tables
where owner='SCOTT';
select 'exit' from dual;
exit

The chr(10) will return a line feed (may not be apparent in the code below but there is a line feed in there):

SYS@fsprod SQL> select chr(10) from dual;

C
-

The chr(10) corresponds to the hexadecimal value of A, which in turns corresponds to a line feed under the characterset that I happen to use in this database, which is WE8ISO8859P1.


The result is a file which looks like this:

spool rowcount.out
prompt rowcount for SCOTT.EMP
select count(*) from SCOTT.EMP;

prompt rowcount for SCOTT.DEPT
select count(*) from SCOTT.DEPT;

prompt rowcount for SCOTT.BONUS
select count(*) from SCOTT.BONUS;
.
.
.
exit

Sources: ISO code pages

How to check for existence of and installation of Data Mining in the database,

In this short article, I am following the document "How To Manually Install Data Mining In Oracle 11g? (Doc ID 818314.1)"

select * from v$option where PARAMETER = 'Data Mining';

PARAMETER     VALUE
------------  ------------
Data Mining   FALSE

In my case, Data Mining is not installed. Data Mining files need to be installed and the Oracle executable needs to be linked with Data Mining in the Oracle Home.

Oracle states some important facts about Data Mining in 11g:

"As per the overview of changes of Data Mining in 11g , there are some substantial changes with the Data Mining option in 11g"

* Oracle Data Mining 11gRelease 1 (11.1) has a tight integration with Oracle Database. Data Mining metadata and PL/SQL packages have been migrated from DMSYS to SYS. The DMSYS schema no longer exists in Oracle Database 11g Release 1 (11.1) fresh installations.
* If the database has been upgraded from earlier version, then DMSYS schema could be there, in which case the Document Data Mining Adminstration Guide should be followed the relevant steps to migrate data to SYS schema and then drop DMSYS schema.
* There is no longer an 'odm' directory in the ORACLE_HOME, and no entry for Data Mining in the DBA_REGISTRY but there is a value in V$OPTION (as displayed above)

To enable Data Mining:

1. Shut down all oracle processes running out of your Oracle_home (db, listener)
2. relink the oracle executable

chopt enable dm

Writing to /u01/oracle/product/11204/install/enable_dm.log...
/usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk dm_on ORACLE_HOME=/u01/oracle/product/11204
/usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/oracle/product/11204

3. Start up the database again
4. cd $ORACLE_HOME/rdbms/admin
5. Run the necessary files to finish the installation of data mining:

sqlplus / as sysdba
SQL>spool enable_dm.log
SQL>@catodm.sql
SQL>@dbmsodm.sql
SQL>@prvtodm.plb

6. Confirm that Data Mining is indeed installed:

select * from v$option where PARAMETER = 'Data Mining';

PARAMETER     VALUE
------------  ------------
Data Mining   TRUE