Purge in oracle9i

 
Thread Tools Search this Thread
Homework and Emergencies Emergency UNIX and Linux Support Purge in oracle9i
# 1  
Old 09-27-2012
Purge in oracle9i

From morning we are facing some issues in tablespaces in oracle9i.

Tried deleting some huge records... but even thought it is still giving tablespcases in full.

Then i tried and googling ... found we need to purge recyclebin of oracle..
but thats not woking in oracle9i...

can any one help in cleaningup some spaces for time being ....
# 2  
Old 09-27-2012
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 08:36 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. 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... (0 Replies)
Discussion started by: Purgator
0 Replies

2. 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... (3 Replies)
Discussion started by: xgringo
3 Replies

3. 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. (2 Replies)
Discussion started by: big123456
2 Replies

4. 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... (4 Replies)
Discussion started by: jblecrou
4 Replies

5. 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... (3 Replies)
Discussion started by: uxadmin007
3 Replies

6. 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 (1 Reply)
Discussion started by: JP003
1 Replies

7. 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... (2 Replies)
Discussion started by: mayuhao
2 Replies

8. 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? (1 Reply)
Discussion started by: noobie_doo
1 Replies
Login or Register to Ask a Question

Featured Tech Videos