-
I am in need of some help. I have a box with Oracle on it that I just found out I am the DBA for. Problem is I have little experience with enterprise database servers and less with Oracle.
I have been told this is what I need to have done by my dba (me! :o).
"have the db look at the **** tablespace... its the problem. it needs an alter tablespace add datafile"
1) what I have taken this to mean is that, basically, the tablespace needs more hard disk resources??
2) is there a way I can **LOOK** but not touch this? like a listing of allocated & used space??
It is a Solaris box running Oracle 8.1.6, I have all the passwords I need.
Thanks a lot!
-
The following script will provide you with details of the space availiable in each tablespace:
SELECT
fs.tablespace_name name,
df.totalspace mbytes,
(df.totalspace - fs.freespace) used,
fs.freespace free,
100 * (fs.freespace / df.totalspace) pct_free
FROM
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM
dba_data_files
GROUP BY
tablespace_name
) df,
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) FreeSpace
FROM
dba_free_space
GROUP BY
tablespace_name
) fs
WHERE
df.tablespace_name = fs.tablespace_name;
Once you have determined the tablespace requiring the more space either increase it's size:
ALTER DATABASE DATAFILE 'datafile_name' RESIZE size
or add a new datafile
ALTER TABLESPACE "name" ADD DATAFILE 'datafile_name' SIZE size
Regards
-
use
df.tablespace_name = fs.tablespace_name(+)
-
Get a good Oracle DBA book, read it and start looking though the SQL scripts on this site. Look at scripts related to space management and cache hits. Understand what the SGA is and how it works. Learn some basic SQL queries. Take a class, take a lot of classes, the options are endless. Most of all find a mentor. Good luck.
-
forgive my ignorance, but do I simply use that script as is or do I need to replace things with my database name, etc?
belive me, my plan is to get some oracle certification books and get up to speed with this, but this is a problem that basically needs to be fixed in hours, not days.
thanks again.
-
Run the script as is as the system or sys user.
Regards
-
hmm ok. i finally built up the courage to run that command, and interestingly, the one tablespace im looking for doesn't show up!
if i do a 'select tablespace_name from dba_tablespaces', the tablespace I want is listed (23 rows returned).
if i run the above script i get all but this one (22 rows).
I tried another script doing the same thing as above and got the same result.
My feeling is this is a bad sign... ?
Thanks!
-
Do what pando has suggested - replace the last line of the script with the line he proposed. So the last two lines of the query should be:
....
WHERE
df.tablespace_name = fs.tablespace_name(+);
Note the '(+)' at the right hand side of the condition - this means outer join will be performed (Oracle's proprietary notation for outer joins).
Also, what is the name of the tablespace you are looking for? Is it 'TEMP' or something similar?
-
Are you trying to look at tempfiles???....22 rows may not be...tempfiles!!!!!!!!!
If so query V$tempfile or dba_temp_files..
Thanks.
Thanigaivasan.
