How to name a trace file to find it more easily

alter session set tracefile_identifier = 'your string for easy recognition here';

For example:

alter session set tracefile_identifier = 'data_pump_import_trace.trc';

If you trace your session now, your file will now be much easier to find in the udump directory (pre 11g) or in the diagnostic_dest/trace directory (11g and onwards)

How to set a timeout for DDL operations in oracle11g - to avoid "resource busy" error

ALTER SESSION SET DDL_LOCK_TIMEOUT = 10;

when a DDL statement in the session does not get the exclusive lock, it will not error out.
Instead, it will wait for 10 seconds. In that 10 seconds, it continually re-tries the DDL operation until it is successful or the time expires, whichever comes first.

For exammple, if the following query is executed:


SQL> alter table sales add (tax_code varchar2(10));

the statement hangs and does not error out.

Arup Nanda puts it as

somewhat like a telephone programmed to re-try a busy number

To set it database-wide:

ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10;

The reason for "ORA-12052: cannot fast refresh materialized view" during Online Redefinition

Online Redefinition of an object relies on Material View (MView) logs to keep track of all data changes in the table under redefinition *while* the redefintion is going on.

If there is any change in the definition (DDL), then there is no guarantee that the Mview logs can capture all changes (they can only track DML )

The internal process works like this:

- Set up Mview log to track any changes on the table and consider the temp table as an MView of the original table
- Transfer all the data in the original table to the temporary table
- WHILE this is going on, any DATA changes to the table will be logged in the MView log
- If you perform a SYNC, Oracle essentially performs a "MVIEW FAST REFRESH" using the MView log to replicating the data changes. The MView log is then cleared.
- Once the data transfer is complete, the MView log (if not empty) is processed - the "MView" / temp table is now up-to-date.
- Upon executing dbms_redefinition.finish_redef_table, the MView log is dropped, the MView is converted back to simple table.
- After that, the names of the original and temp table are switched automatically.

If at any point during this process, the structure of the table is changed or DDL in general is executed (say, TRUNCATE, adding a column, dropping a partition, exchanging a partition), then the MView log cannot in itself guarantee it has all the information needed to "replicate" this change of the original table.

Hence, the MView log is regarded as "unusable" and the error you observed occurs:


ORA-42009: error occurred while synchronizing the redefinition
ORA-12052: cannot fast refresh materialized view BWALM.TOMBA
ORA-06512: at "SYS.DBMS_REDEFINITION", line 119
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1740
ORA-06512: at line 2


Oracle therefore recommend to avoid all DDL on the original table while ONLINE REDEFINITION is ongoing.

How to completely move a sub-partitioned table from one tablespace to another, including attributes

1. move the subpartitions physically:

ALTER TABLE lkw.sales MOVE SUBPARTITION P_012013_WEEK1 TABLESPACE SALES_2013_DATA [ UPDATE INDEXES ];

2. alter the default attributes for the *partition* (as opposed to the subpartition which was physically moved in the previous step):

ALTER TABLE lkw.sales MODIFY DEFAULT ATTRIBUTES FOR PARTITION "P_012013" TABLESPACE SALES_2013_DATA;

3. Finally, alter the default attributes for the table:

ALTER TABLE lkw.sales MODIFY DEFAULT ATTRIBUTES TABLESPACE SALES_2013_DATA;


The default attributes for partitions and subpartitions can be found by querying the view DBA_PART_TABLES.

A script that will generate the DDL for you:

accept table_owner prompt 'Table owner: '
accept table_name prompt 'Table name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_movesubpart_&&table_owner..&&table_name..sql
select 'alter session set nls_language=''american'';' from dual;
select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_move_subpart_&&table_owner..&&table_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''MoveSubPart_&&table_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''MoveSubPart_&&table_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''MoveSubPart_&&table_name''); ' from dual;

SELECT 'alter table ' || ts.TABLE_OWNER || '.' || ts.TABLE_NAME || ' move subpartition ' || ts.subpartition_name ||  ' ONLINE TABLESPACE  &&new_tabspc ROW STORE COMPRESS ADVANCED UPDATE GLOBAL INDEXES;'
FROM dba_tab_subpartitions ts
WHERE ts.table_name      = '&&table_name'
AND   ts.tablespace_name = '&&old_tabspc'
AND   ts.table_owner     = '&&table_owner'
ORDER BY ts.subpartition_name DESC;
select 'prompt alter default attributes for partitions:' from dual;
SELECT 'alter table ' || ts.TABLE_OWNER || '.' || ts.TABLE_NAME || ' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' || ts.partition_name ||  ' TABLESPACE  &&new_tabspc;'
FROM dba_tab_subpartitions ts
WHERE ts.table_name      = '&&table_name'
AND   ts.tablespace_name = '&&old_tabspc'
AND   ts.table_owner     = '&&table_owner'
ORDER BY ts.subpartition_name DESC;
select 'exit' from dual;
exit

From 12.1 and onwards, this operation can be done online, meaning that it becomes a non-blocking DDL statement, allowing DML against the partition to continue without interuption. See this post for details.

How to speed up index rebuild

ALTER SESSION FORCE PARALLEL DDL;
ALTER INDEX <schema>.<index_name> REBUILD PARALLEL 4 NOLOGGING;

Note the "NOLOGGING" - this tells oracle that it will not log the *creation* of the index.
This result in faster creation time. The index would not be recoverable during a recover operations, since oracle would not have the information in its redo logs. This may be perfectly acceptable for indexes, since they can easily be recreated anyway if we ever have to recover the database.

After the index is created, remove the PARALLEL clause to impose normal, non-parallelized usage of the index:

ALTER INDEX <schema>.<index_name>
 NOPARALLEL;

Update regarding rebuilds of large partitioned tables with global indexes:

It is my experience that trying to run multiple rebuilds simultaniously, with a low degree of parallelism, or no parallelism at all, is a terrible idea. You will get nowhere and your sessions will create wait-events all over the place.

If you find yourself in a situation where you have to rebuild a number of large global non-partitioned indexes on a large partitioned table, it's better to throw all the CPU power you can find on the database server, and focus all CPU power on building one index at a time. You should run with an aggressive number of parallel slaves.

In my example, I had a virtual VMWare server with 38 cores and exclusive rights to the database.

The following statement was resonably effective:

ALTER INDEX myindx REBUILD PARALLEL 34 ONLINE NOLOGGING;

Then, when the index has been rebuilt, move to the next index and repeat procedure.

How to use the ONLINE keyword during index rebuilds

This feature eliminates the need for DML-blocking locks when creating or rebuilding an index, making the online index operations fully transparent:

ALTER INDEX <schema>.<table_name> REBUILD TABLESPACE <tablespace_name> ONLINE;

How to create an interval range partitioned table based on a function

CREATE TABLE SCOTT.MY_TABLE(
  PID                      NUMBER(12),
  PTDBAL                   NUMBER,
  BUSINESSDAY              VARCHAR2(255 BYTE),
  PTBALCCY                 NUMBER,
  CREATIONTIME             TIMESTAMP(6),
  COCODE                   VARCHAR2(4 BYTE),
  OPERATOR                 VARCHAR2(255 BYTE),
  VERSION                  TIMESTAMP(6),
  MAID                     NUMBER(12),
  LPER                     NUMBER(12),
  ARCHIVALFLAG             CHAR(1 BYTE),
  CREATIONDATE AS
  (
    CAST(CREATIONTIME as DATE)
  ) VIRTUAL
)
-- Partition by range on the virtual column
PARTITION BY RANGE (CREATIONDATE )
-- Use 11gR1 Interval Partitioning
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION P082013 VALUES LESS THAN (TO_DATE('01.09.2013','DD.MM.YYYY'))
)
TABLESPACE DATA;