DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: problem with partitioned tables

  1. #1
    Join Date
    Sep 2001
    Location
    UK
    Posts
    45

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2001
    Location
    UK
    Posts
    45
    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

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jan 2004
    Posts
    15
    You can SPLIT the partition....

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by and
    You can SPLIT the partition....
    Tell us how that helps.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width