How to backup your controlfile to trace

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/fullpath/backup_controlfile.trc' [REUSE] [RESETLOGS | NORESETLOGS];

Note that if you do not specify either RESETLOGS or NORESETLOGS during the create command, oracle will include both versions in the resulting file, and you must pick the appropriate one for your situation.

REUSE will generate a backup controlfile statement which includes the REUSE keyword.
This in turn, indicates that when the "create controlfile" command is executed, you can reuse the existing physical control files on your server. If you don't, and the file exists from a previous database incarnation, Oracle will throw an error, so I prefer to leave it there.

What is the CJQ0 background process?

When starting the Oracle database you may see something like the following in the alert log:

Tue Mar 04 12:40:50 2014
CJQ0 started with pid=27, OS id=5526

This means your database is starting the optional background process for job queue processing

Oracle Database uses job queue processes to run user jobs.

The initialization parameter JOB_QUEUE_PROCESSES states the maximum number of job queue processes that can concurrently run on an instance.

Source: Oracle Documentation

How to enable block change tracking for faster incremental backups:

From Oracle 10g and onwards, you can enable block change tracking to speed up incremental backups. In short,

"if change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile"

Syntax:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/path/PRODDB_rman_change_track.dbf' REUSE;

Database altered.

More info here

SQL: view memory parameter

set lines 200
set pages 100
col name format a30
col value format a40
select name from v$database
/
select name, value/1024/1024 "MB"
from v$parameter
where name in ('java_pool_size',
                'db_cache_size',
                'shared_pool_size',
                'large_pool_size',
                'streams_pool_size',
                'sga_max_size',
                'sga_target',
                'memory_max_target',
                'memory_target',
                'pga_aggregate_target')
/
exit

An easy way to find DML locks

The view DBA_DML_LOCKS is a convenient view to use if you want to identify locks caused by uncommited DML statements.

Consider the following simple example:

INSERT INTO FAMILY_TREE
(PERSON_ID, FULL_NAME, GENDER, CITIZENSHIP, COUNTRY_OF_RESIDENCE)
VALUES
(5, 'Arne Kåsa', 'M', 'Norwegian', 'Sweden');

-- Do not commit --

Logged in as a privileged user from another session, execute:

SELECT * FROM DBA_DML_LOCKS WHERE OWNER = 'VEGARDK';

SESSION_ID	OWNER	NAME	MODE_HELD	MODE_REQUESTED	LAST_CONVERT	BLOCKING_OTHERS
362	VEGARDK	FAMILY_TREE	Row-X (SX)	None	39	Not Blocking

COMMIT;

SELECT * FROM DBA_DML_LOCKS WHERE OWNER = 'VEGARDK';

No rows returned.

How to work around ORA-14080: partition cannot be split along the specified high bound

Problem:
My table had partitions for Jan 2014 and March 2014. Any attempt to split the overflow partition at this point will throw the error message

ORA-14080: partition cannot be split along the specified high bound

The following query shows my partitions for year 2014:

SELECT  PARTITION_NAME, 
        HIGH_VALUE,
        NUM_ROWS
FROM    DBA_TAB_PARTITIONS
WHERE   TABLE_NAME ='SALES'
AND     TABLE_OWNER = 'SCOTT'
AND     (PARTITION_NAME LIKE '%Y2014' OR PARTITION_NAME LIKE '%MAX%')
ORDER BY PARTITION_NAME;

Output:

PARTITION_NAME	HIGH_VALUE	NUM_ROWS
SALES_M01_Y2014	TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	3936
SALES_M03_Y2014	TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	2308
SALES_MAX	MAXVALUE	0

My solution:
Split the partition with the highest bound (March 2014) into two new ones, one that will take care of the rows for February 2014, and another one which will be empty:

SQL> 
  1  ALTER TABLE SCOTT.SALES
  2  SPLIT PARTITION SALES_M03_Y2014 AT (TO_DATE('2014-03-01', 'YYYY-MM-DD')) INTO (
  3  PARTITION SALES_M02_Y2014 TABLESPACE SCOTT_DATA,
  4* PARTITION SALES_MAX_NEW TABLESPACE SCOTT_DATA);

Table altered.

Rebuild local indexes, if any:

SQL> ALTER TABLE SCOTT.SALES MODIFY PARTITION SALES_M02_Y2014 REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

Analyze the partitio, and check to make sure the that it really does contains zero rows. You can use the initial query in this post for that:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','SALES','SALES_M02_Y2014');

PL/SQL procedure successfully completed.

Let's execute the query above once more:

PARTITION_NAME	HIGH_VALUE	NUM_ROWS
SALES_M01_Y2014	TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	3936
SALES_M02_Y2014	TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	2308
SALES_MAX	MAXVALUE	0
SALES_MAX_NEW	TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	0

Drop the temporary partition:

SQL> ALTER TABLE SCOTT.SALES DROP PARTITION SALES_MAX_NEW;

Table altered.

We now have:

PARTITION_NAME	HIGH_VALUE	NUM_ROWS
SALES_M01_Y2014	TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	3936
SALES_M02_Y2014	TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	2308
SALES_MAX	MAXVALUE	0

ORA-39726: unsupported add/drop column operation on compressed tables when attempting to drop column on a (previously) compressed table

My customer had a partitioned table that was previously compressed with FOR ALL OPERATIONS options.
One of the developers wanted to drop a columnn on a table, but receives the following error message:

ORA-39726: unsupported add/drop column operation on compressed tables

According to Oracle, this is expected behaviour. Oracle Support note 1068820.1 "Error ORA-39726 Drop Column Operation On Compressed Tables 10.2 Release" explains:

"In release 10g, a drop column attempt on a compressed table must raise an ORA-39726 error if the column has to be physically removed from the data blocks.
Hence "DROP COLUMN" and "DROP COLUMN UNUSED" are both illegal because they may have to touch the datablocks. SET UNUSED is OK because it is just a data
dictionary operation (as is DROP COLUMN on virtual columns)."

The Oracle support note concludes:

"In 11g it is allowed to drop columns from a compressed table IF compatible is set to 11.1 or higher AND table was created with the "compress for all OLTP" option but even in this situation there is no real drop but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations."

My database unfortunately has compatible set to 11.0. So the only option at this point is to use the syntax

ALTER TABLE owner.table_name SET UNUSED COLUMN column_name;

This will render the column as good as dropped, another column can be added to the table with the same name.
The number of unused columns for a table can be tracked with

SELECT *
FROM USER_UNUSED_COL_TABS
WHERE TABLE_NAME='table_name';