Reorganizing a Table using DBMS_REDEFINITION

Posted at Thursday, July 03, 2008
There are many methods to reorganize/defragment a table in Oracle. If you want to avoid downtime, however, your choices are limited to using the 'SHRINK SPACE' feature and/or employing the DBMS_REDEFINITION PL/SQL package.

This post demonstrates the latter method. The DBMS_REDEFINITION package is most commonly used for redefining a table, however can be used to reorganize a table amongst other things. Here is a great article which covers the package in a little more detail: On-line Table Reorganization and Redefinition.

This example rebuilds the OE.ORDER_ITEMS table using DBMS_REDEFINITION.

Collect information about the table's segments prior to the reorg

ANALYZE TABLE oe.order_items COMPUTE STATISTICS;


SELECT avg_row_len, num_rows, chain_cnt
FROM dba_tables
WHERE table_name = 'ORDER_ITEMS' AND owner = 'OE';


SELECT ROUND (BYTES / 1024) kb
FROM dba_segments
WHERE owner = 'OE' AND segment_name = 'ORDER_ITEMS';

Check whether the table in question violates any redefinition restrictions

EXEC dbms_redefinition.can_redef_table('OE','ORDER_ITEMS', dbms_redefinition.cons_use_pk);

Create the Intermediary Table - in my case, I use the exact same structure

CREATE TABLE OE.ORDER_ITEMS_REDEF
(
ORDER_ID NUMBER(12),
LINE_ITEM_ID NUMBER(3) NOT NULL,
PRODUCT_ID NUMBER(6) NOT NULL,
UNIT_PRICE NUMBER(8,2),
QUANTITY NUMBER(8)
) TABLESPACE USERS;

Start the Redefinition Process

EXEC dbms_redefinition.start_redef_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

You will notice that Oracle creates a Materialized View in order to store the table changes, etc.

SELECT COUNT (*)
FROM oe.mlog$_order_items;

Sync up the Source table with the Intermediary table

EXEC dbms_redefinition.sync_interim_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

Duplicate the table's dependent objects on the Intermediary table

CREATE INDEX oe.item_order_redef_ix ON oe.order_items_redef
(order_id) LOGGING TABLESPACE users;


CREATE INDEX oe.item_product_redef_ix ON oe.order_items_redef
(product_id) LOGGING TABLESPACE users;


CREATE UNIQUE INDEX oe.order_items_redef_pk ON oe.order_items_redef
(order_id, line_item_id) LOGGING TABLESPACE users;


CREATE UNIQUE INDEX oe.order_items_redef_uk ON oe.order_items_redef
(order_id, product_id) LOGGING TABLESPACE users;


ALTER TABLE oe.order_items_redef ADD (
CONSTRAINT order_items_redef_pk
PRIMARY KEY (order_id, line_item_id) USING INDEX TABLESPACE USERS);


ALTER TABLE oe.order_items_redef ADD (
CONSTRAINT order_items_redef_order_id_fk
FOREIGN KEY (order_id)
REFERENCES oe.orders (order_id)
ON DELETE CASCADE DISABLE NOVALIDATE);


ALTER TABLE oe.order_items_redef ADD (
CONSTRAINT order_items_rdef_product_id_fk
FOREIGN KEY (product_id)
REFERENCES oe.product_information (product_id) DISABLE NOVALIDATE);


GRANT SELECT ON oe.order_items_redef TO bi;


GRANT SELECT ON oe.order_items_redef TO pm;

Notice how, I create the foreign key constraints initially as disabled.

To simplify things, I use the following to duplicate any triggers to the Intermediary table

DECLARE
retval NUMBER(5);
BEGIN
dbms_redefinition.copy_table_dependents('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF', copy_indexes=>0, copy_triggers=>TRUE, copy_constraints=>FALSE,copy_privileges=>FALSE, num_errors=>retval);
dbms_output.put_line(retval);
END;
/

Disable any foreign key constraints on the Source table before finishing the table redefinition process

ALTER TABLE oe.order_items DISABLE CONSTRAINT order_items_order_id_fk;

ALTER TABLE oe.order_items DISABLE CONSTRAINT order_items_product_id_fk;

Finish the Redefinition

EXEC dbms_redefinition.finish_redef_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

Drop the Intermediary Table

DROP TABLE oe.order_items_redef;

Rename any dependent objects

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_rdef_product_id_fk TO order_items_product_id_fk;


ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_redef_order_id_fk TO order_items_order_id_fk;


ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_redef_pk TO order_items_pk;


ALTER INDEX oe.item_order_redef_ix RENAME TO item_order_ix;


ALTER INDEX oe.item_product_redef_ix RENAME TO item_product_ix;


ALTER INDEX oe.order_items_redef_pk RENAME TO order_items_pk;


ALTER INDEX oe.order_items_redef_uk RENAME TO order_items_uk;

At this point, the table should be reorganized/defragmented. Optionally, compare the storage footprint post-redefintion

ANALYZE TABLE oe.order_items COMPUTE STATISTICS;


SELECT avg_row_len, num_rows, chain_cnt
FROM dba_tables
WHERE table_name = 'ORDER_ITEMS' AND owner = 'OE';


SELECT ROUND (BYTES / 1024) kb
FROM dba_segments
WHERE owner = 'OE' AND segment_name = 'ORDER_ITEMS';


Labels:

Rebuilding Indexes the Easy Way

Posted at Monday, June 30, 2008
Oracle has made index rebuilding easier through the introduction of the DBMS_INDEX_UTL PL/SQL package back in 10g.

In lieu of creating hundreds of seperate rebuild DDL statements, you can now create single calls to this package's methods in order to accomplish the same task(s).

This example demonstrates how to rebuild all indexes - including those with partitions - for the SH.SALES table - by constructing a single call to the aforementioned package.

Here are the indexes in question for my test system:

SELECT 
TRIM(di.owner) "OWNER", TRIM (di.index_name) "INDEX",
TRIM(MAX (TO_CHAR (dip.last_analyzed, 'MON-DD-YYYY HH24:MI:SS'))) "LAST ANALYZED",
COUNT(dip.partition_name) "PART COUNT",
ROUND(SUM (ds.BYTES) / 1024) "KB"
FROM dba_indexes di,
dba_ind_partitions dip,
dba_segments ds
WHERE di.index_name = ds.segment_name
AND di.owner = ds.owner
AND dip.partition_name = ds.partition_name
AND di.table_owner = 'SH'
AND di.table_name = 'SALES'
AND di.index_name = dip.index_name
AND di.owner = dip.index_owner
AND di.owner = dip.index_owner
GROUP BY di.owner, di.index_name;


OWNER                          INDEX                          LAST ANALYZED              PART COUNT         KB
------------------------------ ------------------------------ -------------------------- ---------- ----------
SH SALES_TIME_BIX JUN-30-2008 16:14:27 28 9216
SH SALES_CHANNEL_BIX JUN-30-2008 16:14:18 28 4288
SH SALES_PROD_BIX JUN-30-2008 16:14:24 28 5056
SH SALES_CUST_BIX JUN-30-2008 16:14:23 28 46848
SH SALES_PROMO_BIX JUN-30-2008 16:14:26 28 2944

5 rows selected.

I will rebuild all these indexes via the following call:

EXEC dbms_index_utl.build_table_indexes (list=>'SH.SALES',just_unusable=>FALSE, locality=>'ALL',concurrent=>TRUE, cont_after_err=>TRUE, max_slaves=>8);
PL/SQL procedure successfully completed.

Afterwards, the same query shows that the overall footprint of the indexes in question has been reduced. Additionally, the segments have been analyzed as part of the rebuild.

OWNER                          INDEX                          LAST ANALYZED              PART COUNT         KB
------------------------------ ------------------------------ -------------------------- ---------- ----------
SH SALES_TIME_BIX JUN-30-2008 17:03:47 28 7104
SH SALES_CHANNEL_BIX JUN-30-2008 17:03:42 28 4288
SH SALES_PROD_BIX JUN-30-2008 17:03:44 28 3968
SH SALES_CUST_BIX JUN-30-2008 17:03:44 28 37632
SH SALES_PROMO_BIX JUN-30-2008 17:03:46 28 3008

5 rows selected.


Labels:

Basic Oracle Maintenance Jobs

Posted at Saturday, May 17, 2008
There are many processes that need to be in place in order to maintain Oracle databases' environments.

There are, also, many 3rd-party tools that are available to assist the DBA as well as many differing methods in which to approach performing these tasks; however, traditionally, shell scripts called from an O/S scheduler - such as cron - have been used to perform - at a bare minimum - the following tasks:

  • Rotate Listener Log

  • Rotate Alert Log

  • Romove Old Files - Trace, etc.

  • Backup Database and Archive Logs, including Deleting Old Logs and Crosschecking Recovery Catalogs/Controlfiles, etc.

  • Gather Object - Table, Index, etc. - Statistics

This example includes a tar file which contains all of the scripts necessary to perform the above 5 tasks on the Linux platform for any Oracle 9i/10g database - these scripts can also be modified to support 11g:

oracron.tar

Simply untar the files to the desired location, edit as appropriate and schedule via your preferred mechanism; below are the entries from my oracle user's crontab file:

49 18 * * * sh -c '. ~/.bash_profile ; ~/scripts/rotate_alert.bsh ldg' >> ~/logs/rotate_alert.log 2>&1
50 18 * * * sh -c '. ~/.bash_profile ; ~/scripts/remove_old_files.bsh ldg 2' >> ~/logs/remove_old_files.log 2>&1
13 20 * * * sh -c '. ~/.bash_profile ; ~/scripts/rman_full_nc.bsh ldg' >> ~/logs/rman_full_nc.log 2>&1
12 21 * * * sh -c '. ~/.bash_profile ; ~/scripts/analyze_db.bsh ldg' >> ~/logs/analyze_db.log 2>&1
23 22 * * * sh -c '. ~/.bash_profile ; ~/scripts/rotate_listener.bsh ldg ldg' >> ~/logs/rotate_listener.log 2>&1

Make sure that oraenv is in the oracle user's path and that each database has a /etc/oratab entry. You will want to adjust the crontab entries above to use the $ORACLE_SID of your choosing. Additionally, my database statistics gathering job requires that you first compile the DBA_STATS_MAINT_PKG in the SYS schema. You will find the ddl for this package within the tar file.

analyze_db.bsh

   #!/bin/bash

# Wrapper Script that calls PL/SQL function to analyze your
# database to suite your particular needs

function quit {
echo "${0} ORACLE_SID";
exit;
}

# Make Sure Oracle instance name is passed
if [ "" == "${1}" ]; then
quit;
fi;

# Set the Environment
export ORACLE_SID=${1};
export ORAENV_ASK=NO;
. oraenv;

date ;
sqlplus -s /nolog <<INP
connect / as sysdba ;
exec dba_stats_maint_pkg.disable_auto_job('GATHER_STATS_JOB');
exec dba_stats_maint_pkg.gather_stats;
exec dba_stats_maint_pkg.gather_data_dict_stats;
INP
date ;

remove_old_files.bsh

   #!/bin/bash

# Works for Oracle databases pre-11g
# 11g introduces universal diagnostic dest

function quit {
echo "${0} ORACLE_SID DAYS_OLD";
exit;
}

# Make Sure Oracle instance name is passed
if [ "" == "${1}" ]; then
quit;
fi;

# Make Sure the Days Old argument is passed
if [ "" == "${2}" ]; then
quit;
fi;

# Set the Environment
export ORACLE_SID=${1};
export DAYS_OLD=${2};
export ORAENV_ASK=NO;
. oraenv;

# Get the location for the bdump
bdump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <<EOF
connect / as sysdba ;
set head off;
set feedback off;
set verify off;
select value from v\\$parameter where name='background_dump_dest';
exit;
EOF `

# Get the location for the udump
udump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <<EOF
connect / as sysdba ;
set head off;
set feedback off;
set verify off;
select value from v\\$parameter where name='user_dump_dest';
exit;
EOF `

# Make sure the directories are valid and cleanup the files
if [ -d ${bdump} ] && [ -d ${udump} ] ; then
echo "Removing old files at `date +%Y%m%d%H%M` for ${ORACLE_SID}" ;
echo "Background Dump Before Cleanup `ls -l ${bdump} | wc -l`";
echo "User Dump Before Cleanup `ls -l ${udump} | wc -l`";
echo "Listener Log Dump Before Cleanup `ls -l ${ORACLE_HOME}/network/log | wc -l` ";
find ${bdump} -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;
find ${udump} -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;
find ${ORACLE_HOME}/network/log -mtime +${DAYS_OLD} -name "*" -type f -exec rm {} \;
echo "Background Dump After Cleanup `ls -l ${bdump} | wc -l`";
echo "User Dump After Cleanup `ls -l ${udump} | wc -l`";
echo "Listener Log Dump After Cleanup `ls -l ${ORACLE_HOME}/network/log | wc -l` ";
fi;

rman_full_nc.bsh

   #!/bin/bash

# Backup/Catalog Crosscheck Delete using RMAN
# This is for Controlfile backups w/o RMAN Catalog

function quit {
echo "${0} ORACLE_SID";
exit;
}

# Make Sure Oracle instance name is passed
if [ "" == "${1}" ]; then
quit;
fi;

# Set the Environment
export ORACLE_SID=${1};
export BACKUP_TAG=FHB_${ORACLE_SID}`date +%Y%m%d%H%M`
export ARCH_TAG=ARCH_${ORACLE_SID}`date +%Y%m%d%H%M`

export ORAENV_ASK=NO;
. oraenv;

date ;
rman<<EOF
connect target / ;
show all ;
crosscheck backup ;
crosscheck archivelog all ;
delete noprompt obsolete ;
backup full database tag ${BACKUP_TAG} plus archivelog tag ${ARCH_TAG} ;
exit ;
EOF
date ;

rotate_alert.bsh

   #!/bin/bash

# Works for Oracle databases pre-11g
# 11g introduces universal diagnostic dest

function quit {
echo "${0} ORACLE_SID";
exit;
}

# Make Sure Oracle instance name is passed
if [ "" == "${1}" ]; then
quit;
fi;

# Set the Environment
export ORACLE_SID=${1};
export ORAENV_ASK=NO;
. oraenv;

date ;
# Get the location for the alert log
bdump=`${ORACLE_HOME}/bin/sqlplus -s /nolog <<EOF
connect / as sysdba ;
set head off;
set feedback off;
set verify off;
select value from v\\$parameter where name='background_dump_dest';
exit;
EOF
`
logfile=${bdump}/alert_${ORACLE_SID}.log ;

# Ensure that the background dump dest exists
# If so, rotate the log
if [ -d ${bdump} ]; then
if [ -f ${logfile} ]; then
echo "${logfile} rotated at `date +%Y%m%d%H%M`" ;
mv ${logfile} ${bdump}/alert_${ORACLE_SID}.`date +%Y%m%d%H%M`.log
if [ $? == 0 ]; then
touch ${logfile};
fi;
else
echo "Alert Log ${logfile} does not exist...";
quit;
fi;
else
echo "Directory does not exist...";
quit;
fi;
date ;

rotate_listener.bsh

   #!/bin/bash

# Works for Oracle databases pre-11g
# 11g introduces universal diagnostic dest

function quit {
echo "${0} ORACLE_SID LSNR";
exit;
}

# Make Sure Oracle instance name is passed
if [ "" == "${1}" ]; then
quit;
fi;

# Make Sure Listener name is passed
if [ "" == "${2}" ]; then
quit;
fi;

# Set the Environment
export NOW=`/bin/date +%m%d%y%H%M%S`;
export ORACLE_SID=${1};
export LSNR=${2};
export ORAENV_ASK=NO;
. oraenv;

date ;

if [ -d ${ORACLE_HOME}/network/log ]; then
if [ -f ${ORACLE_HOME}/network/log/listener_${LSNR}.log ]; then
cat ${ORACLE_HOME}/network/log/listener_${LSNR}.log >> ${ORACLE_HOME}/network/log/listener_${LSNR}.log.$NOW ;
${ORACLE_HOME}/bin/lsnrctl << EOF
set current_listener LISTENER_${LSNR}
set log_file temp.log
EOF

rm ${ORACLE_HOME}/network/log/listener_${LSNR}.log ;

${ORACLE_HOME}/bin/lsnrctl << EOF
set current_listener LISTENER_${LSNR}
set log_file listener_${LSNR}.log
EOF

cat ${ORACLE_HOME}/network/log/temp.log >> $ORACLE_HOME/network/log/listener_${LSNR}.log.${NOW} ;
rm ${ORACLE_HOME}/network/log/temp.log ;
fi ;
else
echo "Directory not found...";
quit ;
fi ;
date ;

Edit the above mentioned in any way you see fit. Although these are written using bash, you could easily adapt these scripts to your preferred shell.


Labels: