Home » RDBMS Server » Server Administration » whats causing space
whats causing space [message #280695] Wed, 14 November 2007 09:28 Go to next message
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 #280698 is a reply to message #280695] Wed, 14 November 2007 09:42 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

1. which tables you trying to copy ?
and instead of copy did you try to move tables from SYSTEM TABLESPACE to USER'S SCHEMAS tablespace.

Now my disk shows 99%used.

You have to check again in your disk (you talking about harddisk) why it is going full ?

Re: whats causing space [message #280699 is a reply to message #280695] Wed, 14 November 2007 09:46 Go to previous messageGo to next message
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 #280701 is a reply to message #280695] Wed, 14 November 2007 09:54 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Whats the syntax to move them?
Re: whats causing space [message #280712 is a reply to message #280701] Wed, 14 November 2007 10:24 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

alter table <tablename> move tablespace <tablespacename>

after moving rebuild all indexes.

alter index <indexname> rebuild
Re: whats causing space [message #280719 is a reply to message #280695] Wed, 14 November 2007 10:30 Go to previous messageGo to next message
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 #280730 is a reply to message #280719] Wed, 14 November 2007 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can never remove a file from a tablespace (prior to 10g).

Regards
Michel
Re: whats causing space [message #280731 is a reply to message #280695] Wed, 14 November 2007 10:55 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Ok that does not matter much i will resize it to few kbytes.

But alter table move command is available in oralcle 7?
Re: whats causing space [message #280748 is a reply to message #280731] Wed, 14 November 2007 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

But alter table move command is available in oralcle 7?

IIRC, No.

Regards
Michel
Re: whats causing space [message #280753 is a reply to message #280695] Wed, 14 November 2007 11:57 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
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.
Re: whats causing space [message #280758 is a reply to message #280695] Wed, 14 November 2007 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>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.
It depends.
It appears that you think that an "empty" table does not require much space.
However depending on a myriad of factors, the new copy of the table(s) could be taking up sizable amount of space.

STORAGE (INITIAL     6144  
            NEXT        6144 
            MINEXTENTS     1  
            MAXEXTENTS     5 ); 

Re: whats causing space [message #280760 is a reply to message #280753] Wed, 14 November 2007 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
export, import with indexfile, modify the generated file to fit your requirement (small table size), drop the original tables, execute the file.

Regards
Michel
Re: whats causing space [message #280762 is a reply to message #280695] Wed, 14 November 2007 12:53 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Confused
Ana, taking your example intial is the size of first extent in bytes.ie 6144 is reserved for the first extent.
Since the tables are empty intial is empty but the space is reserved
So its one and the same thing.
For the hard disk the space is reserved.
NOw when data is inserted, the views show the acquired size in the extents..I guess this is what it is supposed to be..
Re: whats causing space [message #280764 is a reply to message #280695] Wed, 14 November 2007 13:03 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
My code example was just CUT & PASTED from SQL Reference manual.
Your actual values could be much, much larger.
The ONLY way to ensure the new copy of the empty table is the size you want it to be is to explicitly use the STORAGE clause.
Re: whats causing space [message #280777 is a reply to message #280695] Wed, 14 November 2007 13:24 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Michel,how to modify the .dmp file?
Re: whats causing space [message #280780 is a reply to message #280777] Wed, 14 November 2007 13:50 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not the dmp file, the indexfile created by import.

Regards
Michel
Previous Topic: Diferent instances
Next Topic: dataabse size
Goto Forum:
  


Current Time: Sat Dec 03 16:23:29 CST 2016

Total time taken to generate the page: 0.23243 seconds