| whats causing space [message #280695] |
Wed, 14 November 2007 09:28  |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
I have some empty tables in System tablespace.
I need to put these tables in the user's schema.
I am doing create table "user.tablename" as select * from table tablename
It shows the error:
ora-01653:unable to extend minextent beyond 8 in tablespace rxvp.
I added one datafile of size 700m with autoextend on .One table got created well in users schema but for the other tables still i get the same error.
Its oracle 7.(apologies but the project is old).
Now my disk shows 99%used.
Now the database has been shut down and i would tomorrow try to fix the problem.With 99% used space,i think the database should not have any problem to start.
But the tables are empty then what is causing the space?
|
|
|
|
|
|
| Re: whats causing space [message #280699 is a reply to message #280695] |
Wed, 14 November 2007 09:46  |
monto
Messages: 60
Registered: November 2007
Location: DALLAS
|
Member |
|
|
See if you have any other objects of any other schema in that tablespace,
select distinct SEGMENT_TYPE ,count(*),owner from dba_segments where tablespace_name='RXVP' group by SEGMENT_TYPE,owner order by 3;
[Updated on: Wed, 14 November 2007 09:47] Report message to a moderator |
|
|
|
|
|
|
|
| Re: whats causing space [message #280719 is a reply to message #280695] |
Wed, 14 November 2007 10:30 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
So after moving, system tablespace size shall be decreased
and hopefully MOve comand works with oracle 7.
and how can i delete the datafile?
Would this command work?
alter tablespace "name" offline
removing physically
alter tablespace "name" online
[Updated on: Wed, 14 November 2007 10:42] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: whats causing space [message #280753 is a reply to message #280695] |
Wed, 14 November 2007 11:57 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Sorry taj your option is too hi-fi.
So Michel how to resolve it?
The tables in system are empty and i am getting ora-01653 everytime i issue CTAS.
Why is it so?
I created the new datafile with autoextend option.
I cannot delete it as you said.
The /home/oracle7 is 99% full.
and what is IIRS?
[Updated on: Wed, 14 November 2007 11:59] Report message to a moderator |
|
|
|
| Re: whats causing space [message #280755 is a reply to message #280695] |
Wed, 14 November 2007 12:22 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| varu123 wrote on Wed, 14 November 2007 10:28 |
It shows the error:
ora-01653:unable to extend minextent beyond 8 in tablespace rxvp.
I added one datafile of size 700m with autoextend on .One table got created well in users schema but for the other tables still i get the same error.
|
In my world, ORA-1653 is
01653, 00000, "unable to extend table %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
|
|
|
|
| Re: whats causing space [message #280756 is a reply to message #280695] |
Wed, 14 November 2007 12:30 |
varu123
Messages: 754
Registered: October 2007
|
Senior Member |
|
|
Thats done but the space is getting filled.
I am just copying empty tables from system schema to users schema.
I cannot keep on adding space without knowing what is causing it to be full.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
