-
Hi :
I have a locally managed tablespace (TESTSP) created with AUTOALLOCATE clause. Initilaly it was created with 1MB datafile and then added another 1MB datafile.
Now when I try to add records (total size less than 40KB) into the table in this tablespace I am getting the following error:
"ORA-01653: unable to extend table USR.TEST1 by 256 in tablespace TESTSP".
I queried the dictionary tables and it seems the table/tablespace is using only one of the datafiles (only 1024K is occupied). I have almost 1MB of free space in the tablespace.
DBA_EXTENTS shows 16 extents with 64KB each.
Now, my question is about the AutoAllocate clause of the Locally Managed Tablespace.
1, Am I missing anything here?
2, When I add more data files after the creation of the Tablespace, wouldn't it be taken into consideration?
3, If adding a datafile, doesn't help, is the only other way to "Migrate the locally-managed tablespace to a dictionary management" and then alter the extent parameter?.
4, could anyone of you point me to some good documentation on how the autoallocate clause works. (I went thru the oracle manuals, but I couldn't get much info on why the insert fails even though there is enough space)
Thanks,
-
correction,
------------------------------------------------------------------------
I queried the dictionary tables and it seems the table/tablespace is using only "one of the datafiles" (only 1024K is occupied).
------------------------------------------------------------------------
I am not sure on this, but I do know only 1MB is used. 15 extents are on the 1st datafile and the 16th on is on the newly added datafile.
-
Are you sure that, the second datafile is attached to this TB?
Thanks
Kishore Kumar
-
I am very sure.
See the o/p of the following qry,
select substr(file_name,1,40) "FileName", tablespace_name TS, bytes, status,
AUTOEXTENSIBLE, maxbytes,user_bytes, user_blocks
from dba_data_files
where tablespace_name = 'TESTSTP';
FileName TS BYTES STATUS AUT MAXBYTES USER_BYTES USER_BLOCKS
---------------------------------- ------ ------- --------- ----- -------- ---------- -----------
/oracle/dbacct/oradata/testsp01.db TESTSP 1048576 AVAILABLE NO 0 983040 240
/oracle/dbacct/oradata/testsp02.db TESTSP 1048576 AVAILABLE NO 0 983040 240
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
|