Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48

    Unanswered: How do I know if a tablespace is in use

    Hi everybody...
    We have a tablespace named "precise", I want to know if it is in use by any user, is there a simple way to check this out?
    Also I want to delete it if is not in use, how can I do it?
    regards,
    -eduardo s.m.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select count(segment_name)
    from dba_extents
    where tablespace_name = 'PRECISE';
    If it return 0, then OK to drop it.
    If it contains objects, then more research is needed.
    Make sure you have a good backup BEFORE dropping the tablespace!

  3. #3
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48
    OK, I ran the following select:

    Code:
    SQL> select count(segment_name) 
    from dba_extents 
    where tablespace_name ='PRECISE_INDEX_TBS';
    
    COUNT(SEGMENT_NAME)
    -------------------
                    158
    SQL>
    does it means that the tablespace is in use???
    regards,
    -eduardo s.m.

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    Hi ,
    Does a tablespace online means it is in use or rather it is ready for use?
    I have seen this because if u bring the specified tablespace offline and then execute the query given it will result in
    COUNT(SEGMENT_NAME)
    -------------------
    0
    Now the statement seems to count the segments present in that TBlspace.
    My question is that I am having three tables in a particular tblspace then i make it offline so anybody working on it will be forced out after that I bring it online. But I make sure that nobody is working on that tblspace.
    In the above scenario can I say its in use or its ready for use?

    Kaushik

  5. #5
    Join Date
    Mar 2003
    Location
    SJ, Costa Rica
    Posts
    48
    Well, I think it means that it is there and exists, as the select statement count the number of the segmentes of the tablespace...
    But I'm not sure if it is active or somebody is using it...
    regards,
    -eduardo s.m.

  6. #6
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: How do I know if a tablespace is in use

    Originally posted by midget
    Hi everybody...
    We have a tablespace named "precise", I want to know if it is in use by any user, is there a simple way to check this out?
    Also I want to delete it if is not in use, how can I do it?
    regards,
    -eduardo s.m.
    Another way to find out that perticular tablespace (online) is being used by any user(s) or not is :

    Find the object(s) that are currently locked and residing into this tablespace. If you finf any such object(s) , meaning the tablespace is currently being Used.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Find the object(s) that are currently locked and residing into this
    >tablespace. If you finf any such object(s) , meaning the tablespace is
    >currently being Used.

    SELECT never locks any object!
    User might get upset if their query fails because the TS went missing.

    The only way to "know" if any of the objects in the tablespace is to
    enable AUDIT on them & then wait to see if they appear in SYS.AUD$.
    Of course keep in mind that I have some tables which are only used for year-end processing, so you might have to wait a long time to be sure.

  8. #8
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    That means we need to audit on all the tables residing on a specific tablespace and that has to be done for every table and for every tblspace in the DB to identify a tblspace being used at any point of time.
    Secondly if we have a table that span accross two tblspaces then how would u able to confirm which of the tblspace among the two is in use.
    Moreover while working with an index residing on a Index tblspace puts a lock on the associated table residing in some other tblspaces.
    Although 9i provides some mechanism of rebuilding indexes with minimal locking of table but still its a concern.

    It does not sound very convincing.

    Anyway thanks
    Kaushik
    Last edited by kausik_m123; 03-03-04 at 16:42.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >if we have a table that span accross two tblspaces
    HUH?
    Please demonstrate where a TABLE can "span accross two tblspaces".
    I contend this is impossible in Oracle.
    USER_TABLE has only one field for TABLESPACE_NAME!

  10. #10
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    I meant a table partition..
    I also mentioned about some indexes u have an answer for that.
    Moreover auditing feature has its own utility not to identify whether a tblspace is in use or not.
    Ur suggestion of auditing each and every table is completely absurd.
    Last edited by kausik_m123; 03-03-04 at 17:30.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I meant a table partition..
    That is not what you posted previously & I am not a mind reader.
    ==========================================
    >Moreover auditing feature has its own utility not to identify whether a tblspace is in use or not
    =============================================
    Please define "tblspace is in use or not".
    Inquiring minds would like to know the name of this utility.
    What is your solution to the OP's problem?

  12. #12
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    As I have already asked a question in my previous updates that whether a tablespace online means it is in use or ready to use or it is only used when any user writes to any object stored in that tblspace.
    If I had an answer to the question I would have simply posted the answer without getting into this debate.
    Regarding mind reading it was a Typing mistake and I don't expect u to be a mind reader , I only expect sensible oracle expertise from u.
    The purpose of auditing a table is to monitor the table so that we can identify any unwanted tampering.If u would like to know more about auditing go and get a good book this is not a place for teaching.
    I think our discussion is turning out bitter . This site is very helplful for all those oracle experts and all those who aspire to become an Oracle DBA.
    I don't want to fight to prove my technical skills . I have my respect for all those who love Oracle.

    Kaushik
    Last edited by kausik_m123; 03-03-04 at 18:31.

Posting Permissions

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