Home » RDBMS Server » Server Administration » ORA-01653 unable to extend table error
ORA-01653 unable to extend table error [message #191588] Thu, 07 September 2006 03:25 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
can anyone please help me with this scenario


i have a table TBL_STATION_NAMES in schema A...whose data is ever increasing

(1000 records per second)....the tablespace for this is "system", as i did not

mention any other tablespace name while creating the user "A".


when my java team is inserting data into this table, when lakhs of data is inserted,

the performance is getting affected, so i gave the following command, as i am not

a DBA, and to whatever knowledge i have and searched, i gave this command in system/manager


alter user A quota unlimited on system; 




but now they face this exception


java.sql.SQLException: ORA-01653: unable to extend table A.TBL_STATION_NAMES by 2397
in tablespace SYSTEM




which means, the tablespace quota is refusing to increase..

what went wrong? how could i give some other commands for them not to get this

error, or to increase the performance?

I am not a dba and i have no idea about "datafiles" etc except a basic idea...

how to go about it?
Re: ORA-01653 unable to extend table error [message #191590 is a reply to message #191588] Thu, 07 September 2006 03:31 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i have created an index for a column for this table

will that have any effect?


Re: ORA-01653 unable to extend table error [message #191597 is a reply to message #191590] Thu, 07 September 2006 04:01 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


-- Increase the size of SYSTEM tablespace.... or


-- Move the table to a separate tablespace...

Alter table table_name move tablespace new_tablespace_name
Re: ORA-01653 unable to extend table error [message #191602 is a reply to message #191588] Thu, 07 September 2006 04:19 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
1st option is increase the size of system tablespace

which i did, when i said "alter user username quota unlimited

on system" ....but it is not working


so now 2nd option, is move the table to seperate tablespace,

which means i need to create new tablespace for this purpose?

and then move the user? how to give a very very big size to this

new tablespace ? can you please let me know? also please explain

how to "move" my user to this new tablespace?


Re: ORA-01653 unable to extend table error [message #191603 is a reply to message #191588] Thu, 07 September 2006 04:22 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
ok then, you want me to move this table to a new tablespace

and not the user itself...

can you provide me the syntax of creating this new tablespace

with a very big size, almost unlimited size?


and then i can give the command you specified


Re: ORA-01653 unable to extend table error [message #191611 is a reply to message #191603] Thu, 07 September 2006 05:25 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Gautam,

1. "alter user username quota unlimited on system" ....but it is not working

The above command assigns unlimited quota on the specified tablespace but no way increase the size.

2. so now 2nd option, is move the table to seperate tablespace, which means i need to create new tablespace for this purpose?

Not necessarily, you can move it to an existing tablespace also provided it has sufficient space to hold the data.

3. can you provide me the syntax of creating this new tablespace with a very big size, almost unlimited size?

Bad idea, always keep a watch on the growth of the tablespace otherwise it will eat all your filesystem. Start with a normal size and keep growing as per your requirement.

 Create tablespace new_tablespace_name datafile '...../new_tablespace.dbf' size 100M;




Re: ORA-01653 unable to extend table error [message #191623 is a reply to message #191588] Thu, 07 September 2006 06:09 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
ok now to begin with, i wanted to know all the datafiles

available, so in system/manager, i did this


SQL> select name from v$datafile
  2  ;

NAME
--------------------------------------------------
C:\ORACLE\ORADATA\INDUS\SYSTEM01.DBF
C:\ORACLE\ORADATA\INDUS\USERS01.DBF
C:\ORACLE\ORADATA\INDUS\RBS01.DBF
C:\ORACLE\ORADATA\INDUS\TEMP01.DBF
C:\ORACLE\ORADATA\INDUS\OEMREP01.DBF
C:\ORACLE\ORADATA\INDUS\INDX01.DBF
C:\ORACLE\ORADATA\INDUS\TOOLS01.DBF
C:\ORACLE\ORADATA\INDUS\TEST.DBF
C:\ORACLE\ORADATA\INDUS\MYTOOL01.DBF







i dont know my table comes under which tablespace, and what

datafile is under this tablespace

but i randomely chose users01, and increased the size of this

datafile to 1gb as follows


alter database datafile 'C:\ORACLE\ORADATA\INDUS\USERS01.DBF' resize 1024M;
Database altered.



now i wanted to create a new tablespace and associate it with

this datafile, so that i can move my table to this tablespace

using "alter table table_name" command

SQL> Create tablespace gautam datafile 'C:\ORACLE\ORADATA\INDUS\USERS01.DBF' size 1024M;
Create tablespace gautam datafile 'C:\ORACLE\ORADATA\INDUS\USERS01.DBF' size 1024M
*
ERROR at line 1:
ORA-01537: cannot add data file 'C:\ORACLE\ORADATA\INDUS\USERS01.DBF' - file already part of
database



this is what i am getting




Re: ORA-01653 unable to extend table error [message #191624 is a reply to message #191588] Thu, 07 September 2006 06:12 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
btw, i am using an intresting query, which will give an idea

about the usage each tablespace takes in my system

SQL> select a.TABLESPACE_NAME, round(total,1) Total_M, round(free) Free_M, round(100*(1-free/total),1) Usage
2 from (select TABLESPACE_NAME,sum(BYTES)/(1024*1024) total from dba_data_files group by TABLESPACE_NAME) a
3 ,(select TABLESPACE_NAME,sum(BYTES)/(1024*1024) free from dba_free_space group by TABLESPACE_NAME) b
4 where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) order by 4 DESC;

TABLESPACE_NAME TOTAL_M FREE_M USAGE
------------------------------ --------- --------- ---------
TOOLS 17 0 98.9
SYSTEM 500 64 87.2
USERS 1024 936 8.6
RBS 150 138 7.9
TEST 10 10 .6
mytool 10 10 .6
INDX 2 2 .1
OEM_REPOSITORY 5 5 0
TEMP 12 12 0

9 rows selected.
Re: ORA-01653 unable to extend table error [message #191630 is a reply to message #191588] Thu, 07 September 2006 06:32 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i did this, so far so good




1) datafiles

give this from system/manager

select name from v$datafile

C:\ORACLE\ORADATA\INDUS\MYTOOL01.DBF

2)

Create tablespace astra datafile 'C:\ORACLE\ORADATA\INDUS\gau.DBF' size 1024M;


Tablespace created.

2) go to your user (isro/isro)

alter table tbl_station_names move tablespace astra;
Table altered.




Re: ORA-01653 unable to extend table error [message #191631 is a reply to message #191630] Thu, 07 September 2006 06:36 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


Correct, but do read some Oracle manuals/books so that you can understand the functionality well.
Avoid this hit and trial approach, it can cost you a lot..
Re: ORA-01653 unable to extend table error [message #191636 is a reply to message #191588] Thu, 07 September 2006 07:08 Go to previous message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
yes, thanks !
Previous Topic: Finding out the value of max_dump_file_size
Next Topic: Create DATABASE without being OS Administrator
Goto Forum:
  


Current Time: Sat Dec 03 01:29:47 CST 2016

Total time taken to generate the page: 0.08474 seconds