How to group output using GROUPING SETS.

Following up with the table from my previous post "How to use the MERGE statement with a subquery", here is an example on how to group the information using the GROUPING SETS functionality in SQL:

The query

SELECT MAJOR,COURSE,TEACHER,SUM(CREDITS) "Total Credits"
FROM  COURSE_CATALOG
GROUP BY GROUPING SETS( (MAJOR,COURSE,TEACHER),(COURSE,TEACHER), (MAJOR,TEACHER),NULL );

The NULL directive tells SQL to generate a grand total at the end of the grouping.
Notice how the GROUPING SETS translates directly into the equivalent of grouping the rows three separate times

First by using

GROUP BY MAJOR, COURSE, TEACHER

then by using

GROUP BY COURSE,TEACHER

and finally by using

GROUP BY MAJOR, TEACHER

MAJOR	COURSE	TEACHER	Total Credits
English Literature	Ex. Phil	A.Peterson	8
Nuclear Science	Physics A	Goodman	8
Nuclear Science	Physics A	A. Brown	8
Nuclear Science	Physics A	R. Franklin	8
Nuclear Science	Mathematics A	M. Stevens	8
English Literature	Greek Mythology	D.Simmons	10
Nuclear Science	Advanced Algebra	K.Wise	10
English Literature	Early British Lit	A.Peterson	8
	Ex. Phil	A.Peterson	8
	Physics A	Goodman	8
	Physics A	A. Brown	8
	Physics A	R. Franklin	8
	Mathematics A	M. Stevens	8
	Greek Mythology	D.Simmons	10
	Advanced Algebra	K.Wise	10
	Early British Lit	A.Peterson	8
Nuclear Science		M. Stevens	8
Nuclear Science		A. Brown	8
English Literature		D.Simmons	10
Nuclear Science		Goodman	8
Nuclear Science		R. Franklin	8
Nuclear Science		K.Wise	10
English Literature		A.Peterson	16
			68

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.