Purge in oracle9i Post: 302706615

Sponsored Content

Homework and Emergencies Emergency UNIX and Linux Support Purge in oracle9i Post 302706615 by radoulov on Thursday 27th of September 2012 04:33:29 AM

09-27-2012

Registered User

You don't have a recycle bin in Oracle < 10g and, in any case, you don't need to purge it manually (it gets purged automatically when space pressure occurs).

In order to reclaim the free space in the tablespace, in Oracle 9i, you need to recreate (move) the table (and rebuild its relative indexes) subject to mass deletions.

Code:

alter table <table_name> move;
alter index index1 rebuild;
alter index index2 rebuild;
...

You need to take special care if you need to do this in production, because (this is not a complete list):

1. During the move the table will be locked in exclusive mode.
2. Right after the move the indexes on the table will be marked unusable and statements that try to use those indexes will fail (you'll need a maintenance window).
3. Before you begin you'll need to check the definitions of the involved objects - for example if you have compressed indexes you may end up with no compression after the rebuild (i.e. you'll need to include compress in the alter statement too).
4. You could speed up the operations by using parallel degree greater than 1 (only EE), just remember to reset the degree back to their original values.
5. You could rebuild the indexes online (EE only), but you could move the table online only if it's an IOT (Index Organized Table).
6. Remember to re-gather statistics after the move/rebuild operations.

That said, you can, of course, ad some space to the tablespace.

Hope this helps.

Last edited by radoulov; 09-27-2012 at 07:36 AM..

radoulov

View Public Profile for radoulov

Find all posts by radoulov

8 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

purge xterm

Being new I had my focus in the wrong place and typed in xterm instead of <If you can't guess, good>. Can I purge xterm so that this word no longer appears using up or down arrows?

2. AIX

install oracle9i on aix5.3

I am installing oracle9i on aix5.3 and an link error appears during the install process. 'Error in invoking target iokdstry of makefile /export/home/oracle/OraHome/network/lib/ins_nau.mk' happened I tried to find the way out,and there seems to be one...

3. Shell Programming and Scripting

script for purge

Hi , I want to purge 7 days older data from a list of data sorted on date in a log file... Can anyone provide me with the shell script for the same.. Thanks, Jaz

4. Red Hat

find help with directory purge.

Hi, I have a purge directory called /b02/purge which has backed up directories like this drwxrwxr-x 17 root root 4096 Jan 5 15:33 purge1 drwxrwxr-x 17 root root 4096 Jan 5 16:21 purge.new1 drwxrwxr-x 15 root root 4096 Jan 5 16:21 purge.new2 drwxrwxr-x 17 root root 4096 Jan 12...

5. Shell Programming and Scripting

purge logs, keep the 30 lasts

Hi, I have to make a script to purge logs in a directory (this script will run automatically every day) and this script has to keep just the 30 last files. So I want it to count all the files in the directory, find the 30 most recents and delete the others. I just started shell scripting and...

6. UNIX for Advanced & Expert Users

Purge MAil file

Hi, merry christmas. on AIX 6.1, the file /var/spool/mail/user, should/can be purged manually ? Any commande line to purge it ? Thanks.

7. Shell Programming and Scripting

Menu for Purge

I have these commands that help me find and delete files over certain days. How can I build a menu to list the files, and then hit y for yes to delete or no? find /logs/212/abinitio/prod/mfs/partitions/part0/mfs_12way_001/mfs_12way/sncrpt/main/ -name "*dat" -mtime +1 -exec ls -ltr {} \; find...

8. UNIX for Dummies Questions & Answers

Apt-get install --purge

Hello, i would like to know what is the use of that command apt-get install --purgeI understand the use of --purge while using with remove parameter, but i don't get it when it's about installing. Enlight me please ! Thx ---------- Post updated 09-03-16 at 11:35 AM ---------- Previous...

LEARN ABOUT DEBIAN

glfrontface

GLFRONTFACE(3G) 						  [FIXME: manual]						   GLFRONTFACE(3G)

NAME

       glFrontFace - define front- and back-facing polygons

C SPECIFICATION

       void glFrontFace(GLenum mode);

PARAMETERS

       mode
	   Specifies the orientation of front-facing polygons.	GL_CW and GL_CCW are accepted. The initial value is GL_CCW.

DESCRIPTION

       In a scene composed entirely of opaque closed surfaces, back-facing polygons are never visible. Eliminating these invisible polygons has
       the obvious benefit of speeding up the rendering of the image. To enable and disable elimination of back-facing polygons, call glEnable()
       and glDisable() with argument GL_CULL_FACE.

       The projection of a polygon to window coordinates is said to have clockwise winding if an imaginary object following the path from its
       first vertex, its second vertex, and so on, to its last vertex, and finally back to its first vertex, moves in a clockwise direction about
       the interior of the polygon. The polygon's winding is said to be counterclockwise if the imaginary object following the same path moves in
       a counterclockwise direction about the interior of the polygon.	glFrontFace specifies whether polygons with clockwise winding in window
       coordinates, or counterclockwise winding in window coordinates, are taken to be front-facing. Passing GL_CCW to mode selects
       counterclockwise polygons as front-facing; GL_CW selects clockwise polygons as front-facing. By default, counterclockwise polygons are
       taken to be front-facing.

ERRORS

       GL_INVALID_ENUM is generated if mode is not an accepted value.

ASSOCIATED GETS

       glGet() with argument GL_FRONT_FACE

SEE ALSO

       glCullFace(),

COPYRIGHT

       Copyright (C) 1991-2006 Silicon Graphics, Inc. This document is licensed under the SGI Free Software B License. For details, see
       http://oss.sgi.com/projects/FreeB/.

[FIXME: source] 						    05/30/2012							   GLFRONTFACE(3G)