urgent - Tablespace extent problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: urgent - Tablespace extent problem

  1. #1
    Join Date
    Nov 2000
    Posts
    160

    Unhappy

    Dear all,

    I tried to create a table in a tablespace test which is only 1.3% used out of total 300M, but I got error message as :
    ORA-01658: unable to create INITIAL extent for segment in tablespace TEST.

    The original script for creating tablespaces test was,
    CREATE TABLESPACE test
    DATAFILE '/orajde1/test01.dbf' SIZE 80M
    AUTOEXTEND ON NEXT 1024K MAXSIZE 300M
    DEFAULT STORAGE
    (INITIAL 500K
    NEXT 500K
    MINEXTENTS 1
    MAXEXTENTS 121
    PCTINCREASE 0);

    I don't understand why it has extended all up to 300M since this tablespace has only one small table in it so far? Are some of the parameter settings inside the default storage are too big? There are enough space in this tablespace, why not able to create the table? Please advise!

    Thanks,
    Unna

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    run this query and paste the output plz

    Code:
    select  a.tablespace_name, total "SIZE(MB)",
            free "FREE(MB)", 100 - trunc(free*100/total) "%USED", fragments,
            biggest "BIGGEST_FREE_EXTENT", smallest "SMALLEST_FREE_EXTENT"
    from    (
    	select tablespace_name, sum(bytes/(1024*1024)) "TOTAL" 
    	from dba_data_files 
    	group by tablespace_name
    	) a,
            (
            select tablespace_name, trunc(sum(bytes/(1024*1024)), 2) "FREE", count(bytes)
    	"FRAGMENTS", max(bytes) "BIGGEST", min(bytes) "SMALLEST"
            from dba_free_space group 
            by tablespace_name
            ) b
    where   a.tablespace_name = b.tablespace_name(+)
    group   by a.tablespace_name, total, free, fragments, biggest, smallest
    order   by a.tablespace_name
    /

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Can you post CREATE TABLE statment.

  4. #4
    Join Date
    Nov 2000
    Posts
    160
    I can create table with very few columns, if more than certain rows like what I was doing it gave me the error.

    I run the script and the result is

    TABLESPACE_NAME SIZE(MB) FREE(MB) %USED FRAGMENTS
    ------------------------------ ---------- ---------- ---------- --------BIGGEST_FREE_EXTENT SMALLEST_FREE_EXTENT
    ------------------- --------------------
    TEST 300 295.92 2 6
    140034048 4710400


    Please advice, many thanks!!!

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well space seems good, now let's check you create table statement

  6. #6
    Join Date
    Nov 2000
    Posts
    160
    CREATE TABLE "PROD" ("CO" CHAR(5), "DCT" CHAR(2),
    "DOC" NUMBER, "GJ" NUMBER(6, 0), "LN" NUMBER, "XTL" CHAR(2), "POST" CHAR(1), "ICU" NUMBER, "ICUT" CHAR(2), "ICJ" NUMBER(6, 0), "SYJ" NUMBER(6, 0), "TICU" NUMBER, "LCO" CHAR(5),"NI" CHAR(29), "AM" CHAR(1), "ID" CHAR(8), "MCU" CHAR(12))
    PCTFREE 10 PCTUSED 40
    REM INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 422821888 NEXT 57344
    REM MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST
    REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST" ;

    This table has 620000 rows.



  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    did you try

    CREATE TABLE "PROD" ("CO" CHAR(5), "DCT" CHAR(2),
    "DOC" NUMBER, "GJ" NUMBER(6, 0), "LN" NUMBER, "XTL" CHAR(2), "POST" CHAR(1), "ICU" NUMBER, "ICUT" CHAR(2), "ICJ" NUMBER(6, 0), "SYJ" NUMBER(6, 0), "TICU" NUMBER, "LCO" CHAR(5),"NI" CHAR(29), "AM" CHAR(1), "ID" CHAR(8), "MCU" CHAR(12))
    PCTFREE 10 PCTUSED 40

    without the REM and stuffs

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