Home » SQL & PL/SQL » SQL & PL/SQL » tablespace
tablespace [message #188663] Mon, 21 August 2006 02:46 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello

How can I find out how big a table is. Could someone give me the sql statement for that?

Thanks
Stefan
Re: tablespace [message #188667 is a reply to message #188663] Mon, 21 August 2006 03:03 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
SQL> SELECT bytes/1024/1024 "Meg" FROM user_extents WHERE segment_name = 'EMP'
  2  /

       Meg
----------
     .0625
Re: tablespace [message #188672 is a reply to message #188663] Mon, 21 August 2006 03:14 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Thanks, but I don't understand your code. I'm a user of an oracle DB and want to get the size of my table TEMP. When I run this code

SELECT bytes/1024/1024 "Meg" FROM user_extents WHERE segment_name = 'TEMP'



I get a table with numbers in 100 records! Thats that?

Thanks
Stefan
Re: tablespace [message #188675 is a reply to message #188672] Mon, 21 August 2006 03:21 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Oops, use user_segments:

SQL> CREATE TABLE temp (col1 NUMBER);

Table created.

SQL> SELECT bytes/1024/1024 "Meg", bytes/1024 "K", bytes
  2    FROM user_segmentss WHERE segment_name = 'TEMP';

       Meg          K      BYTES
---------- ---------- ----------
     .0625         64      65536

Re: tablespace [message #188680 is a reply to message #188675] Mon, 21 August 2006 03:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's a problem with test scripts - no large volume tests.
If you've got a lot of data in a table, you get multiple extents.

SELECT segment_name, sum(bytes/1024/1024) "Meg" 
FROM user_extents 
WHERE segment_name = 'FPS_EXP_BC01'
group by segment_name;
Re: tablespace [message #188681 is a reply to message #188663] Mon, 21 August 2006 03:29 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Sorry, but I realy don't understand your code. First, you create a table 'temp'. But this is the table I want to know how big it is?

Thanks for explanation.
Stefan
Re: tablespace [message #188683 is a reply to message #188681] Mon, 21 August 2006 03:33 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
I didn't have such a table - so, I created it for testing purposes. You can skip that statement Smile
Re: tablespace [message #188686 is a reply to message #188663] Mon, 21 August 2006 03:44 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Now the solution from JRowbottom works perfect. Thanks for your help!
Re: tablespace [message #188840 is a reply to message #188663] Tue, 22 August 2006 01:16 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hello

I've got another problem: how can I find out the available memory space in the segment where I have all my tables?

Could you please post the sql code?

Thanks!
Stefan
Re: tablespace [message #188937 is a reply to message #188840] Tue, 22 August 2006 08:49 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Memory space? I don't know what you mean by memory space as there is no such thing. Do you mean free space?
select * from dba_free_space
where tablespace_name='USERS';

will give you all the free chunks of space, or simply use sum(bytes) to get a total number of bytes.
Re: tablespace [message #188953 is a reply to message #188663] Tue, 22 August 2006 09:56 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Thanks, but with sum(bytes) I only get the table space but not the free space. With

select * from dba_free_space
where tablespace_name='USERS';



I get an error that the table dba_free_space does not exists. Any other solution?

Thanks
Stefan
Re: tablespace [message #188954 is a reply to message #188953] Tue, 22 August 2006 10:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'll need to connect as someone with DBA to see it.

Also, what release are you on?

You can try a query like this:
SELECT  DISTINCT
 a1.tablespace_name||a4.tablespace_name Tablespace_Name,
 a3.Segments Fragmentation,
 a2.Filesize_MB Tablespace_Size_Mb,
 a3.Bytes_left_MB Space_Left_Mb,
 ROUND(((a2.filesize_Mb - Bytes_left_MB)/(a2.filesize_Mb)*100),2) Used_pct
FROM
 DBA_DATA_FILES a1, DBA_TEMP_FILES a4,
 (
 SELECT  Tablespace_name,
  SUM(bytes/1024/1024) FileSize_MB
 FROM
  DBA_DATA_FILES
 GROUP BY Tablespace_name
 UNION ALL
 SELECT  Tablespace_name,
  SUM(bytes/1024/1024) FileSize_MB
 FROM
  DBA_TEMP_FILES
 GROUP BY Tablespace_name
 ) a2,
 (
 SELECT
  tablespace_name,
  ROUND(SUM(bytes/1024/1024),2) Bytes_left_MB,
  COUNT(*) Segments
 FROM  DBA_FREE_SPACE
 GROUP BY tablespace_name ) a3
WHERE
 a1.tablespace_name(+) = a2.tablespace_name
AND a2.tablespace_name = a3.tablespace_name(+)
AND a2.tablespace_name = a4.tablespace_name(+)
ORDER BY Used_Pct DESC;

Re: tablespace [message #188975 is a reply to message #188672] Tue, 22 August 2006 11:14 Go to previous message
fahadsami
Messages: 31
Registered: May 2006
Member
just do this add on to stefan's query to get total size:

SELECT sum(bytes/1024/1024) "Meg" FROM user_extents WHERE segment_name = 'EMP'

Thanks


Fahad
Previous Topic: Problems with DBMS_XMLQUERY
Next Topic: Year Format
Goto Forum:
  


Current Time: Wed Dec 07 12:51:59 CST 2016

Total time taken to generate the page: 0.19608 seconds