| ORA-01653 unable to extend table error [message #191588] |
Thu, 07 September 2006 03:25  |
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 #191602 is a reply to message #191588] |
Thu, 07 September 2006 04:19  |
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 |
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 |
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 |
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 |
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 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
