How to use the MERGE statement with a subquery

Here is an example on how to use the MERGE statement to update a table, using a self-referencing subquery. One of many ways to clean up unstructured data

Tables look as follows:

MAJOR	COURSE	CLASS	CREDITS	ROOM_NO	TEACHER
Nuclear Science	Physics A	A. Brown, Room 208	8
Nuclear Science	Mathematics A	M. Stevens, Room 56	8
Nuclear Science	Advanced Algebra	K.Wise, Audit. 20	10
English Literature	Ex. Phil	A.Peterson, Room 208	8
English Literature	Early British Lit	A.Peterson, Room 208	8
English Literature	Greek Mythology	D.Simmons, Room 206	10
Nuclear Science	Physics A	Goodman, Room 208	8

To populate the ROOM_NO and TEACHER columns, I used the following query:

MERGE INTO COURSE_CATALOG A
USING 
(SELECT CLASS FROM COURSE_CATALOG) B
ON (A.ROWID = B.ROWID)
WHEN MATCHED THEN
    UPDATE SET A.TEACHER = REGEXP_REPLACE(REGEXP_SUBSTR(CLASS, '[[:alpha:]]+.{1,}[^[:digit:]]',1,1,'i'), ', Room |, Audit.', ''),
               A.ROOM_NO = REGEXP_SUBSTR(B.CLASS,'[[:digit:]]+'),
               A.CLASS = NULL
               
;
COMMIT;

Result:

MAJOR	COURSE	CLASS	CREDITS	ROOM_NO	TEACHER
Nuclear Science	Physics A		8	208	A. Brown
Nuclear Science	Mathematics A		8	56	M. Stevens
Nuclear Science	Advanced Algebra		10	20	K.Wise
English Literature	Ex. Phil		8	208	A.Peterson
English Literature	Early British Lit		8	208	A.Peterson
English Literature	Greek Mythology		10	206	D.Simmons
Nuclear Science	Physics A		8	208	Goodman

How to deregister a database from a Recovery Catalog

Log on to the target database:

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 28 14:00:50 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
 305453026 PRODDB01

connect to the recovery catalog through sqlplus:

sqlplus uid/pwd@RMANCAT

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 28 14:01:33 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> select db_key,dbid,name from rc_database
  2  where dbid=305453026;

    DB_KEY       DBID NAME
---------- ---------- --------
         1  305453026 PRODDB01

Finally, execute the procedure dbms_rcvcat.unregisterdatabase, passing in the parameters db_key and dbid:

SQL> execute dbms_rcvcat.unregisterdatabase(1,305453026);

PL/SQL procedure successfully completed.

How to set a column to UNUSED

Setting a column to UNUSED can be a solution when you cannot or should not attempt to drop a column due to performance reasons.

Syntax:

ALTER TABLE [table name] SET UNUSED (col1, col2, ... col n);

or

ALTER TABLE [table name] SET UNUSED COLUMN (col1);

Example:

ALTER TABLE MYTABLE
SET UNUSED COLUMN MODIFIED_DATE;

Verify that the columns were set to UNUSED:

SELECT * 
FROM USER_UNUSED_COL_TABS;

Result:

TABLE_NAME	COUNT
MYTABLE	1
INVOICES_TMP	1

ALTER TABLE MYTABLE
DROP UNUSED COLUMNS;

The column is now gone:

SELECT * 
FROM USER_UNUSED_COL_TABS;

TABLE_NAME	COUNT
INVOICES_TMP	1

You can also use the keyword CHECKPOINT when dropping the unused columns:

ALTER TABLE MYTABLE
DROP UNUSED COLUMNS CHECKPOINT 1000;

Oracle explains about the CHECKPOINT clause:

"This clause causes a checkpoint to be applied after processing the specified number of rows"
and
"Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo Space."

Note that:
Setting a column to UNUSED will drop constraints and indexes on the column
An UNUSED column cannot be accessed, nor recovered.
You can however, add a new column with the same name as any UNUSED column for the table.

Sources:
Oracle Documentation: Marking columns unused
Oracle Documentation: ALTER TABLE

Important files and directories in a Netbackup setup

Important netbackup files and directories on a typcial unix server:

/usr/openv/netbackup/version --> displays the netbackup version
/usr/openv/netbackup/bp.conf --> displays important settings such as SERVER, MEDIA_SERVER, CLIENT_NAME
/usr/openv/netbackup/logs/user_ops/dbext/logs --> log files for rman backups

Example of bp.conf

SERVER = backupserver1.mydomain.com
MEDIA_SERVER = server1-bkp.mydomain.com
CLIENT_NAME = server1-bkp.mydomain.com
REQUIRED_INTERFACE = server1-bkp.mydomain.com

Note that the CLIENT_NAME used above translates into the NB_ORA_CLIENT typically used in when sending backups to tape using Netbackup:

NB_ORA_CLIENT=server1-bkp.mydomain.com

How to save a value in a bind variable

Useful for tracing or explaining queries that use bind variables:

connect scott/tiger
var leom char(10);
var eod char(10);

exec :leom := to_date('30.09.2013','DD.MM.YYYY');
exec  :eod := to_date('31.10.2013','DD.MM.YYYY');

SELECT col1, col2 ....col n
FROM   TABLE1
WHERE  START_DATE <= :eod
AND    END_DATE   <= :leom;

How to shrink a datafile in a temporary tablespace

alter tablespace TEMP shrink tempfile '/data/oracle/PRODDB01/datafiles/temp_20.dbf' KEEP 8G;

This feature was introduced in Oracle 11g.

How to create a temporary tablespace and assign it as the database default

CREATE TEMPORARY TABLESPACE TMP TEMPFILE
  '/data/oracle/u01/PRODDB01/datafile/tmp_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;