Home » SQL & PL/SQL » SQL & PL/SQL » resizing the database
resizing the database [message #242870] Tue, 05 June 2007 06:21 Go to next message
vkrmhj
Messages: 9
Registered: May 2007
Junior Member
My table data is going to increase by 1 million, so i have to add tablespace space.I have run the following query for the table"select avg(nvl(vsize(col1),1))+avg(nvl(vsize(col2),1))+... from tab1" and multiplied the result(in bytes) with 1 million.I am increasing the tablespace by this result.Am i right?Is there any other better way to do it.
Thanks in advance,
Vicky
Re: resizing the database [message #242873 is a reply to message #242870] Tue, 05 June 2007 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Divide the used blocks per your number of rows then multiply this per one million, you then have the number of blocks to add.

Regards
Michel
Re: resizing the database [message #242876 is a reply to message #242873] Tue, 05 June 2007 06:49 Go to previous messageGo to next message
vkrmhj
Messages: 9
Registered: May 2007
Junior Member
Michael..thanx for ur suggestion..
i add space by "alter tablespace ts1 add datafile df1 size xKB" so why do i need to know the space a row occupies in a block..(i calculate the space for each row and multiply this by 1 million- as i did)
Is the method i am using erroneous in any way?
Re: resizing the database [message #242879 is a reply to message #242876] Tue, 05 June 2007 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is overhead due to Oracle internal data and the way you use the table (inserts, deletes, updates).
So the method with blocks is more accurate and faster.
Your method gives too low figures.

Regards
Michel
Re: resizing the database [message #242881 is a reply to message #242879] Tue, 05 June 2007 07:17 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Is there any other better way to do it?


Is there any specific reason not to use autoextend?

e.g.

ALTER TABLESPACE XXXX 
      ADD DATAFILE '/foo/bar_02.dbf' SIZE 40M 
      AUTOEXTEND ON NEXT 5M MAXSIZE 200M


or something like that?
Previous Topic: Unable to fetch data through Oracle Net
Next Topic: enable and diasable of constraints
Goto Forum:
  


Current Time: Fri Dec 09 05:40:12 CST 2016

Total time taken to generate the page: 0.05125 seconds