Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    28

    Unanswered: global temp table + user temp tablespace

    The global temp table apparently works in the User Temprary Tablespace. The more the data in the GlblTmpTable the bigger the UsrTmpTablespace seems to need to be. And the MAX size for the User Temp Tablespace is 32K pagesize. Does this mean that we cannot have a Global Temp Tablespace beyond a certain size? For 1000 records 16K was not enough so I changed it to 32K. Now if I had 10,000 records and 32K was not sufficient, is there a workaround or are Global Temp Tables merely meant to be used only for limited number of rows??

    Thanks for any help.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I think you are confused about the pagesize and number of pages ...

    4,8,16,32 K are the pagesizes . IF DMS, you will have to define how many such pages you want in each tablespace

    Please can you post the DDL you used to define the tablespace

    Cheers
    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2004
    Posts
    28
    Its a shame I cant find the DDL and the dba that created it for me sits on another planet. But here is what I see from the Control center for this UserTempTablespace:
    Page size 32KB
    Buffer Pool - some abcPool that says 'Page size=32' and 'Size in 32KB pages=1000'
    Extent size=32 32 KB pages
    Prefetch size=32 32 KB pages

    Is this enough info or else I can contact the other planet.

    I now see I was confused about the tablespace size Vs the page size. I just dont know how to make sure I dont have trouble if my global temptable were to have 10,000 records. I noticed the error 1st time when I had additional columns (I think) and a 2nd time when I had additional rows.

    Thanks, Sathyaram.

  4. #4
    Join Date
    Jun 2004
    Posts
    28
    Anybody.....

    Sathyaram, I was hoping you could help.... please...

    Thanks.

  5. #5
    Join Date
    Mar 2004
    Posts
    448
    You also have the option to create buffer pools or table spaces that have an 8 KB, 16 KB, or 32 KB page size. All tables created within a table space of a
    particular size have a matching page size. A single table or index object can be
    as large as 512 GB, assuming a 32 KB page size.
    You can have a maximum of
    1012 columns when using an 8 KB, 16 KB, or 32 KB page size.
    The maximum
    number of columns is 500 for a 4 KB page size.
    Maximum row lengths also vary, depending on page size:
    When the page size is 4 KB, the row length can be up to 4005 bytes.

    When the page size is 8 KB, the row length can be up to 8101 bytes.
    When the page size is 16 KB, the row length can be up to 16 293 bytes.
    When the page size is 32 KB, the row length can be up to 32 677 bytes.

    regards,

    mujeeb

  6. #6
    Join Date
    Jun 2004
    Posts
    28
    Thanks, Mujeeb. I am going to pass this info on to my db guy and he is going to fix stuff for me.

    Thanks.

Posting Permissions

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