OraFAQ Forum: Server Administration » whats causing space

Home » RDBMS Server » Server Administration » whats causing space

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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?

Report message to a moderator

Re: whats causing space [message #280698 is a reply to message #280695]

Wed, 14 November 2007 09:42

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 ?

Report message to a moderator

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 #280701 is a reply to message #280695]

Wed, 14 November 2007 09:54

varu123
Messages: 754
Registered: October 2007

Senior Member

Whats the syntax to move them?

Report message to a moderator

Re: whats causing space [message #280712 is a reply to message #280701]

Wed, 14 November 2007 10:24

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

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 #280730 is a reply to message #280719]

Wed, 14 November 2007 10:52

Michel Cadot
Messages: 68684
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

Report message to a moderator

Re: whats causing space [message #280731 is a reply to message #280695]

Wed, 14 November 2007 10:55

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?

Report message to a moderator

Re: whats causing space [message #280748 is a reply to message #280731]

Wed, 14 November 2007 11:39

Michel Cadot
Messages: 68684
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

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.

Report message to a moderator

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.

Report message to a moderator

Re: whats causing space [message #280758 is a reply to message #280695]

Wed, 14 November 2007 12:39

BlackSwan
Messages: 26766
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 );

Report message to a moderator

Re: whats causing space [message #280760 is a reply to message #280753]

Wed, 14 November 2007 12:43

Michel Cadot
Messages: 68684
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

Report message to a moderator

Re: whats causing space [message #280762 is a reply to message #280695]

Wed, 14 November 2007 12:53

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..

Report message to a moderator

Re: whats causing space [message #280764 is a reply to message #280695]

Wed, 14 November 2007 13:03

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: whats causing space [message #280777 is a reply to message #280695]

Wed, 14 November 2007 13:24

varu123
Messages: 754
Registered: October 2007

Senior Member

Michel,how to modify the .dmp file?

Report message to a moderator

Re: whats causing space [message #280780 is a reply to message #280777]

Wed, 14 November 2007 13:50

Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not the dmp file, the indexfile created by import.

Regards
Michel

Report message to a moderator

Previous Topic:	Diferent instances
Next Topic:	dataabse size

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 18 18:33:01 CDT 2024