Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Purge indexs

Posted on 2007-07-25
18
Medium Priority
?
1,322 Views
Last Modified: 2013-12-18
I have following issue in our database. I Checkted the tablespace have enought space to grow but till its complaing with error blow

ORA-1683: unable to extend index SYSMAN.MGMT_METRICS_RAW_PK_INT partition 2007-07-20 04:00 by 128 in tablespace MGMT_TABLESPACE

i Want to detele or purge  some old data  from above index so that i get rid of above space error above.  Do we have script to delete it

0
Comment
Question by:vadicherla
  • 9
  • 4
  • 2
15 Comments
 

Author Comment

by:vadicherla
ID: 19569353
STATUS      TABLESPACE_NAME      PCT_FREE      INI_TRANS      MAX_TRANS      INITIAL_EXTENT      NEXT_EXTENT      MIN_EXTENT      MAX_EXTENT
USABLE      SYSAUX      10      2      255      65536            1      2147483645
USABLE      SYSAUX      10      2      255      65536            1      2147483645
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 19569371
deleting data will not free up extends automatically.
if you get that error, you should check the NEXT" EXTEND size in regards to the free space, eventually the next extend size is larger than the free sapce, and also check in the dba_free_extends if you have extends that are larger than the next extend size...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19569383
from your second post, the NEXT_EXTEND defaults to the INITAL_EXTEND, means :
2147483645 bytes (= 2GB). do you have another 2GB free space?

you might consider changing the NEXT_EXTEND to a smaller size...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19569396
ALTER TABLE SYSMAN.MGMT_METRICS_RAW_PK_INT  STORAGE ( NEXT 200M );
0
 

Author Comment

by:vadicherla
ID: 19569601
i think it went wrong  copy and past. this is actual

MAX_TRAS  INITIAL_EXTENT      MAX_EXTENT      
255           65536            2147483645      
255            65536            2147483645      
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 1000 total points
ID: 19570493
ORA-1683: unable to extend index SYSMAN.MGMT_METRICS_RAW_PK_INT partition 2007-07-20 04:00 by 128 in tablespace MGMT_TABLESPACE

Since this is related to partition as well, if you drop some unwanted partitions from the table and if the partitioned index is local, then you can get free space.

if you want to drop some unwanted partitions, then you can use

alter table test drop partition p20040131; -- This will drop the partition p20040131 and if the index is local index then oracle will automatically take care of the dropping the partition from the index as well which in turn can give us free space.

NOTE: The partition which we are dropping should not be an empty partition otherwise we will not get considerable amount of free space.
0
 

Author Comment

by:vadicherla
ID: 19570914
alter table test drop partition p20040131;   i am not sure about p20040131 this number. what this will do

Thanks
Sudhakar
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 19570991
that is nothing but my partition name for example. you have to put your partition name there.

just do a select :

select partition_name
from user_tab_partitions
where table_name = 'YOUR_TABLE_NAME'
order by 1;

Then we can give the partition name which we don't want and the oldest partition for that alter table ...
0
 

Author Comment

by:vadicherla
ID: 19571074
Its creating new partition name every hour. If i want to drop all partition name belongs to 2006 what should be the command
Please this is the first time i doing
NO      2006-09-14 21:00      0
NO      2006-09-14 22:00      0
NO      2006-09-14 23:00      0
NO      2006-09-15 00:00      0
NO      2006-09-15 01:00      0
NO      2006-09-15 02:00      0
NO      2006-09-15 03:00      0
NO      2006-09-15 04:00      0
NO      2006-09-15 05:00      0
NO      2006-09-15 06:00      0
NO      2006-09-15 07:00      0
NO      2006-09-15 08:00      0
NO      2006-09-15 09:00      0
NO      2006-09-15 10:00      0
NO      2006-09-15 11:00      0
0
 

Author Comment

by:vadicherla
ID: 19571096
TABLE NAME                               Partition name
LOGMNR_ICOL$            NO       P_LESSTHAN100       0                                    
LOGMNR_USER$            NO       P_LESSTHAN100       0                                    
LOGMNRC_GSII            NO       P_LESSTHAN100       0                                    
LOGMNR_DICTSTATE$       NO       P_LESSTHAN100       0                                    
LOGMNRC_GTLO            NO       P_LESSTHAN100       0                                    
LOGMNRC_GTCS            NO       P_LESSTHAN100       0                                    
LOGMNR_ATTRIBUTE$       NO       P_LESSTHAN100       0                                    
LOGMNR_TYPE$            NO       P_LESSTHAN100       0                                    

How to find table_name for above error
0
 

Author Comment

by:vadicherla
ID: 19571225
I DONT SEE ANY TABLENAME from follwoing command to implement alter statement
select * from dba_ind_partitions;where index_name = 'SSYSMAN.MGMT_METRICS_RAW_PK_INT'


Thanks
Sudhakar
0
 

Author Comment

by:vadicherla
ID: 19571676
can someone update this ??
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19571849
your drop partition statement would be like this:
alter table test drop partition "2006-09-14 21:00";
0
 

Author Comment

by:vadicherla
ID: 19578984
select * from dba_ind_partitions;where index_name = 'SSYSMAN.MGMT_METRICS_RAW_PK_INT'
 i dont see any table name with above query
0
 

Author Comment

by:vadicherla
ID: 19588414
Sorry i was wroing asking so many questions. I Know the solution for this. Thanks for all your  help. Life savers

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question