ALTER TABLE SCOTT.EMPLOYEES RENAME PARTITION START_DATE_FEBRUARY_2014 TO START_DATE_FEB_2014;
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Wednesday, February 5, 2014
How to rename a partition
Wednesday, January 29, 2014
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
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
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:
To populate the ROOM_NO and TEACHER columns, I used the following query:
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 |
Tuesday, January 28, 2014
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 PRODDB01connect 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 PRODDB01Finally, 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:
Verify that the columns were set to UNUSED:
Result:
The column is now gone:
You can also use the keyword CHECKPOINT when dropping the unused columns:
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
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
/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;
Subscribe to:
Posts (Atom)