Home » RDBMS Server » Server Administration » Database Administration
Database Administration [message #266694] Tue, 11 September 2007 07:41 Go to next message
dba_giri
Messages: 26
Registered: July 2007
Location: Hyderabad
Junior Member
Hi..,
I 've executed the following statements on a database which is created using the DBCA(Database Configuration Assistant)
1) create tablespace tt1 datafile 'c:\tt1.dbf' size 1m
Note: This tablespace which won't be autoextended.

2) create user tt1 identified by tt1 default tablespace tt1;
3) grant connect,resource to tt1;
4) CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) ,
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);
Note:
Created the above table with out any Primary key & Foriegn key constraints
Inserted the rows nearly 18000 later I 've got an error like as follows:

ERROR at line 1:
ORA-01653: unable to extend table TT1.EMP by 8 in tablespace TT1

I 've deleted the table EMP and committed

Later I 've tried to create a table , but am unable I've got an error like as follows:

ORA-01658: unable to create INITIAL extent for segment in tablespace TT1

But I can able to create the table with above all above steps in my another database which is created using the following Create Database Script:
Create database orcl
Datafile 'D:\oracle\product\10.2.0\db_1\dbs\orcl\orcl_system.dbf' size 100M
autoextend on next 10m maxsize unlimited
extent management local
CHARACTER SET AL32UTF8
SYSAUX Datafile 'D:\oracle\product\10.2.0\db_1\dbs\orcl\orcl_sysaux.dbf' size 100M
autoextend on next 10m maxsize unlimited
Default temporary tablespace temp tempfile 'D:\oracle\product\10.2.0\db_1\dbs\orcl\orcl_temp.dbf' size 50M
Default tablespace orcl_userdata datafile 'D:\oracle\product\10.2.0\db_1\dbs\orcl\orcl_user.dbf' size 100M
autoextend on next 10m maxsize unlimited
Undo tablespace orcl_undo datafile 'D:\oracle\product\10.2.0\db_1\dbs\orcl\orcl_undo.dbf' size 50M
Logfile group 1('D:\oracle\product\10.2.0\db_1\dbs\orcl\orcl_redo1.log') size 10M,
Group 2('D:\oracle\product\10.2.0\db_1\dbs\orcl\orcl_redo2.log') size 10M

Problem might be what can explain..?


Thanks in advance
Giri K.Y.

[mod-edit] removed illiterate IM speak words.

[Updated on: Tue, 11 September 2007 07:54] by Moderator

Report message to a moderator

Re: Database Administration [message #266708 is a reply to message #266694] Tue, 11 September 2007 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Delete does not release allocated space to the tablespace.

Regards
Michel
Re: Database Administration [message #266868 is a reply to message #266694] Tue, 11 September 2007 21:45 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Instead of use delete you could have been used truncate which frees allocated space.

As you have already done this to to make free space you have to use purge command. This is

Purge tablespace tt1;
Re: Database Administration [message #266907 is a reply to message #266868] Wed, 12 September 2007 00:02 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

autoextend on next 10m maxsize unlimited
YOu are not getting same error on another database becuase there TABLESPACE Created with AUTOEXTEND ON option.

Regards
Mohammed Taj
Re: Database Administration [message #266962 is a reply to message #266694] Wed, 12 September 2007 02:11 Go to previous messageGo to next message
dba_giri
Messages: 26
Registered: July 2007
Location: Hyderabad
Junior Member
Hi.., all,
I am requesting you that please read my problem very clearly(ofcourse it is some what lengthy)..
My question is ,
after deleting the rows(which is reached the max extent) am able to create a new table and able to insert the data which on a database using the DB Creation script which I 've posted.
But it is not happening on a Database which is created using the DBCA.

** The tablespace created in both Databases is Auto extend is off

So, please reply me the problem might be what..?

Thanks in Advance..
Giri K.Y.
Re: Database Administration [message #266965 is a reply to message #266694] Wed, 12 September 2007 02:22 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

It is totally depend on what is inside the script. Check autoextnd and maxsize.
Re: Database Administration [message #266975 is a reply to message #266962] Wed, 12 September 2007 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I am requesting you that please read my problem very clearly

It would be easier if you post it clearly that is formatted.

If you want a clear answer, you have to provide FULL data, description of the tablespaces, datafiles, default storage parameters, tables storages parameters and so on.
And this from the dictionary and not what you think it is.
You also have to copy and paste the sessions when you have or not the error.

Regards
Michel
Re: Database Administration [message #267144 is a reply to message #266975] Wed, 12 September 2007 10:21 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
after deleting the rows(which is reached the max extent) am able to create a new table and able to insert the data which on a database using the DB Creation script which I 've posted.
But it is not happening on a Database which is created using the DBCA.


TRY Shrink Before other activities.
Previous Topic: os level (veritas) fail over for db
Next Topic: 10g Upgrade - block size change
Goto Forum:
  


Current Time: Sun Dec 08 05:32:23 CST 2024