DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: pctused for image tables

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    pctused for image tables

    Hi Friends,

    I am creating a table for BLOB images. This table is insert only and no update.
    Does pctused and pctfree important here?

    Say, do the following create command differ and matter much?

    1. create table image_master(id number, image blob) tablespace IMAGE_TS;

    2. create table image_master(id number, image blob) (PCTUSE 99 PCTFREE 1)tablespace IMAGE_TS

    Does Oracle 10g don't need to specify pctused and pctfree?


    Thanks a lot
    Behind The Success And Failure Of A Man Is A Woman

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I am not sure i am able to understand your question as PCTFREE and PCTUSED does not apply to LOB segments .

    read here
    http://download.oracle.com/docs/cd/B....htm#sthref213


    I would have suggested Automatic Storage Segment Management fro pctfree and pctused but they have proved buggy to me when i was on a Datawarehousing System and also when i am trying to delete data over a database link.

    Take advise with a pinch of salt

    regards
    Hrishy

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    pctfree will not be managed by ASSM
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Quote Originally Posted by abhaysk
    pctfree will not be managed by ASSM
    Thanks dear....so what is it going to suggest?

    Do I take advise with a pinch of sugar or salt
    Behind The Success And Failure Of A Man Is A Woman

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by yxez
    Thanks dear....so what is it going to suggest?

    Do I take advise with a pinch of sugar or salt
    Are your tablespaces created as ASSM?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Feb 2007
    Posts
    212
    I think its not dear I just created it using the 10g OEM and use all
    default options there. Is ASSM touched by OEM too?

    Anyways, my concern really is....for my IMAGE table, I want may disk storage
    to be 100% or 99% used (insert) and maybe 1% room for updates.

    I often see default pctfree for tablespaces 60 pctused 40 pctfree.
    Does this mean that if I have 1000kb size tablespace for my IMAGE table,
    I can only use 600kb for my insert data and the remaining 400kb is reserved
    for updates?


    Thanks a lot for your clarifications

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    You are not reading previous posts Jenn but, it doesn't surprise me anymore.

    Please refer to hrishy post... "PCTFREE and PCTUSED does not apply to LOB segments"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    sorry dear .... it just that abhaysk is commenting on ASSM issue, I thot
    itS affecting my pctused for BLOB

    Its so hard and confusing this BLOB images to handle
    Like in my case now....i take backup of my image table (IMG_MSTR) using export with around 500,000 rows in it. when I check the segments sizes I see the table IMG_MSTR segment occupies 50M storage space, while it SYS_LOB$$ contents have 26Gb storage spcace.

    I tried to import it to another backup server. I first created the table with
    inital size of 30 GB coz i want to fill it in one segment. But when I tried
    importing its just hangs up to the 15gb mark of the tablesapace I created.
    The import was not moving up anymore. so I aborted it

    Any tips it importing a blob table please. does it use rollbacks or temp?
    do I have to put commit=y? I checked the TEMP and UNDO TS in OEM while
    the import is running but they are not growing too. It means BLOB imports
    doesnt use much of temp and undo even if the image size is 26GB.
    i check tha allocated space of 30GB and it is half used even if I aborted the
    import. I counted the img_mstr import and its zero.

    How do i debug my blob import pls....sny tips to make it fast? can I use
    direct path import for blob?

    Thanks alot
    Last edited by yxez; 09-22-2007 at 01:41 PM.
    Behind The Success And Failure Of A Man Is A Woman

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I'm having the feeling Jennifer2007 and yxez are one and the same person.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Feb 2007
    Posts
    212
    Yes dear this is me ....My other nick was banned before for debating with some Advisors here I just tried it today and its fine now

    Anyways, back to my problem....As I said I have an Image table IMAGE_MSTR
    on our PROD database with 500,000 rows and checking the user_segments dictionary I saw that the table segment itself occupies around 50Mb space while its counterpart SYS_LOB$$ has 29Gb or around 29,000Mb, too far a ratio.

    I imported the above table and restore it to the BACKUP server. I precreated the table on a local managed tablespace, and used default storage starting at 64kb.In my import, I now put commit=y so i will know how many rows are being inserted.

    While doing monitoring of my import (by select count(*) from img_mstr)
    and (select bytes from user_segments where segment_name like 'SYS_LOB%')
    I observed that it stops at row count 38,062 while the SYS_LOB segment
    size is at around 2,000Mb

    Any ideas please why my import hangs ups(stops) at this magic numbers?


    Thanks a lot
    Last edited by jennifer2007; 09-22-2007 at 08:18 PM.

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