How to check tablespace size and if it's exceeded 2

[fireant]

Hi all,

I'm new to Oracle and I just inherited the database i'm currently working on so please bear with me. It's an Oracle 8i and runs on Solaris 2.6

I need to find out the ff. from my system:

1) Existing tablespaces and their size allocations;
2) the tablespace where the table I'm working on and if the size has been exceeded already; and
3) the version (release?) of my Oracle.

Your soonest replies are greatly appreciated.

Regards,
Louie C.

Louie C.

 

[rcurva]

1) For your tablespaces setup, you can use:

SELECT *
from dba_tablespaces;

To see how much is still free, and the max contiguous space:

SELECT a.tablespace_name,
SUM(a.tots) Total_Size,
SUM(a.sumb) Total_Free,
SUM(a.sumb) * 100/SUM(a.tots) Pct_Free,
SUM(a.largest) Max_Free,
SUM(a.chunks) Chunks_Free
FROM (SELECT tablespace_name, 0 tots, SUM(bytes) sumb,
MAX(bytes) largest, COUNT(*) chunks
FROM dba_free_space
GROUP by tablespace_name
UNION
SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
FROM dba_data_files
GROUP by tablespace_name) a
GROUP by a.tablespace_name;


2)

SELECT *
FROM DBA_TABLES
WHERE TABLE_NAME = 'TABLE1';

** Take note of tablespace column, check for max_extents of table. If set to a small value, increase it. Check also the next_extents value of the table and compare it with the max_free of the the above query.

3)


Try to connect via sqlplus, then diconnect. This will show you the version of your DB.

sql> disc

OR

select *
from v$version;


I just hope that you have the necessary privileges to select some of the views that I mentioned here. You must have DBA privs. to have access on some of the views here.

 

[fireant]

hey rcurva,

Thanks for the tip! It was really helpful!

Louie C. Louie C.