Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    1

    Unanswered: Tablespace full with empty objects

    I am receiving error SQL0289N while attempting to create several tables (just the table definition, no data). This is a new tablespace, is 100M in size, pagesize 32K and extentsize 128. It currently contains 3 empty tables that have never contained data. How can I determine what is taking up all of the space in this tablespace? Is space preallocated for a table when it is created? I wouldn't think 3 empty tables would take up any space initially. Can anyone help me with understanding why I'm getting this error with no data?

    TIA,
    C Haku

  2. #2
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39

    Re: Tablespace full with empty objects

    Hi,

    Can you do a list tablesapces show detail and check for the free pages

    And after that do a reorg on all these three tables.
    Again do a list tablespaces show detail and check for the free pages.
    Do you see any difference.

    Thanks
    Sateesh
    Originally posted by chaku
    I am receiving error SQL0289N while attempting to create several tables (just the table definition, no data). This is a new tablespace, is 100M in size, pagesize 32K and extentsize 128. It currently contains 3 empty tables that have never contained data. How can I determine what is taking up all of the space in this tablespace? Is space preallocated for a table when it is created? I wouldn't think 3 empty tables would take up any space initially. Can anyone help me with understanding why I'm getting this error with no data?

    TIA,
    C Haku

  3. #3
    Join Date
    Mar 2003
    Posts
    343
    Some findings on a dms tablespace:

    I created a tablespace with a 4k pagesize and 10000 pages on each node (eee 7.2 fp7 - aix433 - multiple logicals/physical).

    1] After creating the tablespace, a list tablespaces show detail returned for each node : (there were no objects in there)

    Tablespace ID = 218
    Name = USERSPACE2
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 10000
    Useable pages = 9920
    Used pages = 48
    Free pages = 9872
    High water mark (pages) = 48
    Page size (bytes) = 4096
    Extent size (pages) = 16
    Prefetch size (pages) = 80
    Number of containers = 5
    Minimum recovery time = 2003-06-04-18.38.08.000000

    2] After creating a table with 2 columns, it returned for each node :

    Tablespace ID = 218
    Name = USERSPACE2
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 10000
    Useable pages = 9920
    Used pages = 80
    Free pages = 9840
    High water mark (pages) = 80
    Page size (bytes) = 4096
    Extent size (pages) = 16
    Prefetch size (pages) = 80
    Number of containers = 5
    Minimum recovery time = 2003-06-04-18.41.18.000000

    c] After creating another table with 3 columns, it returned for each node :

    Tablespace ID = 218
    Name = USERSPACE2
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 10000
    Useable pages = 9920
    Used pages = 112
    Free pages = 9808
    High water mark (pages) = 112
    Page size (bytes) = 4096
    Extent size (pages) = 16
    Prefetch size (pages) = 80
    Number of containers = 5
    Minimum recovery time = 2003-06-04-18.43.00.000000

    e] After creating a table with 10 columns it returned

    Tablespace ID = 218
    Name = USERSPACE2
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 10000
    Useable pages = 9920
    Used pages = 144
    Free pages = 9776
    High water mark (pages) = 144
    Page size (bytes) = 4096
    Extent size (pages) = 16
    Prefetch size (pages) = 80
    Number of containers = 5
    Minimum recovery time = 2003-06-04-18.53.18.000000

    So for a tablespace with a 4k pagesize and an extentsize of 16, the overhead for a tablespace is 80 pages + 48 pages and for each regular empty table(no special datatypes) is 32 pages. And in my opinion, this overhead will not linearly increase with pagesize and extentsize. So that might just be where the 100m went.

Posting Permissions

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