DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: need some guidence...

  1. #1
    Join Date
    Sep 2002
    Posts
    3
    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! ).

    "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!

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    use

    df.tablespace_name = fs.tablespace_name(+)

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  5. #5
    Join Date
    Sep 2002
    Posts
    3
    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.

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Run the script as is as the system or sys user.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  7. #7
    Join Date
    Sep 2002
    Posts
    3
    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!

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Are you trying to look at tempfiles???....22 rows may not be...tempfiles!!!!!!!!!
    If so query V$tempfile or dba_temp_files..
    Thanks.
    Thanigaivasan.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width