-
problem with partitioned tables
Hello,
I have a partitioned table on which i have created an index as follows
CREATE UNIQUE INDEX IDX1_PSIF_PRODUCT_SUPPORT ON
PSIF_PRODUCT_SUPPORT(LOAD_DT, PART_NO, LOCATION)
GLOBAL PARTITION BY RANGE (LOAD_DT)
(
PARTITION IPWK5 VALUES LESS THAN (4782) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK9 VALUES LESS THAN (4810) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK10 VALUES LESS THAN (4817) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK14 VALUES LESS THAN (4845) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK18 VALUES LESS THAN (4873) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK23 VALUES LESS THAN (4908) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK27 VALUES LESS THAN (4936) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK33 VALUES LESS THAN (4978) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK38 VALUES LESS THAN (5013) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK42 VALUES LESS THAN (5042) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK46 VALUES LESS THAN (5070) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK51 VALUES LESS THAN (5105) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK52 VALUES LESS THAN (5112) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK53 VALUES LESS THAN (5114) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK01_2004 VALUES LESS THAN (5118) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK02_2004 VALUES LESS THAN (5125) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK03_2004 VALUES LESS THAN (5132) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWK04_2004 VALUES LESS THAN (5139) TABLESPACE DMDSS_LM4M_IDX,
PARTITION IPWKMAX VALUES LESS THAN (MAXVALUE) TABLESPACE DMDSS_LM4M_IDX);
The problem is that after every 3 weeks or so i get a index tablespace problem even though there is enough free space available
This occurs when the max value partition holds more than 3-4 weeks data
As a result i always have to drop the index and re-build according to the current week's position
Is there any workaround to this problem
This table holds a large volume of data in a datawarehousing project
Thanks in advance
-
Re: problem with partitioned tables
Originally posted by dinesh_hm
The problem is that after every 3 weeks or so i get a index tablespace problem even though there is enough free space available
This occurs when the max value partition holds more than 3-4 weeks data
As a result i always have to drop the index and re-build according to the current week's position
What are the symptoms of the problem?
-
As a result of this tablespace error the daoliy loading of the table
comes to a halt and all successive jobs are held up due to this
-
Hi,
This might be due to fragmentation on the tablespace. Use this SQL to find the free space available by extents and recreate your index with same extent size with pctincrease 0.
select block_id,(bytes)/1024/1024 from dba_Free_space where tablespace_name=(tablespace_name) order by bytes;
This will give you blocks with their size.Based on this info create ur index.For example if your output looks like
BLOCK_ID (BYTES)/1024/1024
---------- -----------------
207503 200
370384 243.623047
357439 285.259766
315535 383.722656
188507 431.824219
20692 550.009766
125644 754.603516
It shows that the amount of free space in extents.So you can create ur index with extent size as say 50M or 100M.
Also coalesce your tablespace
alter tablespace tablespace_name coalesce;
Regards
anandkl
anandkl
-
I do not understand why you are creating partitions for index. When create Index on partition key it should partition the index for you.
I have never experienced this. What version of oracle you are running ?
Reddy,Sam
-
Originally posted by dinesh_hm
As a result of this tablespace error the daoliy loading of the table
comes to a halt and all successive jobs are held up due to this
You know what you might like to do? Actually tell us what the error is, and what you've done to show that it is incorrect. That'd help.
-
You can SPLIT the partition....
-
Originally posted by and
You can SPLIT the partition....
Tell us how that helps.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|