-
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
-
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
/
-
Can you post CREATE TABLE statment.
-
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!!!
-
well space seems good, now let's check you create table statement
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|